Next >> << Prev

6. Transform & Load Tab: Transform and Load Data

The Load Tab is where you manage all your data loads (i.e. data imports) and transformations.

Load Tab
 

6.1. List of Load Groups and Jobs


The list on the left contains all your load groups and jobs. Jobs represent data loads performed by DMT and groups are a way to organize jobs like folders and files. The right hand side section (2,3,4) displays information about the group or job currently selected.

You can navigate in the list just like in the Windows explorer. You can rename groups and jobs, and move them around by drag and drop.

The check box on the left indicate if a group or job is active (default). When you uncheck this box, the corresponding group or job becomes inactive i.e. you can no longer make changes to it and it cannot be run.

 

6.1.1. Shortcut Buttons

The buttons underneath the list give you access to the main commands for load groups and jobs.

Shortcut Buttons

New: Create a new load group or job

Del.: Delete the currently selected group or job from the list.

Validate: Check that a group or a job doesn't contain any errors and thus is ready to be run.

Run: Run a group or a job directly in the client (i.e. ignores the scheduling parameters).

 

6.1.2. Load List Context Menu

Most of the commands to manage load groups and jobs are accessible from the load list context menu. Right click anywhere in the list, and it opens the context menu for the currently selected group or job.

The menu commands are pretty self-explanatory. They are the same whether a group or job is currently selected (only the name changes).

Load List Context Menu

New Group: Create a new load group in the list.

New Job: Create a new load job in the list. The job is added wherever the current selection in the list is. In particular, if the current selection is a group, the job is added to the group.

New Standalone Job: Create a new standalone load job in the list. A standalone job is a job that doesn't belong to a group.

Note: New groups and jobs are automatically named with today's date. You can rename them to something meaningful and just remember that group and job names must be unique.

Rename: Rename the currently selected group or job in the list. The shortcut key is F2 like in the windows explorer.

Cut, Copy: Cut or copy the currently selected group or job into the clipboard. The shortcut keys are Ctrl+X and Ctrl+C like in the windows explorer.

Paste: Paste the group or job currently in the clipboard into the list. The shortcut key is Ctrl+V like in the windows explorer.

Delete: Delete the currently selected group or job from the list. The shortcut key is Delete like in the windows explorer.

Validate: Check that a group or a job doesn't contain any errors and thus is ready to be run. It is good practice to validate a group or job before running it.

Reset: Reset the run counter and the last run date of a group or a job as if it had never run.

Run: Run a group or a job directly in the client (i.e. ignores the scheduling parameters).

Bulk operation: Open a sub menu containing bulk operation commands.

Load List Context Menu

Activate All: Make all the groups and jobs in the list active.

Inactivate All: Make all the groups and jobs in the list inactive.

Validate All Active: Validate all the active groups and jobs in the list. Same as the Validate command but for all active groups and jobs.

Reset All Active: Reset all the active groups and jobs in the list. Same as the Reset command but for all active groups and jobs.

 

6.2. Load Group or Job Parameters


In this section you can view and change the main attributes of a load group or job. The attributes are slightly different whether a group or job is selected in the list.

Load Group Parameters
 
Load Job Parameters

Name: The name of a group or job. It is used to identify it in DMT and each name must be unique. You can use spaces and most special characters in the names.

Linked to Ext.: Select here the name of the export group or job to link to. When a load is linked to an export, it automatically loads the data file produced by the export it's linked to. You can link to any export group or job as long as the export produces a data file (e.g. you cannot link to logical or "schedule only" export groups).

Group Type (Group Only): This field allows you to set the type of a load group. Values "Schedule Group", "Data Group" and "Multi Table Group" can be set manually. The value "Logical Group" is set automatically when a group is neither a Schedule nor a Data Group.
Note that selecting "Multi Table Group" automatically selects "Schedule Group", "Data Group".

For more details on the group types and their characteristics, see the group definition.

Database Connection (Job Only): In this field, choose the database connection the job uses to load data (i.e. where is the data loaded into). The database connection can be any ODBC connection available.

You can define the ODBC connections in the Settings Tab. You can refresh the ODBC connections by right clicking on this field.

Data Folder: Folder where the data file is stored. You can enter the folder name manually or click on the button on the right ( > ) to open the folder selection form.

Data File: Name of the data file to be read by the group or job (i.e. file containing the data to load). You can enter the file name manually or click on the button on the right ( > ) to open the file selection form.

File Format: Select here the format of the data file read by the group or job. Click here to see details of all the supported formats.

File Parameters: This link opens the Data File Parameters Form form allowing you to set additional parameters describing the format of the data file (e.g. character encoding, separator char, header row, etc.).

Pre-Conditions: This link opens the Run Pre-Conditions form allowing you to set run pre-conditions for the group or job. Run pre-conditions can be used to allow the run of a group or job only if certain conditions are met.

PP Process: This link opens the Pre and Post Processes form allowing you to set processes that can be executed before or after the group or job run.

Advanced Parameters: This link opens the Advanced Parameters form allowing you to set additional parameters to the group or job (e.g. data file backup, error handling, Run Variables, etc.).


Note: Certain fields are enabled or disabled depending on the type of group or job you're viewing (e.g. data file parameters are disabled for a job belonging to a data group because the data file is set at the group level and shared for all the jobs within the group).

 

6.3. Load Mapping


The Load Mapping section is available for load jobs only. In here you define where and how the data is loaded into the target database.

Load Mapping


Dest. Schema/Table: In these fields you can choose the names of the destination schema and table (i.e. table into which you want to load data). These fields are drop down lists that show you the list of schemas and tables available in the target database (i.e. the database connection you've selected).

In the example above, the destination schema is not specified and the destination table is Prod_History.

Column Definitions: This link opens the Column Definitions Form allowing you to create or automatically load the column definitions (i.e. the list of columns) for the data file and the target table.

Note: The data file and the target table column definitions must be loaded before a load can run so DMT knows what columns are available in the data file and target table.

Load Condition: In this field you can specify an optional filter on the data to be loaded. The load condition must be true for a data line to be loaded. For instance if you don't want to load records where the field Product Type in the data file is 'TEST', set the load condition to $DFC_[Product Type] <> 'TEST'. $DFC_[Product Type] represent the column "Product Type" for each row in the data file.

The button on the right ( > ) opens the Expression Editor Form that helps you write, validate and evaluate expressions.

Column Mapping: The column mapping area is where you specify how to populate the destination table. It consists of a grid in which each row describes how to populate one column in the destination table.
e.g. in the example above, the NAME column in the destination table (Prod_History) is populated with the PROD_CODE column from the data file.

The column mapping grid contains 7 columns:
  • Index: (first column with no header) Automatically populated with the index of each row in the column mapping grid
  • Active: Indicate that the column mapping is active (i.e. data will be loaded in the destination column). When unchecked, data is not loaded in the destination column.
  • UK/FK: Drop down list indicating which role the destination column will play in the load.
    • Empty (default): The destination column is a simple data column
    • UK: The destination column is a User Key, i.e. its value can be used to reference a row in the destination table.
    • FK: The destination column is a Foreign Key, i.e. its value contains the same value as the UK column

    Note: The UK/FK values can only be used for load jobs belonging to a Multi Table Group.

  • Dest. Column: Drop down list in which you choose the name of a column in the destination table
  • Eval: Indicate if the expression entered in the "Value" column should be evaluated before it’s sent to the destination database.
    • If unchecked (default): Expression in "Value" is not evaluated and sent as is to the database. You can use database specific expressions, e.g. SUBSTR($DFC_[NAME], 1, 2) for Oracle or LEFT($DFC_[NAME], 2) for SQL Server
    • If checked: Expression in "Value" is evaluated and its result is sent to the database. You must use DMT specific expressions and syntax, e.g. Trim($DFC_[NAME])
  • Value: Value to be placed into the destination column. The value can be any valid expression combining constants, Run Variables or any of the Data File Columns. e.g.:
    • $DFC_[PROD_CODE] Destination column is populated with the content of the PROD_CODE column from the data file
    • 'N/A' Destination column is populated with the string 'N/A'
    • $DFC_[PROD_UNIT_PRICE] + 10 Destination column is populated with the content of the PROD_UNIT_PRICE column from the data file + 10
    • SUBSTR($DFC_[FIRST_NAME], 1, 1) || SUBSTR($DFC_[LAST_NAME], 1, 1) Destination column is populated with the initials from the FIRST_NAME and LAST_NAME columns from the data file
      Note that this example uses the Oracle syntax ||
    • NVL($DFC_[LAST_NAME], 'Unknown') Destination column is populated with the content of the LAST_NAME column from the data file and if it's NULL, populated with 'Unknown'

    This is where you can transform the data to be loaded into the destination columns. There are endless combinations and possibilities.

    Tips:

    You don't have to type the expression yourself. You can just right click on the Value column to open a context menu that lets you select any data file column or run variable from a list.

    You can also open the Expression Editor Form by double clicking in the Value column.

  • Comments: Free text comment. It's sometimes useful to explain the content of the column and how it’s generated if the expression is complex.
 

6.4. Scheduling Parameters


For groups and jobs that need to be scheduled, you can define all the scheduling parameters in this section (e.g. run frequency, run time, run days, etc).

Scheduling Parameters
 

This section is identical for all Export / Load / Groups / Jobs.

For details on how to use the scheduling parameters see: Scheduling Parameters.