Detecting duplicate members in multiple hierarchies

[JamiseBondi]

Hi,

I came across a link which explained the use of an MDX statement that would detect when an element has been duplicated under multiple hierarchies:

I can't find the original link but this is the excerpt:

"This query returns members that have been consolidated twice or more at some point under the given consolidated member – this will often mean there has been an accidental double-count.
EXCEPT (
TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE ),
TM1SUBSETALL( [Product] ), ALL)
It will return one instance of the multi-consolidated member for each time it is consolidated greater than once – i.e. if it has been consolidated 4 times then it will return 3 instances.
This is due to the fact that TM1SUBSETALL( [Product] ) will only return one instance of a member that has been consolidated multiple times while the TM1DrilldownMember function will return all the instances. You are reminded that [Dimension].[Member] is actually a shortcut that usually works in TM1 but because the MDX specification allows for member names to be non-unique within a dimension the full address of a member is actually [Dimension].[Parent1].[Parent2]…[Member]. Therefore more specific references to duplicate members may be needed, for example [Product].[Demand Loan].[MidasHCBK] will address a different instance of MidasHCBK than would [Product].[Discount Loan].[MidasHCBK]. In this case, with the Except function, they are treated as if they are different member names altogether."

I have found that this isn't working as expected, even after running the RefreshMDXHierarchy TI process. The TM1SubsetAll function seems to be fetching all instances of a duplicated member, not just the first one.
I'm using TM1 10.1 in this instance.

Does anyone have any useful methods of trying to detect duplicated members/elements that appear in multiple hierarchies in a dimension? I'm looking at how to do this either with a TI or an MDX statement (am open to other ideas as well).
Thanks.

[declanr]

This topic has been discussed very recently:

http://www.tm1forum.com/viewtopic.php?f=3&t=9156

[JamiseBondi]

Thanks Declan,

Useful thread!
cheers.