Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

1 Dimension 1 Fact table connected by 2 Keys Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 6:52 AM
Points: 5, Visits: 94
Hi, I hope all is well.

I'm having a dilemma in my cube setup and i hope you guys can help.

I have 1 cube dimension created from a UNION of 2 Dimension tables leaving me with duplicate keys on 1 column. To get around this, in my DSV I connect this dimension to the FACT table with 2 Relationships. Seen below:



Somehow this relationship is not being followed when I view the cube.

have you guys encountered this?
Post #1469880
Posted Wednesday, July 3, 2013 3:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 13,043, Visits: 10,809
Two relationships are two seperate relationships (like role playing dimensions); it's not the same as one relationship with a compound key of 2 columns.

I think it would be more ideal to create a new surrogate key in your UNION ALL statement (you could use ROWNUMBER()) and create a relationship using that new surrogate key.

If you use ROWNUMBER(), you better do a full process each time, as the keys might change.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1469955
Posted Wednesday, July 3, 2013 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 6:52 AM
Points: 5, Visits: 94
Hi Koen!
Thanks for your response!

I did try your suggestion and it worked! I used a concatination of the 2 keys and some other character to make sure every key is unique and also did the same on my Fact table.

Thanks again for your help.
Post #1470046
Posted Thursday, July 4, 2013 6:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 13,043, Visits: 10,809
michaeleusebio721 (7/3/2013)
Hi Koen!
Thanks for your response!

I did try your suggestion and it worked! I used a concatination of the 2 keys and some other character to make sure every key is unique and also did the same on my Fact table.

Thanks again for your help.


No problem, glad to help and thanks for posting back!




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1470432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse