MDX Get children with Multiple Parents

Post Reply
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

MDX Get children with Multiple Parents

Post by MarenC »

Hi,

I was wondering if it was possible to write some MDX to list all leaf elements within a dimension that have more than, say, 2 parents?
Have tried a few things but it doesn't look possible to me.

If I was doing this in a TI process I would loop over the dimension and do an elparn against each leaf (which is an okay alternative).

I assume that this would have many caveats, such as dimension structure, but for the sake of simplicity, lets say this was a simple dimension with an All elements root consolidation and a single rollup attached to this root.

Also, for now, don't worry about the parent level.

Maren
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Get children with Multiple Parents

Post by gtonkin »

Are you stalking me? Did this just yesterday to meet a requirement:

Basically looking at all the elements in a rollup and counting those that appear multiple times.
You could likely add your own TM1FilterByLevel where/if necessary:

Code: Select all

FILTER
(
  {DESCENDANTS({[<dim>].[<hier>].[<rollup>]})},
  COUNT(
    {INTERSECT(
	  {DESCENDANTS({[<dim>].[<hier>].[<rollup>]})},
	  {[<dim>].[<hier>].CurrentMember},
	  ALL)}
	)>1
)
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: MDX Get children with Multiple Parents

Post by Steve Rowe »

Nice, that's a good one!
Technical Director
www.infocat.co.uk
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX Get children with Multiple Parents

Post by MarenC »

Hi George,

fabulous! I wasn't stalking you, honest, but doesn't seem like the worst idea in the world when you can help with things like this :D

Just a note: I added a union and except to your code in order to remove the elements showing multiple times.

I think I know where I went wrong with trying to solve this, I wasn't using the actual rollup in any of my attempts, as in the back of my mind, I thought doing this over multiple rollups would be ideal. I only put a single rollup in my post for simplicity sake, but now thinking about it, one rollup is all I need anyway!

Thanks again

Maren
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Get children with Multiple Parents

Post by gtonkin »

Glad it helped.

I left the duplicates in as I have ideas of adding more MDX around them to show their parents in the rollup so that it is easy to track down the nonsense. A project for another day...
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX Get children with Multiple Parents

Post by Wim Gielis »

To find the leaf elements that have multiple parents in a rollup, you can consider:

Code: Select all

Filter( TM1FilterByLevel( Descendants( [<dim>].[<hier>].[<rollup>] ), 0 ), TM1TupleSize( Intersect( Descendants( [<dim>].[<hier>].[<rollup>] ), {[<dim>].[<hier>].CurrentMember}, All ).Item(1)) = 1 )
This features the functions TM1TupleSize and Item, which we (I) don't use every day.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX Get children with Multiple Parents

Post by Wim Gielis »

Should you want to be presented the list of MUNs that apply, it is the answers above.
Should you want to be presented the list of unique element names that apply (keep only one of the results of each one):

Code: Select all

Generate( Filter( TM1FilterByLevel( Descendants( [<dim>].[<hier>].[<rollup>] ), 0 ), TM1TupleSize( Intersect( Descendants( [<dim>].[<hier>].[<rollup>] ), {[<dim>].[<hier>].CurrentMember}, All ).Item(1)) = 1 ),
{ StrToMember( "[<dim>].[<hier>].[" + [<dim>].[<hier>].CurrentMember.Name + "]" ) })
This is a trick of mine to convert a set of duplicate element names, but unique MUNs, to what folks commonly understand when they talk about unique elements.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply