title: SSAS - How do I handle dimensions with valid duplicates?
body: <p>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:</p>
  
  <pre><code>  Fact Table
  ==============
  Account Value
  ==============
     1     10
     2     20
     3     30
  </code></pre>
  
  <p>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 <code>HideMemberIf</code> 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:</p>
  
  <pre><code>          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)
  </code></pre>
  
  <p>When navigating the dimension, this would result in something like:</p>
  
  <pre><code>======================================
  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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>=================================================
  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
  </code></pre>
  
  <p>The problem? When processing the dimension, SSAS complains that <code>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'.</code>. 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:</p>
  
  <pre><code>=================================================
  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
  </code></pre>
  
  <p>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?</p>