Help on designing SSAS Dim and Fact table I am having a problem designing a Dim and Fact Table for the following problem.

I have a Pivot Grid with data as below :


USER | RELATED USER | RELATIONSHIP | PHONE NUMBER
--------------------------------------------------
Alex | Alex         | Self         | 123456 
     | Alice        | Spouse       | 123456
--------------------------------------------------
Alice| Alice        | Self         | 654321 
     | Alex         | Spouse       | 654321


If i try to remove the Related User Dimension, i am getting:


USER | RELATIONSHIP | PHONE NUMBER
-----------------------------------
Alex | Self         | 123456 
     | Spouse       | 123456
-----------------------------------
Alice| Self         | 654321 
     | Spouse       | 654321


Anyone can help me how to design the table for this SSAS Cube, so that it will show as?


USER | RELATED USER | RELATIONSHIP | PHONE NUMBER
--------------------------------------------------
Alex | Alex         | Self         | 123456
       Alice        | Spouse       | 654321
--------------------------------------------------
Alice| Alice        | Self         | 654321 
     | Alex         | Spouse       | 123456


and


USER | RELATIONSHIP | PHONE NUMBER
----------------------------------
Alex | Self         | 123456 
Alice| Self         | 654321


Thanks >.&lt;
You want your Users table to be just the Self records, as a dimension table. Phone number should be a column here. Then you want your Relationships table to be your Fact table, hooked into the Users Dimension table twice, once through Original User and once through Related User.

For your main report, pull the Phone number from the Related User dimension, not the Original User dimension.