Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Two level keys from Hierarchhy Dimension into Fact table

2 posters

Go down

Two level keys from Hierarchhy Dimension into Fact table Empty Two level keys from Hierarchhy Dimension into Fact table

Post  jhoomjp Sat Apr 18, 2015 8:57 pm

Hi there

I have scenario where I am inclining towards putting two keys from hierarchy into fact table. To explain more lets say below are three tables in question:

DimDate: Hierarchy based Dimension: Year->Semester->Quarter->Month
FactOrder: Grain of data coming from source is Month
FactForecast: Grain of data coming from source is Quarter

Business users have primary scenario of reporting data from Order and Forecast at Quarter level.

Question: Is it really bad idea to put Quarter as well into FactOrder along with Month. What are disadvantages here? I understand that only lowest grain should be stamped in facts but this can help when we do reporting as we can easily see data at same level instead of always rolling up in FactOrder in reports (like cube or ssrs reports)

Thanks
Jay

jhoomjp

Posts : 1
Join date : 2015-04-18

Back to top Go down

Two level keys from Hierarchhy Dimension into Fact table Empty Re: Two level keys from Hierarchhy Dimension into Fact table

Post  ngalemmo Sun Apr 19, 2015 10:04 am

It is customary to key at the lowest appropriate level and let the hierarchy do the work. The primary reason for this is it simplifies the query pattern, one pattern to report at any level of the hierarchy. Having the extra key now gives you two query patterns to do essentially the same thing.

You will probably find there is no significant performance advantage with the extra key.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum