Data Transformation

Field names are loaded from the data source. These names will be used to form attribute names in dimensions.

To rename attribute, double-click name of the corresponding field in table and specify a new name:

NOTE. It is recommended to rename fields with values on this page. These names will have facts in fact dimension.

All fields data obtained from the source will be used on import. If some data is irrelevant, it can be excluded. To do this, execute one of the following operations:

• Select the Exclude item in the column's context menu.

• Click the button and select the Exclude item.

NOTE. To import to cube, there must be at least two columns, to time series database - at least three columns.

Type of data contained in source fields is determined automatically in accordance with the advanced settings. If the type is determined incorrectly, it can be corrected. To change data type, click the button and select the required type:

The fields with the Data type are determined automatically by checking the correspondence with most frequently used date formats, for example, DD.MM.YY, DD/MM/YY, and so on. If dates are set in some specific format, it can be specified by executing the additional command: Date > Set Up. The Date Format dialog box opens. Dialog box is described in theSetting Up Date Format subsection. Determine format by using available substitutions and required delimiting characters. After clicking the OK button, the format is applied to determined date values.

String fields can be divided into several fields if they have a particular delimiter character. Click the button for division and select the Split into Several Fields item. In the dialog box that opens select delimiter in the drop-down list and click the OK button. After analyzing values and searching the selected field delimiter, the necessary number of additional fields is created, and appropriate values are placed into them. The number of created fields corresponds to the number of parts, into which a field value can be divided, that contains maximum number of delimiter characters. If paired delimiter characters are used (round brackets, square brackets, and so on), new fields include only the values located inside these delimiters. The values outside the paired delimiter characters are excluded.

NOTE. If division is not available, an error message appears. Select another delimiter and repeat division.

Each created field is calculated, if required, it is available to change expression for it. The selected delimiter character is excluded from values. Data type is determined automatically for all created fields.

Extra fields obtained on the division can be excluded or deleted. To do this, click the button and select the corresponding item.

Example

If the Indicator field is divided by means of the , (Comma) delimiter, the result is the following:

Additional columns are added to table that correspond to the set division.

The Add Calculated Field button enables the user to create additional fields in table, which values are calculated during data loading by specified expressions. Clicking the button opens the expression editor to form an expression with the use of source fields and various functions and operators.

After clicking the OK button the calculated field is added to the table, and value is calculated by previously loaded records:

NOTE. Working with calculated fields in a crosstab is identical to working with calculated fields in a standard table.

Managing Calculated Fields

To edit the expression, by which the created field is calculated, click the button and select the Edit item. Selecting the item opens the expression editor.

To remove the calculated field, click the button and select the Remove item.

If not all data should be imported from the data source, filtering by field values can be set up at the stage of data correction. Filtering condition can be manually set in the Data Filtering Formula box or form in the expression editor, which opens on clicking the Edit button. The condition is formed by means of fields and various functions. The condition should return logical value. The records are imported, which satisfy the specified condition.

The Apply button enables the user to apply the specified filter to the first loaded records, which are displayed in table.