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 123»»»

SSAS - How do I handle dimensions with valid duplicates? Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 3:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
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!
Post #1494976
Posted Monday, September 16, 2013 5:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1495021
Posted Monday, September 16, 2013 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
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.
Post #1495066
Posted Monday, September 16, 2013 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1495068
Posted Monday, September 16, 2013 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
How could I do that with only one dimension?
Post #1495174
Posted Monday, September 16, 2013 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1495195
Posted Tuesday, September 17, 2013 7:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
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.



Post #1495505
Posted Tuesday, September 17, 2013 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
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.
Post #1495536
Posted Tuesday, September 17, 2013 8:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
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,



Post #1495541
Posted Tuesday, September 17, 2013 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
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:

Post #1495553
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse