PBCS Summary Drill to Transaction

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

Conditional FIX statements

A few years ago the very very talented Mike Henderson gave a Kscope presentation entitled Power Tools: Using VAR + LOOP for If-Then Procedure Logic and ARRAY for Speed (the presentation can be found here.) First and foremost the presentation has the easiest to understand and most detailed description of how arrays work in essbase – including a perfect use case and lashings of ginger beer code!

It also describes a nifty way of building conditional IF statements, allowing differences in treatment between two scenarios, without the performance and code cleanliness hit of having to do repeated IF(@ISMBR(“Sparse”)) intersections.

The only issue I had with Mike’s proposal is that…it’s slightly confusing.

SET RUNTIMESUBVARS { 
  runForecast=0 Enter 1 to skip Forecast scenario; 
  runBudget=0 Enter 1 to skip Budget scenario; 
};
 
VAR ForecastBreak = &runForecast;
LOOP(1,ForecastBreak)
  [Forecast code goes here]
ENDLOOP
 
VAR BudgetBreak = &runBudget;
LOOP(1,BudgetBreak)
  [Budget code goes here]
ENDLOOP

Basically it’s almost inverse of what you’re expecting. The variable ForecastBreak needs to be set to 1 if you want to SKIP the Forecast code. After I’d used the code in a few different places, I ended up practically shelving it because I was having to explain what the code was doing (sometimes to myself. I tried not to do this out loud.)

That said, it’s still an elegant solution to a fairly fundamental problem. Most other coding languages have the ability to do a conditional IF statement, and it definitely has use cases within Hyperion.

Fast forward a few years

A few years later I was doing some (all too brief!) work with the brilliant Danish Hotta and noticed in his code the following:

SET EMPTYMEMBERSETS ON;

FIX("Forecast" AND {Var_Scenario})
  [Forecast code goes here]
ENDFIX
FIX("Budget" AND {Var_Scenario})
   [Budget code goes here]
ENDFIX

Being an inquisitive fellow, and having never seen that code pattern before, I immediately asked (somewhat incredulously in hindsight) what on earth that was meant to do. He explained that it was to split the code up between Forecasts and Budgets.

I’d like to pretend that I believed him immediately and obviously didn’t have to have it proved to me…and given that this is my blog I shall take the liberty in stating that it only took around 10-15 minutes of patient explanation before I understood what it was doing.

So what IS it doing then?

Since this appears to be completely undocumented behaviour, let’s break it down to the component parts and work out what is happening.

SET EMPTYMEMBERSETS ON;

Emptymembersets is that function that you don’t need to understand, right up until it bites you and then you set it in every script.

The documentation is actually very clear as to what it’s doing:

If EMPTYMEMBERSETS is ON, and a FIX command evaluates to a (sic) empty member set, the calculation within the FIX command stops and the following information message is displayed: “FIX statement evaluates to an empty set. Please refer to SET EMPTYMEMBERSETS command.” The calculation resumes after the FIX command. If a calculation script contains nested FIX commands, the nested FIX commands are not evaluated.

However, the pertinent point is ‘what does evaluation to an empty member set’ actually mean. It is not a evaluation of data, instead, an empty member set is where the intersection of the fix statements resolves to something fundamentally invalid. Often times this will occur when using functions within FIX Statements ie:

SET EMPTYMEMBERSETS OFF; /* This is the default */
CLEARDATA "Actual";
FIX(@Children("BegBalance"))
    Actual = 100;
ENDFIX

Begbalance is a level zero member, ergo it does not have children, so the reference it is an invalid. The EMPTYMEMBERSETS flag then defines the calculation behaviour, with the default behaviour completely ignoring the Fix statement and loading data to all periods.

emptymbrsets_off_log
emptymbrsets_off

Setting EMPTYMEMBERSETS to ON then changes this behaviour – ignoring both the fix statement and all of the calculations within it

SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual"; 
FIX(@Children("BegBalance")
    Actual = 100;
ENDFIX

emptymbrsets_on_log
emptymbrsets_on

To further the point, the greatly venerated C.Lackpour wrote a very long blog post on this – going into detail about differences in behaviour of EMPTYMEMBERSETS with nested fix statements. I would strongly recommend reading it before continuing this post – I’ll wait.

Okay…you’re back

So now we know what EMPTYMEMBERSETS does – how does this help us with a conditional IF? This is where that Boolean AND function comes into play.

SET RUNTIMESUBVARS
{
Var_Scenario="Budget";
};

SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
CLEARDATA "Budget";
FIX(Actual AND &Var_Scenario)
	AUD = 100;
ENDFIX
FIX(Budget AND &Var_Scenario)
	AUD = 200;
ENDFIX

In FIX statement above, the scenarios are hardcoded, and then a scenario variable is passed through with a Boolean AND statement. In this case I’m using Essbase run time sub variables but could just as easily be done in a Planning application with calculation manager variable.

Tricka Technology

Triggering the calc above and looking first at the output in smartview, we can see that the Budget data has calculated, and the actual data has not changed.
budget_output
Reviewing the log we can see the that the resolutions of Actual AND budget has resolved to an empty set, therefore ignoring the FIX statement and all of the code therein.
budget_log

Tricka Technology the Remix

Changing the variable to Actual and rerunning?

SET RUNTIMESUBVARS
{
Var_Scenario="Actual";
};

SET EMPTYMEMBERSETS ON;
CLEARDATA "Actual";
CLEARDATA "Budget";
FIX(Actual AND &Var_Scenario)
	AUD = 100;
ENDFIX
FIX(Budget AND &Var_Scenario)
	AUD = 200;
ENDFIX

Exactly what we’d expect to see:
actual_output

The most interesting aspect of this is to look at the log. The evaluation of the fix statements actually occurs as the first step of the code – and essbase is therefore aware that this fix statement can be ignored and doesn’t even try to resolve it later. This can act as a staggering optimisation in any code because it completely skips over even the resolution of anything within the fix statement.
actual_log

So there you have it

A clean and simple method to have conditional and variable driven FIX statements optimally calculating a component of a script. It works across both Dense AND sparse intersections and can be used both to standardise and simplify code – as well as providing the ability to selectively optimise any rules, particularly where you can pass through parameters from a planning dataform.

Cheers
Pete