Wednesday, 17 January 2018

Simplifying a planning process with help again from Groovy

I recently had the chance to look at improving an error prone and over complicated on-premise process to run FDMEE data load rules from Planning business rules.

The solution started with a business rule using the Calculation Manager custom MaxL function to call a batch file with the SHELL command.


The batch script then called another script using PSExec to allow remote execution as the script required running on the FDMEE server and not on the Essbase server, this was required because the FDMEE load rule was being run with the standard batch scripts that need to be executed from the FDMEE server.

The process included the calling of MaxL scripts to read and update substitution variables, the output of these scripts also needed parsing as part of the batch.

To add to all the complication there was a major issue where multiple users could run the same process while it is was already active.

As you can image there was plenty of room for improvement with the current solution, though instead of trying to fix areas of the process I took a step back and tried to look at the solution as a whole and this is where Groovy came in to help.

If you are not aware the ability to use Groovy scripting with planning business rules or Essbase calculation scripts came into action from Calculation Manager patch 11.1.2.4.006, it provides much more flexibility over Essbase custom defined functions and is easy to get up and running.

Anyway, straight away I knew Groovy could help with running of the FDMEE load rules as I have already tackled this problem before, instead of covering old ground again there are a couple of posts I have written in the past that go through the steps.

The first post looks at using Groovy to call a Java servlet which basically the same method as what the FDMEE batch scripts do behind the scenes, the advantage of using the Groovy method is that the script does not have to be run from the FDMEE server as it just requires http access to the web server.

Planning to FDMEE with a little help from Groovy

The above post was written before the FDMEE REST API appeared in 11.1.2.4.210, not to worry though I have covered using the REST API with Groovy in part of the following post:

Loading Planning metadata from a relational source through FDMEE and Planning

If you have not already I recommend going through the posts as in this post I am going to assume there is an understanding of what Groovy scripting with business rules is all about.

By creating a Groovy script to run FDMEE rules from a business rule I have already improved the original solution and removed the need to run batch scripts across servers with PSExec.

Now on to the next problem of stopping the rule from running if it was already active, the original solution involved a user selecting an entity with a runtime prompt so I am going to stick with it in my example.

Basically, you wouldn’t want the rule to run if it was already running for a selected entity, in planning one of the techniques could be to set a flag within in the database and if the flag is active then stop running the rule.

In the following example, a fix is made down to a single block in a restricted part of the database, the entity is defined by a RTP

A member named “FDMEE_Run_Flag” controls whether the process is already running, if the value is 1 then it is running, if it is 0 it is not running.


If the flag is set to 1 then the rule is stopped using the @RETURN function, if it is not set to 1 then the flag is set to 1, the Groovy script is then called using the @CalcMgrGroovyNumber function to run the FDMEE process and after it completes the flag is set back to 0.

To demonstrate I updated the value of the flag member to 1 or “Running” as the member has been assigned a Smart List.


The business rule was launched and the same entity selected.


An error is returned and the rule then exits.


This type of solution can be perfectly acceptable but there can be times where the flag is not reset for example if the rule did not complete, this would mean the end user would receive the same error message each time the rule is run until the flag was reset, using this type of method does also require some minor changes to the planning application and process.

I was looking for a solution that had minimal impact to the planning application and processes, as I was already executing a Groovy script then maybe it could help to restrict the FDMEE data load rule from being run.

Before I go through solution I want to point out that this is just one method out of a lot of possibilities and it doesn’t mean it is right for everyone.

Whenever a business rule is launched there is information captured in the Job Console.


This means you know when a rule is running or not, the information is stored and read from a database table in the planning applications repository called “HSP_JOB_STATUS”.


The table has a column called “RUN_STATUS” which defines whether the rule is running, the table also captures RTP details and the value selected.

Groovy SQL makes it easy to query a database table so the idea was to check the table to see if a rule is running for a specified entity, if it is running return an error, if it is not carry on with the processing and then run the FDMEE load rule.

Just before I get on to the script it is worth pointing out that I have been testing with the latest version of Calc Manager, which at the time of writing is 11.1.2.4.010, Groovy was made available from 11.1.2.4.006 but there were a few changes in 11.1.2.4.008 which I take advantage of so I recommend being either on the latest patch or at least 008.

If you are just starting out then download the latest 2.4 version of the Groovy jar because from version 2.5+ the requirement is for Java 7 and unfortunately 11.1.2.4 still implements Java 6.

To get up and running check out my earlier blog but in summary you only need to download the Groovy jar, copy to the Essbase server and create a properties file, restart Essbase and off you go, easy.

From Calc Manager 11.1.2.4.008 it is possible to create a properties file called groovycdf.properties in the same location as the Groovy jar which is

<MIDDLEWARE_HOME>\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf


You probably want to set the “staticcompile” parameter to false unless you want to go down the route of having define every variable and have it assigned the correct type, which means you would lose some of the Groovy goodness, though saying that I think Groovy in EPM Cloud has it enabled by default.

Setting the “sandbox” parameter to false overrides the whitelist package and classes “groovyaccess.properties” file which means you instantly have access to all the packages within the classpath, this is great for developing though you might want to consider restricting the packages when you move to a production state.

On to the script, I am not going to include every line of code as it there to provide an idea of how it was achieved, I have changed around the script a little so it hopefully makes more sense, so before the purists hound me that is the reason why :)

The script could be simplified as I have written it so that no database connection information including passwords are included, this adds more steps but at the same time makes it reusable and requires no changes when migrating across environments.

I will break the script down into bite-sized chunks, I am going to show the business rule script later but all you need to know is that three variables are passed into the Groovy script from the rule, one for the rule name and the others for the RTP name and value, so in this case it would be “Entity” and the entity name, this is required when querying the job console table.

The first section converts the entity name to include quotes, this is because the RTP values in the job console database table are surrounded by quotes.

The passed in variables are outputted to the log.

Next, a function is called which directly queries the EPM registry and returns the connection information to the planning system database.


Now that the planning system database connection details have been gathered, a query can be made to return the connection information to the planning application database.

The table “HSPSYS_DATASOURCE” holds the connection information for all the planning applications.

Basically, a query is made to return the following information:


This is done with simplicity using Groovy SQL and once the information has been retrieved it is then stored.

If for some reason no records are returned the script ends and returns an error number back to the business rule.


If you wanted to hardcode the connection information to the planning application database in the script then the above would not be required and you would only need the next section.

Now a new database connection can be made to the planning application and the job status table queried to see if the rule is already running for the same entity.


If the query returns a count greater than one it means the rule is already running against the same entity so return an error code back to the business rule.

The count value should always be at least one because there will be an entry in the table for the current execution of the rule.

If the query doesn’t return a count greater than one the processing continues and calls the FDMEE REST API to run the data rule.

Taking an example of the rule running, the job console would display a run status of “Processing”


The script would run the following SQL to check if the rule is running for the specified entity.


The query returns a count of 1 which means there is only one instance of the rule running.

On to the business rule which fixes down to a single block and one member so the Groovy script is only called once.

The Groovy script is then executed using the @CalcMgrGroovyNumber function and Calc Manager RTP variables for rule name, RTP name and value are passed into it.

A temporary Essbase variable holds stores the value returned from the Groovy script, if the value is 2 then it means the process is already running, if the value is 3 an error occurred in the script, both values generate an error back to the user.


The RTP name and value variables are set to be hidden so they are not shown at runtime.


Now on to running the rule.


If the rule is not currently running for the same entity the script should run through the end.


The log will contain:


If the rule is run again for the same entity while the process is still running the following error will be displayed and the process will stop.


This time the log will contain the following:


If an error occurs in the process the following error will be returned to the user.


Earlier I mentioned that the original solution called MaxL scripts to read and update substitution variables as well as having to parse the output log to achieve this.

With Groovy it makes life much simpler as sub vars can be passed into the script, for example say we are interested in these variables.


The sub vars could be passed in Groovy script in the following way.


The Groovy script would automatically be able to access them.


You can then process them however you like, in this example they are just written to the log.


You don’t have to pass them into the script as Groovy should be able to directly access the Essbase Java API packages.

With the following script, the variables can be retrieved using the API or just as easily be created/updated.


The above script calls a function which returns a SSO token so there is no need to hardcode passwords, alternatively the password can be included in the connection to Essbase.

The script reads the “curYear” and “curMonth” variables and writes  them to the log.


A new sub var has also been created.


When you put this all together, one Groovy script has replaced the original over complicated process, the new solution can restrict rules from running concurrently, execute FDMEE data load rules, manage substitution variables and removes the requirement to run scripts across servers.

If you want to find out more then please feel free to get in touch.

Monday, 11 December 2017

What’s new in FDMEE 11.1.2.4.220

Well it almost a year since the 11.1.2.4.210 patch set was released but finally the next patch .220 has landed.

Besides bug fixes there are some new features in the .220 release which I will quickly go through in this post.

I must admit I was expecting more of the functionality from EPM Cloud to be pushed down to on-premise in this release but unfortunately that has not been the case.

There are four new features in the patch readme and one of them existed in .210, anyway let us have a quick look, the first new feature is:

Import and Export of Mapping Scripts in Text Mapping Files

“FDMEE now supports the export and import of mapping scripts in a text file. This support includes both Jython and SQL scripts. The scripts are enclosed in a <!SCRIPT> tag.”

It is amazing it has taken this long to include custom scripting in mapping text files, if I take the following mapping which has custom Jython scripting applied.


Then export the mapping using either the “Current Dimension” or “All Dimensions” option.


The file is then saved to a specified location under the FDMEE root folder.

If the exported mapping text file is opened you will see that the only difference to previous versions is that the custom script section is included.


The custom script can be spread across multiple lines and just needs to be enclosed with

Now to import a mapping text file that contains a simple custom SQL script.


Just like in previous versions the file needs to uploaded or copied to a location under the FDMEE root folder and then imported.


The mapping file is selected.


The import mode and validation options are exactly the same as before.


Now the imported custom script is available with the mapping definition.


It is also possible to use the REST API to import and export the mappings which you can read about here.

On to the next new feature:

Registering Duplicate Target Application Names

“FDMEE now enables you to register target applications with the same name. This may be the case where a customer has multiple service environments and the application name is the same in each environment, or the application names are identical in development and production environments. 

This feature enables you to add a prefix to the application name when registering the application so that it can be registered successfully in FDMEE and be identified correctly in the list of target applications.

Target applications with prefixes are not backward compatible, and cannot be migrated to an

11.1.2.4.210 or earlier release. Only a target application without a prefix name can be migrated to an earlier release.”

So basically, when you add a new target application you have the option to include a prefix.


I have already written about this functionality in EPM Cloud and it operates in the same way, you can read about it in more detail here.

The third new feature is:

Support Member Names with a Comma When Exporting to Planning

“When export to Planning, you can now export a dimension member name that contains a comma (,).  A new Member name may contain comma setting has been added to Target options, which enables the feature.”

This is another feature that has been pushed down from EPM Cloud where it appeared in the 17.07 release.

I have always wondered why it needs to be an option and it should be able to handle member names with commas by default.

The property can be set at either target application level.


or in the target options for a data load rule.


If the property value is not set it will default to no.

I had a look at this functionality in the past and found that it does not relate to all load methods.

If I take a rule with a load method of numeric data only and set “Member name may contain a comma” to no.


In the following example, there is an entity member containing a comma, the export to the target planning application completes successfully.


The file that is produced before being loaded to the target application shows that the member containing the comma is enclosed by quotes so there is no problem with it loading.


There are no problems loading members containing commas with both the numeric data only load methods.

If I switch the rule to an all data loads method.


This time the export fails.


The process log contains the following error:

“The member Dummy does not exist for the specified plan type or you do not have access to it.”

Looking at the export file generated by FDMEE provides a clearer indication to why the load failed.


When setting the load method to “All Data Types” the outline load utility (OLU) will be used to load the data, if it numeric data only load method an Essbase data load rule will be created to load the data.

With the OLU method the Point-of-View contains a comma separated member list and because the member contains a comma this is causing the load to fail, if it was a driver member such as account that contained the comma the load would not have failed.

Also contained in the log is reference to the file delimiter.

DEBUG [AIF]: SELECT parameter_value FROM aif_bal_rule_load_params WHERE loadid = 1464 and parameter_name = 'EXPORT_FILE_DELIMITER'
DEBUG [AIF]: fileDelimiter: comma

The export delimiter will be set depending on the yes/no value in the “Member name may contain comma” property.

You can also see that the /DL parameter is set as part of the OLU load.

DEBUG [AIF]: Data Load: exportMode=STORE_DATA, loadMethod=OLU
Property file arguments: /DL:comma /DF:MM-DD-YYYY /TR

The POV is comma separated if you add a member into the POV that has a comma then it will fail.

Let me repeat the process but this time set the property value to yes.


This time the export is successful.


The export file that is produced is tab delimited.


The value retrieved from the FDMEE repository is tab.

DEBUG [AIF]: SELECT parameter_value FROM aif_bal_rule_load_params WHERE loadid = 464 and parameter_name = 'EXPORT_FILE_DELIMITER'
DEBUG [AIF]: fileDelimiter: tab

The OLU field delimiter is set to tab using the /DL parameter.

DEBUG [AIF]: Data Load: exportMode=STORE_DATA, loadMethod=OLU
Property file arguments: /DL:tab

So, even though “Member name can contain comma” can be set for any load method it will only apply to all data types and only affects members in the “point-of-view”.
Remember if the property is not set it will default to no.

The final new feature is actually not a new feature as it exists with the same functionality in 11.1.2.4.210

Support for REST APIs

“REST API can be used now in FDMEE to execute various jobs that run data load rules, batches, scripts, reports, and the import and export of mapping rules.”

I think the only difference is that the REST APIs are now officially supported even though they are referenced in the 11.1.2.4.210 FDMEE documentation.

I have previously written a couple of detailed posts about the REST API in FDMEE which you can read all about here and here.

There are some new features that have been missed from the patch readme, one of them being the data type load method “All data types with auto-increment of line item


Not to worry I have two posts about this functionality which you can read about here and here

Finally, with the all data load types method there is the option to apply mappings to a data dimension.


Guess what, I have also written about this functionality which you can read about here, look for “Is mapping data values possible with the all data type?

I think that covers off what's new in FDMEE 11.1.2.4.220