Rittman Mead - Delivered Intelligence

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."

Posted in Oracle OLAP | 1 Comment »