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 Actions 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. Excluded fields can be used on composing expression for calculated fields.

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 Actions 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 slit into several fields if they have a particular delimiter character. Click the Actions button for splitting 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 split that contains the 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 splitting is not available, an error message is displayed. Select another delimiter and repeat splitting.

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

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

The example:

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

Additional columns are added to table that correspond to the specified split type.

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 create an expression with the use of source fields and various functions and statements. Excluded fields are also available for use.

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.

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

To delete the calculated field, click the Actions button and select the Delete 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 can be created in the expression editor, which opens on clicking the Edit button. The condition is created 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.