The Facts Binding Page: Standard Cube

Cube data is stored in relational data sources. A source stores the following information on cube cells: a collection of fields that uniquely identifies cell position in a cube, and fields containing facts' data.

To project cube structure to data source, bind cube facts and source fields. Different cube facts can be linked to fields of different data sources and different calendar levels:

To add a relational data source to the Data Sources area:

Click the Add button and select the required data source.
Drag the required data source from the object navigator to this area.
Select the Add Source item in the context menu of the Data Sources area.

To delete the selected data source from the Data Sources area:

Click the Delete button.
Select the Delete Source item in the source's context menu.

NOTE. If one of the data source fields is linked to the cube fact, a confirmation dialog box appears on clicking the Delete button.

To set up object displaying in the Data Sources area, open the context menu and select the required radio button:

Show Names. Only names are displayed in the tree.
Show Identifiers. Only identifiers are displayed in the tree.
Show Names and Identifiers. Names and identifiers are displayed in the tree.

To determine fields binding:

Select required relational data source in the Data Sources area, expand its structure and select the field to bind with the cube fact.
In the Cube Facts area select the fact, to which the field is to be bound.
Execute one of the operations:

Click the Bind button.
Drag the selected field from the Data Sources area to the Cube Facts area.

Fields are bound to cube facts.

Click the Unbind button to remove the binding of the cube fact selected in the list.

To set up expressions for bound facts:

In the Cube Facts area double-click the selected bound fact, for which it is required to set expression.
In the expression editor set up fact calculation formula.

If any source fields are not bound to dimensions in the cube, it could happen that several fact vlues will be available by one cube coordinate. In this case, to get unambiguous data view by fact, it is possible to set aggregation that will be applied to get any one value.

An individual aggregation method can be set up for each data binding. To do this, in the Cube Facts area:

Select a fact binding.
Select the required type in the drop-down list of the Aggregation Method column:

Not Determined. No aggregation, the last non-empty value is selected (the return order of values is selected by DBMS).
Sum. Fact values are summed up.
Number of Values. The number of fact values is determined.
Arithmetic Mean. The mean value of the fact is determined.
Minimum. The minimum value of the fact is selected.
Maximum. The maximum value of the fact is selected.
Standard Deviation Based on Sample. The standard deviation for fact values is calculated.
Median. The median for fact values is determined.
Standard Deviation Based on the Entire Population. The standard deviation for all fact values is calculated.
Number of Different Values. The number of fact unique values is determined.

To set up data aggregation by hierarchy of different cube dimensions, go to the Data Aggregation page of the standard cube creation wizard.

NOTE. To use aggregation methods in fixed dimensions that are set for cube facts, select the Aggregate Data by Dimensions with Full Selection checkbox on the Dimensions page of cube editing wizard.

Calculation Location

A fact dimension can be aggregated:

At server. Aggregation is executed by DBMS server.
At client. It is performed using Foresight Analytics Platform in the client workstation after extracting all data from the server.

The On Client checkbox is selected by default.

To change aggregation calculation location, click the button in the Data Sources area and select the appropriate radio button on the panel that opens:

Features of Work with Configured Aggregation of Fact Dimension

The table provides drill down to relational data that displays all data source fields, which were used to get cell value.

On setting up output of totals by rows or columns based on source data, the Grand Total method of calculating totals is available for regular report data area.

On using a query-based cube, select the Use Subqueries when Retrieving Data checkbox.

To extract data on building a cube, the following query is formed:

Using subqueries:

select a.* from (select a.ID,a.VALUE,a.VDATE,... from <TableName> a) a where ((a.VDATE in (TO_DATE('1995-01-01','YYYY-MM-DD'),...))) and ((a.ID in (1,...))) and ...

Without using subqueries:

select a.ID,a.VALUE,a.VDATE,... from <TableName> a where ((a.VDATE in (TO_DATE('1995-01-01','YYYY-MM-DD'),...))) and ((a.ID in (1,...)))...

NOTE. On using a query-based cube, this checkbox is required to aggregate facts.

Subqueries are relevant for use in case, when cube sources are built on complex queries working with a big number of fields and performing calculations. In the cube, it is required to use only dome source fields and calculate and aggregate data.

On executing subqueries, it must be remembered that each complementary subquery nesting level increases the time required to execute the whole query. The whole nesting and the number of subqueries depend on the number of sources and dimensions in cube. Subqueries are used by default.

If aggregation is set up for facts, when working with data in various Foresight Analytics Platform tools, drill down of data is also available. If aggregation is not set up, drill down can be implemented by means of a custom SQL query.

To enable drill down up to relational data, execute the following:

Select the Show Drilled Down Data on Query checkbox.
Select the required query in the drop-down list.

The query must meet the following requirements:

Names of fields, that can be obtained after query execution, must match the identifiers of the fields that are bound to dictionary attributes and cube facts.
The number of fields, that can be obtained after query execution, must not be less than the number of source fields that are bound to dictionary attributes and cube facts.

If a parametric query is used, its parameters can be bound to cube parameters. Binding is executed on the Manage Parameters page.

In case, if fact is bound to a maximum one source field, it is possible to not bind dimensions to data sources. Dimensions that are not bound to source fields are not used for sampling, but they can be used to set up the cube, for example, act as controlling dimensions for other dimensions.

NOTE. The absence of binding leads to the fact that the data multiplies across the entire dimension. This is due to the fact that the selection in the dimension does not affect the data sampling. The data obtained from the selection from other dimensions will be placed on each element of the unbound dimension. On an unbound dimension, the data will be the same for each element.

If there are unbound dimensions in the cube, then when you go from the Cube Dimensions page to the next wizard page, you will see the message:

If the facts have several bindings, and the dimensions are not all bound, then the cube cannot be saved:

Cube Facts Binding

Calculation Location

Features of Work with Configured Aggregation of Fact Dimension