EPM Cloud Planning Essentials: Data Grid Builders for Focused Modeling

Published March 13 2020 by Vatsal Gaonkar
Back to insights

Data Grid Builders for Focused Modeling

EPM Planning implementations nowadays increasingly demand that calculations or models compute on fly, to yield more time for analysis and reporting in the planning process. The idea is to use time savings from the computational power and apply to many other “what-if” assumptions in running the model. With the advent of Essbase 19c and Hybrid Block Storage Option (BSO) databases in EPM Cloud, computational power has increased significantly. Using intelligent modeling and calculation techniques, examples for which are ubiquitously available in EPM Cloud Planning out of the box frameworks, calculations can be written and executed more efficiently than ever. However, some business requirements need calculations and models to run in one scenario with basis data in another scenario. In this case, let’s consider scenario as data between Actuals vs. Budget and not business scenario, where the model is trying to consider the impacts of a change in business process.

Figure 1 - Flex Model Example

In the field, especially with health care clients, we see requirements to use basis data in Actuals to model forecast results. One such example is what Alithya refers to as “Flex Model” (see figure 1 above). The model allows to flex revenues and expenses based on historical actuals and volumes in the model. Please note, some of these clients are large enterprises that have massive dimensionality, and by extension, data in their applications. While forecasting for flex lines such as patient revenues and drug supplies, the calculation will need to solve for historical volumes and flex lines in cost centers, and then apply the run-rate to new volumes in the forecast. While regular calculation manager scripting can take care of such modeling requirements, a faster and more efficient way of executing such models is by using data grid builders in Groovy business rules.

Groovy business rules allow models to design and execute complex business requirements that the normal business rules cannot solve efficiently. Here is the link to Groovy business rules.

Returning to our case, the data grid builder will solve through the historical actuals data and give us cost centers that have drug supplies and commensurate volumes that go along with the drug supplies. Efficiency is gained by running the Flex Model calculation only at intersections/ cost centers where data exists. The first step is to create an imaginary data grid or data form like you would in EPM Cloud Planning using Groovy business rule to solve for intersection where historical data exists.

Design starts with setting a connection to the plan type where you would build your data grid. You can also specify whether you would like suppression on for this data grid, which invariably is the case - turn suppression on.

Figure 2 - Defining the Grid in Groovy Business Rule

Once the data grid is defined, next comes defining the layout like you would have in a data form. The goal is to seek data in historical actuals, say Actual Year1, Actual Year 2, Flex Lines (Drug Supplies accounts), and Cost Centers where there is data.

Figure 3 - Solving for focused existing data intersections

Once a focused existing data intersection is solved, we could send the intersections to execute a regular Business Rule template that would execute the logic to calculate ‘Rate per Unit’ and apply to the Forecasted Volumes (Forecasted Patients seen).

Figure 4 - Sending focused intersections for Business logic calculation

Increasingly, we are seeing the use of data grid builders in other scenarios for Health Care implementations, such as

  • Revenue lines calculation based on historical Volumes and Revenues
  • Labor lines calculation based on historical Volumes and Hours

Some design considerations and pre-requisites as organizations embark on solving sophisticated business model requirements –

  • If Groovy is an option, ensure a design consideration is made to see what requirements Groovy can solve in addition to normal business rules. Invariably, every EPM Cloud Planning implementation is using Groovy scripting in some capacity these days.
  • Data grid builders do have an upper limit, just like data forms. Take into consideration the size of data you are trying to solve for while creating these data grids. If data grids don’t get you all the intersections, use other Groovy data iterator concepts to solve for focused data intersections. Groovy has many powerful objects that can solve for focused calculations in addition to Data Grid Builders.
  • At the time of writing of this post, Standard EPM Cloud subscription does not allow custom Groovy scripting. Enterprise EPM Cloud subscription does. Consider Groovy business rules as an integral part of your Enterprise Planning solution, and by extension, a part of making a subscription decision on Oracle EPM Cloud Standard vs. Oracle EPM Cloud Enterprise.

For comments, questions, or suggestions for future topics, please reach out to us at infosolutions@alithya.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.

Contact us