Why does Order ignore subselect?

I've run into some confusing behavior in Analysis Services 2005 (and 2008 R2) and would appreciate it if someone could explain why this is happening. For the sake of this question, I've reproduced the behavior against the Adventure Works cube.

Given this MDX:

SELECT [Customer].[Education].[(All)].ALLMEMBERS ON COLUMNS,
    Order(DrillDownLevel({[Customer].[Customer Geography].[All Customers]}), 
          ([Measures].[Internet Order Count]), 
          ASC) ON ROWS
FROM (SELECT {[Customer].[Education开发者_运维百科].&[Partial High School]} ON COLUMNS FROM [Adventure Works])
WHERE [Measures].[Internet Order Count];

The query evaluates with the ordered set on rows:

All Customers: 2, 136

Germany: 269

France: 298

Canada: 304

United Kingdom: 311

United States: 457

Australia: 497

However, if I include the All Member (or defaultmember) for Education in the tuple used in the order statement:

SELECT [Customer].[Education].[(All)].ALLMEMBERS ON COLUMNS,
        Order(DrillDownLevel({[Customer].[Customer Geography].[All Customers]}), 
              ([Measures].[Internet Order Count], [Customer].[Education].[All Customers]), 
              ASC) ON ROWS
FROM (SELECT {[Customer].[Education].&[Partial High School]} ON COLUMNS FROM [Adventure Works])
WHERE [Measures].[Internet Order Count];

Then the the set comes back in a significantly different order:

All Customers: 2, 136

France: 298

Germany: 269

United Kingdom: 311

Canada: 304

Australia: 497

United States: 459

Note that France and Germany are out of order relative to each other. Same with Canada / UK and with USA / Australia. From what I can tell, it's ordering based on the aggregation before the sub-cube is evaluated.

Why does including this member (which should implicitly be in the tuple in the first example?) cause the evaluation of the order statement to look outside of the subcube's visual totals? Filter and TopCount etc functions seem to have the same behavior.

Cheers.

---

My guess is that since the 2 attributes are related only by key (no direct relationship exists) the resulting crossjoin of the attributes results on the key members being aggregated and this is where the visualtotals is not applied (it's one of the ways how to calculate tho nonvisualtotal with subselects).

I've constructed a query to demonstrate what is happening as a result of that crossjoin:

WITH

MEMBER sortExpr as
AGGREGATE(Descendants([Customer].[Customer Geography].CurrentMember), [Measures].[Internet Order Count])

SELECT 
[Customer].[Education].[(All)].ALLMEMBERS 
*
{[Measures].[Internet Order Count], sortExpr} ON COLUMNS,
Order
(
    DrillDownLevel({[Customer].[Customer Geography].[All Customers]})
    ,sortExpr
    ,ASC
) ON ROWS
FROM 
(
    SELECT 
    {[Customer].[Education].&[Partial High School]} ON COLUMNS 
    FROM [Adventure Works]
)
;

EDIT: Here is another query that shows that the expression works correctly with attribute overwrites once you solve the subselect problem using a query scoped set (well-known solution):

WITH

set sub as
[Customer].[Customer Geography].[Customer]

MEMBER sortExpr2 as
Aggregate(existing sub, [Measures].[Internet Order Count])

SELECT 
[Customer].[Education].[(All)].ALLMEMBERS 
*
{[Measures].[Internet Order Count], sortExpr2} ON COLUMNS,
Order
(
    DrillDownLevel({[Customer].[Customer Geography].[All Customers]})
    ,
    (
--      [Customer].[Customer Geography].CurrentMember,
        [Customer].[Education].[All Customers],
        sortExpr2
    )
    ,ASC
) ON ROWS
FROM 
(
    SELECT 
    {[Customer].[Education].&[Partial High School]} ON COLUMNS 
    FROM [Adventure Works]
)
;

Thanks to Boyan for tweeting this question. Regards, Hrvoje

---

Note: Have a look at http://www.bp-msbi.com/2011/07/mdx-subselects-some-insight/ where I explained the behaviour in more detail.

A great article about subselects is Mosha's 2008 MDX: subselects and CREATE SUBCUBE in non-visual mode

Note what happens when you use a subselect. Implicit Exists and visual totals are applied. The key bit of information here in regards to what you are experiencing is:

2. Applies visual totals to the values of physical measures even within expressions if there are no coordinate overwrites.

In your first query SSAS applies visual totals by default. You can change your query to not do this like this:

SELECT [Customer].[Education].[(All)].ALLMEMBERS ON COLUMNS,
    Order(DrillDownLevel({[Customer].[Customer Geography].[All Customers]}), 
          ([Measures].[Internet Order Count]), 
          ASC) ON ROWS
FROM NON VISUAL (SELECT {[Customer].[Education].&[Partial High School]} ON COLUMNS FROM [Adventure Works])
WHERE [Measures].[Internet Order Count];

The NON VISUAL keyword in a SELECT statement tells SSAS to only apply the implicit Exists, but not the visual totals part. The results of the query will be the same as in the second case, but you will also see the real numbers it is ordering by.

Because you are explicitly overwriting the All member in the second query, SSAS does not apply the visual totals to this expression and orders by the total amounts for all years. However, it still displays the visual totals for the selected on ROWS measure after it evaluates the order on non-visual totals.

---

I'm not a SSAS specialist but this is related to attribute overwrite.

You're overwriting your sub-select in the Order function evaluation. Note, this behavior is context dependent (axes eval, calculated members and pivot). In you example the context is a function during the evaluation of the axis. The behavior is different from the evaluation of the pivot (once you're axes are known). It's complicated but the way it is.

Note that in icCube and after discussing with some MDX specialist we decided to simplify and not follow this behavior: subselect filter is always applied.