TM1 Forum

When using the DimensionDeleteAllElements for a dimension and the HierarchyDeleteAllElements for the only created hierarchy for the dimension, why does the "leaves" hierarchy still contain elements?

Hi

When using Real Named Hierarchies, any of the Dimension TI Functions actually apply to the Main aka Default Hierarchy of the Dimension - What we traditionally think of as the Dimension.

So I would guess that what you are seeing is that DimensionDeleteAllElements is clearing all elements from the Main Hierarchy which is called Dimension:Dimension.

However, there is always a Dimension:Leaves hierarchy which is an automatically maintained hierarchy that just has the base level elements of the Dimension. These are shared by the Main Hier and any Named Hierarchies in the Dimension called Dimension:<Hier Name>. It is perfectly possible for an element to appear in the Leaves hierarchy that does not appear in the Main Hier. Not perhaps what you would expect but when you delve more into hierarchies it is easy to see why IBM decided to go this route.

In order to completely delete a base level element from a dimension I believe that you need to delete it from the Main Hier, any Named Hierarchies, and the Leaves Hierarchy. I will probably need to check this again since it is a while since I did the experiments (and the documentation is not at all clear). It is not that easy to kill off an element. That is probably a good thing since if you remove an element you lose any data associated with it.

However, I would advise you against using DimensionDeleteAllElements, or its Hierarchy equivalent. In most cases you are going to be using these to clear out consolidation links prior to re-building the dimension. If that re-build fails then any elements, in particular consolidations, will be removed from any static subsets and it may not be easy to rebuild these particularly if they have been created by end user. You could also potentially invalidate rules.

We are sticking with the traditional approach of deleting all links between elements but leaving the elements be they base level or consolidations in place. We then have an orphans process to handle any elements that are no longer linked to the hierarchy.

We are also adopting the approach of having any Named Hierarchies as perfect hierarchies ie each element only has one parent, and keeping any alternate hierarchies to the Main Hier, ie Dimension:Dimension.

Regards

Paul Simon

Thanks Paul I appreciate the response!

I've been doing some testing with hierarchies, trying to figure out the leaves hierarchy. It looks like that it is automatically created when a 2nd hierarchy (not the main hierarchy dimension:dimension) is created. Since it was automatically created and populated when the 2nd hierarchy was created, I thought that it was dependent upon those hierarchies, and thus thought its elements would be removed when all of the elements from the other hierarchies were removed. It's confusing why that isn't the case, especially since the dimension and hierarchies could be repopulated with completely new elements. You would then have a leaves hierarchy that contain elements that are not found anywhere else.

To delete a leaf element from all hierarchies you delete it from the Leaves hierarchy.
e.g. Deletes the element from not only the Leaves hierarchy but also the same named hierarchy and any other hierarchies in which the element exists.

This is because leaf elements are like "shared resources". This isn't the case for consolidations which are hierarchy specific. If a consolidation of the same name exists in multiple hierarchies then you have to remove it separately from each hierarchy.

All DimensionX functions act only on the same named hierarchy. They do not act "globally" on the dimension as a whole (except of course for DimensionCreate and DimensionDestroy). The one exception to this is DimensionElementDelete which will delete a leaf element not only from the same named hierarchy but also from Leaves (and therefore also from all other hierarchies). IMHO given how all other DimensionX functions behave, this is a clear bug. But it is still not clear whether those with the power fix it believe it to be a "feature" versus a bug.

@Lotsaram

What I was getting at is that from what I have seen TI functions such as DimensionElementInsert operate on the Main Hierarchy, or the Default Hierarchy, what most of us think of as the traditional dimension, but of course, the idea of a traditional dimension goes as soon as you introduce a named hierarchy.

By comparison HierarchyElementInsert has the additional Hierarchy Name parameter and is designed to operate on a specific Named Hierarchy. You can however, make it operate on the Main Hier by making the Hierarchy Name parameter the same as the Dimension.

The same is true of DimensionElementComponentAdd and HierarchyElementComponentAdd

Interesting that DimensionElementDelete operates on Leaves. That does seem like a bug. However, it is not something I use in an automated process. We tend to explicitly delete base level elements because of the risk of losing data. Generally speaking if an element is there, then even if it isn't current, it will have some historical data on it. Even if it has never been used, there might be outstanding purchase orders, etc, so we are very cautious about deleting elements. Probably worth reporting to IBM though.

@MPetty

It might initially seem odd that you can have an element in the Leaves hierarchy that does not exist in any other hierarchy. That is due to the special place that the base level must have. The base level still needs to attach to the data in the cube. It is therefore the route by which any data will consolidate up into any of the hierarchies.

Many of us who have been developing for years will have traditional alternate hierarchies in the main hierarchy of the dimension. That meant that it was probably not the best place to start saying that this is where the common base level elements will be stored. If was at a greenfield site now, I might very well design a system where the main hier only had a simple roll up of the leaves and any other hierarchies were named hierarchies.

One role for the Main Hier is to act as the Default Hierarchy. This comes into play when you write an MDX query against a cube, and, if you don't specify all dimensions in the cube, MDX will want to know a Default Element in a Default Hierarchy to use to resolve the query against that dimension. The Default Hierarchy will be the Main Hierarchy. The Default Element will be given in the }HierarchyProperties cube.

If you look at the }HierarchyProperties cube now, you will see that as well as the Main Hier represented by the Dimension name, you also have Dimension:Hierarchy elements in the }Dimensions dimension. This allows you to name the levels in each of the Named Hierarchies.

Below is a link to an interview with Hubert Heijkers which covers this. The good looking one on the left is Alan Kirk

https://www.youtube.com/watch?v=tL3puB0AK74

Regards

Paul Simon

lotsaram wrote: ↑Mon Sep 16, 2019 5:47 am All DimensionX functions act only on the same named hierarchy. They do not act "globally" on the dimension as a whole (except of course for DimensionCreate and DimensionDestroy). The one exception to this is DimensionElementDelete which will delete a leaf element not only from the same named hierarchy but also from Leaves (and therefore also from all other hierarchies). IMHO given how all other DimensionX functions behave, this is a clear bug. But it is still not clear whether those with the power fix it believe it to be a "feature" versus a bug.

As of TM1 server 11.7 this issue is fixed and DimensionElementDelete acts only on the same named hierarchy.