Problem with CreateHierarchyByAttribute

[PavoGa]

Having a problem with this function, as well as the manual method of creating an alternate hierarchy on an attribute through PAW.

My time dimension has consolidations by year (2018, 2019, ..., 2023) with child periods of 201801...201812, 201901...201912, etc. Added an attribute called MonthName and populated it with Jan, Feb, ..., Dec. Wrote a process using

CreateHierarchyByAttribute('Fiscal Period', 'MonthName', 'Orphans', 'All Months').

I get a hierarchy of this:

Jan
201801
201901
202301
Feb
201802
201902
202302
...

Note that elements for 2020, 2021, and 2022 are missing. So far, no matter what I have tried, I get the same result. No errors in the log indicating an issue when the process runs. The only thing that seemed to potentially be the problem is that rules applied to the 2020, 2021 and 2022 elements, but not the 2018, 2019 and 2023. I shifted the rules to use 2023 and those elements were still included when the new hierarchy was built.

I do not use this function, preferring to use TI's to structure the hierarchy with HierarchyElementInsert and HierarchyElementComponentAdd functions, but was playing around and ran headlong into this issue.

Has anyone else experienced this behavior? Any ideas on why?

[macsir]

Just curious, what PAW and PAL version do you use?

[PavoGa]

Just curious, what PAW and PAL version do you use?

PA: 2.0.9.1
PAW: 2.0.53

[PavoGa]

Well, here is an update on this. As stated, there was a set of elements that were being ignored by the CreateHierarchyByAttribute function in a TI and through PAW, which I understand uses this function through the API.

So, as it turned out we do have a single dimension for which we use this function, I wrote a process that validates alternate hierarchies based on attribute values and will fix it if requested. During the course of testing it against the time dimension given in the example from above, the CreateHierarchyByAttribute function began picking up the missing elements and building out the alternate hierarchy in full.

While I could not find the specific problem, I suspected something was up with the dimension because of ignoring a specific set of elements when processing. This is a puzzle. Would be interested if/when anyone else runs into this.

[Harvey]

Unless I misread your question, the result you got is the one expected.

If you have a look at the documentation for CreateHierarchyByAttribute, you'll see it's very limited in what it can do. It can create a 3-level hierarchy, with level 0 being your leaf elements, level 1 being the value of the attribute specified, and level 3 being a root element that you provide.

You appear to be trying to create a 4-level hierarchy, or at least, a 3-level hierarchy with multiple root elements, which is not supported.

CreateHierarchyByAttribute('Fiscal Period', 'MonthName', 'Orphans', 'All Months') should:

• Create a root element in the [Fiscal Period] dimension called [All Months]
• Add the specified consolidation [Orphans] and make it a child of [All Months]
• Loop through all leaf elements and;
  • Create a consolidated element named using the value of current leaf element's [MonthName] attribute (Jan, Feb, etc)
  • Attach the created consolidated element [MonthName] to the root element [All Months]
  • Attach the current leaf element to the above consolidated element [MonthName], or if the attribute was blank, attach it to [Orphans]

I created some functionality for this purpose as part of the TM1 Foundation Framework. It does the same thing, but with more flexibility in naming and support for up to 8 hierarchy levels.

If you find the whole framework too heavy for your needs, maybe you can browse the code and pull out just the hierarchy functionality.

You can find the open source code on on github, and if you want to see the functionality, I created a video demonstrating it on my TM1 Innovators YouTube channel.

[PavoGa]

Unless I misread your question, the result you got is the one expected.

If you have a look at the documentation for CreateHierarchyByAttribute, you'll see it's very limited in what it can do. It can create a 3-level hierarchy, with level 0 being your leaf elements, level 1 being the value of the attribute specified, and level 3 being a root element that you provide.

You appear to be trying to create a 4-level hierarchy, or at least, a 3-level hierarchy with multiple root elements, which is not supported.

CreateHierarchyByAttribute('Fiscal Period', 'MonthName', 'Orphans', 'All Months') should:

• Create a root element in the [Fiscal Period] dimension called [All Months]
• Add the specified consolidation [Orphans] and make it a child of [All Months]
• Loop through all leaf elements and;
  • Create a consolidated element named using the value of current leaf element's [MonthName] attribute (Jan, Feb, etc)
  • Attach the created consolidated element [MonthName] to the root element [All Months]
  • Attach the current leaf element to the above consolidated element [MonthName], or if the attribute was blank, attach it to [Orphans]

I created some functionality for this purpose as part of the TM1 Foundation Framework. It does the same thing, but with more flexibility in naming and support for up to 8 hierarchy levels.

If you find the whole framework too heavy for your needs, maybe you can browse the code and pull out just the hierarchy functionality.

You can find the open source code on on github, and if you want to see the functionality, I created a video demonstrating it on my TM1 Innovators YouTube channel.

You misread the question. When I said the elements for the fiscal year consolidations were missing, I assumed it was understood the components of those consolidations were the missing elements in the new hierarchy in spite of their MonthName attribute being populated as well.

If you read my last post on the topic, you will notice that once that dimension was "updated," the function began to work as expected. I currently have two instances, one with an untouched dimension and one where it was updated/recompiled. CreateHierarchyByAttribute performs exactly as expected in the instance where the dimension was updated but fails in the instance with the untouched dimension, seeming to indicate as I stated something was wrong with the dimension. I suspect it is alias related, but have not taken time to confirm by recreating the problem.

[gtonkin]

HI Ty,

Your last line about suspecting an alias being the issue brings an issue I had earlier to mind.

A dynamic report was returning #Values when the TM1RPTROW had the Alias, Description on. Remove it and values were retrieved as expected.
Copying the members with the alias on and pasting into the set editor resulted in errors saying that the elements do not exist (yet I just copied them from the report).
Changing the hierarchy to Leaves and pasting with the Alias worked. Pasting to the default hierarchy did not. Still having a WTF moment to understand this. No rules on attributes, Description your vanilla Alias with values populate via load file.

Long story short, may be worthwhile checking if you have a similar issue on your side.