SQL Analysis Services - Dimension attributes with a "many" cardinality

Question

I am creating a cube with the following tables:

Customer CustomerID, Name

Customer Rep CustomerID, RepID

Rep RepID, Name

The important thing here is that there is a many to many relationship between Reps and Customers. I want to be able to ask the question "How much sales for customers working with rep 'A'?" In the data source view i set up the relationships between both customerid columns and both repid columns. I set up the rep attribute in the dimension builder and when I try to build the cube I get this error:

Errors in the high-level relationship engine. the 'Rep' table that is required for a join cannot be reached based on the relationships in the data source view.

Answer 1

You don't show how sales is related to Customers/Reps, but I would guess that you have a CustomerID in the Sales fact table.

You need to setup Customer and Rep as 2 separate dimensions. The Customer Dimension would be joined directly to the Sales Fact.

To join the Rep dimension you would need to create an measure group from the "Customer Rep" table and then create a many-to-many relationship between Rep and Sales Fact, using the "Customer Rep" measure group.

There is a really good white paper on many-to-many relationships here