Delete elements quickly
- Sep. 14, 2022
Introduction
In the past I have seen many instances of code to delete elements, or serving similar purposes, using loops. While loops are certainly neither bad or particularly slow (to the contrary), there are better options. Consider that Bedrock TM1 also relies on loops (e.g. this process with functions like HierarchyElementDelete).
Example
You must have seen code like this in the Prolog tab of a Turbo Integrator process. It is used to delete all consolidated elements in a dimension:
## Delete consolidated elements
####
iLoop=DIMSIZ(pDim);
WHILE(iLoop>=1);
vElement=DIMNM(pDim,iLoop);
IF(ELLEV(pDim,vElement)>=1);
DimensionElementDelete(pDim,vElement);
ENDIF;
iLoop=iLoop-1;
END;
iLoop=DIMSIZ(pDim);
WHILE(iLoop>=1);
vElement=DIMNM(pDim,iLoop);
IF(DTYPE(pDim,vElement)@='C');
DimensionElementDelete(pDim,vElement);
ENDIF;
iLoop=iLoop-1;
END;
This code loops over all elements in a dimension (twice !) and deletes the elements of type consolidation. It's fair to say that I hate this code. I see the following disadvantages/issues:
- Quite a lot of code is involved, error-prone.
- The code can lead to wrong results. For instance, if one wants to delete all level 0 elements, and there is a consolidation containing only level 0 elements, the code will remove that one too since it becomes a level 0 after deleting its children.
- Looping can be slower if other options exist (see below)
- There is always the risk of creating an endless loop, causing issues on the server. For instance, if the developer is distracted, he will write +1 to increment the looping variable rather than -1 to decrement it.
But I found a much easier and better alternative:
vDim = 'Customer'; vMDX = 'Except( TM1SubsetAll([' | vDim | ']), TM1FilterByLevel( TM1SubsetAll([ ' | vDim | ']), 0 ))'; SubsetCreateByMDX( 'tmp_', vMDX, vDim, 1 ); DimensionDeleteElements( vDim, 'tmp_' );
This code has several advantages:
- Very elegant and succinct code, no distraction and no (looping or variables) errors.
- In my tests it is at least quicker than looping over elements, and often much quicker.
- The subset that I use is both temporary and dynamic.
- If no consolidated elements exist in the dimension, the code does not raise an error.
Information on DimensionDeleteElements can be found here.
Some people already contacted me to suggest the use of functions like HierarchyDeleteAllElements. While this can be useful too, the above code proposal is not just limited to deleting elements to unwind a dimension. You should view this contribution in a much broader sense. For instance, deleting elements that don't have data in a certain cube and that are marked with a certain attribute value. Or, delete only String type elements. In many cases, loops are not necessary and can be replaced by a neat and elegant MDX solution together with 1 line of code to delete the returned elements.
Feel free to use functions like HierarchyDeleteElements to only target defined hierarchy instead of the dimension/same-named hierarchy. This exercise is left to the reader, the MDX statement will very likely change as well.
Reminder: deleting level 0 elements can lead to a loss of data ! String data can also be lost, and don't forget text data posted against consolidated elements. Be careful !
Enjoy your day !
