Hierarchies in the customer dimension.

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

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,

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

Hi Jamison,

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

Best regards,