none
Processing Dimensions Issue - Process Full vs Process Update

    Question

  • I am running SQL Server 2008 and have been adding enhancements to my current project.  I have had no issues processing and deploying the SSAS solution files in the past but some of the source data has changed.  I am trying to process a dimension with the following schema:

    Hierarchy
    A
    B
    C

    Attribute Relationships
    D(Key) ->C->B->A

    The KeyColumns for these attributes are:
    A: A
    B: B,A
    C: C,B,A 
    D(Key): D

    If I choose to Process Full, I get the error "Errors in the OLAP storage engine: A duplicate attribute key has been found when processing, the attribute is B ".
    However, if I Process Updates, I get no errors and everything works fine.

    None of these attributes contain NULLs.  I have experimented with a variety of different Key Values for these attributes but the only ones that work than don't display attribute B correctly (ie. instead of just showing distinct values in Filter, shows multiple).

    What is causing this to happen?  What is the difference between Process Full and Process Update?  What can I do to get this to work?

    Any help would be much appreciated.

    Thanks!
    Tuesday, April 07, 2009 10:40 PM

Answers

  • A Process Full will completely rebuild the entire dimension, executing a Process Clear on all partitions with relationships defined to it. My guess to the cause of your error is if you executed a SELECT DISTINCT A, B, B-Name FROM DimenionTable from your relational source, you'll find that two different names are mapped to the same composite key for at least on of your records. Never realized that a Process Update works on a last one wins so I guess that's the reason your Process Update is not giving you an error. But I'm not real confident of that answer though.

    • Marked as answer by MickWags Wednesday, April 08, 2009 8:26 PM
    Wednesday, April 08, 2009 2:05 AM

All replies

  • A Process Full will completely rebuild the entire dimension, executing a Process Clear on all partitions with relationships defined to it. My guess to the cause of your error is if you executed a SELECT DISTINCT A, B, B-Name FROM DimenionTable from your relational source, you'll find that two different names are mapped to the same composite key for at least on of your records. Never realized that a Process Update works on a last one wins so I guess that's the reason your Process Update is not giving you an error. But I'm not real confident of that answer though.

    • Marked as answer by MickWags Wednesday, April 08, 2009 8:26 PM
    Wednesday, April 08, 2009 2:05 AM
  • It looks like there was a situation where the data was not getting handled appropiately.  There was a Case Statement that referred to the same column as an ISNULL Statement.  This combined with the logic of the Case statement was causing columns to be incorrectly updated.  This was causing the duplicate attribute keys.

    I am still not sure why the Process Update was working and the Process Full was not.

    Wednesday, April 08, 2009 8:26 PM
  • I am still not sure why the Process Update was working and the Process Full was not.


    At a guess the update was probably applied multiple times for the multiple keys, whereas when you do a full process it was effectively trying to insert the duplicate values.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thursday, April 09, 2009 6:19 AM
    Moderator