2 Replies Latest reply: Dec 6, 2017 10:59 AM by Marcus Sommer RSS

    Connecting data sources with missing entries in fact table

    Florian Pfaffl

      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?

       

      Thanks in advance.