# 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_GROWTH``

This 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_INCOME``
• Formula for EXPENSE data cube:

``SALARY + BONUS``
• Formula 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_GROWTH``

The analytic calculation engine determines that this is a recursive system by performing the following steps:

1. 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.

2. 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_INCOME``
• Formula for EXPENSE data cube:

``SALARY + BONUS``
• Formula 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.

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.