How to get top parent of an element ?

Post Reply
CellPutN
Posts: 18
Joined: Mon Oct 01, 2018 1:50 pm
OLAP Product: TM1
Version: 10.2.20500.75
Excel Version: 2016
Location: Montreal, Canada

How to get top parent of an element ?

Post by CellPutN » Sun Feb 10, 2019 2:48 pm

Hello all,

I wonder if there's a more efficient way to retrieve the top parent (by top parent I mean the 'highest' consolidated element) of an element.

In other word, for all leaf-level elements in a source dimension, I want to retrieve the top parent/ancestor and recreate it in a target dimension.

Code: Select all

#==================================================
#  Retrieve Top Parent and Create it if doesn't exist  
#  Maximum of 4 level is allowed here.
#==================================================

sParent1 = ELPAR( sDimSource, vElement, 1 );
IF(ELPARN( sDimSource, sParent1 ) <> 0 );
    sParent2 = ELPAR( sDimSource, sParent1, 1 );
    IF(ELPARN( sDimSource, sParent2 ) <> 0 );
        sParent3 = ELPAR( sDimSource, sParent2, 1 );
        IF(ELPARN( sDimSource, sParent3 ) <> 0 );
            sParent4 = ELPAR( sDimSource, sParent3, 1 );
            sTopParent= sParent4;
        ELSE;
            sTopParent = sParent3;
        ENDIF;
    ELSE;
        sTopParent = sParent2;
    ENDIF;
ELSE;
    sTopParent = sParent1;
ENDIF;

IF(DIMIX( sDimTarget, sTopParent ) = 0);
    DimensionElementInsertDirect( sDimTarget, '', sTopParent, 'C' );
ENDIF;
Thanks,
Jack

User avatar
macsir
Community Contributor
Posts: 591
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: How to get top parent of an element ?

Post by macsir » Sun Feb 10, 2019 8:08 pm

Use loop to get it
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

lotsaram
MVP
Posts: 3210
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to get top parent of an element ?

Post by lotsaram » Mon Feb 11, 2019 6:49 am

I think there is a pretty easy way to get your result via MDX
E.g.

Code: Select all

{Tail( {Ascendants([Dimension].[Hierarchy].[Element])}, 1 )} 
In TM1 an element may have multiple parents within a hierarchy so if there are multiple parent branches you may need the intersect between the ancestors and all top node elements in the dimension ...

Code: Select all

{Intersect( 
    {Ascendants([Dimension].[Hierarchy].[Element])}, 
    {Filter( {TM1SUBSETALL( [Dimension].[Hierarchy] )}, [Dimension].[Hierarchy].CurrentMember.Parent.Name="")} 
)}
Replace "Element" with the actual element name you wish to inspect then create a temporary subset with SubsetCreateByMDX. I can't see why this won't work.

But I would also kinda want to ask why do you need to do this? As in this requirement would indicate the dimension structure is a mess. In a well structured system you should KNOW what the top node(s) are for any given element.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

CellPutN
Posts: 18
Joined: Mon Oct 01, 2018 1:50 pm
OLAP Product: TM1
Version: 10.2.20500.75
Excel Version: 2016
Location: Montreal, Canada

Re: How to get top parent of an element ?

Post by CellPutN » Mon Feb 11, 2019 3:17 pm

Thanks Lotsaram !

And it was for a very specific task for one of my customer ;)

User avatar
macsir
Community Contributor
Posts: 591
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: How to get top parent of an element ?

Post by macsir » Mon Feb 11, 2019 9:10 pm

Looks like Ascendants only returns all parents in the first rollup?
1.PNG
1.PNG (2.83 KiB) Viewed 402 times
2.PNG
2.PNG (2.03 KiB) Viewed 402 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

lotsaram
MVP
Posts: 3210
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to get top parent of an element ?

Post by lotsaram » Mon Feb 11, 2019 9:55 pm

macsir wrote:
Mon Feb 11, 2019 9:10 pm
Looks like Ascendants only returns all parents in the first rollup?1.PNG2.PNG
Yes seems to be the case. I just checked with Ancestors function and seems to be the same restriction.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
macsir
Community Contributor
Posts: 591
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: How to get top parent of an element ?

Post by macsir » Tue Feb 12, 2019 8:52 pm

