Defining sorting based on attribute key

Question

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.

Answer 1

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.

Answer 2

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,

Answer 3

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

Answer 4

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,

Answer 5

Hi Philip,

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

Thanks

--Noor

Answer 6

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,

Answer 7

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.

Answer 8

 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?

Answer 9

while processing the dimension in the processing screen it will show the Queries for each of the attribute.