Talking Tech: Graphical Business Rules in ASO Cubes

Let’s Talk Tech.

For some of us who have been in the Oracle EPM space longer than we care to mention, the ability to create graphical business rules has not been openly embraced by the majority of the Oracle community. Admittedly, we are as guilty as the next developer in this regard; but recently we have found some useful ways to incorporate graphical business into real-world client solutions. This post will provide a quick demonstration about how to create graphical business rules for aggregate storage (ASO) cubes within an EPM Cloud Enterprise Planning application. This can open a whole new world of allowing you to execute calculations against an ASO application, which maintains its speed and reporting benefits.

Once logged into the Planning application, the first step would be to use the Navigator menu to browse to Rules. From there, you will select an ASO cube, right click on Rules, and click New.

Once you are in the design interface, from the New Objects Palette, drag the Point of View component and drop it into the flow chart within the Designer window. The Point of View object is displayed as two circles with arrows inside them. Click on the Begin icon. This is the area where you will set your Point of View or “Global Range” for all dimensions within the ASO cube via the Member Selector icon. If you are familiar with writing Oracle Planning or Essbase business rules, this is akin to opening a FIX statement. You can select individual members or take advantage of several readily available functions such as:

• @Level0Descendant(Member Name) expands to include all level zero descendants of the specified member.
• @FilterSharedAndDynamic(Dimension Name, Member Name) removes all dynamic and shared members from the list of members.
• @ATTRIBUTE(Attribute Member Name) generates a list of all base members that are associated with the specified attribute member.
• @UDA(Dimension Name, User-Defined Attribute String) selects members based on a common attribute, which you define as a user-defined attribute (UDA) on the Essbase server.

The next step is to drag the Formula component from the New Objects Palette and drop it in between the two circles labeled Begin and End. An aggregate storage formula component is comprised of formula calculation statements. To create the calculation statements of a formula, you enter or select members and variables. As you create the formula, each of its calculation statements is listed in a row within a grid in the Component Designer:

To create a formula statement, click in the first formula statement row to the left of the equal sign. Then, enter a member or cross dimension member selection, or click Actions to select a dimension member or variable. To type a cross dimension selection of members, enter each member name, separated by a right arrow. For example, mem1->mem2->mem3.

To complete the formula statement, click in the row to the right of the equal sign and enter members, cross dims, or variables in the same fashion. Below are a few examples of simple ASO formula calculation statements:

• Clear Script:  Actual = Actual – Actual
  • The script above effectively sets all values to zero based on the POV established in the previous step.

Note: After executing a clear script within an ASO cube, you will also need to Merge Data Slices via within Database Properties. This action will remove all zero value cells created via the script above:

• Copy Data Script: Forecast->Working = Actual->Final
  • The script above is a simple way to replicate the DATACOPY command, which is a popular function used in block storage cubes, but not readily available within aggregate storage cubes. In this example, a simple cross dimension combination can be copied from source Scenario/Version to target Scenario/Version.

And if you’re the curious type, you can always view the MDX(ish) code behind the scenes by selecting Edit Script via the drop down menu above the designer window:

Hopefully this post is useful for someone who (like us) has been hesitant to explore the world of Graphical Business Rules, and the functionality that they have to offer – especially within aggregate storage cubes! If you want to learn more about this feature, Oracle EPM Cloud, or how your company can make the transition to the cloud, do not hesitate to contact us.