Many-to-Many-to-Many Relationship in SQL Server Analysis Services; Second Many-to-Many Relationship Not Working

Question

Preface

SQL Server 2008 R2 Standard Edition, Multidimensional Cube

In my data warehouse I have the following tables:

Dimensions

• DimPartnership - Groupings of Partners DimPartner - Groupings of
• Investors (can be in multiple partnerships) DimInvestor - Individual
• investors that can make up multiple partners

Facts

• FactInvestments - Records related to investment activity. Contains a foreign key "InvestorKey" that relates to the DimInvestor table.

Bridges

• BrInvestorPartner - Bridge table to resolve Investors to Partners
• BrPartnerPartnership - Bridge table to resolve Partners to Partnerships

Problem:

I need to create a Many-To-Many-To-Many relationship in SSAS. The first many-to-many dimension is working, the second one is not.

Current Solution:

I have made two bridge tables that link the Investor dimension to the partner dimension and then the partner dimension to the partnership dimension. The cube processes and, as expected, the partner many-to-many dimension works correctly. I am able to slice measures in the fact table by partner members. However, when I apply partnership as a part of the query, it has no effect on the Investments measure group. My Investments measures group is ignoring this dimension, it seems.

Question

Can anyone point out what I'm doing incorrectly? Is this even supported by Microsoft? I can't find anything in their documentation about this, but I would assume this would be supported. I appreciate any guidance toward figuring out what's wrong. Can this be solved with scoping or doing some sort of intersection on Partner Partnership Count?

Pictures

Some pics that might help you:

Faulty results

Values and names edited to protect client privacy - same value returned for all partnerships (the total of all investments)

DSV

Cube Structure

Dimension Usage

Answer 1

And of course once I posted my question, I figured out the problem.

My dimension usage for the Partnership Dimension should use both v Br Investor Partner bridge and the v Br Partner Partnership bridge with Many-To-Many relationships. Everything is now working as expected.

Compare this to the Dimension Usage Screenshot in my OP: