Ty,
It seems that the ordering is dependent on the view structure
- First WHERE
- Then ROW
- Last COLUMN
As MDX axis 0 is columns there is a requirement that a view contains at least one dimension on columns. I think this is the only requirement but your level of MDX I think is better than mine.
As a solution to the issue of variable ordering it looks like this can be solved by having a "ViewExtract" form for the MDX view (with all dimensions cross-joined on columns in cube index order) as opposed by a "user query" view (containing columns, rows & where).
E.g. for Planning Sample
In this format if you want to get an idea of the cellset (with last dimension on column and all other dimensions cross-join on rows)
Code: Select all
sMDX = '
SELECT
NON EMPTY
{ Tm1FilterByLevel( Descendants( [plan_time].[2004] ), 0) }
ON COLUMNS,
NON EMPTY
{ [plan_version].[FY 2004 Budget] }
* { [plan_business_unit].[UK] }
* { [plan_department].[Direct] }
* { Tm1FilterByLevel( Tm1SubsetAll( [plan_chart_of_accounts] ), 0) }
* { [plan_exchange_rates].[local] }
* { [plan_source].[input] }
ON ROWS
FROM [plan_BudgetPlan]
';
Or in this format if it is purely as a source for TI (with all dimensions cross-join on columns). Won't be able to visualize but TI doesn't seem to mind.
Code: Select all
sMDX = '
SELECT
NON EMPTY
{ [plan_version].[FY 2004 Budget] }
* { [plan_business_unit].[UK] }
* { [plan_department].[Direct] }
* { Tm1FilterByLevel( Tm1SubsetAll( [plan_chart_of_accounts] ), 0) }
* { [plan_exchange_rates].[local] }
* { [plan_source].[input] }
* { Tm1FilterByLevel( Descendants( [plan_time].[2004] ), 0) }
ON COLUMNS
FROM [plan_BudgetPlan]
';
Either will achieve the variable assignment in the TABDIM order of the cube. From my perspective this is a pretty acceptable workaround as this is probably how we should be creating "ViewExtract" MDX views anyway (or how IBM
assumed people would be creating MDX views for TI processing). I still think it's a defect that variables are ordered depending on the view structure and there should be a mechanism as for native views to ensure the variable ordering is consistent with the cube structure ordering.
Kudos to my very clever colleague who suggested this solution
Post back in the thread if this works for you and solves your variable assignment problem.
