Hierarchy – order of displaying lowest level attributes

How to create a measure from data that is stored in a dimension table.
September 30, 2008
OBIEE and session cookies, and Google Chrome rambling
October 6, 2008

There was a problem in drill down in one of the hierarchy with the order it displays the lowest level attributes.

When drilling down on a certain category – “Organization”, Answers always shows columns in the following order:
Organization Name,Department Name, Sub-Department Category, Org Detail Number, Org Detail Name

The desired order is:
Organization Name,Department Name, Sub-Department Category, Org Detail Name, Org Detail Number

The hierarchy was defined as following in the RPD (please note that Org Detail Name and Org Detail Number are at the same lowest level defined as keys):

Organization Name
Department Name
Sub-Department Category
Org Detail Name | Org Detail Number

It has been found that it doesn’t sort alphabetically.

With Oracle’s OBIEE support and help – the solution has been found:

1. Use the Query Repository feature under Tools menu in the RPd to do a search of these 2 columns in the RPD.
2. In the Query Repository window note down the IDs for these 2 columns. Please check which one is bigger than the other.
3. The logical table, delete the 2 columns (Org Detail Name and Org Detail Number) and add them again in the desired order. Make sure you add them in the correct order. Query for the columns again, the IDs should be in the reverse order as step 2.
4. Add them in the hierarchy again
5. Change the order of level keys in Dimension hierarchy
6. Save the changes and test again.

After RPD re-deployment – it was working.

There must be an easier way to change order of composite key (complex key) columns.

3 Comments

  1. John Tesson says:

    Question about your description of the problem. When you say that “it doesn’t sort alphabetically” do you mean that the actual values in Org Detail Name and Org Detail Number aren’t sorted correctly or do you mean that the logical column “Org Detail Name” alphabetically should come before logical column “Org Detail Number”?
    I’m asking because our problem is that we can’t get the proper sort order for the actual values from a logical column that is at the lowest level of our hierarchy when having drilled from a higher level logical column. There’s no way to specify a default sort order on a column that you get to by drilling. We’ve tried specifying a sort order within the RPD but it doesn’t help.

  2. Andriy Yakushyn says:

    Hi John,
    The answer to your question is “Org Detail Name” should come before logical column “Org Detail Number”.
    As far as your problem – are you trying to sort a numeric or a varchar value? Have you tried to set sort order in column properties in RPD? Let me know if you need further help with this. I’d be delighted to help.

  3. John Tesson says:

    Yes, we’ve tried setting a sort order in the RPD for the logical column in the BMM layer and it doesn’t help. The value we’re trying to sort is varchar. Thanks for your response!

Leave a Reply