The Relations Page: Standard Cube

Relations enable the user to get fact values using shift towards the current calculation point. It is used on creating expressions of cube calculated facts.

On creating a cube, data is extracted from sources, which are bound with cube facts and calculated facts values are calculated. Calculated facts are calculated individually in each cube cell. The addition of relation to the calculated fact formula enables, on calculating in the particular cell, the use of not only values specified for facts in this cell, but also facts in other cells obtained by shifting in cube dimensions. The shift can be set up by one or several dimensions at the same time. When used in a formula, each relation can be applied for different cube facts.

To add and set up relations, go to the Relations page of the standard cube wizard:

Click the Add button, the Relation Properties dialog box opens.

Set a name and a unique identifier of the created relation in the dialog box.

Select the required relation name and click the Edit button. The dialog box that opens enables the user to determine the name and unique identifier.

Select the relation name and click the Delete button. The relation is deleted after confirmation.

To set up relations of the selected dimensions, execute the following:

Double-click the dimension.
Select dimension and click the Change button.

The Relation Formula dialog box opens when one of the operations is executed:

Select how relation formula will be set using radiobuttons.

None. The current position will be taken as a position by dimension.
Levels Up. The result of this relation is the element owner separated by the specified number of levels from the current element. The number of levels is specified in the number editor, which is available when the radio button is selected. The number of levels up must not exceed the total number of levels minus 1. If, for example, the Perm Region element is the current one, and the one-level-up step is specified, the Ural District element is selected, and if the two-levels-up step is specified, the Russian Federation element is selected:
Elements Back. The result of this relation is the element of the same level separated by the specified number of elements from the current element. The order number is specified in the value editor, which is available when the radio button is selected.
Specified Element. Selecting this radio button enables the dimension tree where the specific element is to be selected. In this case, regardless of the cube current position, the result of the relation is this very element.

NOTE. Selecting this radio button opens the Determine Parameter Values dialog box to specify parameter values for the parametric dimension.

Expression. The result of the relation is the specified expression. To specify the expression, use buttons below the input box or using the expression editor opened by clicking the button. Use the following cube parameters to form the expression in the formula editor, for example:

MyModule.MyFunc(:ITEM)
:ITEM

Where ITEM is the cube parameters identifier.

To create an expression, the buttons can also be used:

T. Current element (should be always used on creating an expression).
UP. One level up.
DOWN. One level down.
NEXT. Next element.
PREV. Previous element.

Expression creating should be started from the current element. To do this, use the T button. Expression examples:

T.PREV.
T.PREV.UP.
T.UP.NEXT, and so on.

To calculate element number in the specified relation, the custom function can be used. The function result is an integer value. Function call format:

Fore. [Unit/form identifier].[Function name]. For example:

MyModule.MyFunc(T);

Fore.NET. [Namespace].[Class name].[Function name]. For example:

MyModuleNET.MyClass.MyFunc(T);

To calculate the offset by elements of calendar dimension, the user can use methods of the CalendarDimension class. To execute calculation relative to the current element, use the T parameter.

Example

See the example of creating a relation and setting up a calculated fact in already existing cube. It is supposed that the facts dimension contains element named Change which is not linked with data source and which is used to calculate values by formula.

To calculate changes of indicator comparing with the current and previous year, create a relation and set up its parameters. To get the previous value by calendar dimension it is necessary to set up relation: create a relation and name it Shift To One Point Back and set the BEFORE identifier. Select Calendar, click the Change button and determine the following settings:

Created relation will get a previous value in each calculation point by shifting one element back in calendar dimension. To calculate indicator change calculation, it is required to set up calculated fact. Go to the Calculated Facts tab, select the Change element and click the Edit button. Click the

button next to the Formula box. Create the Value-Shift One Point Back[Value] formula using elements from data panel in expression editor. After clicking the OK button, all names will be replaced by identifiers and the calculated fact editor will look as follows:

Save changes and close cube editor wizard. Now, if cube will be opened and selection will be added to calculated fact, cube data will look as follows: