Use of DimensionDeleteAllElements

[MarenC]

Hi,

is it still advisable to avoid using DimensionDeleteAllElements in Planning Analytics?

I have read that if you are using alternate hierarchies this means any potential data loss cannot happen when using DimensionDeleteAllElements?

So is it now the case to avoid using DimensionDeleteAllElements only when you are not using alternate hierarchies?

Maren

[Steve Rowe]

Testing would be required.

DimensionDeleteAllElements is regarded as unsafe as if there is a problem with the subsequent rebuild then this could result in permanent deletion and data loss.

In a dimension with hierarchies then DimensionDeleteAllElements will act on the same named hierarchy only (I assume). In general I would still regard it as unsafe as there is no guarantee that all hierarchies will contain all N levels. If the default hierarchy is the only hierarchy to contain an element then you are still exposed to data loss. Smaller risk I guess but really the same problem.

The only thing that may save you is the Leave hierarchy, I've not tested to see if the what happens if an N level is removed from all the hierarchies except the Leaves hierarchy. I would hope that it was removed from the Leaves hierarchy too, though I can see arguments as to why it wouldn't be.

Cheers

[lotsaram]

I have read that if you are using alternate hierarchies this means any potential data loss cannot happen when using DimensionDeleteAllElements?
So is it now the case to avoid using DimensionDeleteAllElements only when you are not using alternate hierarchies?

I would say in general, "yes". The main exception for avoiding DimensionDeleteAllElements when using alternate hierarchies woudl be to avoid loss of string data held against same named hierarchy C elements. Leaf level numeric data is safe as all leaf elements are in the LEAVES dimension. It dosn't matter if they are deleted from the same named hierarchy, delete them and add them back and the data will still be there.

In general I would still regard it as unsafe as there is no guarantee that all hierarchies will contain all N levels. If the default hierarchy is the only hierarchy to contain an element then you are still exposed to data loss. Smaller risk I guess but really the same problem.

It isn't actually relevant if all leaves are in all hierarchies. All leaves must be in the leaves hierarchy. As far as storage of data against leaves goes, this is what matters. Deleting a leaf element from all hierarchies where it exists will not delete it from the LEAVES hierarchy. However, deleting an element from LEAVES hierarchy will delete is from ALL hierarchies. Think of both of the above as a "feature".

Note the one thing to really REALLY watch out for in this new paradigm is that deleting an individual leaf element from the same named hierarchy explicitly with DimensionElementDelete will delete the element from ALL hierarchies. (Only for leaves, not consol or string elements.) I regard this as a bug, and a bloody terrible one. But apparently IBM regard this as a "feature" becasue someone asked for it.

Testing would be required...

Goes without saying.

[paulsimon]

Hi

There are some other reasons to avoid using DimensionDeleteAllElements.

If the rebuild fails

a) Elements will be deleted from any Static Subsets

b) Any views that referenced the deleted element in title subsets may be invalidated

c) If you reference consolidated elements in rules, the rules will be invalidated

d) There is a bug in PA which means that the Leaves hierarchy won't always contain all base level elements, particularly where a consol has lost its base levels and has become a base level. According to some at IBM, once an element has been defined as a consol it should stay as a consol, but that isn't what actually happens. Once a consol has been converted to a base level, it won't appear in the leaves hierarchy and there is no way to be sure that someone has not stored data against it. IBM have confirmed that there is a problem and they are working on a fix.

There are also issues of efficiency. Using DimensionDeleteAllElements implies that you are doing a full dimension rebuild. If this is done overnight, it probably doesn't matter too much. However, increasingly as TM1 systems become global and are therefore used 24x5 then there isn't necessarily an overnight batch window. On the general rule of thumb that once a dimension is built the first time then probably 95% of the dimension will stay the same as before in any update, then you are probably better off using the Dimension .... Direct series of statements and their Hierarchy equivalents to only change those parts of the dimension that need changing. That implies a pruning approach rather than deleting everything and rebuilding everything of which 95%+ is going to be the same as before.

We use a combined approach. We carry out a full dimension update on a copy of the dimension that is not used in any cubes and therefore does not incur any locking. If the update fails, then nothing is lost as the main dimension is left as before, and that is the one against which all data and subsets etc are held. If the update of the copy of the dimension works then we compare the updated copy with the main dim and write out details of the updates which gives a handy audit trail and then we apply the changes using the Direct statements. We organise a periodic DimensionUpdateDirect to compress the dimension down again. Overall this is undoubtedly more complex, and dimension updates can take longer, but the key thing for us, is that the period in which the main dimension is being updated is shorter and therefore the duration of any locks is lower.

Regards

Paul Simon

[lotsaram]

Note the one thing to really REALLY watch out for in this new paradigm is that deleting an individual leaf element from the same named hierarchy explicitly with DimensionElementDelete will delete the element from ALL hierarchies. (Only for leaves, not consol or string elements.)

For completeness and the sanity of others that may happen upon this thread the above is no longer the case as of release 2.0.9 / 11.7 TM1 server. Hopefully it stays this way.