EPM Cloud - Capturing rejections in Data Management - Part 1

I have been asked on numerous occasions whether it is possible to capture rejected records for data loads in Data Management and either send an email with the details or process the rejected records.

EPM cloud changes at a fast pace when it comes to functionality but currently there is no inbuilt ability to handle any of these requirements, also with no Jython scripting like with on-premise FDMEE this rules out a custom solution within Data Management itself. This is not so much of a concern though as most of the automation is handled outside of Data Management with help from EPM Automate or the REST API, so this vastly opens up the possibilities to meet the requirement.

In this two-part blog post, I am going to look at one possible way of capturing rejected records and sending out an email, I will then expand on that to process the rejected records using the metadata functionality available in Data Management.

Before I start I will give my usual disclaimer that this is just one possible solution out of many and it doesn’t mean it is right for everybody, this is not going to be a step by step guide and there is an assumption that you have a good understanding of loading data through Data Management.

Let us first go through an example of loading data where a few records will be rejected, I am going to concentrate on entity members being rejected and will be using the following simple source file.

The above file contains two entity members “901” and “902” that are not currently part of the entity dimension in the target application.

The import format, location, load rule and mappings have already been created which I don't need to go through as there is nothing different about them.

As the data is all numeric, the load method in the data load rule has been set as “Numeric Data Only”, this means data is directly loaded to Essbase using a load rule.

The full data load process is run from the workbench and the export fails but at this stage it is not clear why.

Even if you look at the process details, it only provides details to inform the export data to target application step has failed.

It is not until you open the process log and analyse it that you realise the reason behind the failure.

The process log highlights there were data load errors and includes the records where the error occurred, the error “3303” means that there were rejections, the entry also includes the member which was rejected, so you can see that the entity members “901” and “902” were rejected.

On a side note if the data load method was one of the all data types then the error in the log will be different and looks something like:

The process failing in Data Management is a bit deceiving as the data load did not fail, the data load completes but a few of the records were rejected.

Performing a simple retrieve shows data was loaded to the target application for the valid rows.

It would be nice if there was an option in Data Management to change the failure to a warning and provide a more informative process step message, even better if there was enhanced logging around the errors or a separate download file containing the load errors, maybe this will happen at some point in the future but this is where we are at the moment.

You may be asking, why not use data load mappings to assign any unmapped members to a defined member like:

This is perfectly acceptable but the issue is if you have multiple unassigned members in the data then you would need to first understand if any data has been loaded to the unassigned bucket, find out which members, create the members in the target application, clear the data, update data load mappings and finally reload the data.

The idea of this post is to stick with members being rejected and then be informed if there are errors with the use of automation.

We know that if a load process fails the log contains the errors so it is just coming up with a way to process and automate this.

With EPM Automate it is possible to find out the load process has failed, if we take an example with the same data set using the “rundatarule” command to execute the rule:

In the example above the “LD_Actuals” data load rule is executed just for the export step for the period “Jan-18”, EPM Automate returns that the command failed to execute which is not the most informative message, in reality the command did execute and the process completed with a failure, same as earlier the data has been loaded but there were rejections.

Instead of capturing the returned text from EPM Automate, the exit code could have been accessed which in this case would be 1, either way it doesn’t tell us much.

It is certainly possible to end the process here and send out an email:

I wouldn’t be happy with ending at this point so let us keep going, besides not being presented with the most informative failure message there is no indication to the Data Management process ID, if we know the process ID we could download the process log and then parse to find out more details on the failure, ok there are ways to list out files and then check for the latest log but there is room for error, for example if multiple processes complete around the same time.

EPM Automate is built on top of the REST API so let us take a look at the type of information that is returned if the same process is replicated.

I have covered the REST API in the past so I don’t want to go into too much detail but using a REST client I can simply demonstrate running a rule by passing in parameters in the body of the request.

The response includes some valuable information such as the log details, process ID and the status of the process.

The response indicates that the process is currently running so it can be repeatedly checked using a REST request until it completes.

The response now indicates the process has failed and the advantage over EPM Automate is that we know the name of the process log which could be downloaded.

This process can be automated using scripting and I am going to provide an example of how to do this using PowerShell but it is up to you which scripting language you use, please be aware I try to keep it as simple as possible to display on the blog so it doesn’t handle every possible outcome, I leave that for the final scripts I use.

I will break it down into bite-sized sections and to start with the variables are defined, this include the Data Management data load rule name, period name, import and export modes, the local path for files and logs and the URLs for the REST API resources.

Now on to the real work, the username and password are encoded which will be added to the header of the REST request.

The body of the REST request is built using the defined variables and then converted into JSON format.

Next a request to the Data Management REST resource is made which executes the data load rule based on the body of the request.

Once a response is returned the status can repeatedly checked until the Data Management process completes, I am not going to include this section as I have done it many times in the past and if you want a simple example then have a look here.

At this point if I check the status of the process, it will have failed.

So now that we know the job failed and the log file name, the log can download using one of the application management REST resources.

Once again I have already covered downloading files using the REST API which you can read all about here.

At this stage, the log for the process that failed has been downloaded.

The automated process could end here by sending an email and attaching the log, this could be achieved with something similar to:

The email could contain information such as the data load rule name, the process ID and the log of the failed process.

Taking it a step further, before sending the email the process log could be parsed for data load errors.

In the above example each line of the process log is read and checked for “Error: 3303” and for demo purposes they are outputted to the console.

Instead of outputting the errors to the console these can either be stored in a variable or written to a file.

In my example I have stored the errors and then included them in the body of the email but they could have easily been written to a file and sent as an attachment.

Now the email recipient will know that the data load process has failed with load errors and they will also have visibility of the records that were rejected.

This is all good but it can be taken a step further, the members that were rejected could be loaded into the target application using the Data Management metadata functionality, the data could then be reloaded to the correct members without any rejections.

Look out for the next part where I will go through the process. 

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.