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 :
week(WEEK_ENDING) as week
week("DAY") as Week,
geomakepoint( LATITUDE,LONGITUDE) as LOCATION
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
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.
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:
'Estimate' As Source
'Sales' As Source
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.
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.