This page is used to bind data source fields with cube facts.
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.
NOTE. On working with data analysis and building reports tools to save data to MDM dictionary with multiple values of attributes, select a dictionary as a source and bind dictionary attributes with cube facts. The Key attribute must be linked with cube dimension.
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 in the Values list select the fact, to which the field should 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.
If the cube is used as a data source for a table, which cells will be used for linked as attachments, first, select the dictionary to store attachments and then additionally bind data source fields and cube fact in the Cube Facts area in the Attachments list. The attachment field is available for binding if the value field has been already bound to the fact. If aggregation is set up for facts, attachment fields cannot be bound.
NOTE. The field for storing attachment links should be created in the relational data source. The field should be numeric.
Set up calculated expressions for bound facts
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.
Set up aggregation of fact dimension
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.
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:
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.
Set up working with attachments
Cubes also allows for saving attachments (files, URL, or repository documents) by each coordinate except for the values.
To enable attachments in the cube, select the existing table MDM dictionary in the drop-down list or click the Create button to create a new dictionary. The selected dictionary should have predefined structure and must have the following attributes along with basic attributes (Key, Name, Owner, and Order):
| Attribute identifier | Type of attribute data |
| FILE_NAME | String |
| URL | String |
| TYPE | Integer |
| TIMESTAMP | Date |
| COMMENT | String |
| FILE_SIZE | Real |
| USER_ID | String |
| ATTACHMENT | Binary |
If you plan to store links to repository documents, also create an additional attribute with custom identifier and having the integer data type.
After the dictionary is selected, in the Cube Facts area bind facts with the data source fields, in which MDM dictionary element keys with attachments are to be stored. To do this, in the Data Sources area select the integer field and in the Cube Facts area in the Attachments subgroup select the fact and click the Bind button.
Further work with attachments will be executed in tables of various tools of Foresight Analytics Platform. For details see the Linking Attachments to Cells subsection.
Use subqueries when retrieving data
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.
Show drilled down data based on SQL query
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.
If the cube is used as a data source for a table, which cells will be used for linking attachments, create a new table MDM dictionary or select the one that was created on setting up cubes by the Create button in the drop-down list.
IMPORTANT. Only the dictionaries created during cube setup can be used as attachments dictionaries. It is forbidden to create and edit the dictionary manually.
If the attachments dictionary has not been created yet, create it during cube setup by clicking the Create button. A table MDM dictionary located in the folder with the configured cube is created.
Mandatory dictionary attributes are:
NAME. Attachment name.
FILE_NAME. Attachment file name.
URL. Attached URL.
TYPE. Attachment type.
TIMESTAMP. Attachment loading date or update date.
COMMENT. Attachment comment.
FILE_SIZE. Attachment file size.
USER_ID. Identifier of the user who added or updated attachment.
The dictionary is filled automatically when attachments are linked.
If several facts are set up in the cube, the common dictionary is set to store attachments.
One dictionary can be used for several cubes.
No binding of dimensions to fact table
If a fact is bound to more than one data source field, it is possible to not bind dimensions to data sources. Dimensions that are not bound to data 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. In an unbound dimension, the data will be the same for each element.
If there are unbound dimensions in the cube, when you go from the Cube Dimensions page to the next wizard page, you will see the message:
If there are unbound dimensions, it is recommended to determine aggregation method for facts.
If the facts have several bindings, and the dimensions are not all bound, the cube cannot be saved:
See also: