We recently set up a demanding FDMEE schedule for a global
business who required half-hourly updates from their source system (JD Edwards
in this case) 24/7. This requires running five different FDMEE rules sequentially
on a half-hourly basis which, as you can imagine, is a tight schedule and identifying
any small efficiencies you can is essential.
One source of consternation for us was the drill-region – we
had five rules constantly recreating drill-regions which contained thousands of dimension
members between them, and whenever an error was encountered (such as a new
project needing to be added) the entire process was being held up due to the
drill-through process crashing. There had to be a better way, we thought.
Fortunately, it turns out that there is.
You can access your drill-regions in PBCS by opening the Calculation Manager and selecting the Database Properties button. If you haven’t
discovered this great tool before, you can think of it as the closest thing to Essbase Administration Services
(EAS) that PBCS has. You can play
with all the great functionality in this window on your own time, we’re on the clock here people!
Once you’re in Database Properties, you need to right-click
your database of choice and select Drill-Through
Definitions. Once you’re in this window, you should hopefully see an
existing drill-through definition. If you’ve run an FDMEE load before, then you
will. If not, then why are you reading this blog? I’ve written tons of blogs
about FDMEE automation through EPM Automate, go and trawl through those first!
Edit the existing region, because it’s an enormous pain to
set up one from scratch - think of all that pointless XML you don't have to write!
You should see an XML box and a Regions box. The XML box is
automatically generated by PBCS and deals with the nuts and bolts of connecting
your drill-through command with your browser and the passing through the correct
members so for Christ’s sake don’t change it.
The Essbase-savvy among you may recognise that the region
box looks remarkably like a simple @LIST formula that contains all the members
in your data load one-by-one. How inefficient and lengthy. Turns out that @LIST
works exactly the same as an @LIST formula in Calculation manager or a calc
script. Which means it accepts @DESCENDANTS
and @RELATIVE formulae.
So, we can define the exact intersection of cells that we
want to be drillable. All this drill region really does is add the little green
“drill-through” box around cells in Smart View. The drill-region automatically
doesn’t show up if the cell contains missing values, and if you drill a cell
with no background FDMEE transactions, it comes up with a blank drill-through screen,
which helps you identify any ghost data from when you’ve changed data mappings
for example.
You can control whether the drill-through definition appears
for level-0 only or not using the Level-0
Flag checkbox. What we do is set the drill region to level 0, and use the
following formula:
@LIST("Actual","Working",@Descendants(Project),@DESCENDANTS("Input
Currencies”))
This automatically creates the drill-through box on all
level-0 actuals in any period. Our customer only loads data to their actual
scenario from their source transactional system, so it works for them. However,
this could easily be amended to work for your business.
The final step is to turn off the auto-creation of
drill-region in your regularly run FDMEE rules, and the whole process will be
sped up and deal with errors much more easily. In Data Load Rule -> Target Options you will find the option you
need.
This formulaic method of generating a drill-region can be
used to power a higher level drill-region setup, which is explored in more
detail in this
Oracle white paper. To save you a click, you basically run two rules, a normal
one and one which maps data directly into parent members, then amend the drill
region to contain both. Suddenly your users can drill through from Total
Revenue and see all the transactions which rolled into it. Pretty high maintenance
in my opinion as mappings would need to be amended every time the chart of
accounts rollup changes… but that’s just my view.
I hope the technique above saves you some time. Let me know
in the comments. As ever, check out the rest of my blog posts on http://stay-ahead-in-the-cloud.blogspot.com/
for more innovative FDMEE techniques to keep your PBCS implementation ahead of
the rest!
Until next time,
Mike
