Delete Consolidated Elements
-
- Posts: 6
- Joined: Fri Jun 23, 2017 4:34 pm
- OLAP Product: cognos tm1
- Version: 10.2.2
- Excel Version: 2010
Delete Consolidated Elements
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
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
-
- 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
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.
-
- 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
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:
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:
- 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
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
www.infocat.co.uk
-
- 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
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
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
-
- 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
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.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
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.
-
- 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
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: and then convert to a static subset using the SUBSETMDXSET function:
3) In the MetaData tab have the following code:
2) In the Prolog tab create a subset based on the following MDX code:
Code: Select all
{TM1DRILLDOWNMEMBER( {[MyDimension].[MyTopLevelMember]}, ALL, RECURSIVE )}
Code: Select all
SUBSETMDXSET(MyDimension, MySubset, '')
Code: Select all
IF(ELLEV(MyDimension, MyElement) > 0);
DIMENSIONELEMENTDELETE(MyDimension, MyElement);
ENDIF:
-
- 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
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,
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,
-
- 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
A leaf element has a level of 0 so this IF condition:
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.
Code: Select all
IF(ELLEV(MyDimension, MyElement) > 0);
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.
-
- 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
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
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
-
- 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
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
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