Transaction fact with different grain dimension hierarchy

View previous topic View next topic Go down

Transaction fact with different grain dimension hierarchy

Post  sreenair on Tue Jul 22, 2014 5:55 pm

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)

sreenair

Posts : 2
Join date : 2012-02-17

View user profile

Back to top Go down

Re: Transaction fact with different grain dimension hierarchy

Post  nick_white on Wed Jul 23, 2014 5:53 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Transaction fact with different grain dimension hierarchy

Post  sreenair on Sun Jul 27, 2014 6:35 pm

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

View user profile

Back to top Go down

Re: Transaction fact with different grain dimension hierarchy

Post  Sponsored content


Sponsored content


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