In this article:

Features of Working with Calculated Facts

Setting Up Calculated Facts

Working with slides calculated facts is available in data models with a calendar dimension. If all calendar dimensions are deleted from the data model, working with calculated facts is unavailable.

Creating a Calculated Fact

For a quick setup of a calculated fact select the Calculated Fact item in the drop-down menu of the Actions button for fact or calculated field and select calculated fact type. The command is available for all data model facts: table facts, integer and real calculated fields, facts with set georole.

To create a calculated fact, click the Create Calculated Fact button in the Facts group. The Create Calculated Fact dialog box opens:

Set the parameters:

Name

Enter calculated fact name. One can set equal names for different calculated facts. The default name is Calculated Fact N, where N - number of added fact.

Calculated fact type

Select calculated fact type in the drop-down list:

Running Total. Default value. Aggregation with running total within the specified level:

SUM_S[Fact,L1,ROOT]

Where:

Fact - current value.
L1 - specified level (day (DAY), month (MONTH), year (YEAR).

Absolute Growth (Chain). Difference between the current and the previous values:

Fact - FLYBI_C_PREV[Fact]

Where:

Fact - current value.
FLYBI_C_PREV[Fact] - previous value.

Absolute Growth (Basis). Difference between the current and the first selected value:

Fact - FIRST_S[Fact,L1,ROOT]

Where:

Fact - current value.
FIRST_S[Fact,L1,ROOT] - the first selected values by the specified level.

Growth Rate (Chain). Relation between the current and the previous values:

abs(Fact/ FLYBI_C_PREV[Fact])

Where:

Fact - current value.
FLYBI_C_PREV[Fact] - previous value.

Growth Rate (Basis). Relation between the current and the first selected values:

abs(Fact / FIRST_S[Fact,L1,ROOT])

Where:

Fact - current value.
FIRST_S[Fact,L1,ROOT] - the first selected values by the specified level.

Rate of Change (Chain). Difference between the current value and the previous one relative to the previous value:

(Fact - FLYBI_C_PREV[Fact]) / abs(FLYBI_C_PREV[Fact])

Where:

Fact - current value.
FLYBI_C_PREV[Fact] - previous value.

Rate of Change (Basis). Difference between the current value and the first selected one relative to the first value:

(Fact - FIRST_S[Fact,L1,ROOT]) / abs(FIRST_S[Fact,L1,ROOT])

Where:

Fact - current value.
FIRST_S[Fact,L1,ROOT] - the first selected values by the specified level.

Rate of change (yoy):

Calculation level - Year. Difference between the current year and the previous one relative to the previous one:

(Fact - FLYBI_C_PREV[Fact]) / abs(FLYBI_C_PREV[Fact])

Where:

Fact - current value.
FLYBI_C_PREV[Fact] - previous value.

Calculation level - Month. Compare value with the same month value but a year ago:

(Fact - FLYBI_MONTH_PREV[Fact]) / abs(FLYBI_MONTH_PREV[Fact])

Where:

Fact - current value.
FLYBI_MONTH_PREV[Fact] - value in the same month but a year ago.

Calculation level - Day. Compare value with the same day value but a year ago:

(Fact - FLYBI_DAY_PREV[Fact]) / abs(FLYBI_DAY_PREV[Fact])

Where:

Fact - current value.
FLYBI_DAY_PREV[Fact] - value on the same day but a year ago.

NOTE. Element selection is taken into account on calculation of the following calculated facts: Running Total, Absolute Growth (Basis), Growth Rate (Basis), Rate of Change (Basis). The rest of the calculated fact types are calculated by all elements.

Fact

Select the fact, based on which the calculated fact will be calculated. The list displays all data model facts: facts from tables, integer and real calculated fields. To search for a fact in the list, start typing name in the search string.

NOTE. Based on the same fact one can set up several calculated facts.

Calculation level

Set calendar level, in which calculation will be executed: Year, Month, or Day. The Month value is set by default.

NOTE. The Calculation Level parameter is available for all calculated fact types, except for the Absolute Growth (Chain), Growth Rate(Chain), and Rate of Change (Chain).

To save calculated fact settings, click the Apply button. The calculated fact will be added to the Calculated Fields group on the Facts panel.

Operations with Calculated Fields

The following operations are available when working with calculated facts:

Set up calculated fact

To set up a calculated fact, select the Settings item in the drop-down menu of the Actions button for the fact. The Calculated Fact Settings dialog box opens:

If required, change calculated fact settings. The settings are identical to the settings determined on creating a calculated fact.

The settings are applied in all data model visualizers, which use calculated fact.

Set up data format

To set up data format, select the Data Format item in the drop-down menu of the Actions button for calculated fact. The Set Up Data Format dialog box opens.

Percent data format will be set in all calculation levels for the calculated fact types: Growth Rate (Chain), Growth Rate (Basis), Rate of Change (Chain), Rate of Change (Basis), and Rate of Change (yoy). Custom data format is set by default for the calculated fact types: Running Total, Absolute Growth (Chain), and Absolute Growth (Basis).

Set up georole

To set up georole, select the Georole item in the drop-down menu of the Actions button for calculated fact and select the Latitude or Longitude option.

Add calculated fact to visualizer

A calculated fact can be used to set various fields, except for filter, on building visualizers, as normal facts.

NOTE. To correctly build the visualizer, which uses calculated facts as fields, the source data model should contain a calendar dimension.

Calculated facts can be used on building tables as columns, rows, and facts.

Delete calculated fact

To delete the calculated fact, select the Delete item in the drop-down menu of the Actions button.

If the calculated fact is used as a field on building visualizer, the system asks to confirm the operation. After the operation is confirmed, the selected calculated fact will be deleted from the visualizer, from the Fields and Calculated fields groups on the Facts panel.

Features of Working with Calculated Facts

Delete source fact from data model

If source fact was deleted from the data model, based on which calculated fact was calculated:

The Fact box will be empty on opening the Calculated Fact Settings dialog box. Select the new fact to work with this calculated fact.
The visualizer, which used calculated fact for building, will not be built.

Delete calendar dimensions and source fact from data model

If all calendar dimensions and the source fact, based on which calculated fact was calculated, were deleted from data model, it is prompted to execute the following operations:

Set up source fact for calculation.
Add a calendar dimension to data model.

Features of link between calculated facts and data cubes

When adding a calculated fact to visualizer, the cube that is a data source for the visualizer changes:

The following is added on the Facts tab:

New fact with the specified name for each calculated fact added to the visualizer.
Source fact, based on which calculated fact is calculated.

New relations for calculated facts are added on the Relations tab. Relations are created only for calendar dimensions on adding the following calculated fact types to the visualizer:

Absolute Growth (Chain).
Growth Rate (Chain).
Rate of Change (Chain).
Rate of Change (yoy) for all calculation levels.

Relation identifiers start with FLYBI_.

A proper formula is set for each calculated fact on the Calculated Facts tab.

On deleting calculated facts from the visualizer:

On the Relations tab:

The relations added to cube are not deleted if they are used in formulas of other calculated facts in this visualizer.
The relations added to cube are deleted if they are not used in formulas of other calculated facts in this visualizer.

On the Facts tab:

Calculated facts added to cube are deleted.
Source facts not used in building visualizers are deleted.

Calculated fact and its formula is deleted on the Calculated Facts tab.

When calendar dimension is deleted from the visualizer, all cube relations are deleted.