MDX to return duplicates in a consolidation

[Steve Rowe]

Hi,
I want to use MDX to return duplicates in a consolidation, i.e. elements that roll into a C multiple times.

The MDX primer has an example but states

Note: the following section does not work in v9.1 SP2, but does work in v9.0. Your mileage may vary.

The example reads
EXCEPT (
TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE ),
TM1SUBSETALL( [Product] ), ALL)

My test of this reads
EXCEPT (
TM1DRILLDOWNMEMBER( {[temp].[a]}, ALL, RECURSIVE ),
TM1SUBSETALL( [temp] ), ALL)

against this dimension

dim.png (9.77 KiB) Viewed 2707 times

but an empty set is incorrectly returned.

So does anyone know if this has worked between v9.1 SP 2 and now or have an alternative approach? Has anyone tried to get it fixed?

TIA

[lotsaram]

By "duplicates within a consolidation" I assume you mean ultimate descendants of a rollup appearing multiple times and not direct children?

This is the standard MDX snippet I use for this purpose. It will work for the example you gave and return a set of e,f,g for the rollup a

Code: Select all

{FILTER( 
  {TM1FilterByLevel( TM1SubsetAll( [Dimension] ), 0 )}, 
  COUNT( {INTERSECT({Descendants([Dimension].[Rollup],10,LEAVES)}, {[Dimension].CurrentMember}, ALL )} ) > 1 
)}

Note that this will be slow for any "large" dimension as MDX simply doesn't perform that great over large sets. (By "large" I mean any dimension with say > 5000 elements). For larger dimensions I usually use TI not MDX as the MDX could take several minutes for a large dimension whereas TI will be seconds.
Also obviously the MDX just counts duplicates in the rollup and doesn't take element weighting into account. For (admittedly rarer) cases where an element might appear multiple times but the net weighted contribution to the rollup is still 1 then the MDX would give incorrect results, but this would depend on how you define what is a "duplicate".

And for the inverse purpose to find elements which haven't been allocated a parent at all in the rollup. From your example this would return a set of the element c.

Code: Select all

{FILTER( 
  {TM1FilterByLevel( TM1SubsetAll( [Dimension] ), 0 )}, 
  COUNT( {INTERSECT({Descendants([Dimension].[Rollup],10,LEAVES)}, {[Dimension].CurrentMember}, ALL )} ) = 0 
)}

Admin : Edited to correct missing bracket in MDX

[Steve Rowe]

Great thanks a lot, much appreciated.

Any views as to if the MDX I was trying to use should work? (If the functions worked as documented!)
Cheers

[lotsaram]

Great thanks a lot, much appreciated.

Any views as to if the MDX I was trying to use should work? (If the functions worked as documented!)
Cheers

Reading the documentation for Except https://msdn.microsoft.com/en-us/library/ms144900.aspx it sounds like the All argument should work to return duplicates but from my testing it doesn't look like this works in TM1. Didn't test in SSAS.