SSAS - How do I handle dimensions with valid duplicates?

Question

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
==============
Account Value
==============
   1     10
   2     20
   3     30

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?), 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
========================================
Level 0    Level 1    Level 2    Level 3
========================================
Group 0    Group 0    Group 0    Group 0
Group 0    Group 1    Group 1    Group 1    (empty group)
Group 0    Group 2    Group 2    Group 2
Group 0    Group 2    Account 1  Account 1
Group 0    Group 3    Group 3    Group 3
Group 0    Group 3    Account 2  Account 2
Group 0    Group 3    Account 3  Account 3
Group 0    Group 4    Group 4    Group 4
Group 0    Group 4    Group 5    Group 5
Group 0    Group 4    Group 5    Account 2
Group 0    Group 4    Group 5    Account 3
Group 6    Group 6    Group 6    Group 6    (empty group)

When navigating the dimension, this would result in something like:

======================================
Level 0  Level 1   Level 2   Level 3
======================================
Group 0
         Group 1
         Group 2
                  Account 1
         Group 3
                  Account 2
                  Account 3
         Group 4
                   Group 5
                             Account 2
                             Account 3
Group 6

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:

=================================================
Level 0  Level 1   Level 2   Level 3       Value
=================================================
Group 0                                     110
         Group 1                              0
         Group 2                             10
                  Account 1                  10
         Group 3                             50
                  Account 2                  20
                  Account 3                  30
         Group 4                             50
                   Group 5                   50
                             Account 2       20
                             Account 3       30
Group 6                                       0

The problem? When processing the dimension, SSAS complains that 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:

=================================================
Level 0  Level 1   Level 2   Level 3       Value
=================================================
Group 0                                      60
         Group 1                              0
         Group 2                             10
                  Account 1                  10
         Group 3                             50
                  Account 2                  20
                  Account 3                  30
         Group 4                              0
                   Group 5                    0
Group 6                                       0

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?

If a transaction occurs on Account 2, what determines whether that goes through group 3 or group 5? Or does it always go through both? — mmarie
It should always go through both groups in this case. And in the real dimension, if an account is updated, every occurrence of that account in the dimension should be updated. — STaRGaZeR

Andrew Peterson Andrew Peterson · Accepted Answer · 2017-12-12T16:53:15

On rare occasions, this can happen. Since the Hierarchy needs a unique solution, this is how you can approach the problem in the SSAS Dimensions Structure, if you have an integer based primary key on the dimension table, you can follow this approach.

1) for each attribute in your dimension structure, in the properties section, you have a defined KeyColumns. The default is the column itself. When you have duplicates, Expand the defined key for the attribute to include one or more columns: as follows:

Attribute -> Key Column

------------------- -------------------

MyTopLevelAttribute -> MyTopLevelAttribute

NextLevel1Attribute -> NextLevel1Attribute + uniquetablePK

If you do not have a unique key, you can still build out the lower level key columns using other columns that will provide a unique value, but you should get the approach. The key is to expand the KeyColumns attribute.

SSAS - How do I handle dimensions with valid duplicates?

2 Answers