Multiple Facts/Multiple Grains Design Challenge

[#1]

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.

Shared Dimension - LOCATION

City	Region
San Diego	West
Los Angeles	West
Washington D.C.	East
New York	East

Shared Dimension - CALENDAR

Date	Month
1-1-10	Jan
1-2-10	Jan
1-3-10	Jan
1-4-10	Jan

Atomic Fact

Date	City	Value
1-1-10	San Diego	10
1-2-10	New York	50

Non-Atomic Fact

Date	Region	Value
1-1-10	West	200
1-4-10	East	300

[#2]

Hi

you can just rename the field names as necessary... Or you can use qualify

[#3]

See the attached example..

[jonathandienst]

Hi

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

Jonathan

[#5]

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

Before:

After: