Monday, 12 February 2018

EPM Cloud - Capturing rejections in Data Management - Part 2

Moving on to the second part where I am looking at a possible solution to capturing and handling rejections in EPM Cloud Data Management.

Just a quick recap on the first part, data was loaded through Data Management but there were records rejected due to missing entity members in the target application. With help from scripting and the REST API I covered a method to run the data load rule, check the status of the load rule and if there was a failure then download the process log, the process log was parsed for data load rejections if any were found these were sent in an email.

In this post I am going to extend the process for capturing rejections and handle them by using the metadata functionality that is now available in Data Management.

Like in the previous part I will be focusing on the entity member rejections but there is nothing stopping expanding the process to handle other dimensions, though saying that if there are rejections across multiple dimensions then it might be best to concentrate on starting with a cleaner data source file.

I am going to use the same source file where the rows containing entity members “901” and “902” will be rejected due the members not existing in the target application.

The aim will be to extract the rejected entity members from the process log and write them to a text file which can be uploaded to Data Management.

Using a metadata load rule, the members will be loaded based on mapping logic, so in this example they will be loaded as children of “900”.

Even though I am not yet a fan of the simplified dimension editor I thought I had better start using it in my examples as the classic editor is no longer going to be supported.

Once the members have been loaded and a refresh performed, the data load can be run again and there should be no rejections.

Before we can get to that stage a metadata load needs to be setup in Data Management, I am not going to go into great detail around the metadata functionality as I have already covered this topic in a previous blog which you can read all about here.

Under the Target Application Summary dimensions are added.

For the entity dimension I have only enabled the properties I will be using in the metadata load, these are parent, member and data storage. I could have got away with not including data storage and let the system pick the default value but I just wanted to show that properties that are not contained in the source can be included.

The import format is simple as the source file will only have one column containing the new entity members.

The File Type should be set as “Delimited – All Data Type” and the Data column is ignored for metadata loads.

A new location was added and a new data load rule created against the new location.

I assigned to a category named “Metadata” which I have for metadata type loads.

I did not set a file as I am going to include that in the automation script, the directory was defined as the location folder where the script will upload the rejected member file to.

In the target options of the rule the “Refresh Database” property value was set to “Yes” as I want the members to exist in Essbase when the data is reloaded.

On to the mappings, an explicit mapping is defined for “Data Storage” to map “N” from the import format to “never share”.

For the parent I used a “#FORMAT” mapping type which will take the first character of the member and then suffix “00”, if you look back I also mapped the entity member to parent, so as an example member “901” will be mapped to the parent “900”

The entity member property was defined as a like for like mapping, this is because I want the source member to be the same as the target.

If I wanted to expand the automation process further I could add in a step to upload explicit mappings for the new entity members.

Now the rule is in place it is time to go back to my original PowerShell script from the last blog post and modify it to handle the metadata load.

I am going to continue with the script from the point where the process log has been downloaded, this means the data load has taken place and failed.

In the last part I parsed the process log and added any rejections to an email, this time I am going to parse and create a file with the rejected members in.

In the process log the rejected rows of data will contain “Error: 3303”.

I will break the script into bite sized sections and include the variables which usually would be at the start of the script.

The variables above have comments so I shouldn’t need to explain them but I have cut down on the number of variables for demo purposes in this post, the final script includes variables where possible and functions to stop repetition.

On to the next section of the script which checks if there is an existing entity rejection file, if there is, delete.

Each line of the process log is then cycled through and if a line contains “Error: 3303” then it is parsed to extract the entity member, the script could be enhanced to handle multiple dimensions but I am trying to keep it simple for this example.

To break down the parsing section further let me take the following line as an example:

First the line is split by a pipe delimiter and stored in an array, for the above the array would look like:

The second entry in the array contains the member that was rejected, the third entry contains the data record.

The data record is then split by a comma delimiter and stored in an array which looks like.

A test is then made to confirm that the rejected member is part of the entity dimension as that has been defined as the second entry in the data record, if they match the member is then appended to the entity rejection file.

Now I have a file containing all the rejected entity members.

Using the REST API, the file can be uploaded to the location directory in Data Management, as there could be an existing file in the directory with the same name, the delete REST resource is used to remove the file, it doesn’t matter if the file does not exist.

After this section of the script has run, the entity rejection file should exist in the Data Management location folder.

Next the REST API comes into play again to run the metadata load rule that I created earlier in Data Management.

At this point, if I check process details in Data Management it shows that metadata rule has successfully completed.

In the workbench you can see that entity members have been mapped to the correct parent.

Within the target application the entity members have been successfully loaded.

As the metadata has been loaded and pushed to Essbase the export stage of the data load rule can be run again.

This time the data load was successful

The workbench confirms all rows of data have been loaded to the target application.

A quick retrieve verifies the data is definitely in the target application.

After the data load has successfully completed, a step could have been added to run a rule to aggregate the data, once again this can be achieved by calling a REST resource which I have covered in the past.

A completion email could then be sent based on the same concept shown in the previous blog post.

An example of the output from the full automated process would be:

So there we go, an automated data load solution that captures and handles rejections. If you are interested in a similar type of solution then please feel free to get in touch.

Wednesday, 31 January 2018

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.