Deleting elements without any data

[vaonlydrb]

Oh...I just saw this post & tried the function. This doesn't apply to TM1 ver 10.2.2.

But staying on the subject, is there a similar method to achieve this in ver 10.2.2. I am currently facing an issue where the dimension is getting updated from DWH and we need to delete elements that are not required anymore, ie., inactive projects. When the TI process, the server crashes with the following error message TM1.Lock Attempt to acquire lock during commit or rollback

The TI process imports new projects elements from dwh, updates hierarchy & attributes

The PROLOG section of TI process is below;

-----------------------------------
DimName = 'Project List';
iElm = 0;

#Unwind the dimension we are updating
iElm = 1;
ElmCount = DIMSIZ(DimName);
WHILE(iElm < ElmCount);
sElm = DIMNM(DimName,iElm);
ChildCount = ELCOMPN(DimName,sElm);
WHILE(ChildCount > 0);
sChildElm = ELCOMP(DimName,sElm,ChildCount);
DIMENSIONELEMENTDELETE(DimName, sChildElm );
###DIMENSIONELEMENTCOMPONENTDELETE(DimName, sElm, sChildElm );
ChildCount = ChildCount - 1;
END;
iElm = iElm + 1;
END;

#DimensionDeleteAllElements( DimName);
----------------------------------------------------

We have got this functionality to work with other implementation without issues, but having lock issues for the first time. Any thoughts on how to update a dimension with new elements and delete elements that are obsolete. We have obsolete elements flagged via attributes.

[tomok]

The PROLOG section of TI process is below;

-----------------------------------
DimName = 'Project List';
iElm = 0;

#Unwind the dimension we are updating
iElm = 1;
ElmCount = DIMSIZ(DimName);
WHILE(iElm < ElmCount);
sElm = DIMNM(DimName,iElm);
ChildCount = ELCOMPN(DimName,sElm);
WHILE(ChildCount > 0);
sChildElm = ELCOMP(DimName,sElm,ChildCount);
DIMENSIONELEMENTDELETE(DimName, sChildElm );
###DIMENSIONELEMENTCOMPONENTDELETE(DimName, sElm, sChildElm );
ChildCount = ChildCount - 1;
END;
iElm = iElm + 1;
END;

I'm not sure what you think this is doing but it is not "unwinding" anything. You are basically deleting all the elements in the dimension that have a parent. I assume you are then adding them back again in the Metadata tab? If so, why do you need to remove the obsolete ones? You have already deleted them in the code above. Just don't add them back in the Metadata tab.

[PavoGa]

What wizardry is this? I never heard of this DimensionDeleteElements function! Anyone know since which version this one has been with us?

October 2018, PA 2.0.6

https://www.ibm.com/support/knowledgece ... ocess.html

It's the version where ViewCreateByMDX was introduced too.

ViewCreateByMDX was available in 2.0.4/5 as we used it. However, we stopped because of performance issues with it.

[vaonlydrb]

Thanks for the response Tomok.

The TI is only adding "Active" elements back in the metadata step, and not inactive. But, the issue is when the TI is run it crashes the server with the following error message in the log file TM1.Lock Attempt to acquire lock during commit or rollback.

Basically, what we want accomplished is the dimension should contain only active elements and delete any inactive elements whenever the TI is run. How do we accomplish this without causing the server to crash?

Thanks,
DB

[vaonlydrb]

The PROLOG section of TI process is below;

-----------------------------------
DimName = 'Project List';
iElm = 0;

#Unwind the dimension we are updating
iElm = 1;
ElmCount = DIMSIZ(DimName);
WHILE(iElm < ElmCount);
sElm = DIMNM(DimName,iElm);
ChildCount = ELCOMPN(DimName,sElm);
WHILE(ChildCount > 0);
sChildElm = ELCOMP(DimName,sElm,ChildCount);
DIMENSIONELEMENTDELETE(DimName, sChildElm );
###DIMENSIONELEMENTCOMPONENTDELETE(DimName, sElm, sChildElm );
ChildCount = ChildCount - 1;
END;
iElm = iElm + 1;
END;

I'm not sure what you think this is doing but it is not "unwinding" anything. You are basically deleting all the elements in the dimension that have a parent. I assume you are then adding them back again in the Metadata tab? If so, why do you need to remove the obsolete ones? You have already deleted them in the code above. Just don't add them back in the Metadata tab.

BTW, we tried various option and the one we're using above is from this link of bedrock post

https://exploringtm1.com/use-case-remov ... dimension/

[tomok]

Thanks for the response Tomok.

The TI is only adding "Active" elements back in the metadata step, and not inactive. But, the issue is when the TI is run it crashes the server with the following error message in the log file TM1.Lock Attempt to acquire lock during commit or rollback.

Basically, what we want accomplished is the dimension should contain only active elements and delete any inactive elements whenever the TI is run. How do we accomplish this without causing the server to crash?

Thanks,
DB

Well, a little of snippet of code from the Prolog is not nearly enough information to diagnose why you may be getting this error. I assume you have one or more cubes that use this dimension and some of those cubes have rules and feeders, maybe even some of those rules reference the deleted elements and/or the attributes you are adding. If this is the case it is really a bad idea to remove all elements and then add them back. It would be better to do a true "unwind" where you simply remove the elements from their parents and add them to a hierarchy node called "inactive" in the Prolog and then in the Metadata you do the same thing you are doing today except you add an additional section of code that checks to see if they are a member of the "Inactive" rollup and removes them if they are. At the end you are then left with a node that has all the inactive elements for your review for deletion later. Try that and see if it fixes your error message.

[ykud]

Basically, what we want accomplished is the dimension should contain only active elements and delete any inactive elements whenever the TI is run. How do we accomplish this without causing the server to crash?

I'd do the following:
in your dimension update process
# in prolog remove the isActive attribute from all dimension elements (either a loop or create a view on }ElementAttributes cube and zeroout)
# in data mark the elements you see as active with ATTRPUTS or CellPutS
# in epilog
## in case you used 'direct' functions in metadata or anywhere -- call dimensionupdatedirect
## call another process that removes the isActive = 0 elements

The process to remove the isActive elements is simple loop and dimension element delete similar to what you've posted.

I don't like deleting and re-adding the elements back in the same process, especially with Direct functions, had a few issues with it (in 10.2.2 times), so I switched to idea of '1 process = 1 dimension action' approach and it worked more stable (and faster).

[vaonlydrb]

Basically, what we want accomplished is the dimension should contain only active elements and delete any inactive elements whenever the TI is run. How do we accomplish this without causing the server to crash?

I'd do the following:
in your dimension update process
# in prolog remove the isActive attribute from all dimension elements (either a loop or create a view on }ElementAttributes cube and zeroout)
# in data mark the elements you see as active with ATTRPUTS or CellPutS
# in epilog
## in case you used 'direct' functions in metadata or anywhere -- call dimensionupdatedirect
## call another process that removes the isActive = 0 elements

The process to remove the isActive elements is simple loop and dimension element delete similar to what you've posted.

I don't like deleting and re-adding the elements back in the same process, especially with Direct functions, had a few issues with it (in 10.2.2 times), so I switched to idea of '1 process = 1 dimension action' approach and it worked more stable (and faster).

Thanks ykud, this worked! Think it needs to be split into 2 separate processes for it to work!

[Wim Gielis]

Think it needs to be split into 2 separate processes for it to work!

Not necessarily. For customers, I tend to use 2 processes since that’s more clear. But a recursive approach of process X calling itself again can also be an option. But that’s not the easiest solution.