TM1 Forum

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

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.

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:

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.

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

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.

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:

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,

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.

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

You can turn a dynamic subset into a static subset because you don't want to evaluate the expression anymore. That can improve performance.

Thanks
I am now reading
http://www.wimgielis.com/tm1_subsetcrea ... set_EN.htm