Element Order in Dimension, PAW vs PAfE

[WilliamSmith]

Scenario:

• Dimension was created in the "old world" Perspectives / Architect.
• Using =DIMNM("server:dimensionName", index) in PaFE.
• In PAW, under the dimension tree, I right click the dimension and select "Edit Dimension" (or "Edit Dimension Classic").

In PAW, my dimension now looks like this:

Index 1: Consolidation 1
Index 2: Element A
Index 3: Element B
Index 4: Element C

When I re-query DIMNM functions in PAfE, I expect the dimension element order to respect the new indexing, but it does not, something like:

DIMNM(myServer:myDimension, 1): returns name of Element B
DIMNM(myServer:myDimension, 2): returns name of Element A
DIMNM(myServer:myDimension, 3): returns name of Consolidation 1
DIMNM(myServer:myDimension, 4): returns name of Element C

Am I missing something?

[MarenC]

Hi,

Just out of interest,

What does Dimix return?
What does ElementName return?
What does ElementIndex return?

Maren

[WilliamSmith]

Hi Maren,

(Continuing with the previous example)

DIMNM at index 1 return Element B (although it shows as the third item in the dimension editor in PAW)
DIMIX passing in the string name "Element B" in the same dimension, returns 1.

What would be the best way to test ElementName and ElementIndex?

[MarenC]

Hi,

I must admit I thought the ElementName/Index worked in PAX but you can ascii with a TI, was just wondering if it was hierarchy related.

Maren

[ascheevel]

The dimension editor in PAW Workbench will always show the result of the MDX expression [<dimension>].[<hierarchy>].members which will show every consolidation fully expanded resulting in elements being represented twice when they are children of multiple consolidations. Since an element can only have one index, this view can never be a true indicator of an element's index.

For the set editor in PAW and PAX, the initial All Members view on the right side is the result of the same mdx expression as the dimension editor: [<dimension>].[<hierarchy>].members. If you want to see the exact same order here as when you hit the "SubsetAll" button in the Architect subset editor (all elements ordered by index) you can replace the MDX in the PAW or PAX set editor with this TM1SubsetAll([<dimension].[<hierarchy>]).

[WilliamSmith]

Thank you ascheevel,

I do see now in PAW where I can modify the MDX in the subset editor. Is there a way I can modify the ordering returned by TM1SUBSETALL?

What is more confusing is that in the Dimension Editor (or MDX .Members return), all of the N level elements roll to the top level consolidation, but when I use TM1SUBSETALL MDX, some elements that are shown in the consolidation, are no longer shown under the consolidation (collapse consolidation, and elements are still showing etc.)

PAW_2024-02-22.png (16.03 KiB) Viewed 288 times

PAW_2024-02-22_02.png (21.17 KiB) Viewed 288 times

[ascheevel]

I do see now in PAW where I can modify the MDX in the subset editor. Is there a way I can modify the ordering returned by TM1SUBSETALL?

No, TM1SubsetAll takes only one argument: [<dim>].[<hierarchy>]. You would need to apply other MDX functions to the result of TM1SubsetAll to get desired results.

What is more confusing is that in the Dimension Editor (or MDX .Members return), all of the N level elements roll to the top level consolidation, but when I use TM1SUBSETALL MDX, some elements that are shown in the consolidation, are no longer shown under the consolidation (collapse consolidation, and elements are still showing etc.)

What's the element and component sort type setting for the dimension? My guess is it's still ByInput so when you apply TM1SubsetAll, you're not getting a hierarchy sorted result as the elements are sorted by index. If you apply ByHierarchy to the ElSorttype, do TM1SubsetAll again, and then collapse Total, you won't see any elements visible outside of Total anymore.

[WilliamSmith]

@ascheevel, got it, thank you.

In Architect there does seem to be the ability to edit the dimension (right click -> Edit Dimension Structure) with the indexing that is shown in the return from TM1SubSetAll.

I'm guessing that PAW does not have the ability to edit the dimension in this way? Both Edit Dimension and Edit Dimension Classic seem to return the .Members collection.