Connecting data sources with missing entries in fact table
Hello community,
I have several tables and my maintable is a classical fact-table with the sales for several fields which contain the following:
Date (timestamp) | Date (Month) | store | Item_ID&Item_Color | Item_Size | Sales Volume ... and many more
Of course there is not a sale in every store for every item/color/size combination per month - and this is where my problem starts.
I want to link another table that contains the monthly stock per item/color/size for every store - all combinations are filled here:
Date (Month) | store | Item_ID&Item_Color | Item_Size | Available Stock
I have to link the tables over a selfmade key, because otherwise I get a huuuuuge synthetic Key table.
Fact: Month&'_'&Store&'_'&Item_ID&Item_Color&'_'&Item_Size - some combinations are missing (no sales there)
Stock: Month&'_'&Store&'_'&Item_ID&Item_Color&'_'&Item_Size - all combinations are available, because everyone is filled with a stock-quantity or 0.
Because of the fact table having missing combinations (half or more) my sum of the available stock in the combined data-model is only half or less as if I load the stock.qvd in a single dashboard.
Is there any way to keep and connect all Available Stock values eventhough they sometimes have no counterpart in the fact table?
