Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Multiple Facts/Multiple Grains Design Challenge

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



Tags (2)
4 Replies
Not applicable

Multiple Facts/Multiple Grains Design Challenge

Hi

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

Not applicable

Multiple Facts/Multiple Grains Design Challenge

See the attached example..

MVP
MVP

Multiple Facts/Multiple Grains Design Challenge

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Multiple Facts/Multiple Grains Design Challenge

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: