I am trying to understand how MSAS does hierarchical ordering if the
set to be ordered contains tuples with multiple dimensions. The output
of the following query is somewhat confusing:

select {([Measures].[Unit Sales])} on columns, order({([Customers].[All
Customers].[USA].[CA], [1997].[Q1].[1]), ([Customers].[All
Customers].[USA].[OR].[Portland], [1997].[Q1]), ([All
Customers].[USA].[OR].[Beaverton], [1997].[Q1]), ([Customers].[All
Customers].[USA].[OR], [1997].[Q1].[1]), ([Customers].[All
Customers].[USA].[CA].[San Francisco], [1997]),([Customers].[All
Customers].[USA].[CA].[San Jose], [1997].[Q1]), ([Customers].[All
Customers].[USA].[CA].[Los Angeles], [1997].[Q1])}, ([Measures].[Unit
Sales]), ASC ) on rows from sales

The output is:

OR 1 6909 <--
Portland Q1 1001
Beaverton Q1 1107
CA 1 5377 <--
San Francis 1997 88
San Jose Q1 29
Los Angeles Q1 344

Within OR and CA, for the same granularity of Time dimension (e.g Q1),
the cities are ordered in ascending order of Sales, which is expected.
What I don't understand is OR and CA are not ordered ascending, even
though they are siblings, and their Time dimension member is exactly
the same. Is there any 'hidden' ordering attribute? In general, what
are the criteria used for hierarchical ordering of multi-dimensional
tuples?

Any thoughts?
Thanks,
Aman

It turns out that the sales value of parent of Q1, i.e. 1997 is being
used to do the ordering; so, the value of (OR, 1997, [Unit Sales]) is
less than (CA, 1997, [Unit Sales]), hence [OR] appears before [CA] in
the hierarchical order.

My guess is that the Customer dimension is being ordered with reference
to the Default Member of Time, because even if the query only references
Jan. 1997, in which case its parent is Q1 1997 (not 1997), OR still
precedes CA:

order({([Customers].[All Customers].[USA].[CA], [1997].[Q1].[1]),
([Customers].[All Customers].[USA].[OR].[Portland], [1997].[Q1].[1]),
([All Customers].[USA].[OR].[Beaverton], [1997].[Q1].[1]),
([Customers].[All Customers].[USA].[OR], [1997].[Q1].[1]),
([Customers].[All Customers].[USA].[CA].[San Francisco],
[1997].[Q1].[1]),
([Customers].[All Customers].[USA].[CA].[San Jose], [1997].[Q1].[1]),
([Customers].[All Customers].[USA].[CA].[Los Angeles],
[1997].[Q1].[1])},
([Measures].[Unit Sales]), ASC ) on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!