Hierachies....from the Top Down
- by Joe G
I've been struggling with how to write on the topic of the importance of hierarchy design. It's not so much that hierarchies haven't always been important, it's more of that with Fusion, the timing of when the hierarchies are designed should take a higher priority. I will attempt to explain.....
When I was implementing applications, back in the day, we had the list of detailed account values to enter with the obvious parent accounts. Then, after the setup was complete and things were functioning, the reporting phase started. Users explained the elements that they want on the reports, what totals should be included, and how things should be compared. Frequently, there was at least one calculation that became a nightmare either because it was based on very specific things that didn't relate to anything else or because it was "hardcoded" so that when something changed, someone need to "fix" the report.
With Fusion, the process changes slightly. You still want to enter all of the detailed accounts, but before you start adding parent values, you should investigate the reporting requirements from the top-down. It's better to build hierarchies based on the reporting requirements than it is to build reports based on random hierarchies. Build reports based on hierarchies that resemble the reports themselves, and maintain the hierarchies without rework of the reports.
For example, if you look at an income statement, you may have line items for Material Costs, Employee Costs, Travel & Entertainment, and Total Operating Expenses. In your hierarchy, you have detail values that roll up to Material Costs, Employee Costs, and Travel & Entertainment which roll up to Total Operating Expenses. Balances are stored automatically in the cube for each of these. When you define the report, you pick each of these members - no calculations required. If a new detail value is added, you simply add it to the hierarchy, and there is no need to modify the report.
I realize that there are always exceptions that require special handling, but I am confident that you will end up with much fewer exceptions if you make reporting a priority and design your hierarchies from the top-down.
