Concatenation of fact tables to avoid loops and synthetic keys

Probably, the most common situation that requires more complex data modeling is where we have more than one fact table—a table containing a number that we might use in a calculation—all of which have several common key fields linking to other dimension tables.

There are several ways to deal with this situation; by far the easiest way is to simply concatenate the fact tables together to form one large fact table. The result is often a typical star or snowflake schema.

Getting ready

Load the following script:

Store:
Load * Inline [
StoreID, StoreName
1, Store A
2, Store B
];

Calendar:
Load MonthID As DateID, Month Inline [
MonthID, Month
1, Jan
2, Feb
];

Product:
Load * Inline [
ProductID, Product
1, Product A
2, Product B
];

Sales:
LOAD * INLINE [
    DateID, StoreID, ProductID, SaleQty, SaleValue
    1, 1, 1, 2, 23
    1, 1, 2, 4, 24
    2, 1, 1, 4, 33
    2, 1, 2, 3, 28
    1, 2, 1, 2, 21
    1, 2, 2, 4, 30
    2, 2, 1, 3, 25...