Implementing many-to-many relationships within a dimension can be tricky. We'd like all dimensions to flow nicely from one to many all the way down the hierarchy chain. However, occasionally there is a dimension that wants to defy this convention. Unfortunately, the dimensional model requires that our dimension relationships be one-to-many (or one-to-one).
Consider salespersons who have a varying number of product certifications and you want to track sales by salespersons and also see if you can track a relationship between certifications and sales. So, the requirement is to show all sales for salespersons with certain certifications.
You can force this dimension into a hierarchy by grouping all possible combinations of product certifications into a product certification group level. The hierarchy then becomes product certification -- product certification group -- salesperson -- sales (fact). To see all sales by those who were certified in a given product, simply start at the top of the hierarchy and drill down. Keep in mind that, since product certifications are in multiple certification groups, that sales are non-additive from a product certification perspective.
This approach becomes somewhat unmanageable if you have a large set of products and/or certifications change frequently.
Another way to tackle this is to track salesperson and product certification as separate dimensions, but that forces you to attach a certification
Requires Free Membership to View
When you register, you'll begin receiving targeted emails from my team of award-winning editorial writers on the latest customer relationship management (CRM)and call center technology issues today. Our goal is to keep you informed on the hottest issues facing this fast-changing industry.
Hannah Smalltree, Editorial Director
to the sale instead of to the employee,
which is the objective. You can also use product certification group as the dimension. This
approach also disallows a relationship between salesperson and product certification group except
at the sale level.
On balance, grouping the product certifications and including that in the hierarchy gives the users what they want and that's what our modeling is all about.
For more information, check out searchCRM's Best Web Links on Data Warehousing.
This was first published in January 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation