Customise and Simplify Drill-Through Regions in PBCS

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