Update. We have noticed a possible pattern with regards to output order not matching dimension order when reading an MDX cube view:
We are observing the output order is by:
*** 08/12/2021 Updated this as the original, did not make a lot of sense ***
1) dimensions that are based on the default hierarchy (dimname:dimname) are grouped first by cube dimension order
2) any dimensions using alternate hierarchies in the MDX query are grouped together by
their respective order in the cube and follow the group of dimensions described in #1.
In other words, if any dimensions in the MDX query use alternate hierarchies, the engine creates two groups, one of default hierarchies and one of alternate hierarchies. Within each group, the dimension variables are by the cube dimension order.
*** Update end ***
Say a cube has the following dimensions in this order:
1) Versions
2) Cost Center
3) Accounts
4) Calendar
5) Measure
and the view MDX is like this:
Code: Select all
SELECT
[Measure].[subset] ON COLUMNS
{[Version].[element]} *
[Cost Center].[alt_CostCenter].[subset] *
[Accounts].[alt_Accounts].[subset] *
[Calendar].[subset] ON ROWS
FROM [SourceCube]
The order the TI outputs this cube is:
1) Versions
2) Calendar
3) Measure
4) Cost Center
5) Accounts
If this is the MDX (note Accounts is now based on the default hierarchy):
Code: Select all
SELECT
[Measure].[subset] ON COLUMNS
{[Version].[element]} *
[Cost Center].[alt_CostCenter].[subset] *
[Accounts].[subset] *
[Calendar].[subset] ON ROWS
FROM [SourceCube]
then the output order becomes:
1) Version
2) Accounts
3) Calendar
4) Measure
5) Cost Center
This is what we have seen now in three different examples. Two, like above, are on the same cube. Going to IBM with this so if anyone happens to see the same pattern or not, would appreciate the feedback.
