Definitions

This page gives the definition of some terms used with DMT.

A

B

C

 
Client

See DMT client

D

 
Data File

Data Files are files containing data in various formats. DMT uses data files for 2 purposes:

  • Storing exported data: When an export is run, the exported data is stored in a data file
  • Loading data: When a load is run, the data loaded is read from a data file.

Note that DMT can handle data files even if they haven't been generated by DMT, as long as they are in a supported format.

The supported data file formats are as follow:

FormatDMT VersionDescription
CSV1.1.5Text file where each field is separated by a coma
Tab Delimited2.0Text file where each field is separated by a Tab character
Delimited Text1.0Text file where each field is separated by a delimiter string.
The delimiter string can be any string of your choice.
HTML1.0Text file where data is placed in an HTML table. e.g.
<table>
   <tr><th>First Name</th><th>Last Name</th></tr>
   <tr><td>Joe</td><td>Blogs</td></tr>
   <tr><td>Paul</td><td>Smith</td></tr>
</table>
Excel1.0Excel files are read by DMT version 1.0.
To produce data files that can be read by Excel, you can generate CSV with the Text format.
Fixed Length-Fixed length files will be supported in a later version of DMT
 
Data File Columns

Data Files contain the data exported by DMT or the data to be loaded by DMT. They contain rows of data and each row is composed of one or more columns just like a database table.

Load jobs are designed to load data files into destination data sources. When running a load job, Data File Columns are used to populate destination table columns. Data File columns can be combined in expressions to create complex values.

Data file columns can be easily identified by their $DFC_ prefix.
They typically look like this: $DFC_[Name of the column]

 
DMT

DMT stands for Data Moving Tool. It's a powerful ETL that quickly moves your data from where it is to where it needs to be. Its key features are:

  • Export, Transform and Load data from and to any ODBC compliant data source.
  • Add logic and reporting via Pre-Conditions and Pre/Post Processes
  • Read and produce data files in various formats (CSV, Text, Excel, HTML, etc)

DMT has two components:

  • The DMT client: User interface component that lets you configure and manage exports and loads
  • The DMT service: Background component that allows you to schedule the run of exports and loads
 
DMT Client

The DMT Client is the part of DMT containing the user interface that allows you configure and manage exports and loads.

 
DMT Expression

Expressions are used for load jobs in the Load Mapping area in the load condition field and in the load mapping's grid value column. There are 2 types of expressions:

  • Database or ODBC specific expression: An expression written with database or ODBC specific syntax. These expressions are not evaluated by DMT and are sent as is to the database for evaluation. You can use database or ODBC specific expressions in the Value column when you uncheck the Eval column next to it. They are less portable but faster and in some cases more flexible than DMT expressions.

    Examples of database specific expressions are:
    • SUBSTR('ABCD', 2) for an oracle database
    • LEFT('ABCD', 2) for an SQL Server database

  • DMT expression: An expression written with DMT specific syntax. These expressions are evaluated by DMT before being used or their result sent as to the database. You can use database specific expressions in the load condition field or in the Value column when you check the Eval column next to it. They are more portable but slower than database or ODBC expressions.

    Examples of DMT expressions are:
    • CONCAT('ABCD', 'EF', 'GHIJ')
    • SPLIT(0, 'Joe Bloggs', ' ')

Note: You can use the Expression Editor Form to write and test DMT Expressions.

 
DMT Scheduler

The DMT scheduler is a DMT component that runs exports and loads according to their scheduling parameters. Each export and load groups or jobs have scheduling parameters that dictate when they should be run by the scheduler (e.g. every hour, every day at 12:00, every Tuesday at 7:00, etc.). When the DMT scheduler is started, all active exports and loads are run at the date and time designated by their scheduling parameters.

The DMT scheduler is available in the Professional and Enterprise Editions.

Note: Do not confuse the DMT service and the DMT scheduler. The DMT service is a windows service that contains the DMT scheduler. The DMT service must be started for the DMT scheduler to start, but starting the DMT service doesn't automatically start the DMT scheduler. The DMT service is started from the control panel (like any standard windows service) and the DMT scheduler is started from the Scheduling console Tab in the DMT Client.

 
DMT Service

The DMT Service is the part of DMT containing components running in the background (e.g. the DMT scheduler). The DMT Service is a windows service running in the background.

Note: Do not confuse the DMT service and the DMT scheduler. The DMT service is a windows service that contains the DMT scheduler. The DMT service must be started for the DMT scheduler to start, but starting the DMT service doesn't automatically start the DMT scheduler. The DMT service is started from the control panel (like any standard windows service) and the DMT scheduler is started from the Scheduling console Tab in the DMT Client.

