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.

To create a relation, click the Add button. After executing the operation a relation with default properties is added.

To change properties, use the Relation Properties dialog box.

To edit the selected relation:

Open the Relation Properties dialog box:

In the web application click the Edit button.
In the desktop application execute one of the operations:

Click the Change button.
Double-click the relation.

After executing the operations, the Relation Properties dialog box opens:

Set the parameters:

Name. Enter short description of relation. The Relation name is set by default.
Identifier. Enter unique identifier of relation. The ITEM identifier is set by default.

Click the Apply button in the web application or the OK button in the desktop application.

After executing the operations the relation properties are changed.

To delete the selected relation:

In the web application click the Delete button.
In the desktop application 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.
In the web application click the Relation Formula button next to the dimension or the button with the same name in the existing relation formula.
In the desktop application click the Change button.

After executing one of the operations the Relation Formula dialog box opens:

In the web application:

In the desktop application:

Use the drop-down list in the web application or select radio buttons in the desktop application to select how a relation formula will be set:

None. Default value. 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 of the territory dictionary 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 should be selected. In this case, regardless of the cube current position, the result of the relation is this very element.

NOTE. When this radio button is selected, the Determine Parameter Values dialog box opens for parametric dimension, in which specify parameter values.

Expression. The result of the relation is the element, which index is calculated by the specified expression. The expression is set using the buttons located next to the edit box or using the expression editor.
An expression should be started with the current dimension element. For this use the T button. Next, one can specify the shift, for example, T.PREV, T.PREV.UP, T.UP.NEXT, and so on.
To create an expression, use the buttons:

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.

To open the expression editor:

In the web application click the Insert Formula button.
In the desktop application click the Insert Formula button.

To calculate element index in the expression, one can use various arithmetic, logical, text, custom and transformation functions.

One can also specify a Fore macro in the repository form or unit. The macro should be a function implemented in the global namespace or a static function class. The function can take any required parameters for input; one can also use the T parameter to send the current element. The function should result in the integer value corresponding to element index. Function call format: [Unit/form identifier].[Function name] or [Form/unit identifier].[Class name].[Function name]. For example:

MyModule.GetElement(T);

MyModule.TRelations.GetElement(T);

NOTE. If the Calculate by Physical Data checkbox is selected for cube's calculated fact, the system may return the ForeVariantType.Matrix array containing integer values, as a T input parameter. In this case the custom function should also process and return the array of integer values during relation calculation.

To get information about cube, the macro can use static properties of the CubeClass class, to calculate shift by calendar dimension elements the macro can use methods of the CalendarDimension class.

The function can also take values of cube parameters, for example:

MyModule.GetElementParam(T, :PARAM)

Where: T - the current element, PARAM - cube parameter identifier.

Expression Editor

The expression editor window for setting up relation formula in the web application looks as follows:

Arithmetic operations, functions, numbers, comparison characters and round brackets can be used in a formula. There are some features of use:

To insert a function to the formula area, execute one of the operations:

Using the functions panel:

Select the function. The panel with description opens:

Click the Insert into Formula button.

Double-click the function.
Drag the function to the formula area.

Using the formula area:

Enter a function in the formula area.
Click in the formula area in the place where the function should be inserted, press CTRL+SHIFT and select the required function on the popup panel.

To quickly search for a function, start typing partial or full name of the function in the search string. After executing the operation the functions panel displays the functions, which names satisfy search conditions.

Example

Consider the example of creating a relation and setting up a calculated fact in the existing cube in the desktop application. It is supposed that the fact dimension contains an 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, one should set up a relation: create a relation and name it Shift One Point Back and set the BEFORE identifier. Select Calendar, click the Change button and determine the following settings:

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

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

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