Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question

1 Answer

up vote 1 down vote

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.

share|improve this answer
(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
Ah okay, I see. Thanks! – Jon Seigel Aug 16 '12 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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