Obsolete Consolidated Elements in a Dimension

[tuckster3]

Hi all, firstly apologies if I get some of this terminology wrong.

We use TM1 Perspectives on PAL v2.0.6

We have a Cost Centre dimension which reflects the structure of the organisation. We have a hierarchy called "Profit and Loss" and several other alternative hierarchies also in use.

Given that we have over 500 cost centres in total this is quite big. We've just undergone a major restructuring of the business and for one directorate this is the third restructure in six years. I have done the work to reflect the new structure but we are left with a lot of consolidations which do not exist any more and there is twenty years of actual data which i need to keep.

So the problem is the dimension editor is looking clunky and unwieldy and we want to remove a lot of the obsolete consolidations.

Question is, is there a TM1 formula which can find the obsolete elements in the dimension (ie the ones which do not have any numbers stored against any of the versions in the cube (we use version to post actuals, budgets, forecasts etc) or pick up whether they report to the root of the dimension (the Profit & Loss Account)

Thank you for reading.

[ascheevel]

Tell me if I'm reading your question wrong, but are you looking to determine which obsolete consolidations you can delete, or are you looking for which zero level elements you can delete. Below are responses for both:

Want to delete obsolete consolidations:
Numeric data isn't stored at consoldiated level in TM1. If the consolidation is obsolete, does that mean the consolidation has no children? If so, there would be no numeric data represented at that consolidated element anywhere. You could delete the childless consolidation without issue except for any rules, TI, or reports that reference those consolidations specifically. If you went through a re-org, you'd want to update those anyway.

Want to delete obsolete zero level elements:
I think a lot of folks on the forum would say to avoid ever deleting a level 0 element. If you don't need it anymore, you could bucket it in an "obsolete" consolidation. If you want to test if there's no data for that element anywhere, you could write a TI to check every cube the dimension with the potential obsolete element is used in and then using CellGetN with the specified element and highest level consolidations for all other dimensions, see if you get a value other than zero.

[tuckster3]

Hello there.

Yes I know it isn't good practise to delete any "n" elements

I have a combination of both. I have some elements which were previously consolidated and had children, but after the restructure they are obsolete and are simple n elements with no children as the children have been moved elsewhere.

I then have some other consolidated elements which sit outside the main hierarchy where they have children but after the restructure are obsolete and have a number stored as the children had a number stored.

Crux of my problem is the only way I can see to do it is to go through and check each one individually. I just wondered if there was another way.

If there was a formula that picked up if the element reports into "profit and loss" which is the top of the hierarchy that would help me.

[ascheevel]

Profit & Loss is the top element in the same dimension as your potential obsolete elements? Check out ELISANC in the docs.

[MarenC]

Hi,

with those parents with no children, in my experience there is usually a consistent way the parents are named compared to the children.

So if there is some way you can distinguish between the parents and the childs by their name then you can use elcompn and if for the parents that = 0 then delete them

Maren

[lotsaram]

If indeed you are talking about consolidations which now have no children then this is all you need.

Code: Select all

sDimName = 'Dim Name';
n = DimSiz( sDimName );
While( n >= 1 );
    sElName = DimNm( sDimName, n );
    If( DType( sDimName, sElName ) @= 'C' & ElCompN( sDimName, sElName ) = 0 );
        DimensionElementDelete( sDimName, sElName );
    EndIf;
    n = n - 1;
End;

As numeric data in TM1 is stored at leaf intersections there is no data loss from removing consolidated elements.

HOWEVER as ascheevel pointed out you still need to be careful. Even if the elements contain no children (and therefore no values), if the elements are referenced in any rules then deleting them will cause an error in the rule and all rule values in the affected cube will be broken.

(Also you may need to run such a code snippet multiple times since you could have C elements who themeselves have children, also being C elements, who in turn have children also being C elements, etc, etc. but ultimately with no leaf descendants at the end of the chain).

[lotsaram]

with those parents with no children, in my experience there is usually a consistent way the parents are named compared to the children.

Better to never assume anything!

[MarenC]

Something about making an ass of u and me!

But how do you deal with where the code was consolidated but is now a simple n? (as tuckster3 indicated).

[ascheevel]

But how do you deal with where the code was consolidated but is now a simple n? (as tuckster3 indicated).

from my first response:

Want to delete obsolete zero level elements:
I think a lot of folks on the forum would say to avoid ever deleting a level 0 element. If you don't need it anymore, you could bucket it in an "obsolete" consolidation. If you want to test if there's no data for that element anywhere, you could write a TI to check every cube the dimension with the potential obsolete element is used in and then using CellGetN with the specified element and highest level consolidations for all other dimensions, see if you get a value other than zero.

[lotsaram]

But how do you deal with where the code was consolidated but is now a simple n? (as tuckster3 indicated).

This doesn't actually happen.

Element type is actually an immutable property. When all children are removed from a consolidated element the element is an "empty vessel" but it remains consolidated. The only way to "convert" a consolidated element into a leaf element is actually to DELETE the element and create a new one with the same name and type "N". (The same goes in reverse to "convert" a leaf element to a consolidation).

However, TM1 has the unfortunate feature that adding children to a leaf will convert it to a consolidation (via implicit deletion of the leaf element and any data attached to it and creation of a new one). Most TM1 developers and admins would prefer that this were not the case!

The same doesn't happen for consolidation to leaf, the one exception is the dimension editor in Architect/Perspectives, where removing all children of a consolidation does convert it to a leaf (of course in the background it is still deletion and new creation).

If this is how tuckster3's dimension is being maintained then this is not a good sign in general for how the system is being managed. I think it would be a good idea for the OP to have their system reviewed by someone competent and get some suggestions on how to manage a TM1 system.