New hierarchy

Hi Everyone!

I would need a second hierarchy of a dimension with weighting that is different from the primary hierarchy. Do you recommend handling it as a real second hierarchy? Or I should rather set up alternative summary elements and just build another tree in the same hierarchy? Currently we are not using multiple hierarchies within one dimension, so I don't really know the consequences.

Is there any effect on the usability of the dimension in App Studio, or in setting cube rules?

Thank you, Zsolt

Find more posts tagged with

FAQs, How-To's, and Best Practices

Comments

martin-reischl

Hi Zsolt,

from my experience, here are a few considerations for your decision. I'm sure others will have more input.

Caveats when you use a "real" second hierarchy:

1. In Application Studio, the "Select Element" dialog (or the old listviews) will give you only the elements of one hierarchy. So if you want to let your user select from any hierarchy, you should avoid "real" hierarchies. For the down side of this option, see #2 in my second list below.

2. With rules, you may have to use the "ElementInHierarchy" function to make sure the rules engine understands which element you are referring to. Here is the link to the full documentation on rules and hierarchies:

docs.infor.com/.../default.html

3. In App Engine, you will also have to specify the hierarchy in addition to the dimension when you refer to a dimension element.

The drawbacks of "multiple parents" are these:

1. In App Studio formulas, you may find it difficult to find a specific parent of an element. You may get a "wrong" parent if your element is in more than one branches. This can be relevant especially for time dimensions. There is no way to force a memberget formula to give you the parent from a certain branch.

2. The fact that "Select Element" gives you all relationships can be a disadvantage rather than an advantage. Users who have access to only some elements can be confused that they see their elements multiple times (once for each parent), instead of just once.

3. With just one technical hierarchy, of course every element name has to be unique. So you cannot make "New Products" mean something different in the "Products 2022" branch than in the "Products 2023" branch. This is often sold as the main plus of the "technical" hierarchies. But honestly, most of the time I believe this is not an advantage. I would not want to discuss with a user why they see different values on "New Products" than some other users.

Now, the discussion is open

Best Regards

Martin

Peter Mayall

Great summary Martin. I considered using real hierarchies but I could never work out the benefits, only disadvantages. As such I have solutions to issues when using alternate hierarchies within the one real hierarchy.

1. In App Studio formulas, you may find it difficult to find a specific parent of an element.

I recommend using the "Short" format for unique names in database settings. This will give the correct parent element of the unique name. The "Long" format also works but has other disadvantages.

2. Select element - Users who have access to only some elements can be confused that they see their elements multiple times.

When I have an alternate hierarchy that also has dimension security, I have a top level e.g. H1, H2, etc, which all users have access to but are hidden in the selection list. As an extra security the top level element immediate child(ren) have a zero weighting so even if accessed via Excel Integration they do not get any results.

I would love to hear where others have implemented real hierachies and what the benefits were.

gottesheim-daniela

Thanks, too, for this helpful summary!

We have one real second OLAP hierarchy in one of our dimensions. All other dimensions (in total nearly 190) only have one real OLAP hierarchy, but within this hierarchy there can be one or more top elements.

What was the reason for us to decide for a second OLAP hierarchy? We have a dimension DYEAR, which in former times included only base elements (so one element for each year). We didn't have the need for a top element which sums up all years - until some years ago we got exactly this request. So we had to decide between 3 options:

add a top element in the existing hierarchy
add a second hierarchy in the existing dimension
create a new dimension which includes a top element and all necessary base elements

At this point of time we had more than 500 reports and a huge number of them would have been affected by the changes caused due to option 1 and 3. Hence we decided for option 2, because it had the least effect on our existing reporting and OLAP model, but of course we also struggle with the disadvantages that Martin mentioned before.

zsolt-pinter

Thank you very much Everybody for the input, very useful!! We also only used in the past parallel trees within the same hierarchy. It's just that in this case it's about the dimension that drives the company structure and I would like to keep it as clean as possible. In fact, with the same company elements, all I want to achieve is a different weighting.

The new hierarchy would be used in a separate application than the old one, so the problem of only having one hierarchy available on reports is not a problem.

So in this case, I would say, adding a second hierarchy would be a better option, and like this I wouldn't need to duplicate the summary elements.

I'm worried though about the rules: are you sure in V11 cube rules work with multiple hierarchies? What happens with the existing rules if I add a second hierarchy? I cannot find the function ElementInHierarchy in the rule fuctions, and that gives me the feeling that this would only work in V12.

Have a great day and thanks again!!

Zsolt

Attachment_4321.zip

Copyright © 2025 Infor. All rights reserved.