Hierarchies in the customer dimension.

View previous topic View next topic Go down

Hierarchies in the customer dimension.

Post  JamisonWhite on Tue Oct 26, 2010 5:56 pm

I am having trouble deciding how to represent a hierarchy. I have a fixed-depth hierarchy of master reseller, resellers, customers, and services. Each of these is a 1:M relationship, so a service has a customer has a reseller has a master reseller. There is also a second hierarcy for sales person and sales manager where a customer has a sales person has a sales manager.

Our users want reports for customers and services based on the following:
a) customer type
b) reseller type
c) specific reseller

We have looked at 1) storing that relationship in the dimension, 2) adding all three dimensions to the facts, or 3) using a factless fact table.

#1 The DWTK book recommends embedding 1:M hierarchies in a dimension, but does it make sense for multiple dimensions to store the same hierarchy? Also we are using SCD2 on these columns, so changing a reseller's name will generate a large ripple effect on the customer and service dimensions.

#2 This is the centipede problem.

#3 I'm leaning towards this, but we would need to do a drill across query (or a view) anytime we needed a service's reseller or customer. Our dataset is small enough that performance will not be horrible. Are there any other concerns with this approach?

DimReseller
-ResellerCode
-ResellerName
-ResellerType
-MasterResellerCode
-MasterResellerName
-SalesPersonCode
-SalesPersonName
-SalesManagerCode
-SalesManagerName
DimCustomer
-CustomerCode
-CustomerName
-CustomerType
-ResellerCode
-ResellerName
-ResellerType
-MasterResellerCode
-MasterResellerName
-SalesPersonCode
-SalesPersonName
-SalesManagerCode
-SalesManagerName
DimService
-ServiceCode
-ServiceName
-ServiceType
-CustomerCode
-CustomerName
-CustomerType
-ResellerCode
-ResellerName
-ResellerType
-MasterResellerCode
-MasterResellerName
-SalesPersonCode
-SalesPersonName
-SalesManagerCode
-SalesManagerName

JamisonWhite

Posts : 2
Join date : 2010-07-09

View user profile

Back to top Go down

Hierarchies in the customer dimension

Post  alex.caminals on Wed Oct 27, 2010 9:39 am

Hi Jamison,

In my opinion, you should model the dimensions independenly. From a logical point of view it seems to me that you are in front of three indepedent dimensions. It's true that they have 1:M relationships but that happens sometimes and it shouldn't change your design model. I think that you discarded option #2 (adding all three dimensions to the facts) too quickly.

Please take a look at the following blog post. I think it will clarify your question.

http://www.clearpeaks.com/blog/data-warehousing/modeling-related-dimensions

Best regards,
avatar
alex.caminals

Posts : 15
Join date : 2009-02-25
Age : 41
Location : Barcelona (Spain)

View user profile

Back to top Go down

Re: Hierarchies in the customer dimension.

Post  JamisonWhite on Wed Oct 27, 2010 3:13 pm

Thanks Alex,

I think your advice plus #3 will get us where we need to be. The #3 solution will be an ownership fact.

FactCustomerOwnership
-----------------------
CustID
ResellerID
MasterResellerID
SalesPersonID
StartDate
EndDate


JamisonWhite

Posts : 2
Join date : 2010-07-09

View user profile

Back to top Go down

Hierarchies in the customer dimension.

Post  alex.caminals on Thu Oct 28, 2010 3:40 am

Hi Jamison,

Basically yes. With both stars you will have the coverage/ownership (factless fact table) and the actuals (transaction fact table).

Best regards,
avatar
alex.caminals

Posts : 15
Join date : 2009-02-25
Age : 41
Location : Barcelona (Spain)

View user profile

Back to top Go down

Re: Hierarchies in the customer dimension.

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