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

Transaction fact with different grain dimension hierarchy

2 posters

Go down

Transaction fact with different grain dimension hierarchy Empty Transaction fact with different grain dimension hierarchy

Post  sreenair 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

Back to top Go down

Transaction fact with different grain dimension hierarchy Empty Re: Transaction fact with different grain dimension hierarchy

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Transaction fact with different grain dimension hierarchy Empty Re: Transaction fact with different grain dimension hierarchy

Post  sreenair 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

Back to top Go down

Transaction fact with different grain dimension hierarchy Empty Re: Transaction fact with different grain dimension hierarchy

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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