Working with Circular Formulas and Circular Systems
This topic provides overviews of circular formulas, circular systems and recursive systems, recursive system resolution, and circular system resolution, and discusses how to change circular formula and circular system options.
See “Circular Reference” in Understanding Design Time Rule Error Messages.
When a data cube's formula refers either directly or indirectly to that same data cube, it is considered to be a circular formula.
Note: The analytic calculation engine determines—on the data cube level—whether formulas are circular. However, the analytic calculation engine resolves circular systems and recursive systems on the cell level.
Direct Circular Formulas
This is an example of a direct circular formula for the SALES data cube:
SALES + SALES_GROWTHThis formula states that sales equals sales plus the sales growth.
It is a direct circular formula because the data cube's formula refers directly to that same data cube.
Indirect Circular Formulas
In an indirect circular formula, a data cube's formula refers indirectly to that same data cube, as in this example:
Formula for BONUS data cube:
BONUS_PERCENTAGE * NET_INCOMEFormula for EXPENSE data cube:
SALARY + BONUSFormula for NET_INCOME data cube:
INCOME - EXPENSE
In this example, none of the data cubes refer directly to themselves. However, each data cube refers indirectly to itself by means of the other data cubes:
BONUS refers to NET_INCOME.
NET_INCOME refers to EXPENSE.
EXPENSE refers to BONUS.
When the analytic calculation engine determines—on the data cube level—that a circular formula exists, it analyzes the calculation conditions of the cells within the circular formula to determine whether the cells are dependent on those same cells for their values. If so, these cells either create a recursive system or a circular system.
In a recursive system, the values of the cells are not dependent on the values of those same cells.
In a circular system, the values of the cells are dependent the values of those same cells.
The analytic calculation engine resolves recursive systems immediately without using the process of iteration.
To understand the process of how the analytic calculation engine determines and resolves recursive systems, consider the following formula for the SALES data cube:
PREVSELF(MONTHS) + SALES_GROWTHThe analytic calculation engine determines that this is a recursive system by performing the following steps:
The analytic calculation engine determines that this is a circular formula because the PREVSELF built-in function, which refers to the SALES data cube, exists within the formula.
The analytic calculation engine analyzes the calculation conditions of the cells within this formula and determines that these cells create a recursive system, because the cells within this formula are not dependent on the values of those same cells.
The analytic engine then resolves this recursive system immediately without iteration.
The analytic calculation engine uses the process of iteration to attempt to resolve all of an analytic model's circular systems. You set the iteration parameters by using the General tab of the analytic model's properties. If the cells converge on a solution within the iteration parameters, the circular system is resolved. If the cells do not converge on a solution within the iteration parameters, the analytic calculation engine returns an error. All cells within the circular system remain unresolved.
Note: Because you enable or disable iteration for all circular systems, you cannot enable or disable iteration for a particular circular system.
When you enable the resolution of circular systems through iteration, you must select one of the following iteration options:
Maximum number of iterations
When the values of the cells have not changed more than the specified maximum amount, the circular system is considered to be resolved.
Maximum change in values
When the analytic calculation engine has calculated the cells within the circular system for the specified maximum number of times, the circular system is considered to be resolved.
Example of Resolving a Circular System
In this example, an analytic model contains the following formulas (for simplicity, assume that each data cube contains only a single cell):
Formula for BONUS data cube:
BONUS_PERCENTAGE * NET_INCOMEFormula for EXPENSE data cube:
SALARY + BONUSFormula for NET_INCOME data cube:
INCOME - EXPENSE
First, the analytic calculation engine determines that this is an indirect circular formula because the data cubes' formulas refer indirectly to those same data cubes.
Next, the analytic calculation engine analyzes the calculation conditions of the cells within this indirect circular formula and determines that these cells create a circular system. This is because the cells within this formula are dependent on the same values of those same cells.
Assuming that the value for the BONUS_PERCENTAGE cell = 5, the value for the INCOME cell = 10000, and the value for the SALARY cell = 6000, then the circular system is resolved when the analytic calculation engine iterates until it returns these solutions:
Cell for BONUS = 190.48.
Cell for EXPENSE = 6,190.48.
Cell for NET_INCOME = 3,809.52.
If you plug these values into the preceding formulas, each formula is true: the left side of the formula is (almost) equal to the right side of the formula. Once this occurs, the circular system is considered to be resolved.
Use the Analytic Model - General tab to change circular formula and circular system options.
Navigation
Select to access PeopleSoft Application Designer.
After signing in to the PeopleSoft Application Designer, open an analytic model definition.
Select the name of the analytic model in the part browser.
Image: Analytic Model - General tab
This example illustrates the fields and controls on the Analytic Model - General tab. Definitions for the fields and controls appear following the example.
|
Field or Control |
Definition |
|---|---|
| Description |
Enter a description of the analytic model. Note: This field pertains to the analytic model as a whole, not to circular formula options. |
| Resolve circular system through iteration |
Select to attempt to resolve all of an analytic model's circular systems through iteration. By default, this option is disabled. |
| Maximum number of iterations |
Enter the number of iterations in which the analytic calculation engine is to resolve circular systems. By default, the maximum number of iterations is 100. If the analytic calculation engine cannot resolve a circular system during this number of iterations, the analytic calculation engine returns an error. Note: You must select the Resolve circular system through iteration check box to activate this option. |
| Maximum change in values |
Enter the maximum change in values. By default, the maximum change in values is 0.001000. A circular system is considered to be resolved when the values of its cells do not change more than the specified maximum change. If you enter a smaller value, the solution is more accurate but may require a longer calculation time. If you enter a larger value, the solution not as accurate but requires a shorter calculation time. Note: You must enable the Resolve circular system through iteration check box to activate this option. |
| Warn about circular formulas |
Every time a circular formula is defined: Select for the analytic calculation engine to provide a circular formula warning every time a circular formula is defined. |