1. Oracle Enterprise Performance Management Cloud Operations Guide
  2. Troubleshooting EPM Cloud Issues
  3. Troubleshooting Business Rules and Member Formula Errors and Performance
  4. Business Rule Optimization
  5. Optimizing Rules: An Example

Optimizing Rules: An Example

After identifying the passes to optimize, edit the business rule. Ensure that the optimal logic and conditions are specified for each pass.

Consider the following business rule definition, which calculates two YTD accounts and then aggregates the values through the Product and Entity dimensions:

SET UPDATECALC OFF;
/* PASS 1 BEGINS*/  
FIX ("BaseData","Plan","FY17",  "FY17" "FY16")
      "BU Version_1"(
          IF(@ismbr("Jan"))
             "4110_YTD" = "4110"; "4120_YTD" = "4120"; "4130_YTD" = "4130";
             "4140_YTD" = "4140"; "4150_YTD" = "4150";
          Else
             "4110_YTD"="4110" + @prior("4110_YTD"); "4120_YTD"="4120"
  								+ @prior("4120_YTD");
             "4130_YTD"="4130" + @prior("4130_YTD"); "4140_YTD"="4140"
  								+ @prior("4140_YTD");
             "4150_YTD"="4150" + @prior("4150_YTD");
          Endif)
  /*  PASS 1 ENDS -- PASS 2 BEGINS*/
      Agg("Entity","Product");
  /*  PASS 2 ENDS */
 ENDFIX

On running this rule in Oracle Hyperion Calculation Manager (see Identifying Areas for Rule Optimization), the Log Message tab shows a message similar to the following when Pass Only is selected:
Sample Message Displayed on Running a Rule with Pass Only Selection

An analysis of the information in the log file indicates that 99.995% of the execution time (79.235 seconds) is spent on pass 1, and only 0.005% on pass 2.

If you deselect Pass Only, blocks, read, and write information, similar to that shown in the following illustration, is displayed:
Sample Message Displayed on Running a Rule Without Pass Only Selection

The preceding rule definition has these issues:

  • It does not have a FIX on Entity and Product dimensions, thereby forcing all the rules to be run on all levels of Entity and Product dimensions.

  • Pass 1 needlessly calculates the upper levels. The Agg function in pass 2 does this and overwrites what is done in pass 1.

The script can be optimized as follows:

SET UPDATECALC OFF;
FIX ("BaseData","Plan","FY17")

/* PASS 1 BEGINS*/

	Fix(@LEVMBRS("Entity",0), @LEVMBRS("Product",0))
		"BU Version_1"(
			IF(@ismbr("Jan"))
				"4110_YTD" = "4110";
				"4120_YTD" = "4120";
			Else
				"4110_YTD"="4110" + @prior("4110_YTD");
				"4120_YTD"="4120" + @prior("4120_YTD");
			Endif)
	ENDFIX
/*  PASS 1 ENDS --  PASS 2 BEGINS*/

    Agg("Entity","Product");

/*  PASS 2 ENDS */

ENDFIX

On running the updated rule in Calculation Manager (see Identifying Areas for Rule Optimization), the Log Message tab shows a message similar to the following:


Sample Message Displayed on Running a Fixed Rule

An analysis of the information in the log file indicates that the execution time taken in pass 1 is 15.901 seconds less compared to the previous run.

If you deselect Pass Only, blocks, read, and write information similar to that shown in the following illustration is displayed for pass 1:
Sample Message Displayed When Running Fixed Rule Without Pass Only Option

A comparison of the blocks, read, and write information with similar data from pre-optimization indicates an across the board reduction in the processing statistics of the business rule.