none
Defining sorting based on attribute key

    Pregunta

  • Hi Team,

    I am trying to define the sort order for an attribute based on attribute key

    This is data in my dimtable

    Period sortorder(attributekey)

    APR-MAR 1

    APR-SEP 2

    OCT-MAR 3

    null  4

    Defined the attribute, defined relationship made hierarchy enable as false, optimized state as notoptimized.

    when trying to deploy it throws an error and deployment fails

    Error    14    Internal error: The operation terminated unsuccessfully.        0    0   
    Error    15    Server: The operation has been cancelled.        0    0   

    Regards,

    Eshwar.
    miércoles, 20 de junio de 2012 9:42

Respuestas

  • Mdx teghem,

    Thanks a lot...

    I have resolved the issues it is because of the duplicates.

    Noor: Just process that dimension alone when it is processed it will create distinct queries...copy that query run it against the DW database then you will be able to find if there are any duplicates as such. Then you may need to change the ETL logic or Cube tweak to avoid the duplicates.

    Regards,Eshwar.

    jueves, 21 de junio de 2012 7:06

Todas las respuestas

  • I have tried processing only the dimension here is actual error:

    Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimPeriod', Column: 'BasePeriodDesc', Value: ''. The attribute is 'Base Period Desc'.

    and some more data

    PeriodCode BasePeriodDesc    PeriodSort
    Y NULL    1
    Y APR - MAR    1
    Y NULL    1
    Y NULL    1
    Y NULL    1
    Y NULL    2
    H1 APR - SEP    2
    H1 NULL   2
    H1 NULL   2
    H2 OCT - MAR    2

    H2 NULL   2
    H2 NULL   2

    Regards,

    Ram.

    miércoles, 20 de junio de 2012 10:24
  • hi,

    In order to set the orderbyattribute property of A to attribute B, you have probably define in the "attribute relation tab" attr B as related to attr A: attribute A --> attribute B.

    The error has probably been raised because while processing the dimension, a record has been find that does not comply with that relation rule. A duplicate value for attr B has been found for a particular value of attribute A.

    If the number of attribute values are manageable, you could define a named calculation in dsv that would be use for sorting. The "days of the week" in the date dimension for example are typically associated with {1:7}  for ordering purposes.

    Philip,


    • Editado VHteghem_Ph miércoles, 20 de junio de 2012 13:17
    miércoles, 20 de junio de 2012 12:45
  • Hi Philip,

    I am also having the same issue. Could you please help me understand in fixing this as you said to define a named calculation in dsv.

    And in my case I've to sort the Questions based on QSortId not on Name. so for this I've created a relationship between Question and QSortId but while cube processing it given a duplicate attribute key error for the Attribute Question.

    Now, is that mean I've duplicate value for QsortId for Question?

    How to fix this, please help

    Thanks in advance

    --Noor

    miércoles, 20 de junio de 2012 14:32
  • Hi Noor, Hi Ram,

    In the customer dimension the "ComuteDistantSort" is a named calculation ( defined in the customer table in dsv) that is used for ordening the "commute Distance" attribute.

    ComuteDistantSort

    CASE 
        WHEN CommuteDistance= '0-1 Miles'  THEN 
            '1'
        WHEN CommuteDistance= '1-2 Miles'  THEN 
            '2'
        WHEN CommuteDistance= '2-5 Miles'  THEN 
            '3'
        WHEN CommuteDistance= '5-10 Miles' THEN 
            '4'
        WHEN CommuteDistance= '10+ Miles'  THEN 
            '5'
    END

    In the Attribute RelationShips Tab, you will notice that "commute Distance Sort" has been related to "Commute Distance"

    In the properties of "Commute Distance",

    "OrderBy is set to "AttributeKey" 

    OrderByAttribute is set to "Commute Distance Sort. (This last value is listed only because of the existing relationship)

    If not sorted, a list the '10 Miles' value would come just after '1-2 Miles' in any set 

    Philip,

    miércoles, 20 de junio de 2012 16:05
  • Hi Philip,

    Can you suggest how to fix my issue as the above related to Ram.

    Thanks

    --Noor

    jueves, 21 de junio de 2012 5:52
  • Hi Philip,

     I've created a relationship between Question and QSortId 

    I guess the column QSortId contains the desired sort order and represents a business requirement. To be usefull, it should respect the relationship. You should review it in regard of those requirements. A named calculation can be helpfull if the number of values are manageable, if you know the desired order and are pretty sure the result will respect the relationship. all  those conditions are met in the example i gave.

    Philip,




    jueves, 21 de junio de 2012 6:48
  • Mdx teghem,

    Thanks a lot...

    I have resolved the issues it is because of the duplicates.

    Noor: Just process that dimension alone when it is processed it will create distinct queries...copy that query run it against the DW database then you will be able to find if there are any duplicates as such. Then you may need to change the ETL logic or Cube tweak to avoid the duplicates.

    Regards,Eshwar.

    jueves, 21 de junio de 2012 7:06
  •  Just process that dimension alone when it is processed it will create distinct queries...copy that query 

    Regards,Eshwar.

    Interesting ! Where are those query to be found?
    jueves, 21 de junio de 2012 7:31
  • while processing the dimension in the processing screen it will show the Queries for each of the attribute.

    jueves, 21 de junio de 2012 14:13