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..

Welcome

Welcome to the new Absolute EPM blog.

There are some good blogs out there, providing lots of technical insight into the Oracle EPM products, new features etc. But we wanted to be a little different and instead provide a blog that not only will give technical insight, but also more of a functional and project management insight into delivering performance management solutions successfully.

So who are Absolute EPM?

Absolute EPM are UK based specialists in the delivery of Oracle Hyperion Enterprise Performance Management (EPM) solutions for budgeting, planning, forecasting and analysis.  We have extensive experience in the design, development and delivery of Oracle Hyperion EPM solutions across a wide range of industries.

The founders of Absolute EPM have over 20 year of experience in providing performance management solutions. The founders started their careers in finance roles, where they were usually the drivers for improvements in financial processes, which eventually led to their involvement in the delivery of Enterprise Performance Management solutions.  From starting out as clients, the founders have then moved on to developing best practices in the implementation of Oracle Hyperion EPM by working directly as implementation consultants for Hyperion and then Oracle.

Calculating the difference between dates within Hyperion Planning

This post will take us through a typical example of calculating the difference in days between two dates in Hyperion Planning.

Hyperion Planning has had the ability to store data as ‘Date’ for some time. But even though you set the storage type as ‘Date’, once refreshed into Essbase the member changes and ends up with the storage type as ‘number’. The date is converted into a number like 20130810, which relates to the date 10^th August 2013.

The problem with this is that the standard date functions that exist in Essbase require the date to be passed as the system date in seconds since 1st January 1970. This has always been a real pain and in the past there have been numerous ways to which you could combat the problem.

The normal approach has always been to breakdown the date into its elements (Year, Month and Day) and then using formulas work out some long winded solution to calculating the difference between dates, or carrying out any sort of date tests or calculation. Another approach could be to develop a CDF (Custom defined function), or use some of the CDF’s that are available for download from Oracle that cover date logic. See here – http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html

The problem is that unless you are comfortable with creating CDFs or are comfortable with knowing how to use the Essbase Sample code CDFs and registering them in Essbase, you are really stuck with developing long lines of complex code for some calculations using dates. Also, any sort of upgrade etc and you will need to remember to register the CDFs again.

One of the calculations that can often be difficult is calculating the difference between two user input dates in Hyperion planning in terms of days.

Well finally we are getting nearer to having the ability to do this using a standard function! (well close to a standard function :))From the 11.1.2.2 release of Hyperion Planning & Essbase, Oracle have added some more pre-installed CDF’s and specifically relating to dates, with one in particular a new CDF called @HspDateDiff. Even though this is a CDF, as it is pre-installed with Essbase so as developers you won’t need to know CDFs to an advanced level, all you need to do is know it exists and use it.

If you right click and select edit against the essbase server in EAS you will see the functions available to you, with @HspDateDiff

 

As normal there is little documented about even the pre-installed CDFs  so below I will demo the use of this new pre-installed CDF using a real life business requirement.

Let us imagine we are a business that distributes DVDs or other entertainment media. The way we forecast Revenue is to take the lifetime revenue forecast for each territory around the world for each DVD release and apply a revenue recognition profile that is based on days from the release date in each of those territories. Each day from release will relate to a cumulative percentage that revenue should be recognised at, up to 100% recognised.

So that the business can forecast the monthly forecast values, each fiscal period is assigned a period end date. This period end date for each month of the year can then be compared to the release date of the DVD release to come up with a days from release by month.

Once we have the days from release by month, we can then lookup from this to the days in the revenue recognition profile for each territory. This will give us the cumulative percentage of revenue by month and then can be applied to the lifetime revenue forecast to drive our monthly forecast.

The challenging part to this calculation is calculating the days from release. Now you can combat this by breaking down the date as I have previously referred to, but the code can become a little complex once you start spanning multiple years. Also if you require the ability to calculate ‘minus’ days from release i.e for marketing spend for example where you may incur marketing spend before the DVD is released, this can make the code even more complex.

So let us use the new @HspDateDiff CDF to combat the requirement.

Let us assume we have the below profile of period start and end dates. Periods are in the columns with lifetime value (A custom period) added to the far left. In the rows we have the accounts period start, period end dates and also the release date and cumulative days members. The period start and end dates are entered against a no product and the release date for this example will be entered against the product “A NEW DVD”

 

What we want to do is enter a release date in the Lifetime value column on the left next to “A NEW DVD” product and then see the cumulative days come back against each months columns under that release date

To test this function let us just make the cumulative days example be dynamic calc and then enter a member formula as shown below using the @HspDateDiff function. The @HspdateDiff function works by passing two dates into the function that are held in int type and in the format YYYYMMDD. So the syntax for the function is @HspDateDiff(Int,Int)

Now the difference calculated is in absolute terms and therefore if the period we are calculating is before that of the release date we will need to apply a minus to the start of the function so that we can show minus days from release.

The below formula calculates the difference in days between the release date and the period end date for each month. The first section tests to say if the release date is less than or equal to the period end, then use @HspDateDiff(Release date, Period end date) else take - @HspDateDiff(Release date, Period end date)

 So we have added the member formula and carried out a database refresh

Let us go back to the form and enter a release date of 15^th May 2013 and see the results

 

 

 As you can see, its worked. The Cumulative days example member now has negative days up to Apr and then positive days past that. With May saying a days from release of 16 (31 days in May – release on and inc 15^th May). Also Apr brining back -15 days from release as the DVD releases on the 15^th May. Also notice we have crossed financial years into FY14 and the calculation still works.

So there you go, a new simple way to calculate the difference in days between two dates in Hyperion Planning without lots of scripting or building or registering and custom CDFs!