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
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 72
I'm new to SSAS and I got a problem I'm not able to resolve. Let's start with two tables, this one being a fact table with accounts and their values:

Fact Table:



Then there is the account hierarchy table that feeds a dimension. This dimension contains the accounts hierarchy. Accounts are grouped into Groups, and a given account can be placed as many times as wanted inside several different groups. The case of having the same account two or more times inside the same Group is not possible. For performance reasons, the hierarchy is naturalized (is this the right term?), and then handled using the HideMemberIf property. Groups have no values by themselves, they take their value from the agreggation of their children. The real hierarchy has 16 levels, but for simplicity here's an example with only 4 levels:

Dim Groups-Accounts:



The goal is to relate the last level of the naturalized hierarchy, Level 3 in this case, with the appropiate value in the fact table:



The problem? When processing the dimension, SSAS complains:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'CT_DimGroupsAccounts', Column: 'Level_3', Value: 'Account 2'. The attribute is 'Level_3'.

This is true, as Account 2 and Account 3 are duplicated in the Level 3 attribute. If I tell SSAS to ignore Duplicate key errors then processing ends successfully. However, when navigating the resulting cube, the result is:



The duplicated keys (Accounts 2 and 3 as children of Group 5) are not in the dimension, and the result is incorrect. How can I achieve the correct result given this scenario?

Cheers!
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 13298
Why don't you use the ID field of the groups-account dimension as a surrogate key in your fact table?


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

MCSE Business Intelligence - Microsoft Data Platform MVP
STaRGaZeR69
STaRGaZeR69
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 72
You mean this?:



And then join DimID with the dimension ID? I already considered doing this, but it's not possible. In the real fact table there are more foreign keys, the most typical being Date. As you can see Account 2 and Account 3 values are duplicated, so when filtering by date in the cube I get wrong values.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 13298
OK, then your problem looks a lot like a many-2-many issue.
So you could try to model your dimension as a many2many dimension in SSAS.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
STaRGaZeR69
STaRGaZeR69
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 72
How could I do that with only one dimension?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 13298
You probably will need to change your design in the database.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
RonKyle
RonKyle
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7102 Visits: 3623
And then join DimID with the dimension ID? I already considered doing this, but it's not possible. In the real fact table there are more foreign keys, the most typical being Date. As you can see Account 2 and Account 3 values are duplicated, so when filtering by date in the cube I get wrong values.


Sorry, but I'm not following this. If each of the dimension entries has its own ID, which it should, this shouldn't be an issue. The repetition of keys occurs with SCD 2 dimensions. This doesn't look like one of those, but you should be able to keep in separate, just like names for counties and cities are kept separate.

I'm not ruling out a many-to-many issue as the previous poster has indicated, but I'm not convinced that that must be the issue. Please provide more details if you'd like more help.



STaRGaZeR69
STaRGaZeR69
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 72
As I said, the fact table has more foreign keys, I didn't expose them in my original post for simplicity. Indeed, if there were no other dimensions Koen's solution would be ideal as it becomes a one-to-one relationship. However, when we consider the real scenario:



Then if we apply Koen's solution it becomes like this:



In the second case, if we filter by the Groups-Accounts dimension only, everything would be correct. However, if we filter by date (20130901) then we'll have a Value of 110. This is incorrect, if you look at the original fact table the total for 20130901 should be 60.

To my understanding this is a one-to-many relationship (a single account with a single value can appear many times in the Groups-Accounts dimension), but I'm not sure if: a) this can be handled as such in SSAS without issues and performance penalties, and b) it's possible to convert this to a regular relationship to keep a star schema, which would be the ideal solution.
RonKyle
RonKyle
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7102 Visits: 3623
To my understanding this is a one-to-many relationship (a single account with a single value can appear many times in the Groups-Accounts dimension), but I'm not sure if: a) this can be handled as such in SSAS without issues and performance penalties, and b) it's possible to convert this to a regular relationship to keep a star schema, which would be the ideal solution.


The answer to these is yes. There can be a performance hit if the names can't be designated as unique, but it's normally not signficant. My location has a lot of repeated city and the county names which repeat in the various states. Despite having one entry per zip code, there is no human noticeable delay retrieving the data.

I need to see all the relevant columns in the dimension table. You show them in a heirarchy. That means there should be at least three columns in the dimension: surrogate ID, group, account. The latter two could even have their own key, which I would find ideal.

Thanks,



STaRGaZeR69
STaRGaZeR69
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 72
The dimension table is what I showed, there is only one flatted hierarchy, the Groups-Accounts hierarchy:



The Parent-Child table from where this originates would be like this:


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