FDMEE and Essbase calculations

Since January, I have been on a project using FDMEE against Essbase and Planning applications. There are certainly many more companies using FDMEE with HFM, so I wanted to share some things I have learned about FDMEE and Essbase.

The topic of this post is about the process of running calculations in Essbase. Typically with Essbase data loads, especially repetitive loading of data during a close cycle, we will want to clear out the data first and then load in the new data. Of course, after that data loads to Essbase (BSO) we will likely need to run an aggregation after the load.

Lucky for us, Oracle baked in the ability to run calculations on your Essbase Target Applications at certain points during the load process. In this post, we will cover how to set up a clear script before a data load, but an aggregation script after the load is the same process with a different script.

First, we need an Essbase calculation script to run a CLEARDATA command. For this occasion, I wrote a quick little CLEARDATA script to clear out Sales in California for whatever period I happen to be running in FDMEE. I have saved the script in the Sample.Basic application as “fdmClear”.

SET CALCTASKDIMS 4;
SET CALCPARALLEL 4;
SET UPDATECALC OFF;

SET RUNTIMESUBVARS
{
POVPer = "";
};

FIX(&POVPer, "Sales", "California")

CLEARDATA Actual;

ENDFIX

Notice the use of RUNTIMESUBVARS for my POVPer variable. By defining these in our calculation script, FDMEE will be able to pick up the variable and pass a value when it runs the calc script.

To set up these calculations, we need to be on the Setup tab of FDMEE and have the Target Applications window up. If we switch to the Calculation Scripts tab, we can add our scripts.

EsbCalcs1

We need to click the Add button to set up our clear script. I have entered in the “fdmClear” name for the script and clicked on the pencil icon to add my calc script parameter. In the “Set Calculation Script Parameters” window, I clicked the magnifying glass icon. This forces FDMEE to look back at the calc script and pick up the POVPer variable.

EsbCalcs2

Now we have a choice as to what value to assign to our POVPer variable.  In our simple use case, we will use the POV Period option and click OK.

EsbCalcs3

Next, we can select the appropriate scope for the calculation. For instance, if we have a clear for this application and it only affects a particular slice of data, we would want to use a more granular scope like data rule or location. Script scopes with higher granularity override those with lower granularity. So, if we have multiple scripts with different scopes, the most granular one will take effect (data rule scope overrides a location scope, location scope overrides category scope, and category overrides application).

EsbCalcs4

For any selections other than Application, a Scope Entity box will appear and allow you to specify what rule, location, or category to use. I chose Location and selected my Loc1 sample location.

The next choice is to decide when to execute the calculation: before the data load, after the data load, before the check process or after the check process.  In my case, I selected Before Data Load.

EsbCalcs5

Finally, if we end up with multiple calculations with the same script scope, scope entity, and event, we can specify a Sequence value to order the calculations appropriately. I only have one calculation, so this is left null.

Calculation Script Parameters

I was curious if a regular substitution variable would work, so I created a Market subvar (mkt) on the Sample.Basic application and a Measures subvar (acct) as a global variable to test.

EsbCalcs6

EsbCalcs7

No dice. When we attempt to add our substitution variables in the parameter names window, FDMEE is only able to retrieve the RUNTIMESUBVARS defined within the script.

EsbCalcs8

Advertisements

3 comments

  1. If using substitution variables why would you need to add them to the parameters? It would mean that you could not change them from FDMEE, but wouldn’t the calculation still run and populate the variables based on what is defined on the Essbase server?

    It would be less dynamic than passing variables from FDMEE, but I would think it would still execute

    1. Good point, Glenn. You could still use an Essbase subvar and the values would populate as they would in any other calc script. It would be less dynamic, you are absolutely right.

      The point I was making was that a regular subvar cannot take on the value of an FDMEE POV value, it must be a RUNTIMESUBVAR.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s