none
How to prevent duplicate attributes

    Question

  • I'm re-learning SSAS after a 2 yr layoff of only doing the occasional prototype.  One thing that has me confused is how to prevent the following: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_dimXXXX', Column: 'XXXXName', Value: SomeValue'. The attribute is 'XXXXName'.

     

    For some reason I thought that only the dimension key had to be unique (hence using an arbitrary surrogate key), but it looks like each member in a hierarchy has to be unique also.

     

    Take the following example of a dimension called SomeDim

     

    All (Level1)

    A (Level2)

    1 (Level3)

    a (Level4)

    b (Level4)

    2 (Level3)

    c (Level4)

    d (Level4)

    B (Level2)

    1 (Level3)

    e (Level4)

    f (Level4)

    4 (Level3)

    g (Level4)

    h (Level4)

     

    This will appear to generate an error when processing the 3rd level in the hierarchy because there are two 1's, one of which is a member of A and another of which is a member of B.  The way I am getting around this is by setting the Level3 values 1, 2, 4, etc... (in this hypothetical example) as the NameColumn and creating a Named Calculation called (hypothetical) Level3KeyName = Level2 + Level3 (so I get A1, A2, B1, and B4) and setting that as the KeyColumn for Level3 to create uniqueness.  Is this the correct way to handle this?  Or am I missing something?

     

    Tuesday, February 12, 2008 7:05 PM

Answers

  • ".. To sum it up, if your attributes are in a hierarchy, then the keycolumn for the attribute must be unique .." - I would put it this way: if your attributes form a natural hierachy, then a higher-level attribute relates to (can be determined from) the preceding lower-level attribute. In that case, a key value from the lower-level attribute (which identifies the member) must not occur under multiple members of the higher-level attribute, because then the higher-level attribute doesn't really relate to (ie. can't be determined from) the lower-level attribute.

     

    Of course, you could still create an unnatural hierarchy where this doesn't have to hold (as in the blog entry above). But you may want to set up some natural hierarchies, as discussed here:

     

    What are the natural hierarchies and why they are a good thing

    Thursday, February 14, 2008 10:39 PM
    Moderator

All replies

  • In the properties window of a given attribute you can set the key relationships.  Choose Key Columns and add attributes then go to the 'Source' and add column binding attribs to build a multi-level key.

     

    (It's easier to do than explain.)

     

    Tuesday, February 12, 2008 8:51 PM
  • I appreciate the response but it doesn't answer my question.  I know how to set the key, name, and value bindings.  The question concerns the concept of uniqueness of members in the hierarchy.

     

    Thanks,

    Lee

    Thursday, February 14, 2008 7:56 PM
  • I've always used unique member names.

     

    Thursday, February 14, 2008 8:48 PM
  • Keep in mind that AS 2005 dimensions (unlike AS 2000) are not hierarchy-based, but rather attribute-based. I think your example is similar to the Date dimension in Adventure Works - the same month (say January) can occur under multiple years. So there are 2 separate attributes - Month Name (which includes both month number and year for keys, as John had suggested), and Month Of Year (which only has month number as a key). They differ in terms of attribute relationships, so both the natural hierarchies (Calendar and Fiscal) use Month Name. But a user hierarchy could be created with Month Of Year instead of Month Name (see the "Year By Months" hierarchy in this blog entry), though that might not perform as well.

    Thursday, February 14, 2008 9:35 PM
    Moderator
  • Thank you very much!  I think this might answer my question, and yes, my experience was more with 2000 than 2005 (and actually using 2008 CTP now).  Your date example is exactly the type of situation I am encountering.  To sum it up, if your attributes are in a hierarchy, then the keycolumn for the attribute must be unique (which is different than how 2000 worked).  So it sounds like I am taking the correct path by creating keys for all the attributes that I will use in my hierarchies.  Does that sound correct?  If so, I'll set the above to be the answer.

     

    Thanks again!

     

    Lee

    Thursday, February 14, 2008 9:52 PM
  • ".. To sum it up, if your attributes are in a hierarchy, then the keycolumn for the attribute must be unique .." - I would put it this way: if your attributes form a natural hierachy, then a higher-level attribute relates to (can be determined from) the preceding lower-level attribute. In that case, a key value from the lower-level attribute (which identifies the member) must not occur under multiple members of the higher-level attribute, because then the higher-level attribute doesn't really relate to (ie. can't be determined from) the lower-level attribute.

     

    Of course, you could still create an unnatural hierarchy where this doesn't have to hold (as in the blog entry above). But you may want to set up some natural hierarchies, as discussed here:

     

    What are the natural hierarchies and why they are a good thing

    Thursday, February 14, 2008 10:39 PM
    Moderator
  • Hi there,

    Does anyone of you guys have experience with SSAS 2008?
    The story above was completely clear to me untill I started with SSAS 2008...

    For the date dimension i.e., I have a typical hierarchy Year, Month, Date - which works fine...
     
    untill I add the attribute Holiday Flag to my Date dimension, then I get the error: 
    "Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_Dim_x0020_Date', Column: 'Holiday_x0020_flag', Value: '0'. The attribute is 'Holiday Flag'."

    So it's just an attribute related to the dimension key, nothing more.

    Just curious to know if I'm the only one struggling with dimensions in SSAS 2008.

    Regards,
    Tom
    Tom De Cort - BI Consultant
    Thursday, February 12, 2009 6:01 PM
  • Have you solved this problem. I am getting the same error for columns of a dimension, often but not all the time and I have been unable to determine what makes the difference. I am working with 2008 and do not have the problem with 2005. Providing the row key will make in unique but totally messes up using the column in a cube selection.


    Tom
    Thursday, March 5, 2009 1:27 AM
  •  Do you have NULL values in your attribute key? These generate the same error...

    Since I replaced these NULL values by 'N/A' i.e., I don't have the error anymore

    -Tom
    Tom De Cort - BI Consultant
    Wednesday, March 25, 2009 4:36 PM
  • Thanks Tom, for the tip. I have been strugling with this issue for a while. SSAS2008 is much more stricter than SSAS2005. You have to be very precise. It is better but you have to get used to.

    For other people look at this thread, it can be interesting. http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/91086c77-b399-40e8-b323-c5e5ded938d1. Another tip: go and visit the site http://www.learnmicrosoftbi.com/.
    Sr. Business Intelligence Consultant
    Saturday, April 18, 2009 11:05 AM
  • Hello there,

    Does anybody has the complete resolution for duplicate entries data. I am showing the data below .

    RegionName BranchName LoanOfficerFullName
    PRIME - TEAM NORTHEASTERN CALIFORNIA ALONA  HAYES
    PRIME - TEAM WESTERN RENO-DAMONTE RANCH VINCENT LOTITO
    PRIME - TEAM WARTON NEWARK (Chicago) CHRISTOPHER CLIFTON
    PRIME - TEAM WARTON GRAND HAVEN (West Michigan) SEAN  FOLEY
    PRIME - TEAM SALMANS LAWTON (Amarillo) THOMAS  STEARNS
    PRIME - TEAM BARTON CALIFORNIA ALONA  HAYES
    PRIME - TEAM ROBINSON LAS VEGAS ANGELA DEATON
    PRIME - TEAM ROBINSON LAS VEGAS HERMAN  VANDER VELDT
    PRIME - TEAM ROBINSON LAS VEGAS DAWN ROBINSON
    PRIME - TEAM ROBINSON LAS VEGAS MICHAEL  BIRK

     Here i have the Hierachies in order LoanOfficerFullName-->BranchName-->RegionName. Loan Officer is the lowest level. I had created another column and make that unique entries so that I could make the unique column as the Key and the hierachies i have linked with the key column value. The issue

    is that i am getting the expected result in OLAP  from data side. But the data is not getting aggregated like Team Barton is coming twice though for the Dimension I make IsAggregatable property to True. If anybody can help me I will aprecciate. If any other clarification need I can provide.

    Thanks in advance.

     

    Saturday, March 27, 2010 4:04 AM
  • I'm facing the same issue in SSAS 2012. Two of the higher level attributes of the hierarchy are sharing a same lower level attribute. how to fix this? 

    thanks

    Friday, March 3, 2017 9:22 PM
  • In a hierarchy, the aggregation from child members to parents has to have a unique path. If you have a child member in a hierarchy that truly has two parents in the business domain, your dimension design will need to be modified. You would either need to create two different hierarchies for the two different paths or implement something more complex such as a M2M hierarchy like so. Parallel Hierarchies

    HTH,

    Martin


    Martin Mason Wordpress Blog

    Saturday, March 4, 2017 2:36 PM