Dimension Order in an MDX cube view as datasource

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 337
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: 3251
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: 337
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

Post Reply