Chaining Many-To-Many Dimensional Relationships in SSAS

Question

I'm developing a cube in SSAS and attempting to model the following relationships:

• Many Facts to 1 Customer
• Many Customers to Many Sales Reps
• Many Sales Reps (Subordinates) to Sales Reps (Managers)
• Each M2M relationship is facilitated by a bridge table which also acts as a fact table in the cube

I have most of this working. I can slice Facts by Customer and by Sales Rep (Subordinate), but when I add Sales Rep (Manager) to the query it appears to return every subordinate/manager combination regardless of whether or not that relationship exists in the bridge table.

Any ideas as to what I might be doing wrong?

Answer 1

Some of my dimensions were missing relationships to the intermediate measure groups.

http://msdn.microsoft.com/en-us/library/ms345139(v=sql.90).aspx