Dimensions and Dictionaries window
Admin | 
ETL > ETL Domains > Dimensions and Dictionaries
IMPORTANT: if the ETL domain is locked, the window will open in read-only mode.
Purpose of window¶
This window allows you to define the staging table fields and a set of the permitted values for the dimension.
Actions on this page
| Element | Description |
|---|---|
| This filters the data shown on the page. | |
| This adds a row in order to insert a dimension. | |
| This duplicates the selected row with all the information inserted, except for the code. | |
| Deletes the element contained in the selected row. | |
| Enables/Disables the grid modify function. This allows you to change the characteristics of the selected elements directly in the table, and to hide the configuration tabs. | |
| Enables multi-row selection to allow mass changes. | |
| Selects the columns to be shown/hidden in the table and those to be locked when scrolling the table. | |
| Exports the data in Excel format so they can be edited and reimported into CCH Tagetik. | |
| Imports data from a previously prepared Excel file. |
Attributes tab
This tab allows you to define dimensions with the following values:
| Field | Description |
|---|---|
| Code | Numerical value which identifies one of the columns of the staging table. - dimensions 1 to 89 are "varchar" type, with a length of 50 characters; - dimensions 90 to 99 are "varchar" type, with a length of 255 characters; - dimensions 100 to 130 are "varchar" type, with the length defined in the Text length field; - dimensions 131 to 200 are "clob" type Note: up to 200 dimensions can be managed. See Staging Tables Topic |
| Description | Contains the description of the dimension (numerical, text-based, etc.). |
| Acronym | Brief description which can be used to refer to the dimension in the dynamic syntax of texts and the ETL’s SQL filters. |
| Type | Indicates the type of data that populates the dimension: - String: only for dimensions whose code is between 1 and 99; - Number: only for dimensions whose code is between 1 and 99; - Date: only for dimensions whose code is between 1 and 99; - Text: only for dimensions whose code is between 100 and 130; - Clob: only for dimensions whose code is between 131 and 200. IMPORTANT: Clob type dimensions, which allow you to manage large volumes of text-based content, can be used in all setups and have some specific features: - they cannot be stored in extracted data or transformed data - for ETL domains that use those dimensions, all steps of the routine must be run. IMPORTANT: Since all columns in the staging table are “varchar” type, the system only uses thetype indicated for every dimension in the following cases - to format the data to be displayed following a query on the staging table or log table. Numerical data are formatted based on the user’s nationality; - to perform an automated conversion of the value of the element of a dimension every time the Acronym is inserted within a dynamic syntax in an ETL filter, an SQL model or an SQL query. For example, suppose we have a number type ETL dimension (e.g.CAMPO10) whose acronym is “IMP” and we want to multiply the value by -1, just write {IMP} * -1 in the query in order for the system to automatically convert the value to a number before multiplying it by -1. |
| Date format | - Indicates the date format that the data to be imported will have. Mandatory for dimensions whose type is “Date”. |
| Text length | - Indicates the number of characters that the dimension can contain. Mandatory for dimensions whose type is “Text”. |
| Identity | Allows you to relate the dimension that will contain the values to a CCH Tagetik logical dimension. Identities are useful for restricting the possible values in selecting parameters linked to these dimensions and in certain transformation or loading tasks. The possible identities are as follows: - None - Entity - Scenario - Period - Event scenario - Account - Custom dimension - Classifier - Counterparty zone - Counter - Category - Currency - Original currency - Record num. IMPORTANT: you cannot specify an identity for Clob type dimensions. |
| Identity code | Mandatory for the following identities: - Custom dimension indicates one of the managed custom destinations - Classifier: indicates a classification variable Note: The value of this field must be unique for the ETL domain. |
| Encode | If active, this option indicates that the values entered in this dimension must be encoded in the extraction step. |
| Checks section | |
| Completeness checks | Link via which the Dimension completeness checks window is accessed IMPORTANT: if the ETL domain is locked, the window will open in read-only mode. |
Dictionary tab
This tab allows you to set dictionaries for every “String” or “Text” type dimension to indicate a set of permitted values for the dimension.
IMPORTANT: The use of a dictionary does not prevent the insertion of values that are not present in that dictionary for a dimension.
However, the insertion of a Dictionary check type transformation task in an ETL routine will mark data processed with values that not present in the dictionary with warnings during the execution step.
By activating the Signal empty values in the check option, you can tell the check to signal any undefined fields with warnings.
| Field | Description |
|---|---|
| Dictionary | The following options can be defined for the selected dimension: None: no dictionary defined. Manual: activates the Dictionary elements definition link Note: if the ETL domain is locked, the window will open in read-only mode. - From Tagetik: the dictionary elements are represented by the values of a field read from a CCH Tagetik table. |
| Tagetik table | Only for From Tagetik. Allows you to select a table of the CCH Tagetik DB from which to extrapolate the dictionary elements. |
| Dictionary code field | Only for From Tagetik. Specifies the field of the selected table from which the dictionary item codes will be extracted. |
| Dictionary description | Only for From Tagetik. Specifies the field of the selected table from which the dictionary item descriptions will be extracted. |
| Dictionary filter | Allows you to restrict the dictionary elements. Example: even if all the entity codes present in the ENTITY table have been entered in the dictionary, it is possible to consider only those whose code starts with A0 (in this case the SQL condition to enter is COD_AZIENDA LIKE 'A0%') |
| Signal empty values in the check | If active, the option marks undefined fields with warnings. |
| Delegate management | Only for Manual. If active, this option allows you to delegate the management of the dictionary elements to the End User (this is generally done by the Admin). |
Example - From CCH Tagetik
To use the entity codes present on the “ENTITY” table as dictionary elements, you must define the Tagetik table element as ENTITY, the Dictionary code field as COD_AZIENDA and the Dictionary description field as DESC_AZIENDA0. The dictionary elements can be restricted by inserting a filter condition in the Dictionary filter attribute. For example, even if all the entity codes present in the ENTITY table have been entered in the dictionary, it is possible to consider only those whose code starts with A0 (in this case the SQL condition to enter is COD_AZIENDA LIKE 'A0%’).