 |
|
This page gives the definition of some terms used with DMT.
See DMT client
Data Files are files containing data in various formats. DMT uses data files for 2 purposes:
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:
| Format | DMT Version | Description |
|---|---|---|
| CSV | 1.1.5 | Text file where each field is separated by a coma |
| Tab Delimited | 2.0 | Text file where each field is separated by a Tab character |
| Delimited Text | 1.0 | Text file where each field is separated by a delimiter string. The delimiter string can be any string of your choice. |
| HTML | 1.0 | Text 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> |
| Excel | 1.0 | Excel 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 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 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:
DMT has two components:
The DMT Client is the part of DMT containing the user interface that allows you configure and manage exports and loads.
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:
Note: You can use the Expression Editor Form to write and test DMT Expressions.
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.
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.
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:
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.
See DMT Expression.
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.
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 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.
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 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)
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.
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:
Note: Only one type of Pre or Post Process can be run at a time.
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:
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 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:
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 Variable | Description |
|---|---|
| :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_NAME | Name of the group or job |
| :GJ_TYPE_EL | Type of the group or job (Export or Load) |
| :GJ_TYPE_GJ | Type of the group or job (Group or Job) |
| :GROUP_TYPE | Type of the group (Schedule, Data, Logical, Multi Table). Empty for jobs |
| :DATAFILE_NAME | Name of the data file used by the group or job. Empty if no data file is used (e.g. for a logical group) |
| :DATAFILE_TYPE | Type 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_CONTEXT | Run context in which the group or job is run (UI or Scheduler) |
| :RUN_NUMBER | Run number of the current run for the group or job |
| :LINE_NUM | Current line number being exported or loaded |
| :RUN_BEGIN_DT | Begin date of the current run |
| :RUN_END_DT | End date of the current run |
| :LAST_RUN_BEGIN_DT | Begin date of the prior run |
| :LAST_RUN_END_DT | End date of the prior run |
| :RUN_RESULT | Run result of the current run (OK, Warning, Error) |
| :NUM_ROWS | Number of lines exported or loaded |
| :NUM_ERRORS | Number of errors during the run |
| :NUM_WARNINGS | Number of warning during the run |
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:
For more details on Scheduling Parameters, please refer to DMT Tour Part 5.
See DMT Scheduler
See DMT Service
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.
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.).
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.
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.
|
|
|