The Easy-to-use Extract, Transformation and Load (or EZETL) tools are a separate set of software that provide an exceptionally easy ability to move data from one place to another, whether it be for database upgrade, database conversion or routine data processing. Information is stored in the EZ Data Dictionary® database to define the databases involved and any mapping between them and data transformations to occur during the process. The EZ Data Dictionary® software provides the ability to define, view, modify and manipulate this information as required for the EZETL tools.
This combination provides a powerful system where data mapping information is easily modified and the data transfer process is relatively automated. Specific benefits include:
1. The interfaces can be developed very quickly.
2. An audit trail from the conversion is easy to follow.
3. As new rules and data are discovered, modification of the interfaces is very easy.
An Interface is defined describing how to map, transform and transfer data from one database to another. The database containing the original data is called the Source Database, and the destination for the mapped and transformed data is the Target Database. Once the interface has been fully defined, the EZETL tools can perform the interface, transforming and moving the data as specified.
The EZETL tools provide:
1. A mechanism to specify the Source database and Target database with associated user id and password so that the passwords are accessible but encrypted. This information data is generated to the ETL.ini file.
2. A standard program (ETL_Standard.exe) that will read this ETL.ini file, verify access the Source database and Target database, extract the data from the Source database, transform the data according to the mapping information defined, write out an ASCII file containing the transformed data, and then load that data to the desired location in the Target Database.
3. A log file that is generated from the ETL_Standard.exe program as an audit trail.
4. Additional housekeeping tasks performed by the ETL_Standard.exe program for an audit trail.
5. Additional standardized programs, such as SQL_Execute.exe which will execute an SQL statement and produce a log file.
The EZ Data Dictionary® Database provides a repository for the information required by the EZETL tools. This includes any databases involved (they become EZDD included databases, for example the Source and Target databases), the mapping to be performed, and any data transformations to take place during the process. . The EZDD_Admin software provides the ability to define, view, modify and manipulate this information as required for the EZETL tools to accomplish the desired results.
In EZDD_Admin, the Maintain_Interface_Tables option on the main screen will take the user to the module of options related to the tables defined for use by the EZETL tools. These screens can be seen in the relevant sections of the User Manual, and understood more comprehensively with the following definitions:
Interface: Defines a Source Database, Target Database and Target Table for an Interface, and specifies the SQL to be used to extract the original data from the Source Database.
Interface_Columns: The set of mapping information for all columns in the Target Table
Mapping: Specifying what piece of data from the Source database is to populate which column in the Target Table
Source_Database: The database with the originating data
Target_Database: The destination database where the transformed data is to be placed
Target_Table: The table in the destination database to be populated by the transformed data
Translation: Any transformations to be applied to any data elements during the transfer process
The EZ Data Dictionary® User Manual documents the screens used to define, view, modify and manipulate the interface, mapping and transformation definitions for the EZETL process. Further details and information on the EZETL process are documented in the EZETL User Manual.