4 Replies Latest reply: Jul 6, 2009 3:45 PM by Marc Donckers RSS

    How to deal with multiple fact tables

    Marc Donckers

      I am new to Qlikview and running into what I guess is a quite common issue. I have searched the forums, but so far I haven't really figure out how to solve my issue. Here it is:

      I have two fact tables:

      • Table ordercosts: Orderline, ProductItem, CostID, Orderprocessingcosts
      • Table productioncosts: ProductItem, CostID, Productioncosts

      I use the CostID field to be able to analyse different types of costs included in the cost figures (I.e. labor costs, depreciation, energy costs, etc.). Both tables contain about 50.000 lines.

      What should I do to prevent circular references? I could probably concatenate the two tables, but than transparency of the underlying business activities (order processing and production) is completely lost. Is there a more elegant way?

      Thanks for helping me out.

       

       

       

       

        • How to deal with multiple fact tables
          Rakesh Mehta

          Best way to create a Link Table. What you can do is create a concatenated key with ProductItem, CostID and generate a link table.

          Your tables would have:

          1. ordercosts: Orderline, LinkTabKey, Orderprocessingcosts

          2. productioncosts: LinkTabKey, Productioncosts

          3. LinkTab: LinkTabKey, ProductItem, CostID

          Try first and if need further help, just post a sample data file here.

          • How to deal with multiple fact tables

            You can keep join the two tables on the productitem field and rename CosID in the first table to OrderCostId and CostId of the second table as ProductionCostId.

            That will keep the two cost types separated but will avoid the circular reference.

            Ex:

            TableCost:

            load OrderLine,

            ProductItem,

            CostId as OrderCostId,

            OrderProcessingCosts

            from odercosts;

            keep join(TableCost)

            load ProductItem,

            CostId as ProductionCostID,

            ProductionCosts

            from productioncosts

             

            Hope that helps.

             

             

            • How to deal with multiple fact tables
              Michael Solomovich

              Marc,
              You certainly can follow suggestions from Rakesh and Philippe, but I don't see anything wrong with concatenation either. If you wish, you can add flags for processing and production, or keep dupicated IDs. Actually in the latter case it will be pretty much the Philippe's version.