lotsaram wrote:
Mon Feb 11, 2019 9:55 pm
macsir wrote:
Mon Feb 11, 2019 9:10 pm
Looks like Ascendants only returns all parents in the first rollup?1.PNG2.PNG
Yes seems to be the case. I just checked with Ancestors function and seems to be the same restriction.
Thanks for confirming. I am thinking if we can get same MDX function as what this "rollup" button does, it would be good. It can return all parents obviously.
1.PNG
1.PNG (17.66 KiB) Viewed 369 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: How to get top parent of an element ?

Post by PavoGa » Wed Feb 13, 2019 12:46 pm

This will do it:

Code: Select all

FILTER( FILTER( TM1SUBSETALL([test_ANC]), [test_ANC].currentmember.parent.name = ''), 
    TM1TupleSize(INTERSECT({[test_ANC].[e]}, DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)
This assumes we are looking for all the top level consolidations of 'e'.

And this:

Code: Select all

FILTER( FILTER( TM1SUBSETALL([test_ANC]), [test_ANC].currentmember.parent.name = ''), 
    TM1TupleSize(INTERSECT(TM1FILTERBYLEVEL(TM1SUBSETALL( [test_ANC]), 0), DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)
returns the top level consolidations for all leaf elements. Would only eliminate top level elements without any leaves...
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: How to get top parent of an element ?

Post by PavoGa » Wed Feb 13, 2019 1:17 pm

This returns ALL the ancestors of 'e':

Code: Select all

FILTER( EXCEPT(TM1SUBSETALL([test_ANC]), TM1FILTERBYLEVEL(TM1SUBSETALL([test_ANC]), 0)), 
    TM1TupleSize(INTERSECT({[test_ANC].[e]}, DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)
Ty
Cleveland, TN

User avatar
macsir
Community Contributor
Posts: 591
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: How to get top parent of an element ?

Post by macsir » Thu Feb 14, 2019 12:59 am

PavoGa wrote:
Wed Feb 13, 2019 1:17 pm
This returns ALL the ancestors of 'e':

Code: Select all

FILTER( EXCEPT(TM1SUBSETALL([test_ANC]), TM1FILTERBYLEVEL(TM1SUBSETALL([test_ANC]), 0)), 
    TM1TupleSize(INTERSECT({[test_ANC].[e]}, DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)
Thanks, PavoGo. Learnt a lot from your MDX. Follow your thought, I think we can do it in a simpler way.

Without self

Code: Select all

EXCEPT(  EXCEPT(   TM1SUBSETALL([test2]), TM1FILTERBYLEVEL(TM1SUBSETALL([test2]), 0 ) ),  { DESCENDANTS( {[test2].[d]} )  }   )

With self

Code: Select all

EXCEPT(  EXCEPT(   TM1SUBSETALL([test2]), TM1FILTERBYLEVEL(TM1SUBSETALL([test2]), 0 ) ),  { DESCENDANTS( {[test2].[d]}, 0, AFTER )  }   )
But still, I couldn't find a way to return its all parents only ( excluding any grandparents ), just like the way the button is doing. :?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: How to get top parent of an element ?

Post by PavoGa » Thu Feb 14, 2019 2:25 pm

I thought you were trying to find the top or root level consolidations for a given leaf element. The MDX queries you state as simpler specifically reference a consolidation ('d') according to the example you provided while I thought you were interested in determining ancestors of a leaf element. I believe these may provide what you are looking for (assuming you are really looking for ancestors of a leaf) only if (1) you know the parent of the targeted leaf element, (2) it only has one parent, (3) want all consolidations except the one specified, not just the top level and (4) there are no other consolidation lines without that leaf element. There may be other problems, but this is just off the top of my head.

It seems your queries work specifically for your example. I provided you with queries that work for any given leaf element even if the dimension has a ragged hierarchy.
Ty
Cleveland, TN

User avatar
macsir
Community Contributor
Posts: 591
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: How to get top parent of an element ?

Post by macsir » Thu Feb 14, 2019 8:25 pm

Yes, you are right. My query is not right. Anyway, learnt how to use TM1TupleSize functon. :P
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: How to get top parent of an element ?

Post by PavoGa » Thu Feb 14, 2019 8:34 pm

macsir wrote:
Thu Feb 14, 2019 8:25 pm
Yes, you are right. My query is not right. Anyway, learnt how to use TM1TupleSize functon. :P
Good deal! Glad I could help.
Ty
Cleveland, TN

Post Reply