Routine - Transformation
Admin | 
ETL > Domains > select the desired domain > Routines > Transformations link
Note: if the ETL domain is locked, the link will not be selectable.
Purpose of the page¶
This page allows you to manage Transformations, i.e. tasks of a routine which, in the execution step, allow the following activities on data in order to make them ready for loading:
- filtering
- check
- enrichment
What it looks like¶
Attributes tab
| Field | Description |
|---|---|
| Enable | If enabled, the task will be run. Selected by default. |
| Sorting | indicates the order in which the task must be run. You cannot set the value 0 or a sorting that already exists |
| Task | indicates the activities that can be run, subdivided into the following macro types: - Filter task - Check task - Enrichment task IMPORTANT: Transformation tasks are run sequentially following the Sorting defined in the setup step. Every task acts on the staging table and processes the data produced by the previous task (except for the first, whose input data are those generated by the extraction step). Note: Each task may require further information to be defined. A specific details panel will appear in the window depending on the selected task. The details of each panel are explained in the section on task type. |
| Descriptions | Descriptions of the tasks in the languages enabled in the environment. |
| Notes | indicates any notes |
| ETL filter (SQL Model) | allows you to indicate a filter that has been defined previously in the SQL models to limit the number of data items to be extracted; |
| ETL filter (specific) | allows you to indicate a filter with the SQL syntax to limit the number of data items to be extracted. The ETL’s dynamic syntax can be used (see ETL syntax) Note: If the user specifies both the ETL filter (SQL Model) and the ETL filter (specific), the system will concatenate the conditions with the logic operator AND |
Filter task
| Task | Description |
|---|---|
| Parameter choice | allows you to define up to 9 Parameters and, based on the definition type, enable the following options: - define a dimension for all records to be transformed (if the parameter type is Free, From Tagetik or From dimension dictionary and it does not have the Multiselection option active); - filter the data to be transformed by deleting from the staging table all records for which the dimension related to the parameter does not contain a certain value selected by you for the parameter (if the parameter has a different configuration from that described in the previous point). Note: The values to be assigned to a parameter are selected on execution and depend on the definition of the parameter used (see Parameters). |
| Delete by SQL | allows you to delete from the staging table all records that do not satisfy the filter defined in the ETL filter (SQL Model) and/or ETL filter (specific) fields. |
Check task
| Task | Description |
|---|---|
| Invalid characters check | allows you to check whether the values of a given Dimension defined in the Dimension to check field contain characters that are not allowed in CCH Tagetik codes. |
| Dictionary check | allows you to check, for the ETL domain dimensions of the routine with which a Dictionary is associated, that the values present in the processed data belong to the set of values allowed for the defined dictionary. By default, the system runs the check on all dimensions present in the routine’s ETL domain for which a dictionary has been defined. If the Data Source field is defined, you can limit the check to the ETL domain dimensions used in the data source fields definition. By activating the Signal empty values in the check option in the dimension’s dictionary definition, you can make sure that this task also includes the absence of a value for the dimension as one of the check’s fail conditions. |
| Completeness check | allows you to check, for the ETL domain dimensions of the routine with which a Completeness Check is associated, that the values present in the processed data satisfy the completeness rules related to the record. This means that for every record of the staging table and for every value of a dimension included in the check, all other dimensions defined as mandatory must contain a value, whereas those defined as prohibited cannot contain any value (NULL value). This check is useful for verifying that: - data extraction has taken place correctly; - enrichment tasks following extraction have been run correctly. By default, the system runs the check on all dimensions present in the routine’s ETL domain for which a completeness check has been defined. If the Data Source field is defined, you can limit the check to the ETL domain dimensions used in the data source fields definition. Note: These tasks generate user warnings, for the staging table records on which they are run that do not pass the checks. |
| Completeness and dictionary check | This task allows you to run both the Dictionary Check task and the Completeness check task. |
| Check with SQL | This task allows you to identify all the records that do not satisfy the filter defined in the ETL filter (SQL Model) and/or ETL filter (specific) fields. It is usually used to search for data containing logically incorrect information. This task may be useful, for example, if run before a Delete by SQL task in order to allow the user to see what the system will delete in the next task and thus allow them to make any corrections. |
| Lookup Table Diagnostic | allows you to check that the lookup table defined in the Lookup Table field has been set up correctly. The following checks are performed: - if the lookup table has the Full relationship option active, the system checks that the staging table contains all the values that male up the input combinations defined in the lookup table elements; - if the lookup table does not have the Multiple relationship option active, the system checks that the combination of input values defined in the lookup table elements is unique; - if the lookup table has the Multiple relationship option active, the system checks that the combination of input and output values defined in the lookup table elements is unique. |
Enrichment task
| Task | Description |
|---|---|
| Lookup table feeding | Only valid for lookup tables that have their feeding type defined as Tagetik or Deployed: allows you to feed the lookup table elements defined in the Lookup Table field. The system will delete all existing elements (even if edited) and insert the new elements from the data source defined by the lookup table. |
| Data grouping | allows you to aggregate the data obtained in the extraction step or the data enriched by a previous transformation task. In the Dimensions to aggregate (SQL Model) / Dimensions to aggregate (specific) fields, you must specify the list of fields (separated by commas) of the staging table that must be aggregated, including the aggregation function to be used. You need not use upper case when writing the text E.g.: “MAX(CAMPO1), MIN(CAMPO2), SUM([conversion function in number format](CAMPO3))"). When a numerical aggregation function is used, you must set, for the staging table field, the conversion function in number format required by the database type. Alternatively, you can have the system manage any conversion in number format by replacing the CAMPOx syntax with the value of the Acronym field between {} of the dimension related to the staging table field. Any filters set on the task will only perform the aggregation of the portion of the data that satisfies that filter. The non-aggregated portion of the data will support the new aggregated data and will be available for the next transformation task. |
| Enrichment: lookup table | this allows you to apply the transcoding rule defined in the lookup table specified in the Lookup Table field. In the staging table, the output dimensions are defined with the values of the output elements for the records where the values of the input dimensions are aligned with the values defined in the input elements. If the Multiple relationship option is active for the rule, the system will open as many records as there are combinations of values defined in the output elements, with the same combination of input values, for every combination of values found in the input dimensions. |
| Enrichment: matrix calculation | Function used in the context of the Italian banking system. Allows you to manage specific additional information that is useful for relating data of various origin and nature (loans, current accounts, etc.), with the consolidated chart of accounts. |
| Enrichment: SQL instruction | allows you to define the staging table field related to the dimension defined in the Output Dimension field using an SQL syntax defined in the Input text (SQL model) or Input text (specific) fields, where it is also possible to use the ETL’s dynamic syntax (see Filter Syntax or SQL Query). You can indicate a query or a fixed value delimited by single inverted commas. Example 1: Suppose we want to define the field related to the custom dimension (6 - Custom dimension) with the last three characters of the value of the field related to the account (5 - Account) only for accounts starting with IC. To do this, we must define: Output Dimension: 6 - Custom dimension Input text: RIGHT(CAMPO5, 3) ETL filter (specific): LEFT(CAMPO5, 2) = 'IC' Thus, the following result is obtained: |
| Enrichment: formula | allows you to define the staging table field related to the dimension defined in the Output Dimension field by applying a numerical formula previously saved as the value of the field related to the dimension defined in the Input dimension field that uses, in its syntax, references to other fields made through the ID of the dimension number between braces (EXAMPLE: "({2}*{3} / 100)"). All the dimensions used both in the definition of the task and in the syntax of the formula must be numerical type and contain a valid number. Example: Suppose that the field related to the amount to be loaded onto CCH Tagetik (4 - Result) is represented by numerical operations defined as a formula in the field related to dimension "1 - Formula" from among the amounts contained in the fields related to dimensions "2 - Amount1" and "3 - Amount2". To do this, we must define: Input dimension: 1 - Formula Output Dimension: 4 - Result Thus, the following result is obtained: |
| Enrichment: amounts total | allows you to define the staging table field related to the dimension defined in the Output Dimension field with the sum of the amounts contained in the field related to the dimension defined in the Input dimension field, by summing up, with the same values contained in other fields of the staging table listed in the Dimensions to aggregate (SQL model) / Dimensions to aggregate (specific) fields (e.g. "CAMPO1, CAMPO2, CAMPO3"). Note: The input and output dimensions must be numerical type and the relative fields must contain numerical values. Example: Suppose we want to sum up the amount contained in the field related to dimension "4 - Amount" for the fields related to dimensions "1 - Scenario", "2 - Period", "3 - Entity” and save it in the field related to dimension "5 - Total amount". To do this, we must define: Input dimension: 4 - Amount Output Dimension: 5 - Total amount Dimensions to aggregate (specific): CAMPO1,CAMPO2,CAMPO3 Thus, the following result is obtained: |
| Enrichment: ranking | This task allows you to define the staging table field related to the dimension defined in the Output Dimension field with a cumulative number with the same values contained in other fields of the staging table. The method by which the cumulative number is assigned is indicated in the Ranking Type field: - Differentiate sorting (value priority): the cumulative number is increased for every record contained in the range of records with the same values in the fields on which to run the ranking. However it is reset for every new range of records; - Keep sorting (value priority): the same cumulative number is assigned to the records contained in the same range of records with the same values in the fields on which to run the ranking. It is increased for every new range of records. The fields of the staging table on which to run the ranking and for which the sorting takes place and the cumulative numbering is reset are defined in the Dimensions to aggregate (SQL model) / Dimensions to aggregate (specific) fields (e.g.: GROUP BY CAMPO1, CAMPO2, ORDER BY CAMPO1, CAMPO2); these fields can contain: - a GROUP BY clause which indicates the staging table fields which, with the same values, are inserted in the same range of records on which the ranking is applied, as defined in the Ranking Type field; - an ORDER BY clause which indicates the staging table fields by which to sort before assigning the range to each record. Example: Suppose we want to sort the amounts contained in the field related to dimension "2 - Amount” with the same value as the code of the account contained in the field related to dimension "1 - Account”, sorting by amount. To do this, we must define: Ranking Type: Differentiate sorting (same values) Output Dimension: 3 - Output ranking Dimensions to aggregate: GROUP BY CAMPO1 ORDER BY CAMPO2 DESC Thus, the following result is obtained: |
| Year-to-date calculation | Transforms the loaded amounts, by period, into cumulative values. Based on Period length dimension. This calculation is performed in order to standardise the imported data with the data managed by CCH Tagetik and is only applied to Profit & Loss and Other Variations accounts or to Variation accounts. The calculation depends on the process type. |
| Verticalization | This task allows you to define, in Tagetik, details related to the dimension defined in the Output Dimension field, which are subdivided among various fields in the staging table. The calculation multiplies the rows, combining the fields for all the combinations required for the Output Dimension |
| Counterparty zone unbundling | Task used by Italian banking entities for loading their disclosures to the Bank of Italy |
| Transfer Price from the Rule | The amount to be defined is calculated as “Price X Quantity” The formula is applied by indicating the field that expresses the quantity and the field on which to write the result of the formula |
| Allocation | Task used to repair the data based on the percentage obtained via a driver (which is defined through a staging table field: Weight dimension) |