Oracle Enterprise Cloud Planning Tech Tip – Focused Calculations
Renowned French philosopher Paul Virilio once said, “absolute speed is absolute power.” The speed he is referring to is the pace at which space and time were being compressed during the industrial revolution. During the industrial revolution, steam engines and rail roads were continuously integrating supply chains thereby compressing the time it took to travel distance. Today, we are living in the Information Revolution Age. Modern information “railroads” are the constant stream of connections of web and databases.
These information networks of seamless and continuous connections between the demand and supply side of the businesses are changing how businesses operate. For financial planning and analysis, this means finding ways to increase the speed of decision- making by spending more time on analysis within applications and less time on modeling.
Focused calculations allow for expediency in financial analysis. Below I will discuss three types of focused calculations with use cases for financial planning and analysis.
1. Flagging Edited Form Data
Flagging edited form data can be used in any implementation where Groovy business rules calculate based on changed data in planning. Oracle has built a wealth of information in its user assistance program with illustrative examples for these kinds of focused calculations, but I will use an example below for edited department data.
Use Case: When data is changed on a form, whether at one intersection or many, it’s more efficient to run a calculation based on only changed data to determine its effect on revenue. The script below is looking for any data changes on the form for departments. The script uses a data cell iterator method to check for any changed departments on the form. If there are no changed departments, the rule just posts a “no edited cells” message to the job console.
If departments are changed, they are collected in a departments variable and passed on to create the calculation script string. The string is used to calculate revenues for only the departments that have been changed. The calculation script is calling a rule template to recalculate revenues based on the changes from the data form. This calculation script is passed to the underlying database to calculate. In the application job console, you can check the script itself by passing it on as well along with the status of the calculation.
2. Incremental Calculations
The second kind of focused calculations is are for incrementally calculating applications. Often, with large enterprise customers, we encounter large EPM planning applications. To optimize calculations it’s quicker to calculate for a subset of a larger dimension, such as projects where data exists.
Use Case: This use case is an example of a 200,000-member strong project dimension where we are calculating project revenue one subset at a time. The example illustrates how to incrementally calculate data only at project member intersections where there is data.
The calculation is being performed using a Chipper Set. What I mean by chipper is that you would perform the calculation for smaller portions of the project dimension at a time and only for where data exists in that chipper set. In the example shown below, there are run -time prompts, one of which takes a rollup member from the project dimension - could also be the top of the project dimension - and another that takes an integer chipper variable value. This chipper variable is what you're going to use to chop up the project dimension for calculations. We start off by evaluating all the level members of the project dimension and populating a projectList variable. Then, we break up this list into increments of 1,000 evaluated members. In this chipper process, for example, if you are evaluating 200,000 members, you would have 200 chipper sets.
In this script we perform the calculation one chipper set at a time, using the For loop in Groovy. The chipper set brings up the intersections at which data exists.
Just like a previous example for an edited data form, I can pass these intersections to a calculation, in this case, a project revenue calculation. With incremental calculations, we have implementations where we have reduced six calculation periods to run in 90 minutes—much more expedient for the end- user.
3. Handover Calculations
Handover calculations are for situations where we want to model or flex the existing models based on a decision support system driver or a global assumption, or simply for intersections in a large database where actuals exist. An assumption or a driver in one location calculates a data at another location.
Use Case: With handover calculations, a planner might be changing data in a separate intersection of the application, which then can be used to calculate a totally different intersection. Below is an example where changing actuals data in the Sales plan type of the application will force a calculation for Product revenue forecast. We are trying to determine whether new information coming through from the actuals must be used to forecast product revenue in a department.
The runtime prompts to tell the script, the department, and the current period actuals that were loaded. Next, we start with the process of grid builder, which is essentially a grid that you can create on the fly to collect intersections at which data exists. This is the same method used in the incremental calculation example above to derive only the projects in each of those 1000 chipper sets that had data. In our hand-over calculation case here, we use the grid builder to derive products in actuals that haves sales data for the current period and the department for which processing is underway. The grid builder is used to give us only the products that have sales volume and sales revenue.
Next, we take this product list, just like in our previous example, and pass it on to the forecast product trend-based revenue calculation.
In the example, a change in actuals, which is a separate scenario than forecast, in a separate cube i.e. Sales cube, invoked product revenue calculation for the forecast scenario, and for only the products that had actual sales volume and revenue in the period. This kind of calculation is very handy and widespread across many of our implementations.
I hope the above is good information to get you started on focused calculations using Groovy scripting if you are not already doing it—it can really save your finance department time and give them a broader picture for financial analysis.
For comments, questions, or suggestions for future topics, please reach out to us at email@example.com. Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management. Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.