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):
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.