A business need which has become quite common in the last two to five years is taking your budget/plan data and placing it in a reporting application. Although Oracle has made ASO capabilities part of Planning it’s not always the best fit especially if hierarchies diverge between the systems. In my specific instance our planning application has a summarized account structure where are reporting application has the full general ledger chart of accounts.
In this post I am going to show visually the steps we use to move the data and identify technology we make use of.
With this solution our planning administration team can on-demand launch a rule to:
- Export the data
- Clear target region
- Map and load data to target cube
While considering the solution the different levels of detail on the accounts dimension needed to be handled. We thought of a few ways to attack the problem and decided all three were not as elegant as the above solution.
- Pure FDM EE e.g. extract from source, process through FDM EE, export
- Essbase load rules with hard-coded maps
- Alias tables on source side having target accounts
What makes the diagram much more elegant is our mapping is “free” and automatically updated. We have a process which loads actuals into our planning system via Financial Management Extended Analytics tables. This process does a summary mapping on our accounts dimension. Via some SQL Fu our HP to ASO maps reverse that summary logic to target the first child of the detail mapping.
More to come in future posts with some script logic as well!