Delete Consolidated Elements

Post Reply
ravbidari
Posts: 6
Joined: Fri Jun 23, 2017 4:34 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: 2010

Delete Consolidated Elements

Post by ravbidari »

Hi All,

I have two main dimension hierarchy, regular and alternate hierarchy.
I need delete parents and along with children and ancestor for the ONLY few of the consolidated elements (Parents) from alternate hierarchy.
This alternate hierarchy have multiple parents/rollups under it.

I tried following;

1) Created a dynamic subset MDX to delete and included the consolidated elements and used the WHILE loop
2) Tried the asciiout out of all the elements and then delete - did not work.

Here the need is to delete the alternate hierarchy consolidated elements without the impacting the regular hierarchy.
Any direction in this will help me next steps in achieving the requirement
Thanks
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Delete Consolidated Elements

Post by tomok »

Both of those options "should" work but if you don't code it correctly then nothing is going to work. The only caveat to an MDX subset approach is that you would need to start with the last member and work forward, instead of first to last. Regardless, no one is going to be able to help you without seeing your code as I can almost guarantee you that the reason it doesn't work is because you didn't code it correctly.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ravbidari
Posts: 6
Joined: Fri Jun 23, 2017 4:34 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: 2010

Re: Delete Consolidated Elements

Post by ravbidari »

Hi tomok,
Thanks for reply:

I took the data source as dimension subset and coded

vDimName = 'Account';
vSubset = 'Delete_Consolidation';
IF(SubsetExists(vDimName,vSubset)=1);
SubsetDestroy(vDimName,vSubset);
Endif;
SubsetCreatebyMDX(vSubset, '{EXCEPT({[ACCOUNT].[ACCOUNT_CUSTOM_TM1].CHILDREN}. {[account].[alt_abc].[account].acc_custom_tm1]})};

AND then below while loop:
Counter=1;
Maxcount =DIMSIZ(vDimName) ;
WHILE ( Conter<=MaxCount);
vElement = DIMNM(vDimName,counter);
IF (ELISANC(vDimName, 'All Expenditure Type',vElement) = 1));
SubsetElementInsert(vDimName,vSubset,vElement, 1);
ENDIF;
Counter=Counter+1;
END;

Condition on delete:
IF(DTYPEvDimName,InputeElem)@= 'C');
DIMENSIONDELEMENTDELETE(vDimname,InputElem);
Endif:
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: Delete Consolidated Elements

Post by Steve Rowe »

You've not mentioned if your Cs for the two hierarchies are mutually exclusive. If you have the same Cs in both hierarchies then your code will break both hierarchies.
Technical Director
www.infocat.co.uk
ravbidari
Posts: 6
Joined: Fri Jun 23, 2017 4:34 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: 2010

Re: Delete Consolidated Elements

Post by ravbidari »

Hi,

I am still trying to get there-

Condition on delete:
IF(DTYPEvDimName,InputeElem)@= 'C');
DIMENSIONDELEMENTDELETE(vDimname,InputElem);
Endif:

I have tried using the ELISANC(dimension, element1, element2)
example:IF(DTYPEvDimName,InputeElem)@= 'C' & LISANC(Account, element1, element2)

The problem i am facing the Account (ALT) multiple parents upto 15 levels -i need to make sure i deleted only those levels.
And then i need to make sure leaf level elements and DATA do not get deleted from ALT hierarchy as the leaf level elements are part of regular hierarchy.

Any thoughts-will help me next steps.
Thanks
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Delete Consolidated Elements

Post by lotsaram »

ravbidari wrote:Hi,

I am still trying to get there-

Condition on delete:
IF(DTYPEvDimName,InputeElem)@= 'C');
DIMENSIONDELEMENTDELETE(vDimname,InputElem);
Endif:

I have tried using the ELISANC(dimension, element1, element2)
example:IF(DTYPEvDimName,InputeElem)@= 'C' & LISANC(Account, element1, element2)

