G’day All,

Apologies – haven’t written in forever, real life got in the way as it sometimes does!

But I decided to put something together on a new feature dropping in the Sept-18 PBCS release: Summary Level Drill.

Summary what now?

So FDMEE\Data Management has had the ability to drill back to staging table for a while – and for specific sources (read: Oracle) back to the transaction detail in the GL. A great feature sure, but not without it’s limitations. Specifically you were limited to drilling only from the intersection where the data was loaded – notionally almost always level-0.

Dependant on the structure of the chart of accounts, that can often mean users need to go to the bottom of a number of reasonably superfluous dimensions in order to get to a point where you can drill from. Making it even less functional for the users, often you want to see comparisons of transactions across all dimensions ie: all the transactions for a certain account, no matter which entity \ cost centre \ product \ customer \ project it was posted. This problem is only gets worse with ASO cubes with additional levels of detail and additional dimensions for reporting.

This lead to a number of interesting alternatives.

Essbase Studio \ Essbase Integration Services

Old school – but it works. Cubes built through essbase studio natively inherited the ability to drill from upper levels – mostly because the query could be handed off to essbase studio which already knew the level-0 members.

Downsides are plenty, but fundamentally
– it doesn’t work for planning
– it’s basically deprecated
– an equivalent doesn’t exist in the cloud

Posting to an upper level in FDMEE

The talented Ahmed Hafez (aka: HyperionJedi) worked out this gloriously hacky method.

FDMEE Drill Through – Myth Buster Edition – What is really possible?

Basically because FDMEE allows drill from anywhere you load data – you load data to the summary nodes.

The downsides?
– You need to create an additional load per upper level dimension
– You need to always load the data multiple times
– it has (significant) scale issues past 2-3 additional dimensions

Drillbridge

The brainchild of essbase nerd Jason Jones – this is a full featured drill through reporting tool.

Basically he’s doing it ‘properly’. When you hit an upper level – drillbridge fires off a query to the API to get the level 0 members and builds the query that way. Intentionally so, delivering an effective replacement for Essbase Studio (so people didn’t implement Studio JUST for drillthrough) was one of the main reasons behind the build.

So what’s annoying?
– It’s another app in the stack
– You need to store the data ‘somewhere’ – that will likely easy to do on-premise, but a possibly a little harder if you’re going 100% cloud.

So let’s look at Oracle’s crack at it

For this example I’m going to assume you’ve already got drillthrough turned on.

It’s fair to say they’ve made summary drill through as simple as possible – simply go into the Application Options in target application settings, and Enable Drill from Summary

Then trigger a quick reload:

And Bob’s your mother’s brother!

Or…err…Not. We still can’t drill from the upper levels.

So what went wrong?

Let’s go and look at the drill through definition that has been defined by FDMEE:

So the level-0 flag is now disabled, but the @LIST of drill intersections is a bit weird.

Basically we’ve ended up with mostly the level zero members – specifically the members where data is loaded.

Let’s change it to something else for the account dimension.

And let’s check our form?


Right click on a parent member

Success! Boil in a bag etc etc.

So did it work? Oh Yes..

It’s gone and brought back all of the accounts from the parent account node that we selected.

As a bonus they’ve built a target data grid that shows all the level zero intersections from Essbase.

AND a bonus drillthrough log!

with the important details – showing all of the accounts that it has returned.

So what about that hack job earlier?

Oh yes – let’s go and fix that.

So in our earlier efforts only the target accounts were being marked as valid in the drill definition. In order to make the upper levels available to drill on we need to turn off this marking so all the parent members are available to drill from.

Returning to our Application Options – untick the Create Drill Region check box on the affected dimension – in this example COA.

Trigger a reload.

And now all the accounts are missing from the list.

And all the summary accounts are available to drill from!

So I just turn off all the drill definitions? Easy!

Er… Not quite.

Let’s see what happens if we turn off the the view dimension definition?

Yeah – that’s not what we want. All of those nodes aren’t drillable…be awfully nice if we could customise it.

So…you know where this is going

Okay – we want to use the FDMEE drill through tables and views, and the ability to drill from summary detail, but we DON’T want to use the default definitions. Welcome to hack country.

Firstly, create a normal definition as per the process above and open it in Calc Manager. Take a copy of the the XML Definition.

Once you’ve got a copy of the definition, delete the default one.

Create a NEW drill through definition with a new URL name, copy in the XML definition from the default, and create your own drill through region with the summary levels defined.

Return to Data Management and turn off the Drill region creation.


And now, whenever you load the data into PBCS it doesn’t recreate the definition. Instead users drill into the custom drill through definition that you’ve defined. Very useful in the circumstances where you have non consolidating members in a hierarchy – or alternatively want specific members to be enabled


Wrapping it up

Okay – so it’s very possible that the default functionality will work for you without needing to turn to hacks. None the less, it’s a really good way to get the absolute most out of the new drillthrough logic!

Now just hoping that Oracle adds some more functionality to the drillthrough report itself – even simple items like giving us the ability to easily organise, hide and reformat columns within the report would really enhance it’s end user appeal. Still, not bad for a 30 minute change over!

Cheers
Pete