3

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 >.<

1

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.

4
  • (I'm starting to learn SSAS as well.) Should the phone number be an attribute of the user, or should it be snowflaked out into it's own dimension?
    – Jon Seigel
    Aug 15 '12 at 13:21
  • Doesn't need to be a dimension. You won't be slicing the cube by phone number, just showing the phone number of the particular user.
    – Rob Farley
    Aug 15 '12 at 23:26
  • Okay, you're saying that if you don't need to slice the cube by something, don't create a dimension for it. Is that correct? I'm curious about a general design practice (if there is such a thing) for this kind of situation.
    – Jon Seigel
    Aug 16 '12 at 1:01
  • Right. The user is the dimension. The phone number is just extra information about the user. So you don't even have a hierarchy on the phone numbeer attribute, and you access that through the phone number property. You'd never have a list of phone numbers you want to choose from...
    – Rob Farley
    Aug 16 '12 at 2:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.