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

Dimension hierarchy and repeated attributes

3 posters

Go down

Dimension hierarchy and repeated attributes Empty Dimension hierarchy and repeated attributes

Post  Marco Thu Jan 14, 2016 9:41 pm

Which methods you prefer in following two cases

Q1: Consider the following hierarchy (Parent - Child): Company -> Department -> Project

Design1: Create "Flat dimensions" with parent attributes as mentioned here . Use these dimensions in Fact table (not parent dimension).
*We repeat parent attributes in all child dimensions in this design. Is this acceptable.

Design2: Create "Normalized dimensions" without parent attributes. Use parent and child dimensions in Fact tables.
*It need snowflaking to get parent details (not the measures) of child dimension.

Dimension hierarchy and repeated attributes Image111

Q2: Consider the following relation (One to One): Employee -- Department

Design1: Add Department attributes in Employee table (assume department table has around 30 attributes), Use employee key in Employee fact table.
*Shall we add details of more than one dimensions if they dont break the grain? (Details of Department, Company, Employee address, Company address etc)

Design2: Keep normalized Employee and Department dimensions, Use Employee and Department keys in Employee fact table.
*It may need snowflaking to get employee and department details (without measures).

Dimension hierarchy and repeated attributes Image210

Marco

Posts : 3
Join date : 2016-01-14

Back to top Go down

Dimension hierarchy and repeated attributes Empty Re: Dimension hierarchy and repeated attributes

Post  Marco Sun Jan 17, 2016 5:45 pm

Hi,
It would be helpful if someone share the benefits and drawbacks on both designs (Design 1 & 2).

Marco

Posts : 3
Join date : 2016-01-14

Back to top Go down

Dimension hierarchy and repeated attributes Empty Re: Dimension hierarchy and repeated attributes

Post  nick_white Mon Jan 18, 2016 8:38 am

I'd go with design 1 in both cases - it has fewer joins and so will be quicker to query and the hierarchical information is held in a single table.
Design 1 is a standard design pattern you'll see all over the place.
Design 2 is just adding complexity with no obvious benefit - you are building multiple paths to the same data.

nick_white

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

Back to top Go down

Dimension hierarchy and repeated attributes Empty Re: Dimension hierarchy and repeated attributes

Post  Marco Mon Jan 18, 2016 1:58 pm

Thank you Nick.
For Design 1, If any of parent's attribute is changed, then it will close all of its child records and create new records in child dimension. (If company name is changed, then it will close all projects under that company and create new records in Project dimension). Is this acceptable (if no of affected records in child table are significantly high)?
Shall I apply type1 cdc for parent attributes and type2 for child attributes to avoid massive change in child dimension? Or apply type2 for both parent and child and accept the change? Or any other solution?

Marco

Posts : 3
Join date : 2016-01-14

Back to top Go down

Dimension hierarchy and repeated attributes Empty Re: Dimension hierarchy and repeated attributes

Post  ngalemmo Mon Jan 18, 2016 3:26 pm

Option 1 & 2 will both work, but they may provide different information depending on the update processes. For example, if you have a project level fact, and the dimension row was updated to reference a different company, that fact would be reported under the newly changed company. If you have separate dimension FKs in the fact, the FK would reference the original company unless you update the fact. Updating fact tables can be a nuisance.

There are other approaches as well that allow for hierarchy structure changes without requiring fact updates.

You have to figure out how you want to deal with change and consider what are the business requirements are.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Dimension hierarchy and repeated attributes Empty Re: Dimension hierarchy and repeated attributes

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