My model consists of multiple facts at different grains sharing common dimensions. I've read the documentation and posts about the pro's and con's of concatenating vs. creating a link table. It all makes sense but nowhere do I see a viable modeling solution in dealing with facts at different grains sharing a common dimension.
I added a simple set of data below to illustrate.
1) If I loaded all of these into Qlikview it would create an unwanted loop and set one of the facts as loosly coupled.
2) IF the non-atomic FACT had transactions at the City grain I could model this in Qlikview by either concatinating the facts or creating a linked table. Or IF the only common dimension was LOCATION Qlikview wouldn't have a problem because there would be no loop issue. This I understand.
Has anybody out there ran into this problem of multiple facts at different grains sharing common dimensions? If so how to you solve in Qlikview? Any responses would be appreciated.
If I have understood you correctly, I would suggest the following:
You need to consolidate the facts into a single fact table. The problem and solution is the way you structure the dimensions to account for the differing granularity.
The single fact table will contain DateID, LocationID and Value.
The location fact should contain records as in your post for San Diego and New York, but to that add a record for City = NULL, Region = West and City = NULL and Region = East. The "non-atomic" facts can then be added to the fact table, with LocationID pointing to one of the two location records just created.
Hope that helps
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Guys thanks for the good ideas. I was able to create a working model based on the sample data I provided above. It works! The trick was what Jonathan suggested, forcing null values in the dimension and then creating a multi-valued key to tie to FACT. I've attached my simple working example. Thanks again - David