How to Use Alternative Hierarchies in Oracle's PCMCS

Alternate hierarchies are a useful way to achieve aggregations of dimension members for reporting and analysis that differ from those found in the main hierarchy. They are also available in PCMCS for use as references in rules. This is a convenient feature to leverage in cases where hierarchies must exist in a certain arrangement to match general ledgers or other data sources, but where such arrangements may not be the most convenient for rules building.

However, alternate hierarchies do not offer quite the same flexibility as the main hierarchies do when it comes to building rules.

The following failure message may occur during calculation when using Alternate Hierarchy references in a PCMCS rule, such as in the Source tab:

Allocation Rule 'RS090_R999_ABC_RVU_Direct_Depts_VDL_TEST' failed

Cannot Perform Allocation. Essbase Error(1300033): Upper-level members, for example [HCDS_Fixed_Dir_Costs], are not allowed in argument [POV]. Select a level-0 member

This error is deceiving because it seems to suggest that parent members cannot be referenced in rules, i.e. that only level-zero members can be referenced. This is not true.

What the message is saying is that if a parent level alternate hierarchy member is referenced in a rule, the values immediately below that parent must be level-zero shared members. An intermediate parent is not allowed in rules which reference alternate hierarchies. Another way to put it is that you cannot have a parent that has a shared parent beneath it.

Consider the following example to illustrate the point. Assume that the primary hierarchy is as follows:

All_Accounts

              Total_Expenses

                             Payroll_Expenses

AC_100_Wages

AC_101_Salaries

AC_102_Wage_Benefits

AC_103_Salary_Benefits

Non_Payroll_Expenses

AC_104_Materials_and_Supplies

AC_105_Utilities

AC_106_Rent

AC_107_Property_Taxes

 However, for analysis and rules building, one wants to recast the view as follows:

Alternate Hierarchy

Total_Variable_Expenses

              Total_Variable_Labor

                             Direct_Variable_Labor

                                           AC_100_Wages (shared)

                            Indirect_Variable_Labor

                                           AC_102_Wage_Benefits (shared)

              Total_Variable_Other

                             Direct_Variable_Other 

                                           AC_104_Materials_and_Supplies (shared)

                             Indirect_Variable_Other

                                           AC_105_Utilities (shared)

Total_Fixed_Expenses

              Total_Fixed_Labor

                             Direct_Fixed_Labor

                                           AC_101_Salaries (shared)

                             Indirect_Fixed_Labor

                                           AC_103_Salary_Benefits (shared)

              Total_Fixed_Other

                             Direct_Fixed_Other

                                           AC_106_Rent (shared)

                             Indirect_Fixed_Other

                                          AC_107_Property_Taxes (shared)

Then, let us assume that one wanted a rule for which the source pool accounts are only those under the alternate hierarchy parent, “Total Variable Labor”. If this selection is made, the rule will fail and will return the error message shown at the beginning of this article. In order to achieve the goal, the two selections below that parent would need to be selected instead, i.e. “Direct Variable Labor” and “Indirect Variable Labor”.   Since their children are the shared members, the rule when re-cast this way, should succeed.

Below is a table to help guide what is allowed and what is not allowed in the example alternate structure shown:

While this discussion has focused on allocation rules, similar attention needs to be made for custom calculation rules. For those cases, consider that the “Target” tab would act the same way as the “Source” or “Destination” tabs do in allocation rules. Conversely, formulas are expected to be able to read the alternate intermediate references because they would simply be aggregations like those in Essbase.

This requirement may create some limitations on rules building that rely on alternate aggregations. If this condition of the use of alternate hierarchies is too restrictive for the situation at hand, another approach to consider would be the creation of and reference to dimension attributes. However, the steps that need to be undertaken to do that are more complex once an application has been built.

In summary, alternate hierarchies are powerful tools in PCMCS for rule building and reporting. However, users need to be aware of the correct way to use them in Oracle's PCMCS application. 

For comments, questions, or suggestions for future topics, please reach out to us at [email protected].  Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.