Transaction fact with different grain dimension hierarchy
2 posters
Page 1 of 1
Transaction fact with different grain dimension hierarchy
I have a dimension with hierarchy - say Division and Sub Division. Usually we put both Division and Sub Division in one dimension or snowflake the Sub Division into separate table. But, in my case two fact tables are using this hierarchy with different grain. One fact table has grain on Division and another fact table has grain on Sub Division.
Right now, I have this schema, but I am directly connecting the snow flaked dimension. I think it is not a best practice, right? Should I remove the division_key from dim_sub_division and treat them as two separate dimensions.
dim_division (division_key, division, description)
dim_sub_division(sub_division_key, sub_division, description, division_key)
fact_1(customer_key, date_key, division_key, measure)
fact_2 (customerkey, date_key, division_key, sub_division_key, measure)
Right now, I have this schema, but I am directly connecting the snow flaked dimension. I think it is not a best practice, right? Should I remove the division_key from dim_sub_division and treat them as two separate dimensions.
dim_division (division_key, division, description)
dim_sub_division(sub_division_key, sub_division, description, division_key)
fact_1(customer_key, date_key, division_key, measure)
fact_2 (customerkey, date_key, division_key, sub_division_key, measure)
sreenair- Posts : 2
Join date : 2012-02-17
Re: Transaction fact with different grain dimension hierarchy
Keep the division information in your sub-division dimension and also create a Division dimension - you then join your fact tables to whichever Dimension is appropriate.
This is the same design pattern as dealing with Date/Week/Month/Year: your standard date Dimension has date/week/month/year attributes in it. But when you create aggregate fact tables at the week/month/year grain you also create aggregate "date" dimension tables for week/month/year - Kimball calls these "Shrunken rollup dimensions" I believe
This is the same design pattern as dealing with Date/Week/Month/Year: your standard date Dimension has date/week/month/year attributes in it. But when you create aggregate fact tables at the week/month/year grain you also create aggregate "date" dimension tables for week/month/year - Kimball calls these "Shrunken rollup dimensions" I believe
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Transaction fact with different grain dimension hierarchy
Thanks for the reply. I have seen rollup date dimensions. I have either uses dates in the fact tables itself or created one date dimension. Creating the rollup on weekly or monthly should have performance benefit depend on the situation.
sreenair- Posts : 2
Join date : 2012-02-17
Similar topics
» Linking two Fact tables with different grain through a hierarchy dimension
» Hierarchy and grain in a Dimension
» Tracking Support Tickets: accumulating snapshot and transaction grain fact (a presentation question)
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Accumulating Snapshot Fact with Dimension at Same Grain
» Hierarchy and grain in a Dimension
» Tracking Support Tickets: accumulating snapshot and transaction grain fact (a presentation question)
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Accumulating Snapshot Fact with Dimension at Same Grain
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum