Advanced data validation in Hyperion Planning

Since version 11.1.2.0, Hyperion Planning has had the ability to display data validation on data entry forms. This is a fairly good feature and allows the forms to become more interactive. But what if you want to go a little further? Data validation on forms is great, and alerts the users to potential problems or items that need addressing. But its only a visual alert. So it doesn’t stop the user moving forward and saving data and potentially running rules that are attached the forms. What if the process is that you want the rule to error out and data not to be calculated if data meets a certain criteria?

You can attach the form validation to process management and workflow, but to be honest, not all businesses use that functionality or that functionality doesn’t meet the requirements of the application. Also, this functionality relates to signing off data and doesn’t actually physically stop a rule from being run or a calculation taking place.

So how can you accomplish this? Well, the easy way is to just write an IF statement that tests for the data situation. In some circumstances that works. Problem is that as you know IF statements are written WITHIN a FIX statement. So if you had a particular rule that had 100s of lines of code with multiple different FIXs, you would effectively have to have an IF statement in each fix that firstly tests for the data validation test. Bit of a pain really.

In the release of 11.1.2.1, a new function came about, @RETURN. Some people may know all about this function, and if you do, then you have my permission to stop reading going forward. But I suspect most people either don’t realise its existence or, if they do know about it, they don’t fully understand the power it brings.

There are two elements to this function.

1.       Using this function in an IF statement will on result of the condition will return a custom message back to the user in the form/task list or wherever you have selected to run the rule from

2.       This is in my opinion is the powerful part, at the point the @RETURN is used the rule will STOP running. i.e no further part of the script is executed

The Tech Ref has the full description of its use here http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/frameset.htm?return.html

So the @RETURN function would allow you to build a rule that firstly tests for a data or even metadata conditions and return a customisable error message to the screen and at the same time will stop the calculation in its tracks.

The result of this feature of stopping the calculation at the point of the error means that now you can simply place a script at the start of the rule with the data or metadata validation within an IF statement and if the logical condition is met the @RETURN will stop the rule from progressing! Cool feature really.

The real power as well comes when you need data validation on a calculation that you cannot wrap within an IF statement. i.e DATACOPY.

As you know the DATACOPY command can only be used within a FIX and NOT and IF statement. So if you wanted to wrap around some logic over a DATACOPY, to say IF data meets a certain criteria then don’t copy, you can now in theory use @RETURN.

I will show this in a real life example of a process where there was a requirement to not copy data if the data validation shown on the data form was showing red.

In this example the business process is to plan the revenue and costs of a new product that is coming to market. This plan doesn’t consider fiscal month, and is more like planning a project, with the lifetime revenue and costs of a particular product being planned.

The business process is to create many versions of a products plan, mainly based on different market scenarios. So a product will end up with over 25 versions of the plan.

Once the user is happy, a particular version is then picked to then drive further fiscal processes, such as monthly forecast or the annual budget exercise, where the businesses revenue and costs are planned by month.

One of the key sets of data in the process is the start date or release date of the new product or project. This date is what is used to drive the profiling of the lifetime revenues and costs into fiscal months and years. Therefore if the business happened to plan a products revenue and costs and then forgot to plan the date from which to start recognising that data. That data would not be profiled in those fiscal processes and it would make those processes inaccurate.

So this process required some data validation. Originally this data validation was carried out at the time the user selected to take the version they are working on and saving it to a one of the many versions numbers they hold.

The form below shows this validation. The top row is the “Working” version. This is the version that is open to users to plan in. Then under that version is the list of versions that have been created in the past. This form is for one product (In one of the page selectors)

 

 

The data validation on the form is against the “Working” version. The validation is there to test if a flag that is created by there being revenue or costs but no start/release date entered.

As this is at a global level and each country around the world will have a different date, the validation is taken at a total level.  So if there is a flag (i.e there is a country with no date but there is a plan), then highlight the row red.

This validation is there to highlight to the user that they must NOT save the working version to a version number until the validation is green (i.e all data must have a date).

The problem with this is that it is the user’s choice to obey the validation. If they press save, there is a rule attached that then prompts the user to select the version number they wish to save the working version to. The rule then uses a DATACOPY command to copy data from “Working” to the version number entered.

There is then a process to select the version which must be used in other fiscal processes.

The problem is that if data has been versioned to be used in other fiscal processes without a date to start profiling the data out into months from, then the rest of the fiscal process will fail.

So the business requires a hard validation on the form that says when the user saves the form, if the validation is red then DON’T copy the data. This can actually be achieved by using a standard IF statement. The problem is that you cannot use a DATACOPY in an IF statement, so the logic would need changing so that the rule is something like “Version10” = “Working”, or a variation on that.

This would make the rule much slower than a DATACOPY command. Probably taking a few second rule to minutes.

This is where the @RETURN becomes very useful. We can keep the existing rule, that utilises the DATACOPY command, but instead add a script to the rule prior to the DATACOPY that runs the IF Statement and returns the error message

“There are countries with no dates; please return to the process to enter dates before versioning your product. No data has been impacted by this rule”.

At the return of this message the rule stops and the DATACOPY doesn’t take place. If there is no data validation problem the data copies to the version required as before.

See below for the code that I have implemented in this example. I have simply created an IF statement that looks for the flag being a value. If so then return a message, if not  then continue.

The syntax for the @RETURN function is @RETURN(“error message required”, ERROR); 

And the @RETURN must be used within an IF statement.

 

 

So let us test this;

Firstly below you will see our product again with the RED validation on the working version.

 

If we press save and select “version10”, you will see the error message come back

The EAS App log also creates the same message.

Then after you click away from the error you will noticed that no data is sitting in version10.

 

 

Now same product but with GREEN validation on the working version. I.e dates are satisfied

 

If we press save and select version10, the rule completes successfully and version10 is populated with the same data as “working”.

 

So there you have it, a fairly powerful function that can be used for all sorts of data validation. Another common approach would be to fail a rule if you are trying to add a new employee or position in an employee planning model when there are no more placeholders available.

Also, you can take this one step further and have emails sent as part of the same validation! But that is for another post..