One location dimension table with 2 fact tables?

[#1]

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:

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.

[brunobertels · Accepted solution]

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

[jonathandienst]

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;

[reddy-s]

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.

[#5]

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

[#6]

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

[oknotsen]

As many forget about the Helpful flag, I think they will appreciate it .

[reddy-s]

Glad it helped!