How to get the element name from a hierarchy?

[CCYY]

Hello,

I have a hierarchy always have only one element underneath, need to find out what the element name is in TI. I searched online and find a function called ElementFirst. When I put it in under the Prolog likes:

CurrentVersion=ElementFirst('PlanVersion', 'Current Version');

I got an Process Error. It says variable "elementfirst" is undefined. Any idea why I couldn't use this function and how would I get the element name then?

Thanks!

[David Usherwood]

Looking at IBM's own documentation:
https://www.ibm.com/support/knowledgece ... first.html
For this and some (but not all ) of the new hierarchy functions, you need to specify the server name:
ElementFirst("planning_sample:Location", "North America")
This may be a documentation issue but give it a try. Personally I am waiting for IBM to finish their hierarchy functionality before making serious use of it.

[declanr]

I am assuming by hierarchy that you mean a consolidation rather than an alternative hierarchy?

If so you just want to use ElComp ( ‘PlanVersion’, ‘Current Version’, 1 );

The error you receive is standard if you try to use a function that is not available in your version of the software; the function you are trying to use it part of the alternative hierarchies that came in from 2.0 onwards.

[CCYY]

I am assuming by hierarchy that you mean a consolidation rather than an alternative hierarchy?

If so you just want to use ElComp ( ‘PlanVersion’, ‘Current Version’, 1 );

The error you receive is standard if you try to use a function that is not available in your version of the software; the function you are trying to use it part of the alternative hierarchies that came in from 2.0 onwards.

Thanks, ELCOMP worked!

[CCYY]

Looking at IBM's own documentation:
https://www.ibm.com/support/knowledgece ... first.html
For this and some (but not all ) of the new hierarchy functions, you need to specify the server name:
ElementFirst("planning_sample:Location", "North America")
This may be a documentation issue but give it a try. Personally I am waiting for IBM to finish their hierarchy functionality before making serious use of it.

I tried to include the server name, but still got the same error. Replaced the function with ELCOMP() and that worked.

[Alan Kirk]

Looking at IBM's own documentation:
https://www.ibm.com/support/knowledgece ... first.html
For this and some (but not all ) of the new hierarchy functions, you need to specify the server name:
ElementFirst("planning_sample:Location", "North America")
This may be a documentation issue but give it a try. Personally I am waiting for IBM to finish their hierarchy functionality before making serious use of it.

I tried to include the server name, but still got the same error. Replaced the function with ELCOMP() and that worked.

Unfortunately you just fell into a pit of confusion of IBM's making. Let me shine some light on it because otherwise you could stumble into it time and time again.

ElCompN and ElementFirst both relate to hierarchies, but to completely different types of hierarchies which have barely anything to do with each other aside from having the same name.

Since the dawn of time TM1 has modelled data on the assumption that a cube is made up of dimensions, and that each dimension represented one characteristic of the data stored. The elements of the dimension represented the specific instances of the characteristic. For example, If you were storing sales of clothes, you might have the following dimensions (not necessarily in order, before someone gripes about the sparsity):

• Version, representing the version that the data relates to (the elements of which would be Actuals and Budget).
• Store, representing the store that made the sale; the elements would be the individual stores in the group.
• Product, representing the product code of the garment sold.
• Period, representing the period that the sale took place in.
• Measure, with the elements Volume and Value.

Now suppose that you want to analyse your products by Summer Wear vs Winter Wear, but ALSO by Menswear vs Womenswear. Not a problem; you could create a consolidation called Total By Season, put two consolidations named Summerwear and Winterwear under that, then add all of the elements that relate to those seasons under the appropriate one.

You could also create a consolidation called Total By Gender, put consolidations named Menswear, Womenswear and Unisex under that, then add all of the clothing items below the relevant consolidations.

Traditionally, and in a lot of the legacy TM1 manuals and user interfaces, Total By Season and all of the elements under it, and Total By Gender and all of the elements under it, would both be called "hierarchies". They are completely independent of each other and just add up some or all of the N level elements, grouping them in different ways to come to a total. You can for practical purposes have as many of these "hierarchies" as you need.

One problem. Let's suppose that we have one garment that was originally targeted at men, but proved to be popular with women as well. You therefore want to move it from the Menswear group to the Unisex group. You can do that really easily... but it means that if you re-run a report by gender now it will show different numbers for past periods compared to reports that were run at the time.

In the traditional model you can't have two consolidations with the same name meaning different things depending on when they were run. The only way around it was to create new consolidations like Unisex After 19-Apr-20 or some such thing, or just accept that a report run today for last month's period won't be the same as a report that was actually run last month.

Second problem; what if you now want to analyse your sales by size? Or by colour? Worse, what if that information isn't embedded in the product code?

The only solution was to tear down your cube and build a new one with additional dimension for Colour, and an additional dimension for Size.

In Planning Analytics 2.0 IBM introduced a new concept called Alternative Hierarchies. And the ONLY way you can get your head around these is to forget that you ever heard the word "hierarchies" in the sense of the ones that I described above.

The closest I can come to describing hierarchies in one sentence (and I'm still not convinced that it's a good description, but this is really my first shot at explaining it to someone) is to think of them as being "virtual dimensions" rather than consolidation roll-ups in the traditional sense. Or perhaps it would be better to say that the dimension itself becomes "a storage box full of virtual dimensions", because unlike "normal" dimensions which could appear in only one location in a view, you can pull out one or more hierarchies and use each in different places within a view.

You still create a dimension. The dimension will still have elements. The dimension and all of its elements are your Primary hierarchy.

However in addition to that you can have, for example, a reorganisation or snapshot hierarchy. In the example that I gave above the product that you moved from Menswear to Unisex may still be under Menswear in the primary hierarchy, but be under Unisex in a second hierarchy (or the other way around). You can then analyse sales by either hierarchy.

You can also have attribute hierarchies. Instead of adding new dimensions to the cube, you can add attribute hierarchies (like the colour and size attributes above) to the Product dimension. You can then analyse by the primary hierarchy, or one of the alternative hierarchies, or both... but I don't intend to go into that at this point lest I make your head explode. In short, though, you would be able to use (say) the colour attribute in rows and the size attribute hierarchy in columns in a way that you just can't do with traditional pre-hierarchy dimensions.

There are a couple of other types of hierarchies as well, but let's not wander too far down the path yet.

If hierarchies sound too good to be true, they... sort of are. The key problem with them is that they are not supported by the "classic" user interfaces of Client, Perspectives, Architect or TM1 Web. And they never will be. DO NOT get me started on my thoughts about Planning Analytics Workspace (PAW), PAfE or whatever it's called this week, or the installation of those products. I am in broad agreement with the point that David made about them being not quite ready for prime time. I'm sure that there are people out there using them and probably some using them successfully; the concept is actually quite brilliant. The execution, on the other hand (especially in the front end clients)... has some way to go in my opinion.

Alternative hierarchies (which will eventually become known just as "hierarchies") are turned off by default. If you don't have a strong grip on the basics of TM1 first I emphatically do not recommend enabling them until you do so. (Though that said, you WOULD need to learn completely new concepts when you turn them on anyway, so there's an argument for biting the bullet now. Or would be, if I felt that hierarchies are polished enough to put into production. Which I don't. Other people may have varying mileages on that.)

If you are looking at the help page (which, yay you because so many people don't bother), if you're in any doubt at all make sure that you go up a couple of levels to see which category the help page falls into. The one that you had a link to was in the Hierarchies category, which refers to the PA2 "Alternative Hierarchies" that I described above. Functions like ElComp on the other hand are "traditional" "hierarchy" (let us never call them that again) functions, which you will generally find under "Element Information" or "Dimension Information".

HelpGroupings.png (167.28 KiB) Viewed 4073 times

[Wim Gielis]

Let’s turn Alan’s post into a sticky one !

[Alan Kirk]

Let’s turn Alan’s post into a sticky one !

Probably not a sticky but it may be worth a FAQ listing, so if anyone has anything to add or a different way of explaining it it would be more than welcome.

[lotsaram]

Looking at IBM's own documentation:
https://www.ibm.com/support/knowledgece ... first.html
For this and some (but not all ) of the new hierarchy functions, you need to specify the server name:
ElementFirst("planning_sample:Location", "North America")
This may be a documentation issue but give it a try. Personally I am waiting for IBM to finish their hierarchy functionality before making serious use of it.

No really you don't. They have just published the documentation for the worksheet function on the page for the rules function. The function seems to be working as expected from what I can see. Although you do wonder what's the point when DFRST('dimension:hierarchy') works perfectly anyway.

[David Usherwood]

They have just published the documentation for the worksheet function on the page for the rules function.

Makes sense - not sense as in sensible, but the syntax looked really silly.