SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSAS - How do I handle dimensions with valid duplicates?


SSAS - How do I handle dimensions with valid duplicates?

Author
Message
STaRGaZeR69
STaRGaZeR69
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 72
Remember the original fact table:

- Account 1: DatePK 20130901, AccountPK 1, Value 10
- Account 2: DatePK 20130901, AccountPK 2, Value 20
- Account 3: DatePK 20130901, AccountPK 3, Value 30

When slicing by date, the total for 20130901 would be 60. Now the new fact table in the case I described:

- Account 1: DatePK 20130901, AccountPK 1, Value 10
- Account 2 +: DatePK 20130901, AccountPK 21, Value 20
- Account 2 +: DatePK 20130901, AccountPK 22, Value 20
- Account 2 +: DatePK 20130901, AccountPK 23, Value 20
- Account 2 -: DatePK 20130901, AccountPK 24, Value -20
- Account 3: DatePK 20130901, AccountPK 3, Value 30

With this fact table, if we view the cube from the Groups-Accounts dimension, it would be somewhat correct (new names for the Accounts, but that's acceptable as they're intuitive). But if you slice by date, now the total would be 80.

I don't see this working if we modify the fact table, because that directly affects the other dimensions.
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 3495
- Account 2 +: DatePK 20130901, AccountPK 22, Value 20
- Account 2 +: DatePK 20130901, AccountPK 23, Value 20


Why doesn't this account have the same PK? This adds an extra 20, which would account for the difference between 60 and 80.



STaRGaZeR69
STaRGaZeR69
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 72
Because if you don't use separate keys, only one of the three occurrences of the positive account (21, 22 or 23, the one you choose) in the Groups-Accounts dimension would have a value. Remember each occurrence in the dimension has its own unique key. This is the problem of using the regular relationship.
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 3495
Sorry, but I'm not following that at all. As I've said, we might be at the limits of being able to help without actually seeing the item and there could still be something I'm missing. But I have never seen in an SCD1 dimension the same dimension name having multiple surrogate keys. I'm assuming it's an SCD1 as you haven't mentioned anything about the need to provide history. It this is a fact table keying issue, you will need to have the fact table produce its own otherwise meaningless key.



gene.furibondo
gene.furibondo
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 174
strangely, I think I am having the same issue. situation is with financial GL cube. There are accounts that roll up nicely into their income statement buckets. Then, there is a section called EBITDA, which includes many of those accounts again, or a second time. In order to put these in some sort of hierarchy in SSAS is brutal. I'm still not sure the best way to proceed.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search