E

 
ETL

ETL stands for Extract Transform and Load. It’s a type data processing and also the name given to applications performing it.

The ETL data processing consists of 3 stages:

  • Extract: The first stage extracts data (exports) from the source system(s). The source systems are often databases or files. The exported data is usually stored into databases or files.
  • Transform: The second stage applies a series of rules or functions to the exported data to transform it and prepare it for loading into the target system.
  • Load: The last stage loads the exported and transformed data into the target system (usually a database).

Traditionally, ETLs have been associated with large volumes of data required by data warehousing, and thus they're often heavy, complex and expensive.

Today, with the multiplication of data sources and the need to exchange information between them, there is a need for smaller and more flexible tools. Data Moving Tool is an ETL designed to handle smaller volumes of data in a much more flexible way.

 
Expression

See DMT Expression.

F

 
Foreign Key (FK)

A Foreign Key is the name given to a column in a database row containing the ID of another row in the database (in the same or another table). It is used to create a logical link between rows, for instance an order table storing orders would have a column containing the ID of the ordered product.

DMT can handle User Keys and Foreign Keys with Multi Table Groups.

G

 
Group

Groups in DMT are a way to organize jobs by grouping them together. A group contains either Export or Load Jobs. You can think of them as folders containing jobs.

Groups and Jobs

Groups and Jobs look like folders and files in windows explorer.

Job 1 and Job 2 are Standalone jobs

Group A contains 4 Jobs (Job A1, A2, A3, A4)

Group B doesn’t contain any Jobs (no + or - sign on its left)

Group C contains some Jobs but is collapsed (+ sign on its left)


There are several types of groups:

Logical Group
(Export or Load)
A logical group is simply a logical entity containing jobs. It has no functionalities (i.e. it cannot be run)
Schedule Group
(Export or Load)
A group in which all the jobs are run sequentially. It has scheduling parameters, run pre-conditions and pre or post processes to control its run. Jobs within a schedule group don’t run independently from their group (unless they are run manually from the UI).
Data Group
(Export or Load)
A group in which all the jobs populate or read the same data file. It has no scheduling parameters to control its run, instead jobs within the group run independently using their own scheduling parameters.
Multi Table Group
(Load only)
A group in which all the jobs within the group read the same data file and each line from the data file can populate multiple tables. It can optionally be used to obtain a user key (UK) from a master table and use it as a foreign key (FK) in child tables. A multi table group is automatically a schedule and a data group.

Note: A group can be both a schedule and a data group. In that case, it combines the two functionalities: jobs within the group run sequentially when the group is run and they populate or read the same data file.

H

I

J

 
Job

A Job in DMT represents the action of exporting or loading data. It contains all the information required to run the export or load (e.g. Job Name, ODBC connection, Data File info, Run Pre-Conditions, Scheduling Parameters, etc ). A job can be Standalone or part of a Group. Jobs and groups are shown in the left side list of the Export and Load tabs.

Groups and Jobs

Groups and Jobs look like folders and files in windows explorer.

Job 1 and Job 2 are Standalone jobs

Group A contains 4 Jobs (Job A1, A2, A3, A4)

Group B doesn’t contain any Jobs (no + or - sign on its left)

Group C contains some Jobs but is collapsed (+ sign on its left)


K

L

M

N

O

 
ODBC

DMT supports almost any database through its use of ODBC.

ODBC stands for Open Database Connectivity. It’s a standard software interface for accessing databases independently of programming languages, database systems, and operating systems.

ODBC drivers are widely available for virtually all databases. Recent database systems like Oracle, SQL Server, Sybase, IBM DB2 / Informix, MySQL, MS Access and many more, but also older ones like Visual FoxPro, Paradox, Dbase, etc.

DMT uses ODBC to access data, making it very flexible and capable of accessing virtually any data source.

P

 
Pre-Conditions

See Run Pre-Conditions

 
Pre and Post Process

Pre and Post Processes in DMT are external processes that can be executed before or after the run of exports and loads. They are a powerful way to add functionalities to exports and loads.

There are 2 types of Pre and Post Processes:

  • Run SQL statement: Executes an SQL statement against any ODBC database
  • Run Executable: Launch an executable file

Note: Only one type of Pre or Post Process can be run at a time.

Q

R

 
Run Pre-Conditions

Run Pre-Conditions in DMT allow you to trigger the run of an export or load only if specified conditions are met. They are a powerful way to add logic to exports and loads

There are 2 types of Pre-Conditions:

  • Query Pre-Condition: Executes an SQL query against any ODBC database. You can choose to satisfy the pre-condition whether the query returns results or not.
  • File Pre-Condition: Checks the existence of a file and optionally its content. You can choose to satisfy the pre-condition whether the file exists or not and optionally whether its content matches the one you specified.

Note: An export or load can use both types of pre-conditions together (i.e. have 2 pre-conditions, one of type Query and one of type File).

 
Run Variables

Run Variables are a very powerful way to customize exports and loads in DMT. Each group or job (export or load), has a predefined set of run variables. Each time a group or job is run, its run variables are populated with runtime information (e.g. the group or job name, the data source name, the number of rows exported or loaded, etc.). These variables can then be used during the run (Load, Pre-Conditions, Pre and Post Processes) to access this runtime information.

One of the many uses of Run Variables is to use them in Pre and Post Processes to report on the run of groups and jobs. In a Post Process, you can for instance run the following SQL statement:

INSERT INTO RUN_LOG
    (NAME, BEGIN_DT, END_DT)
VALUES
    (:GJ_NAME, :RUN_BEGIN_DT, :RUN_END_DT)

This will insert in the RUN_LOG table the name of the group or job that ran, and the begin and end time the run took place.

Note: Run Variables are identified with the semi column prefix (e.g. :RUN_NUMBER)

The list of available Run Variables is:

Run VariableDescription
:CUSTOM1
:CUSTOM2
:CUSTOM3
These are special writable run variables in which you can place any value of your choice.

They are the only writable run variables, all the other ones are read only.

:GJ_NAMEName of the group or job
:GJ_TYPE_ELType of the group or job (Export or Load)
:GJ_TYPE_GJType of the group or job (Group or Job)
:GROUP_TYPEType of the group (Schedule, Data, Logical, Multi Table).

Empty for jobs

:DATAFILE_NAMEName of the data file used by the group or job.

Empty if no data file is used (e.g. for a logical group)

:DATAFILE_TYPEType of the data file used by the group or job (e.g. Text, html, Excel, etc.).

Empty if no data file is used (e.g. for a logical group)

:RUN_CONTEXTRun context in which the group or job is run (UI or Scheduler)
:RUN_NUMBERRun number of the current run for the group or job
:LINE_NUMCurrent line number being exported or loaded
:RUN_BEGIN_DTBegin date of the current run
:RUN_END_DTEnd date of the current run
:LAST_RUN_BEGIN_DTBegin date of the prior run
:LAST_RUN_END_DTEnd date of the prior run
:RUN_RESULTRun result of the current run (OK, Warning, Error)
:NUM_ROWSNumber of lines exported or loaded
:NUM_ERRORSNumber of errors during the run
:NUM_WARNINGSNumber of warning during the run
S

 
Scheduling Parameters

Scheduling Parameters are a set of parameters for groups and jobs that control the way they are automatically run by the DMT scheduler.

You can choose between 7 different scheduling frequencies for groups and jobs:

  • Manual: Can only be run manually (i.e. never scheduled).
  • Continually: Run continually at specific interval.
  • Daily: Run daily or every X days.
  • Weekly: Run weekly or every X weeks.
  • Monthly1: Run monthly or every X months.
  • Monthly2: Run monthly with more precise tuning (e.g. every last Friday of the month).
  • After Export: For load groups and jobs to run after an export.

For more details on Scheduling Parameters, please refer to DMT Tour Part 5.

 
Scheduler

See DMT Scheduler

 
Service

See DMT Service

 
Standalone Job

A Standalone Job is a job that doesn’t belong to a group. A Standalone Job can be an Export job or a Load job.

See also: Job
 
Stored Procedure

A stored procedure is a procedure (or function) stored in a database that executes some tasks. You can call stored procedures in DMT from anywhere you can enter SQL code (e.g. Run Pre-Conditions, Pre and Post Processes, etc.).

T

U

 
User Key (UK)

A User Key is the name given to a column or a group of columns in a database row used to identify a record. For instance the ID column of a product table can contain the unique ID of each product. This makes the ID column a user key of the product table.

DMT can handle User Keys and Foreign Keys with Multi Table Groups.

V

W

 
Windows Service

A Windows Service is a special windows program that runs in the background without user interactions. One of the advantages of a windows service is that it can run on a machine even if no user is logged on.

The DMT Service that contains the DMT Scheduler is a Windows Service.

X

Y

Z