Dimension Order in an MDX cube view as datasource

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 364
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Dimension Order in an MDX cube view as datasource

Post by PavoGa » Tue Jun 18, 2019 2:25 pm

There does not seem be a rational way to determine the dimension order when a TI is based on a cube view created by MDX.

The original cube order:
  1. Org
  2. HRLevel
  3. Period
  4. Adjustments
  5. Measure
The MDX:

Code: Select all

sViewMDX = EXPAND('SELECT        
        {[Measure].[Measure].[FTE]}
        ON COLUMNS,
        NON EMPTY
        TM1SUBSETTOSET( [Org].[%hierOrg%], "Planning Components") *
        TM1SubsetToSet([HRLevel].[HRLevel], "All Leaves")
        ON ROWS
    FROM [stage.HRActuals]    
    WHERE ([Adjustments].[Adjustments].[Effective],
        [Period].[Period].[%psPeriod%])');
The order the dimensions are read in the process (with placement in MDX statement):
  1. HRLevel (ROW-2nd)
  2. Period (WHERE-2nd)
  3. Adjustments (WHERE-1st)
  4. Measure (COLUMN)
  5. Org (ROW-1st)
Have to do a trial run and ASCIIOUTPUT to determine read order, then name the variables. Would have thought there would be some rational way to determine the dimension order. Am I missing something here?
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3290
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dimension Order in an MDX cube view as datasource

Post by lotsaram » Tue Jun 18, 2019 8:00 pm

Is the cube re-ordered ("optimized") in the order of the variables?
  • HRLevel
  • Period
  • Adjustments
  • Measure
  • Org
It then wouldn't be correct how the variables are being ordered, but it would offer an explaination.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
Community Contributor
Posts: 364
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Dimension Order in an MDX cube view as datasource

Post by PavoGa » Wed Jun 19, 2019 3:03 pm

lotsaram wrote:
Tue Jun 18, 2019 8:00 pm
Is the cube re-ordered ("optimized") in the order of the variables?
  • HRLevel
  • Period
  • Adjustments
  • Measure
  • Org
It then wouldn't be correct how the variables are being ordered, but it would offer an explaination.
Yes, that was checked and I should have stated so. While I have no explanation, all that seems to have changed is the first dimension (Org) simply shifted to the bottom of the order.
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3290
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dimension Order in an MDX cube view as datasource

Post by lotsaram » Mon Aug 05, 2019 4:08 pm

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
Community Contributor
Posts: 364
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Dimension Order in an MDX cube view as datasource

Post by PavoGa » Mon Aug 05, 2019 5:09 pm

lotsaram wrote:
Mon Aug 05, 2019 4:08 pm
Ty,

It seems that the ordering is dependent on the view structure
- First WHERE
- Then ROW
- Last COLUMN
We have seen this, but also, on a couple of queries, have seen an ordering that has no rhyme or reason to it. Has been rare, but has happened. Enough that we do not trust it and now write the TI's by doing an output to what order things are in.
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 ;-)
Ah! Thank you for the tip and I agree we probably should be writing the MDX for view extracts this way henceforth.
Ty
Cleveland, TN

Bakkone
Posts: 102
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Dimension Order in an MDX cube view as datasource

Post by Bakkone » Wed Aug 07, 2019 9:04 am

Hi,

I haven't started with the MDX views yet as my MDX-skils are pretty crappy (Any tips on resources?).

But does an MDX cube view differ in a "normal" cube view when used as a data source when it comes to dimension orders? Exporting using a "normal" cube view uses the cube dimension order. Is this not the case when exporting an MDX view?

lotsaram
MVP
Posts: 3290
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dimension Order in an MDX cube view as datasource

Post by lotsaram » Wed Aug 07, 2019 1:23 pm

Bakkone wrote:
Wed Aug 07, 2019 9:04 am
But does an MDX cube view differ in a "normal" cube view when used as a data source when it comes to dimension orders? Exporting using a "normal" cube view uses the cube dimension order. Is this not the case when exporting an MDX view?
So I guess you (didn't) read the contents of the thread then?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Bakkone
Posts: 102
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Dimension Order in an MDX cube view as datasource

Post by Bakkone » Wed Aug 07, 2019 1:51 pm

lotsaram wrote:
Wed Aug 07, 2019 1:23 pm
So I guess you (didn't) read the contents of the thread then?
I wish I could say I didn't. Maybe I read too much into it and that it doesn't matter at all, but that you want a predictable order of your variables.

lotsaram
MVP
Posts: 3290
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dimension Order in an MDX cube view as datasource

Post by lotsaram » Wed Aug 07, 2019 1:56 pm

Bakkone wrote:
Wed Aug 07, 2019 1:51 pm
you want a predictable order of your variables.
Indeed you do! And that's the point. With a MDX view as the data source you don't necessarily get the variables in the index order of the cube (unless you jump through a few hoops and construct the view in a particular way).
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Bakkone
Posts: 102
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: Dimension Order in an MDX cube view as datasource

Post by Bakkone » Wed Aug 07, 2019 1:59 pm

So the MDX Views are not the magic I was hoping for to force my TI process to loop through the source in a way I want it to then. Thanks for the clarification.

User avatar
PavoGa
Community Contributor
Posts: 364
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Dimension Order in an MDX cube view as datasource

Post by PavoGa » Wed Aug 07, 2019 7:59 pm

Bakkone wrote:
Wed Aug 07, 2019 1:59 pm
So the MDX Views are not the magic I was hoping for to force my TI process to loop through the source in a way I want it to then. Thanks for the clarification.
That is an interesting conclusion drawn from lotsaram's post. Suspect that you are not fully understanding what we are saying.

In a traditional view (CreateSubset, CreateView, ViewSubsetAssign), we know the dimension order is going to be the order in which the cube is constructed or available through TABDIM (easily displayed in Architect as well). This order cannot be manipulated in a traditional view.

What lotsaram is saying is that in MDX, it does not default to that order. Where a Version dimension may be the first dimension in the cube and would read as the first variable in a traditional view, depending on how the MDX view is constructed, it may be read as the THIRD variable, appearing as though the Version dimension is the third one in the cube.

An MDX view does not change the data that is retrieved from the cube. It just may change the order in which it presents the dimensions.
Ty
Cleveland, TN

Post Reply