Routine - Loading

Admin |

application database > Routines > Loading link

Note: if the ETL domain is locked, the link will not be selectable.

Purpose of the page

On this page, you can manage the loading, or the tasks of a routine that allow you to load data from the staging table onto destinations defined in Data target: CCH Tagetik and in Data target: Others.

Note: Loading carried out on CCH Tagetik requires editability checks to verify that the information to be inserted or deleted is correct. Any incorrect records will be shown to the user via warnings at the end of ETL execution.

What it looks like

Attributes tab

Field/section	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
Data Target	indicates the loading policy for the respective targets: - Data target: CCH Tagetik: load onto CCH Tagetik database - Data target: Other: load onto another database or onto one of the managed types of file. 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.
Loading policy	Indicates the policy according to which the system must load data into the destination: Table (update/insert): can only be used if the data loading takes place on a table that has a primary key defined. Mainly used to enter structural data or data regarding tables on which no deletions are required. The following detail tasks are run: - Task1: Update target table; - Task2: Append into target table; - Task3: None. Update data (delete/insert): can only be used if the data loading takes place on a table that has a primary key defined. Typically used to update numerical data. The detail tasks are: - Task1: Delete from target table; - Task2: Insert into Target Table; - Task3: None. Append data (insert): mainly used to insert new structural or numerical data. The detail tasks are: - Task1: Insert into Target Table; - Task2: None; - Task3: None Specify: indicates that the detail tasks to be run must be defined manually by the user.
Loading policy detail	- a Task1 which indicates the first detail task to be run; - a Task1 filter which allows you to indicate a filter to be applied to the operation run by Task1; the ETL’s dynamic syntax can be used (see Filter Syntax or SQL Query); - a Task2 which indicates the second detail task to be run; - a Task2 filter which allows you to indicate a filter to be applied to the operation run by Task2; the ETL’s dynamic syntax can be used (see Filter Syntax or SQL Query); - a Task3 which indicates the third detail task to be run; - a Task2 filter which allows you to indicate a filter to be applied to the operation run by Task3; the ETL’s dynamic syntax can be used (see Filter Syntax or SQL Query); Allows you to specify the policy for loading data into the destination, through detail tasks: in the Task1,Task2 and Task3 fields, one of the following types can be selected: - Delete from target table: deletes the data present in the target table defined in the data target; - Delete from target table; deletes data present in the target table defined in the data target that are also present in the staging table (existence check for primary key); - Delete non-ETL data from target table: deletes data present in the target table defined in the data target that are not also present in the staging table (existence check for primary key); - Insert into Target Table: inserts all data present in the staging table onto the data target. - Append into target table: allows you to insert, into the target table defined in the data target, non-existent data that is present in the staging table (existence check for primary key); - Update target table: allows you to update the data present in the target table with the values present in the staging table (existence check for primary key)
Advanced options	Only for Data target: CCH Tagetik, the following options can be activated: Action detail accounts: for loading onto the CCH Tagetik Gross Amounts and Intercompany tables with Loading policy set to Update data (delete/insert). This activates the loading policy via the window that details, for every account, which amounts are present in the staging table, which are present in the target table with origin related to the current routine, and which are present in the target table with other origins. From this window, you can select which amounts must be inserted and which must be deleted. - Restricted editability (not recommended): for loading onto the CCH Tagetik tables of values, where editability checks are used to verify that the inserted information is correct; it disables the checks related to: - Account restrictions - Custom dimensions - Entity restrictions - Custom dimensions - Counterparty rules

Filter parameters tab

This tab allows you to set up to nine filter parameters to dynamically limit the number of data items to be loaded.

Field	Description
Filter parameter 1, 2...9	You can select one element in each field

Note: If the user has also specified one of the other filters, the system will concatenate the conditions with the logic operator AND.

Filters tab

This tab allows you to limit the number of records processed by each detail loading task using the following filters:

Section	Description
ETL filters	- ETL filter (SQL Model) allows you to indicate a filter that has been defined previously in the SQL Models This filter is applied to the staging table during insert and update operations. - ETL filter (specific) allows you to indicate a filter manually using the SQL syntax ETL’s dynamic syntax can be used (see Filter Syntax or SQL Query). This filter is applied to the staging table during insert and update operations. Note: If the user specifies both filters, the system will concatenate the conditions with the logic operator AND.
Target filter	- a Target filter (SQL Model) which allows you to indicate a filter that has been defined previously in the SQL Models. This filter is applied to the staging table during delete and update operations. - a Target filter (specific) which allows you to indicate a filter manually using the SQL syntax. ETL’s dynamic syntax can be used (see Filter Syntax or SQL Query), This filter is applied to the target table during delete and update operations.
Origin	- a Filter on origin which allows you to indicate the deletion policy to be applied to the field of the target table identified as the data origin. For CCH Tagetik tables, there is a predefined field (ORIGIN) whereas for custom tables both from the CCH Tagetik database and from other databases, the field identified as the origin is indicated via the Value type attribute in the Data target: Others fields definition. The available options are: - Do not delete: no data in the target table will be deleted. This option is also used to let you indicate a different value in the execution step if the Deletion management option is active for the selected routine; - Delete all: does not apply the filter to the origin field; - Delete manual origin records: deletes data in the target table whose origin isMAP*, INPUT_DEFORM, INPUT_WEB and QDL; - Delete manual and other origin records: deletes data in the target table whose origin is MAP*, INPUT_DEFORM, INPUT_WEB, QDL and all other origins defined in the Other origins field; - Delete manual and calculation origin records: deletes data in the target table whose origin is MAP*, INPUT_DEFORM, INPUT_WEB, QDL, CONS_CALCULATED and PROC_CALCULATED; - Delete origin records from current routine: default value, deletes from the target table data whose origin is the calculated origin of the current routine (MAP_'ETL_Domain_code'_'Routine_code'). - a Suffix which allows you to differentiate, for each Loading task, in the insert and deletion phase, the origins generated by the current routine. If specified, the generated origin will be MAP_’ETL_Domain_code’_’Routine_code’_’Suffix'; - an Other Origins which allows you to indicate a set of other origins to be deleted, if the value of the Filter on origin is Delete manual and other origin records.