Dimension Order in an MDX cube view as datasource

[PavoGa]

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?

[lotsaram]

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.

[PavoGa]

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.