Two level keys from Hierarchhy Dimension into Fact table
2 posters
Page 1 of 1
Two level keys from Hierarchhy Dimension into Fact table
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
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
Re: Two level keys from Hierarchhy Dimension into Fact table
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.
You will probably find there is no significant performance advantage with the extra key.
Similar topics
» Surrogate keys in dimension and fact table
» Header Level Dimension for a Fact Table
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» Business keys or Natural keys in the Fact table
» Header Level Dimension for a Fact Table
» Allocating Header Level FACT table Dimensions FKs into Line Level Fact
» Business keys or Natural keys in the Fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum