How to Create Alternate Hierarchy in Hyperion Planning
This is an article I recently wrote for ODTUG about different uses of Alternate Hierarchies, the original article can be found here .
When it comes to alternative hierarchies, the first thing that comes to most consultants' minds is, "They're great for reporting." While certainly true, there are many scenarios where alternative hierarchies can come to our rescue and give us additional solution design options that are often cheap in terms of impact on overall performance, if designed properly.
Alternative hierarchies can be used in different scenarios - from reporting and managing security to mapping hierarchies, just to name a few. In this article, I'll talk a bit more on the mapping hierarchies and how can we utilize them.
Consider an example where we have two separate applications each built for specific functional purpose (e.g. Sales and Finance, Manpower and Finance, Capex, and Finance). In many Planning projects, you are bound to work in a similar structure where you have a dedicated Sales/Manpower/Capex application with often detailed dimensionality that communicates to a target application such as Finance with more granular level of dimensionality, usually at the chart of accounts level. So, for the sake of simplicity, in my example here I'll demonstrate using two applications:
- Manpower as my detailed planning application (Source)
- Finance as my granular level planning application (Target)
I'm sure a lot of you have seen a similar case. Usually it's straight forward, where it's a matter of doing your detailed planning, aggregating your source data to get the granular level of your target application, and simply send the data using one of the many available methods (XWRITE/XREF, export/import data, ODI…). But sometimes we may have a tiny twist that makes it a little bit more difficult and require us to change our design.
Consider the following data form in my source application:
And the data form in the target application:
Notice the difference? I simply just can't perform an Xwrite straight forwardly because the dimension mapping is not one-to-one. So to break it down, I'll explain the dimensionality by looking at the outlines, to start with this is the Manpower application outline:
And the Finance planning application outline:
As you can see, the Finance application outline is quite simple with the six standard dimensions; whereas, the Manpower application has three additional custom dimensions. Now the mapping logic which will lead us to main topic, the following table shows the logic we need to be able to send data from one application to another.
Every manpower element and paygrade is mapped to an account. In effect, we need to somehow merge the two dimensions. So, for a specific cost center, we need to aggregate all dimensions (Employee, EmployeeType, and Paygrade), and merge the Account and Paygrade when sending data to the Finance application. Here is when the alternative hierarchies can come to our aid. So, let us take a closer look at the dimensions to better understand the logic and how to achieve it with alternative hierarchies.
Account dimension in the source application:
This is simple as you can see, no complications in the Account members themselves. It's only when we need to send data to the target application we need to merge this with the Paygrade dimension.
Paygrade dimension in source application.
Here comes the interesting part, this grading structure as shown in the screenshots below, contains five grade categories (A, B, C, D, and Z). Each category is further divided into either two, three, four sub-categories.
The categories (A,B,C,D, and Z) will determine how we finally map the Manpower data to our Account dimension in the Finance application.
Account Dimension in the target application:
As we can see above, the main accounts (Basic Salary….) are 8110x,8120x,8130x,..,8170x (can be directly linked to our Accounts dimension in the source application) and the last digit in the Account code (1,2,3,4, and 5) is derived from the Paygrade category (A,B,C,D, and Z) and here we need the alternative hierarchies to be able to map the two dimensions and send data between the applications.
Now how can we achieve this using alternative hierarchies? I'm going to create two hierarchies in the source application, one in Account dimension and another in Paygrade dimension as follows.
Account dimension alternative hierarchy:
It's a simple hierarchy: I'm using the first four characters of the target Account codes, and mapping the Account element to it by adding it as a shared member. Now you may ask why use a hierarchy in this scenario where it may look as it can be replaced in the business rule/calculation script? The answer is, yes it's true in this case the Account mapping is straight forward, but I see the advantages of hierarchies if you have a case where you have frequent changes to your mapping, or new elements that can be created in the future in the source application, or in case you have multiple elements you need to aggregate and add under one account/node (for example you want to map all allowance under other allowances).
Paygrade dimension alternative hierarchy:
In Paygrade dimension the alternative hierarchy will give us the last character in the Account code. This is simple case here, but be assured the concept can be used in much more complex scenarios. So now from the Account alternative hierarchy I can derive the first four characters from my target Account member, and from Paygrade the last character, and finally I can send data between the two applications as shown in the table below.
The remaining steps, which I'm not going to explain in detail, needed to complete the design are:
- Aggregate data in source application or alternatively you can export and import data using a unique combination and add the values to Essbase instead of overwriting them
- XWrite data at the alternative hierarchies' level, something like:
3. Maxl script to group the steps, and trigger the process when needed
In conclusion, alternative hierarchies can be much more than just reporting hierarchies. They can be relatively easy to maintain, have a relatively light impact on the overall dense/sparse dimensionality, and they are certainly fun to work with.
How to Create Alternate Hierarchy in Hyperion Planning
Source: https://www.linkedin.com/pulse/alternate-hierarchies-more-than-just-reporting-shubeilat-msc-ocp