One dimension different hierarchies

View previous topic View next topic Go down

One dimension different hierarchies

Post  Intelligy25 on Wed Oct 27, 2010 11:21 pm

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

Intelligy25

Posts : 2
Join date : 2010-10-27

View user profile

Back to top Go down

Re: One dimension different hierarchies

Post  hang on Thu Oct 28, 2010 1:15 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: One dimension different hierarchies

Post  Jeff Smith on Thu Oct 28, 2010 9:46 am

It's OK to have multiple hierarchies in a single dimension table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: One dimension different hierarchies

Post  Intelligy25 on Thu Oct 28, 2010 1:57 pm

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

View user profile

Back to top Go down

Re: One dimension different hierarchies

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