An Alternative Solution for Dynamic Organization Roll-up

By on July 14th, 2017

Over my 15 plus years of consulting, a frequent consolidations theme amongst clients pose goes something along these lines:

“How can I have a dynamic organization hierarchy?”

Or “can I report historical data in a frozen view?”

Or “Tell me about the org-by-period functionality and should we use it?”

The ask boils down to this general reporting requirement: the ability to report historical data the way it was reported organizationally at that previous point in time, and also to report current data in a different organizational view at the current point in time. I call this a dynamic organization roll-up when you need to freeze history in its own view(s) and see current period(s) in a different view.

First, let’s discuss the org-by-period functionality and get level set on our understanding.

The org-by-period setting has existed in Oracle Hyperion Financial Management (HFM) for a long time. Org-by-period works on the Entity dimension and there’s a system account named [Active] that you set for each child-parent node to determine if it should consolidate up. By default, you can set the [Active] flag to always be equaled to “1” which means an entity always consolidates to its parent, even new entities that you create. Then you are left to manually change data when an entity should NOT consolidate to its parent. To do so, you change the [Active] flag to a value = “0”. And you need to continue to store the value of “0” for the [Active] flag every period that an entity should NOT consolidate to its parent. And almost since its introduction, this way to use it has not changed and consequently, how consultants have approached it has not changed. That is to say, consolidations consultants try to discourage its usage.

Full disclosure: I fall in in the discourage camp. Why? Lack of ease in maintaining the [Active] flag. Typically, we can add a business rule solution to copy forward the [Active] value = “0” so the HFM administrator does not need to manually input it every period. Then, the HFM administrator is left to be responsible for making an update when there is a new change to the organizational roll-up. The default interface is to use the Manage Ownership module. And if there are a medium-to-high volume of organizational changes, the HFM administrator has a spend a lot of time in the Manage Ownership module because it does not work very well for bulk changes. Alternatively, the HFM administrator could create a custom data grid but it takes some setup time to make it visually easy to use and he/she must set all dimensions correctly. The trick with a data grid is to set the Entity, Account, ICP, and Customx dimensions all to the exact correct combination to input data. That might not sound so bad to more advanced HFM administrators but in my experience, it tends to trip almost everyone up at some point in time.

So, what’s a better alternative?

Well for some clients, it could make sense to use a Custom dimension to maintain the dynamic organizational view. Remember that HFM 11.1.2.x allows extendable Custom dimensions so we are not encumbered with a limit of 4 maximum Custom dimensions anymore. Here’s a scenario where I think it works very well. We have a hypothetical corporation named BigCo Inc. BigCo Inc. has two divergent reporting requirements when it comes to its organizational reporting: (1) it has a legal reporting view that is used for external reporting that should always reflect the current organizational structure for both historical and current data periods; (2) it has an alternative internal reporting view that should freeze historical data in a previous view. The commonality between the two reporting views is they both share the lowest level of the organization unit – base legal entity.

Let’s look at a simplified portion of the BigCo Inc. legal reporting view below.







Now, let’s assume this is a simplified portion of the BigCo Inc. internal reporting view at the beginning of FY 2017 below.







Then, let’s say we’re midway into FY 2017 and we get a directive from management that there will be an internal re-organization and the new internal reporting view should look like this one below.


What we know is that BigCo Inc. will want to keep the first Tot Internal BigCo reporting view for its 2017 data and then start using the Tot Internal BigCo 2018 reporting view in FY 2018 for some unknown duration, at least until the next major internal reorganization. With the alternative Custom dimension solution, we would build the Tot Internal parents as members in the Customx dimension. Here’s how it would look below and it would work for any year of reporting.






We would leave the legal reporting view as the organization hierarchy in the HFM Entity dimension.



And then we would map data from the base legal entities to the IntParent1-IntParent3 in the Customx dimension. We’re using the best data integration tool in the business – FDM/FDMEE so creating a mapping is very straightforward. In FY2017, the mapping table would look like this below.






Then when management needs us to change the internal reporting data roll-up, we would modify the mapping table and change it to the following below.












When we need to drill-down on the internal reporting view, we would use the Customx dimension as our starting point and then drill down to the base level on the Entity dimension. This can be done easily in an HFM data grid, in SmartView Excel, and in FR reports. We would get effectively the same detail that someone would need to see the lowest level for internal reporting. And a big bonus is that FDM/FDMEE is user-friendly so updating the mapping tables will be straightforward for the HFM administrator or a power user.


Leave a Reply

Your email address will not be published. Required fields are marked *

© Copyright 2017 TopDown Consulting. All Rights Reserved.