Controlling The Sort Order of a Dimension Using AWM 10.1.0.4

I noticed a useful thread on the OTN OLAP Forum on controlling the sort order of a dimension in AWM 10.1.0.4. As it's sometimes difficult to link to individual OTN Forum postings (the link seems to break after a while) I've reproduced it here as a reference point:

"I have noticed in the "help" of AWM 10.1.0.4 that there is a sort members dialog box (where it is also possible to edit the sort criteria). However I cannot locate it in AWM, could be so kind as to help me find where it is located."

Reply by Anthony Waite:

"The 'Sort Members dialog box' found in the Help is not yet exposed in AWM 10.1.0.4. This should have been removed from the Help.

So how do you control the sort order of a dimension in AWM 10.1.0.4?

1) Within the Create Attribute dialog box, create a sort order attribute with the attribute type as 'user' and the data type as 'text' or 'integer' for each hierarchy and apply it to its corresponding hierarchy. (Do not use the Default Order attribute type. Currently the OracleBI stack does not support it.)

2) Populate the dimname_attrname text variable (map and load from a source table or use OLAP DML):

limit CUSTOMER_HIERLIST to 'SHIPMENTS'
limit CUSTOMER to CUSTOMER_INHIER
limit CUSTOMER to hierarchy run -
'sort customer a customer_long_description(all_languages
\'AMERICAN_AMERICA\')' -
using customer_parentrel
CUSTOMER_SORT_ORDER_SHIP = convert(1000000+STATRANK(CUSTOMER),text,0)

3) Add a SORT_ORDER property to each dimname_hiername_hierdef object:

consider CUSTOMER_SHIPMENTS_HIERDEF
property 'SORT_ATTRIBUTE' 'CUSTOMER_SORT_ORDER_SHIP'

4) Do the same for each hiearchy in a dimension.

The first step is done within the AWM GUI (Model View), the remaining steps are performed within OLAP Worksheet (OLAP DML).

The creation of a sort attribute is simply applied to the hierarchy(ies).

Controlling the sort order of dimension members while staying within the GUI will be available in the upcoming 10.2. Cheers!"

followed shortly afterwards by:

"Just as an update and to set the record straight, the author of the AWM Help got back with me. These help topics are part of the Query Builder help, which you can access through the Measure Viewer. Display the Dimensions page, then hit the Sort button. They do not reference the sorting of dimension members while defining the dimension in the Model View.

As a byproduct, at least it is known how to sort dimensions members in 10.1.0.4 and that users will be able perform this operation within the Model View GUI for 10.2."