In an Oracle NetSuite Planning and Budgeting application, you can always create alternate hierarchies. An alternate hierarchy aggregates, rolls up, or summarizes members in a way that is different from the primary hierarchy. You can group children under a new parent while preserving the parent-child relationships in the primary hierarchy.

Source: Oracle EPM Tutorials

For example, figure 1 shows the primary hierarchy of the cost centers for Company XYZ. In the primary hierarchy, the company's cost centers are grouped by department, i.e. Research & Development, General Admin, Sales & Marketing, Manufacturing Operations, and Corporate Office. In the primary hierarchy, member 999 (Total Cost Centers) is the parent and members 100 (Research & Development), 200 (General Admin), 300 (Sales and Marketing), 400 (Manufacturing Operations), and 500 (Corporate Office) are the children of member 999.

Figure 1: Cost centers by Department

Say you're the CFO of company XYZ and you notice that your departments include cost centers for Canada and the United States. You now want to view your costs by their geographical locations, in which case you can create an alternate hierarchy as seen in Figure 2 . In this alternate hierarchy, member Cost Center by Country is the new parent while Canada and USA are the children. Note that RandD Canada and GandA Canada are also the children of Canada; RandD US and GandA US are the children of USA. While the primary hierarchy allows you to view cost center accounts by department (i.e. Sales or Marketing), the alternate hierarchy is a powerful feature that enables you to view your cost center accounts under a different viewpoint - in this case, by geographical location (i.e. Canada or USA).

Figure 2: Cost centers by Country

Figure 3 shows another example of primary vs alternate hierarchy, where you can view member data consolidated both geographically and functionally. As we have seen, you can create alternate hierarchies, where a single account can have multiple parents and contribute differently to each parent. This is called a shared member, where only a part of input data is shared across all instances of the accounts.  In this example, California is a shared member under both USA (in the primary hierarchy) and Functional (in the alternate hierarchy) parents. The same goes for member Washington.

Figure 3: Data consolidated in Geographical and Functional viewpoint

In order to add a child to multiple parents in a dimension, you must start with a primary child member that has Never Share for the Data Storage value. In our example below, NY (New York) is the child of East Sales in the primary hierarchy, where the data is consolidated geographically, with Never Share for data storage, as seen in figure 4. You can then add New York as a child to the Sales parent in your alternate hierarchy, where the data is consolidated functionally, as a shared member. You can do so by selecting "Shared" as the Data Storage member property for your shared member, as seen in Figure 5.

Figure 4: Select a primary member with Never Share data storage
Figure 5: Select Shared data storage type for shared member

When creating alternate hierarchies bear in mind that:

  1. Shared members inherit properties, other than aggregation type, from the primary instance of the member.
  2. The shared members of the alternate hierarchy and the main hierarchy are the same. By creating alternate hierarchy, you essentially rearrange the same members to meet different management information needs.
  3. The alternate hierarchy must be created below the primary hierarchy.
  4. You can use alternate hierarchy to meet your reporting needs, however, the number of alternate hierarchies can affect performance, as they increase the database size and data consolidation times.

Conclusion

In conclusion, alternate hierarchy aggregates are a powerful feature to roll up and summarize members in a way that is different from the primary hierarchy, while preserving the parent-child relationship.

Want To Know More

If you would like a free consultation please contact Gerard at Redhill Business Analytics.