How to Add Flexibility to Your Business Rules in Oracle PBCS & Hyperion Planning
September 7, 2018 - By: SC&H Group
Run Time Prompt Variables (RTPs) are a powerful tool in PBCS and Hyperion Planning that allow for user input and flexibility within business rules. Typically, RTPs are used to input member names from an application, such as rtp_Scenario, which could enable a user to select the Scenario for which they would like to run a calculation.
However, being able to pass member names through run time prompts only scratches the surface of the usefulness that RTPs can provide in a planning and budgeting application. Being able to pass numeric values through RTPs further enhances the Hyperion experience for both administrators and end users alike.
In the following example, we will show how to use a numeric RTP in conjunction with a LOOP/ENDLOOP statement in Calculation Manager, which provides a powerful mechanism for choosing whether or not a particular business rule action should take place. Designing a calculation this way is especially helpful when CLEARDATA and DATACOPY commands are necessary since they cannot be performed within IF statements.
Sample Ruleset and Requirements
In our sample use case, we need to have a ruleset that performs the following three actions on the Forecast:
- Copies Actual data to the Forecast (Copy Act to Fcst for short)
- Aggregates the Forecast
- Currency converts the Forecast at the desired rate(s)
In this example, the Copy Act to Fcst rule will be used to copy fresh Actual data into the Forecast for the month that is currently being closed. The Aggregate Scenario and Currency Conversion rules ensure that data appears correctly at upper level members for the necessary currencies in the Forecast. However, to add some complexity, in our example, the sample company creates only a quarterly Forecast (instead of monthly), so the Forecast should be updated with Actual data only for quarter-end months. You may be asking yourself, “How is it possible to use only one ruleset when the Copy Act to Fcst business rule will need to be run in some months but not in others while the aggregations and currency conversions are needed every month?!” Hint hint – with the use of a numeric RTP and a LOOP/ENDLOOP statement.
Building the RTP and SmartList Linkage
Setting up a numeric RTP is quite similar to setting up an RTP that accepts member names as inputs. Simply select “Numeric” from the “Type” dropdown and check the RTP box, while also adding RTP text. Note that a default value for the prompt can be selected, which is a helpful feature for risk mitigation as we will see later.
The use of a SmartList in conjunction with a numeric RTP can further enhance this functionality within Planning applications. When launching a business rule or ruleset that contains the run time prompt and the associated SmartList, administrators or users are given more context as to what values should be input for the numeric RTP. Instead, forcing system users to remember what action a selection of 0 takes versus the impact of running the rule or ruleset with an RTP value of 1 is more cryptic.
For our Forecast ruleset, a SmartList, named CopyFcst_SL, was created as a shared dimension in our sample PLAN application (be sure to double check your application’s evaluation order). One of the SmartList selection options is “Copy”, while the other selection is labeled “Do Not Copy.” That’s a lot more straightforward than trying to decipher 0’s and 1’s – hey, we’re EPM nerds, not computers, okay?!
The metadata and properties of the sample CopyFcst_SL SmartList can be seen in the below screenshots.
A numeric RTP built for use in our sample Forecast ruleset calculation is shown below.
Due to the LOOP functionality that will be employed within the Copy Act to Fcst business rule, we want to make our “Copy” selection have a value of 0 and our “Do Not Copy” selection to be set to 1. These values are what Essbase stores behind the scenes, and they are the fundamental key to dictating whether the functionality within our LOOP processes or not. In addition, it would be preferable to have “Do Not Copy” set as the default value so that Actual data is not copied into the Forecast by mistake if the system user running the business rule makes an error and forgets to choose a selection for this RTP variable upon launch.
Another advantage of using a SmartList in conjunction with a numeric RTP is that the functionality looks a lot cleaner when launching the business rule/ruleset, and it allows for easy toggling between options. Thus, administrators or end users can easily understand what operations each of the SmartList options perform.
The SmartList appears as a dropdown option after the ruleset is launched. The default value, in this case “Do Not Copy Act to Fcst,” is filled in automatically unless the selection is manually changed to “Copy Act to Fcst.”
Creating the Flexible Business Rule
In the sample Copy Actual to Forecast business rule (“ADMIN – Copy Act to Fcst Flex”) below, the rtp_CopyFcst variable will accept either a 0 or a 1 as its input. This input value is then passed to a temporary variable, which is an optional parameter in the LOOP that dictates when the LOOP is broken. In this case, the LOOP is set to process once, based on the first parameter in the “LOOP(1, vCopyFcst)” statement. The value for vCopyFcst then determines whether the LOOP processes once (when vCopyFcst = 0) or is broken before the commands within the LOOP process (when vCopyFcst = 1). This setup within the calculation provides flexibility and enables application users to dynamically choose whether or not to run the Copy Act to Fcst rule as part of the Forecast ruleset depending on if the month is a quarter-end period or not.
The flexible Copy Act to Fcst business rule is shown below using the rtp_CopyFcst variable created earlier.
Bringing It All Together
Using RTPs in the manner discussed above can significantly ease administrator burden by enabling the creation of flexible rulesets that work in a variety of situations. If this approach was not taken, a single ruleset could not be used, and an administrator would potentially have to manually run multiple business rules one after another. Another option would be to create two separate rulesets, one with the Copy Act to Fcst calculation included and the other ruleset with the Copy Act to Fcst rule omitted. Neither of these alternatives are desirable because the first option increases administrative burden and creates additional room for error, while the second option adds an extra, unnecessary artifact to the application. Thus, the moral of the story is to use numeric RTPs with LOOP/ENDLOOP statements when flexibility is needed within business rules or rulesets.