The problem i am facing the Account (ALT) multiple parents upto 15 levels -i need to make sure i deleted only those levels.
And then i need to make sure leaf level elements and DATA do not get deleted from ALT hierarchy as the leaf level elements are part of regular hierarchy.

Any thoughts-will help me next steps.
Thanks
If only deleting elements with DTYPE @= 'C' then you can be completely confident that you will not delete data since data is stored only on leaves.

However
1/ as already noted if the C elements you are deleting exist in multiple rollups then you will break all rollups not just the one you are targeting. There is no way to avoid this unless all C elements are uniquely named (or you go to the latest v11/PA2.0 and use real alternate hierarchies).
2/ Your current logic using a forward incrementing loop will miss elements on deletion unless you also subtract from the counter upon deletion. When deleting the standard approach is to start at the maximum and decrement the counter. That way no elements are skipped over.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Delete Consolidated Elements

Post by tomok »

1) Create a new TI with a TM1 subset as the data source

2) In the Prolog tab create a subset based on the following MDX code:

Code: Select all

{TM1DRILLDOWNMEMBER( {[MyDimension].[MyTopLevelMember]}, ALL, RECURSIVE )}
and then convert to a static subset using the SUBSETMDXSET function:

Code: Select all

SUBSETMDXSET(MyDimension, MySubset, '')
3) In the MetaData tab have the following code:

Code: Select all

IF(ELLEV(MyDimension, MyElement) > 0);
  DIMENSIONELEMENTDELETE(MyDimension, MyElement);
ENDIF:
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ravbidari
Posts: 6
Joined: Fri Jun 23, 2017 4:34 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: 2010

Re: Delete Consolidated Elements

Post by ravbidari »

Hi
The below code effects the regular and alternate hierarchy.
IF(ELLEV(MyDimension, MyElement) > 0);
DIMENSIONELEMENTDELETE(MyDimension, MyElement);
ENDIF:

I need to apply conditions to ONLY Alternate hierarchy.
Example:
Account_Alt (TopLevle)
AAA
A1
A2
BBBB
B1
B2
CCCC
C1
C2

Like above the -I have 20 plus consolidated elements list and then under it- I have about 15 plus parents( C-levels) - then leaf level.
I looking delete ONLY 20 consolidated elements list and 15 parents under it-without effecting the leaf levels.

Thank you for your time.
Appreciated suggestions in this regards,
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Delete Consolidated Elements

Post by tomok »

A leaf element has a level of 0 so this IF condition:

Code: Select all

IF(ELLEV(MyDimension, MyElement) > 0);
means all those elements fail and the DIMENSIONDELETELEMENT function does not execute for them.

If this doesn't work for you then it's because you've done a really poor job of explaining your requirements because my code (and I don't mean just the DELETE part, I mean the entire explanation of the subset and all) will do exactly what you asked for, to delete all parent elements underneath an alternate hierarchy.

You could just take the easy way out and add an attribute to the dimension in question so you can flag all the parents in the alternate hiearchy that need deleting and use that to filter the list to delete. Other than that I have no clue what you are asking for.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ravbidari
Posts: 6
Joined: Fri Jun 23, 2017 4:34 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: 2010

Re: Delete Consolidated Elements

Post by ravbidari »

Hi tom,

Thanks:

I did re-test
IF(ELLEV(MyDimension, MyElement) > 0); and it worked.Thank you.

only thing - i did not understand is. SUBSETMDXSET(MyDimension, MySubset, '')
What is the need of ? SUBSETMDXSET function removes the MDX expression from a dynamic subset (Per IBM)
Regards
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: Delete Consolidated Elements

Post by Wim Gielis »

You can turn a dynamic subset into a static subset because you don't want to evaluate the expression anymore. That can improve performance.
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
ravbidari
Posts: 6
Joined: Fri Jun 23, 2017 4:34 pm
OLAP Product: cognos tm1
Version: 10.2.2
Excel Version: 2010

Re: Delete Consolidated Elements

Post by ravbidari »

Post Reply