TM1SUBSETALL vs .MEMBERS

[davas]

Hi,

I'm having an issue where by a number of duplicate items are appearing in a dynamic subset generated using the TM1SUBSETALL function. If I look at the 'All Members' subset, the hierarchy looks OK but the subset all one has members repeated at different levels.

My question is what is the TM1SUBSETALL function supposed to return and how is the behavior different to referencing .MEMBERS instead?

Is there a best practice around picking one or the other?

Documentation just declares TM1SUBSETALL : "This TM1® -specific MDX function returns the TM1 subset All of <dimname>. "

Thanks,

Chris

[David Usherwood]

From Phil Bichard's excellent MDX guide (http://bihints.com/book/export/html/68):

The basis for many queries, this returns (almost, see below) the entire dimension, which is the equivalent of clicking the ‘All’ button in the Subset Editor.
TM1SUBSETALL( [Product] )

Note that only the final instance in the first hierarchy of members that are consolidated multiple times is returned.

The Members function, on the other hand, delivers the full dimension, duplicates included:
[Product].Members

[babytiger]

I think it depends on what your starting point is.

I have always used TM1SubsetAll([Dimension name]) to return a complete list of elements of the dimension, and the list is unique (ie. no dups). So, not too sure how you've build your MDX.

[davas]

Hi,

Thanks for your replies. It seems I was experiencing the exact opposite of the behavior of .MEMBERS and TM1SUBSETALL...

In my training and work with others, I've only really seen TM1SUBSETALL be used.

The dimension in PM (where some changes were made that highlighted this issue) looks fine but when doing a new subset there are elements appearing in multiple locations in the hierarchy (changes made to the consolidations and levels so the elements are shown in new location and old).

I've tried editing the dimension structure in Architect by hiding duplicates etc. but the dimension reverts when I re-open after saving.

UPDATE: I think I've figured out the cause of this behavior... there was a manual sort set on the dimension - I've switched to auto - hierarchy and it looks OK again.

[Wim Gielis]

Hello,

I always use TM1SubsetAll instead of .Members.
I don't think I used the latter in the past, even though I know the function exists.