7 Replies Latest reply: Feb 5, 2016 5:09 AM by Sangram Reddy RSS

    One location dimension table with 2 fact tables?

      Hi,

      I am new to data modelling and Qlik. I have got to grips with how to work with it but am struggling to get data i have been given into the right model.

       

      I have some data that comprises 2  simple fact tables (Sales , Estimated Demand). Both of these tables contains a Product_Id, Store_id and then a value.  The store_id is the link to a location dimension table for 4 stores A,B,C and D - each of which has a lat, long.

      Small example for data/ columns names is:

       

      Screenshot 2016-02-02 00.28.30.png

       

      I want to load the data into a model so that both the sales and demand data is linked on the store_id.

      ie. So that i can have a sheet with charts for both demand and supply such that I can select store_id and have the graphs for demand and sales both update for the selected stores - and subsequently also show the selected store locations on map.

       

      However, I'm struggling to understand the right model to do this.

      It would seem to mean that I should have BOTH the demand and sales fact tables linked to the store_location dimension table which I understand is not a correct thing to do.

       

      Any suggestions would be very gratefully received.

       

      TIA, Mike.

        • Re: One location dimension table with 2 fact tables?
          bruno bertels

          Hi Mike

           

          By my opinion i would let your table like that with 3 facts tables : sales , demand and location

           

          I see that you have a week ending dimension for demand and day sales dimension in the sales table. I will add a Week dimension in both table so that you can merge sales and demand by store ID by week.

           

          For demand :

           

          demand:

          Load

          week(WEEK_ENDING) as week

          PRODUCT_ID,

          STORE_ID,

          ESTIMATED_DEMAND

           

          from ...

           

          sales:

          Load

          week("DAY") as Week,

          PRODUCT_ID,

          STORE_ID,

          TOTAL_SALES

           

          from .....

           

          location :

          load

          STORE_ID,

          STORE_DESCRIPTION,

          geomakepoint( LATITUDE,LONGITUDE) as LOCATION

           

          from ....

           

          So that your 3 tables will be merge by STORE_ID with a synthetic key on Week dimension and STORE_ID dimension.

           

           

          Then in your charts or tables just ADD Week as time dimention , STORE_ID, PRODUCT_ID and as mesures

          sum(ESTIMATED_DEMAND)

          sum(TOTAL_SALES)

           

          and so on

           

          Hope it helps

           

          PS if you really want only one FACT table , in that case you should join your tables with a left join statement , this will avoid synthetic key.

           

          Bruno

          • Re: One location dimension table with 2 fact tables?
            Jonathan Dienst

            I suggest that you load the two fact sets into a single fact table and load the store dimension as-is. This will greatly simplify the anlysis.

             

            Load each fact table with a Source flag (name it whatever you like) and concatenate the second table onto the first. Something like:

             

                 Fact:

                 LOAD *,

                      'Estimate' As Source

                 FROM ESTIMATED_DEMAND;

             

                 Concatenate

                 LOAD *,

                      'Sales' As Source

                 FROM SALES;

            • Re: One location dimension table with 2 fact tables?
              Sangram Reddy

              Hi Mike,

               

              You can achieve this in 2 ways.

               

              1) Concatenating both the fact tables into one with a flag field.

              Doing this you will face not issues with the location table as it will directly link to the store id field. This is suggested only in the case where the number of records in the fact tables are less.

               

              2)Renaming the all the fields apart from the storeid field. So this will not form any circular referances or synthetic keys.

              So you will have a data model with 3 tables linked on storeid.

               

              Thanks,

              Sangram.

              • Re: One location dimension table with 2 fact tables?

                Bruno, Jonathan, Sangram,

                 

                Thank you very much for very clear and informative answers! They all make very good sense and I will experiment further today will all the solutions.

                 

                Thank you again, really do appreciate it and will return to update.

                 

                Regards, Mike

                • Re: One location dimension table with 2 fact tables?

                  Hi,

                   

                  Have marked Bruno's answer as correct as it was first and covered both scenarios of how to keep 2 tables or merge to one plus also the conversion to week.

                   

                  However, both Jonathan and Sangram's answers were also correct and also added some extra information and tips which I also found useful so have marked both these as helpful. Its a shame only 1 answer can be marked as "correct"!

                   

                  Thanks again for the help guys.

                   

                  Regards, Mike