Dimension hierarchy and repeated attributes

View previous topic View next topic Go down

Dimension hierarchy and repeated attributes

Post  Marco on 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.



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).


Marco

Posts : 3
Join date : 2016-01-14

View user profile

Back to top Go down

Re: Dimension hierarchy and repeated attributes

Post  Marco on 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

View user profile

Back to top Go down

Re: Dimension hierarchy and repeated attributes

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

View user profile

Back to top Go down

Re: Dimension hierarchy and repeated attributes

Post  Marco on 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

View user profile

Back to top Go down

Re: Dimension hierarchy and repeated attributes

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension hierarchy and repeated attributes

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