Two level keys from Hierarchhy Dimension into Fact table

View previous topic View next topic Go down

Two level keys from Hierarchhy Dimension into Fact table

Post  jhoomjp on 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

View user profile

Back to top Go down

Re: Two level keys from Hierarchhy Dimension into Fact table

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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