One dimension different hierarchies
3 posters
Page 1 of 1
One dimension different hierarchies
I have a customer Dim with attributes like
CustomerID
CustomerName
CustomerAddress
CustomerGroup
CustomerIndustry
There are two level rollup that each Customer can rollup in hierarchy : 1) One we call normal parent-child. Let call H1 2) again parent-child but it is custom where business creates different parent and rollup different children to it H2. The measure is fee attributed to Customer level.
For ex: Customer1 - ID:1 , Name: AAA Auto Insurance, Address:xxx, Group: House, Industry: Auto Insurance
Customer 2 - Id:2 , Name: AAA Travel, Address:yyy, Group : Board, Industry: Travel
Customer 3 - Id:3 , Name AAA Health Insurance, Address:zzz, Group : House, Industry: Health Insurance
1)
By One Hierarchy all above 3 customers belong to Parent CustomerParent with its own attributes: Id:4, Name:AAA, Address: xyz, Group:Company, Industry: Insurance
2) By user defined hierarchy Customer1 and Customer 3 belong to parent called relationship with its attributed :5, Name:AAA &co, Group:Co, Industry: Insurance
and Customer2 belong to different parent
Business wants the ability to see both the hierarchies.Based on what they choose the sum up of revenue at parent will differ because of grouping. Since Industry of Customer has its own hierarchy with Sector etc and group with its attributes thought of moving them to seperate dim . Then tie back to Fee Fact. But with 2 different hierarchies on same dimension making it difficult to have different Industry dimension & Group dimension for Child. As business should be able to see Parent Industry and group.
Fee the measure is at the Customer level. But business needs to see aggregate of fees at parent level regardless of which hierarchy they choose. Also they need to measure revenue by Industry and Group
1) Can you please advise if it is good idea to have seperate dimension for Industry and Group. If yes, then how to relate to corresponding parent Industry and group.
2) Is it better to flatten the dimension if there are 2 different type hierarchies on same Customer?
3) What is the best way to implement the above
CustomerID
CustomerName
CustomerAddress
CustomerGroup
CustomerIndustry
There are two level rollup that each Customer can rollup in hierarchy : 1) One we call normal parent-child. Let call H1 2) again parent-child but it is custom where business creates different parent and rollup different children to it H2. The measure is fee attributed to Customer level.
For ex: Customer1 - ID:1 , Name: AAA Auto Insurance, Address:xxx, Group: House, Industry: Auto Insurance
Customer 2 - Id:2 , Name: AAA Travel, Address:yyy, Group : Board, Industry: Travel
Customer 3 - Id:3 , Name AAA Health Insurance, Address:zzz, Group : House, Industry: Health Insurance
1)
By One Hierarchy all above 3 customers belong to Parent CustomerParent with its own attributes: Id:4, Name:AAA, Address: xyz, Group:Company, Industry: Insurance
2) By user defined hierarchy Customer1 and Customer 3 belong to parent called relationship with its attributed :5, Name:AAA &co, Group:Co, Industry: Insurance
and Customer2 belong to different parent
Business wants the ability to see both the hierarchies.Based on what they choose the sum up of revenue at parent will differ because of grouping. Since Industry of Customer has its own hierarchy with Sector etc and group with its attributes thought of moving them to seperate dim . Then tie back to Fee Fact. But with 2 different hierarchies on same dimension making it difficult to have different Industry dimension & Group dimension for Child. As business should be able to see Parent Industry and group.
Fee the measure is at the Customer level. But business needs to see aggregate of fees at parent level regardless of which hierarchy they choose. Also they need to measure revenue by Industry and Group
1) Can you please advise if it is good idea to have seperate dimension for Industry and Group. If yes, then how to relate to corresponding parent Industry and group.
2) Is it better to flatten the dimension if there are 2 different type hierarchies on same Customer?
3) What is the best way to implement the above
Intelligy25- Posts : 2
Join date : 2010-10-27
Re: One dimension different hierarchies
I would suggest a flattened customer dimension with all the necessary grouping attributes as individual columns in one table. Let ETL parse any composite attributes and break them up into distinctive attributes. Then the measures in your fact will correctly roll up to any attribute that business regards as a grouping attribute above the customer.
In this model, the hierarchies are naturally formed by the data in the customer dimension instead of predefined relational tables. It simplifies type 2 SCD process, and is easier for users’ consumption as users don’t need to understand the snowflaked ER to query against hierarchies.
In this model, the hierarchies are naturally formed by the data in the customer dimension instead of predefined relational tables. It simplifies type 2 SCD process, and is easier for users’ consumption as users don’t need to understand the snowflaked ER to query against hierarchies.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: One dimension different hierarchies
It's OK to have multiple hierarchies in a single dimension table.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: One dimension different hierarchies
Thanks for the response. I am going with Hang's approach of flattening the Customer Dim to handle SCD2 in future.
Intelligy25- Posts : 2
Join date : 2010-10-27
Similar topics
» Dimension with two hierarchies
» Hierarchies in the customer dimension.
» Purpose of Hierarchies in a Dimension
» How to store multiple hierarchies within a dimension
» Location Dimension(s) with multiple hierarchies
» Hierarchies in the customer dimension.
» Purpose of Hierarchies in a Dimension
» How to store multiple hierarchies within a dimension
» Location Dimension(s) with multiple hierarchies
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum