Delete all elements with a selected attribute value

[normanbobo]

We have a dimension which contains 5 levels. The leaf level elements exist in a main hierarchy/rollup structure of consolidation elements. Selected leaf elements also exist in two ore more alternate hierarchies/rollup structures using other consolidation elements. There is an attribute on the dimension, strProjTaskHierTypeAttrNm, which is populated when the dimension is built. The values placed in this attribute indicate whether a consolidation element is part of the main "regular" hierarchy or is part of one of the alternate consolidation hierarchies.

I am attempting to build a TI process which spins through the dimension elements and, based on the special attribute, attempts to delete only the alternate consolidation elements, leaving intact the "regular" hierarchy elements and the leaf elements. We are getting some elements deleted, but not all. There seems to be an issue with the DNEXT function not working correctly. I've been tracing the code, but can't get a handle on why some alternate consoildation elements are being left.

Here's the relevant code section of code. Note that this is not the entire code. Some variables have been set prior to this. There are also extra statements in this version just for testing/tracing. This code executes in the prolog of a TI process.

The basic logic is:

1) Start at the first element.
2) Loop until there are no more elements

a) Is the current element part of the standard hierarchy?
1) If no,
a) perform a DNEXT to get the next element in the dimension.
b) delete the current element.
2) If yes, perform a DNEXT

Code: Select all

# Initialize the loop.
# The All Element is known to be the first element in the dimension based on how the dim is initially built.

strProjTaskLoopElement = strProjTaskDimAllElemNm;

# Loop through all of the elements in the project task dimension.

While (strProjTaskLoopElement @<> '');

           TextOutput(strTextOutputFileName, 'strProjTaskLoopElement: ', strProjTaskLoopElement);

           nbrProjTaskLoopElemIndexNbr  = DIMIX(strProjTaskDimNm, strProjTaskLoopElement);
           TextOutput(strTextOutputFileName, ' nbrProjTaskLoopElemIndexNbr: ',
                                                                      NUMBERTOSTRING(nbrProjTaskLoopElemIndexNbr));

           strProjTaskLoopElemHierType    = ATTRS(strProjTaskDimNm, strProjTaskLoopElement,strProjTaskHierTypeAttrNm );

           TextOutput(strTextOutputFileName, 'strProjTaskLoopElemHierType: ', strProjTaskLoopElemHierType);

           # Is this element in the "standard" Project Task hierarchy?
           IF           (strProjTaskLoopElemHierType @<> strProjTaskRegularHierType);

                         # It is not one of standard elements, thus it is part of a portfolio alternate hierarchy

                         # Save off the element to be deleted.

                         strProjTaskDeleteElement = strProjTaskLoopElement;
                         TextOutput(strTextOutputFileName, 'strProjTaskDeleteElement: ', strProjTaskDeleteElement);

                         # Get the next element
                         # This must be done prior to deleting the element b/c DNEXT needs the current element which is about to be deleted.

                         strProjTaskLoopElement = DNEXT(strProjTaskDimNm, strProjTaskLoopElement);

                         # Delete the alt hier element

                         DIMENSIONELEMENTDELETE(strProjTaskDimNm, strProjTaskDeleteElement);

           ELSEIF  (strProjTaskLoopElemHierType @= strProjTaskRegularHierType);

                         # It is one of the standard elements.   No action needs to be taken.  Skip to the next element.

                         # Move to the next element and increment the while loop counter
                         strProjTaskLoopElement = DNEXT(strProjTaskDimNm, strProjTaskLoopElement);

           ENDIF;


# End the While

END;

Does anyone have any ideas why some elements are deleted but many elements are not? Any ideas for other methods to achieve the same goal?

Thanks.

[Oratia623]

I would suggest that you are still getting tripped up by the deleted elements screwing with your DNEXT function.

You could cycle through the dimension, checking for matching attributes, and insert these into a temp dimension.
Then cycle through the elements in this temp dim, and delete each of those out of your main dim.
maybe overkill but should work.

Use the following untested code as an example.

Code: Select all

strTempDim = 'TempDim:
DimensionDestroy( strTempDim );
DimensionCreate(strTempDim );

nDimsiz01 = DIMSIZ(strProjTaskDimNm);
i = 1;
ii = 1;
WHILE (i <= nDimsiz01);
   strProjTaskLoopElement = DIMNM(strProjTaskDimNm, i);
   strProjTaskLoopElemHierType = ATTRS(strProjTaskDimNm, strProjTaskLoopElement,strProjTaskHierTypeAttrNm );
   IF (strProjTaskLoopElemHierType @<> strProjTaskRegularHierType);
       DimensionElementInsert(strTempDim, ii, strProjTaskLoopElement,'N');
       ii = ii + 1;
   End If;
   i = i +1
END;

nDimsiz02 = DIMSIZ(strTempDim);
i = 1;
ii = 1;
WHILE (i <= nDimsiz02);
   strTempLoopElement = DIMNM(strTempDim, i);
   DimensionElementDelete(strProjTaskDimNm, strTempLoopElement);   
   i = i +1
END;

DimensionDestroy( strTempDim );

[Alan Kirk]

I am attempting to build a TI process which spins through the dimension elements and, based on the special attribute, attempts to delete only the alternate consolidation elements, leaving intact the "regular" hierarchy elements and the leaf elements. We are getting some elements deleted, but not all. There seems to be an issue with the DNEXT function not working correctly.

I haven't read through your code in detail, but I concur with Paul's assessment; it's likely to be that when you delete an element it screws with your position and therefore with the value that DNext returns. The general way around this, whether it's working with dimensions or deleting rows in Excel worksheets, is to delete from the end back, not the front forward. Get the dimension size with DimSiz, use DimNm to get that element, then step backwards from that subtracting 1 from your counter as you move through until you reach element 1.

[lotsaram]

Fundamentally your approach is flawed. Personally I have never heard of anyone attempting to use DNEXT within a TI process as part of a while loop control. Simpler is usually better, especially when coding. You can fix your original code quite easily by using an incremented counter to loop through and if you delete an element then simply subtract 1 from the counter. This would be the usual standard approach.

(I have taken the liberty of simplifying the code a tiny bit by just starting from the 1st element and not having a new variable for the element being deleted)
Usual cover statement: this code written blind so no garuntees, there may be typos but it should work.

Code: Select all

# Initialize the loop.
# Start at the 1st element
nLoopIndex = 1;
strProjTaskLoopElement = DIMNM(strProjTaskDimNm, nLoopIndex)
# If the index of the dimension is exceeded then DIMNM will return a blank string so this will not otherwise affect the While loop break test you had

# Loop through all of the elements in the project task dimension.

While (strProjTaskLoopElement @<> '');

           TextOutput(strTextOutputFileName, 'strProjTaskLoopElement: ', strProjTaskLoopElement);
           TextOutput(strTextOutputFileName, ' nbrProjTaskLoopElemIndexNbr: ', NUMBERTOSTRING(nLoopIndex));

           strProjTaskLoopElemHierType = ATTRS(strProjTaskDimNm, strProjTaskLoopElement,strProjTaskHierTypeAttrNm );
           TextOutput(strTextOutputFileName, 'strProjTaskLoopElemHierType: ', strProjTaskLoopElemHierType);

           # Is this element in the "standard" Project Task hierarchy?
           IF(strProjTaskLoopElemHierType @<> strProjTaskRegularHierType);
                         # It is not one of standard elements, thus it is part of a portfolio alternate hierarchy
                         # Save off the element to be deleted.

                         TextOutput(strTextOutputFileName, 'strProjTaskDeleteElement: ', strProjTaskLoopElement);

                         # Delete the alt hier element
                         DIMENSIONELEMENTDELETE(strProjTaskDimNm, strProjTaskLoopElement);
                         nLoopIndex = nLoopIndex - 1;

           ELSEIF(strProjTaskLoopElemHierType @= strProjTaskRegularHierType);
                         # It is one of the standard elements.   No action needs to be taken. 

           ENDIF;

           # Increment the While loop control counter
           nLoopIndex = nLoopIndex + 1;

# End the While

END;

HOWEVER for what you are doing a while loop on the prolog really isn't the way to go. A much better solution to the same problem woudl be to set subset all of the dimension as the data source for the process and do the attribute test and element deletion on the meta data tab. Then there is no need for the while loop at all, your entire code will be reduced to a handful of lines (and could be 2 lines if your do away with the logging.) The code would also run faster as the dimension would then only be recompiled at at the completion of all the element deletions and not after each event.

Code: Select all

# Much simpler on the META DATA tab as no need for a loop at all
# strProjTaskLoopElement is the name given in this case to the only variable on the variables tab
# Assume dimension name, attribute name, constant for standard hierarchy identifier, etc. are set on the prolog the same as before

# Is this element in the "standard" Project Task hierarchy?
strProjTaskLoopElemHierType = ATTRS(strProjTaskDimNm, strProjTaskLoopElement, strProjTaskHierTypeAttrNm );

IF(strProjTaskLoopElemHierType @<> strProjTaskRegularHierType);
   # It is not one of standard elements, thus it is part of a portfolio alternate hierarchy and will be deleted
   DIMENSIONELEMENTDELETE(strProjTaskDimNm, strProjTaskLoopElement);
ELSE;
   # It is one of the standard elements.   No action needs to be taken. 
ENDIF;

[garry cook]

Just a thought but assuming you just want to delete any elements with a certain attribute value, wouldn't it be easier to just create an MDX subset and then use that as the data source to delete all elements on the data source?

You'd only need one line of code then

[lotsaram]

I kind of agree but it's better not to use dynamic subsets as TI data sources.

A TI to first create a static subset based on the attribute to then use as the data source would require more code and one additional step, so from a pure efficiency point of view you might as well just rattle through the dimension the one time and do the attribute check and deletion in one go.

[normanbobo]

I would suggest that you are still getting tripped up by the deleted elements screwing with your DNEXT function.

You could cycle through the dimension, checking for matching attributes, and insert these into a temp dimension.
Then cycle through the elements in this temp dim, and delete each of those out of your main dim.
maybe overkill but should work.

I like this idea from the standpoint that it does not rely on the indexes staying in order as I perform the deletes. It uses an external input to drive the process, an input which cannot be affected by the deletes.

The general way around this, whether it's working with dimensions or deleting rows in Excel worksheets, is to delete from the end back, not the front forward. Get the dimension size with DimSiz, use DimNm to get that element, then step backwards from that subtracting 1 from your counter as you move through until you reach element 1.

Since the elements we need to keep in the dimension (the "regular hierarchy" elements) are always at the start of the dimension, I could first loop through the elements to find the last "regular" element. I could then loop backwards from the end to that element.

How does looping backwards through the dimension solve the problem? I still don't trust what is happening to the indexes during the deletes, so am hesitant about this approach.

Fundamentally your approach is flawed. Personally I have never heard of anyone attempting to use DNEXT within a TI process as part of a while loop control. Simpler is usually better, especially when coding. You can fix your original code quite easily by using an incremented counter to loop through and if you delete an element then simply subtract 1 from the counter. This would be the usual standard approach.

Actually, it was originally coded using a similar counter process which was decremented with each delete. I may have had some bug in the original version, but that process was certainly unreliable. The version I provided was an attempt to not rely on the index numbers.

HOWEVER for what you are doing a while loop on the prolog really isn't the way to go. A much better solution to the same problem woudl be to set subset all of the dimension as the data source for the process and do the attribute test and element deletion on the meta data tab. Then there is no need for the while loop at all, your entire code will be reduced to a handful of lines (and could be 2 lines if your do away with the logging.) The code would also run faster as the dimension would then only be recompiled at at the completion of all the element deletions and not after each event.

Code: Select all

# Much simpler on the META DATA tab as no need for a loop at all
# strProjTaskLoopElement is the name given in this case to the only variable on the variables tab
# Assume dimension name, attribute name, constant for standard hierarchy identifier, etc. are set on the prolog the same as before

# Is this element in the "standard" Project Task hierarchy?
strProjTaskLoopElemHierType = ATTRS(strProjTaskDimNm, strProjTaskLoopElement, strProjTaskHierTypeAttrNm );

IF(strProjTaskLoopElemHierType @<> strProjTaskRegularHierType);
# It is not one of standard elements, thus it is part of a portfolio alternate hierarchy and will be deleted
DIMENSIONELEMENTDELETE(strProjTaskDimNm, strProjTaskLoopElement);
ELSE;
# It is one of the standard elements. No action needs to be taken. 
ENDIF;

This is definitely an improvement from the standpoint that it uses the Metadata tab, which would be more in keeping with overall Turbo Integrator design and expectations -- I don't like going "against the grain" with a tool. (I originally hesitated to use the prolog tab in the first place, but ignored those inhibitions as I simply moved forward with all speed). As in the suggestion from Oratio63, it also does not depend on the index numbers during the delete process. It is certainly less code as well. I am a little concerned with this process in that it still uses the dimension as an input while performing the deletes on the same dimension.

The code would also run faster as the dimension would then only be recompiled at at the completion of all the element deletions and not after each event.

Interesting. Please explain further. What triggers a re-compile of the dimension? Each delete? How does using the metadata tab (as opposed to the prolog tab) reduce the number of recompiles? Would recompiles in any way explain how the dimension element order was being changed in seeming unpredictable ways?

Just a thought but assuming you just want to delete any elements with a certain attribute value, wouldn't it be easier to just create an MDX subset and then use that as the data source to delete all elements on the data source?

You'd only need one line of code then

See comments below.

I kind of agree but it's better not to use dynamic subsets as TI data sources.

A TI to first create a static subset based on the attribute to then use as the data source would require more code and one additional step, so from a pure efficiency point of view you might as well just rattle through the dimension the one time and do the attribute check and deletion in one go.

I am not crazy about a multi-step process either. I would rather keep it all in one TI process if possible/feasible.

I suppose, though, that the process could create the dynamic subset in the prolog tab and then change the DimensionDatasourceSubset variable in the prolog tab. This would change the input and the process could use the metadata tab to perform the deletes. One advantage to this is that it would only cycle through the elements which would need to be deleted. That would eliminate the need for the "IF" statement in the metadata tab. However, the cost savings would likely only be seconds.

Interesting comment, lotsaram, that dynamic subsets should not be used as TI data sources. Why?

[lotsaram]

My guess would be that if the process was originally based on using a looping counter and was unreliable then you probably just had a bug in the logic around incrementing or reducing the counter. Doesn't matter now as you're on the better path of using the meta data tab!

What triggers a re-compile of the dimension? Each delete? How does using the metadata tab (as opposed to the prolog tab) reduce the number of recompiles? Would recompiles in any way explain how the dimension element order was being changed in seeming unpredictable ways?

You can make meta data changes on the prolog but with each event be it a DimensionElementInsert, delete or whatever the object is recompiled (and the file system object itself is also physically saved to disk as TM1 saves meta data changes in real time, it is only cube data where the in memory copy is not synced with the file system copy unless instructed). However the meta data tab as the name implies has a special purpose and a new copy of any changed meta data is held in memory only until the data source is closed on completion of processing the meta data, it is at that point only that the base model object is updated in memory and saved to disk. Therefore is is much more efficient to use the meta data tab. Also to answer your other question the data source being processed is the dimension (or subset actually) from the base model which is unchanged during execution, the elements only disapear as a single batch and the dimension indexes get updated at the same time once processing of the tab has finished. Versus a while loop on the prolog where the elements are deleted one by one and all index values have to be updated on each cycle through the loop where an element is deleted.

Interesting comment, lotsaram, that dynamic subsets should not be used as TI data sources. Why?

This is widely known but I guess not well known (or maybe the other way around!) Dynamic subsets basically violate the rule of meta data not changing on the meta data tab. Let me explain; MDX is very flexible, therefore dynamic subsets in theory can be dependent on any meta data or any cube data anywhere within a TM1 model. Rather than maintaining an exhaustive dependency tree for each and every dynamic subset which would or could presumably be a very high overhead exercise, TM1 takes the easy way out and says "well the dynamic subset might have changed so I'll re-evaluate it". Thus for every record processed on the meta data or data tab the dynamic subset will be recompiled and saved. For small dimensions and simple MDX statements this is a small burdon as TI is so fast but for MDX statements that take a while to run the performance difference in processing a static versus equivalent dynamic subset can be enormouns.

I hope that makes sense. From the length and thought process of your reply I'm thinking that it should have.

[Wim Gielis]

Hi there,

Almost certainly, I would use one of these 2 approaches:

- MDX in the Prolog tab and looping through the returned subset to insert them in a static subset, again in the same Prolog. Then, the data source for this process is the created subset. Delete elements in the Metadata tab. BUT:
* loop backwards using a counter from SUBSIZ(...) to 1
* make sure the MDX returns at least 1 element - if not the process will halt

- Taking the dimension as the Data source for the proceess (subset ALL), and in the Metadata tab, have an IF to test the attribute and delete if needed. This is the simplest when it comes to logic.

In any case, I use only 1 process for this task.

Wim

[normanbobo]

All...thanks for the great input.

I have re-implemented the code with the full dimension as an input and the delete logic in the metadata tab, very close to the code provided by Lotsaram. Be aware that this is a *very* large dimension -- 80K+ "regular" elements ... 150K+ total elements when loaded with alternate hierarchies. Even with this size, the process ran in just a few seconds. Most importantly -- it works! All of the non-regular hierarchy elements are deleted while leaving the regular hierarchy elements in place.

Lotsram gets applause for the great feedback on not just questions of how, but also of why, which hopefully will benefit many other down the road, even beyond the topic of the original post.

[garry cook]

Bah, forum ate my post (in a very Freddy Starr kind of way).

Gist of it was simple though - essentially although I agree it's worth raising the MDX data source issue for future searching of others, if I was doing this I'd presonally just keep the MDX subset live and delete all elements in the sub on a nightly chore - it should keep it clean and for the purpose of what the OP's looking for, it seems very unlikely that it'll have any significant delay in this scenario (and outside of a 24 hour system, it wouldn't make any difference anyway overnight). Would also have the advantage of having an at a glance check if anything's crept in while you're not looking. The returned subset should be relatively small if it's run periodically and if it picks up massive amounts of elements from upload TI's, it'd prompt you to fix the initial load than dealing with the consequences of it

Also, slightly leftfield but why bother using the attribute at all? Seems an extra step to me that might go wrong/require extra processing. Could put a simple ELISANC in to the metadata with all elements used as data source to bypass the extra attribute. Just a thought.

[normanbobo]

Also, slightly leftfield but why bother using the attribute at all? Seems an extra step to me that might go wrong/require extra processing. Could put a simple ELISANC in to the metadata with all elements used as data source to bypass the extra attribute. Just a thought.

Besides helping with the delete process, the attribute will be used on the reporting side to allow us to filter the elements down to a single "alternate hierarchy", to help us prevent double counting in the reports (as a leaf element could appear in multiple alternate hierarchies, but only once in each alternate hierarchy).

[Alan Kirk]

The general way around this, whether it's working with dimensions or deleting rows in Excel worksheets, is to delete from the end back, not the front forward. Get the dimension size with DimSiz, use DimNm to get that element, then step backwards from that subtracting 1 from your counter as you move through until you reach element 1.

Since the elements we need to keep in the dimension (the "regular hierarchy" elements) are always at the start of the dimension, I could first loop through the elements to find the last "regular" element. I could then loop backwards from the end to that element.

How does looping backwards through the dimension solve the problem?

I would agree that in practice using the subset is the more efficient way of doing it, but just for reference...

Think about it for a minute. You have a dimension of 10 elements, and you use an index to loop through them, moving forward.

1 isn't the one that you want to delete, so you move on to 2. 2 isn't the one that you want to delete so you move on to 3. 3 is one that you want to delete, so you delete it and move on to 4. But wait, uh-oh, what is now 4 was 5. When you deleted 3, the original 4th element moved into its position so it's never tested (unless you rememthber to decrement the counter by 1 after a deletion; as I said I didn't look at your code but it's a common trap that people fall into) and is therefore skipped. If the original 4 was an element that you wanted to delete as well, it will be missed by the process.

Now think about doing it in reverse. You test 10, 9, 8, 7, 6, 5, get to 4 and delete it. You then move back one and... it's still 3. It doesn't matter what you do in the range 10 to 4, the positions of 1, 2 and 3 don't change as a result. As I said, this is also why when doing a VBA loop to delete rows in Excel it's easier to work from the bottom up. Whatever you delete (or add for that matter) won't affect the position of the elements (rows, whatever) that you still have to loop through.