The Maintain Interface Tables option gives the user access to the tables relevant to the ICC EZETL Tools. The information used by the EZETL tools is stored in enhanced tables in the EZDD Database. Selecting the Maintain Interface Tables option gives the user access to these enhanced tables as well as to supplemental functions that are integral to the EZETL Tools process.
The ICC EZETL Tools are a powerful capability that provide to the user the ability to transform and transfer data from one database to another. The EZETL Tools define the databases involved, the desired mapping, any useful data transformations, and offer supplemental functions to further facilitate the data transfer. The database structures and metadata for the databases involved are defined by included databases stored and accessed by EZ Data Dictionary®. The mappings used by the EZETL tools are stored in enhanced tables in the EZDD Database that refer to the included databases involved.
A basic concept in the design of the EZETL Tools is that of a Source Database and a Target Database, and these terms are encountered throughout the screens within the Maintain Interface Tables section of EZDD_Admin. The Source Database is the database from which information will be originating, from which information is mapped. The Target Database is the destination database to which information will be transferring and to which the information is mapped.
In the enhanced tables used by the EZETL Tools, the Interface defines which database is the Source database, which database is the Target database, and specifies the SQL to extract the required data from the Source database. The Interface Columns define the mapping of this extracted data to the Target database, and may include Translations where an item of data can index into a table of values to be applied during the mapping.
Supplemental functions include creating an Oracle control file that defines how the transformed data is imported into an Oracle database, and creating an ASCII file for SSIS that helps define how the transformed data is imported to a SQL Server database.
This option gives the user the ability to define a new Interface. The definition of an Interface is started on this screen by specifying a unique Interface Identifier, selecting which database is the Source database and which database is the Target database, specifying the table in the Target database where the transformed data will go and specifying a location for the Oracle control file. By default creating an interface will generate an Oracle control file defining how to import the transformed data into the destination table in an Oracle database. If the destination database is in another DBMS this file will still be generated but can be ignored.
The user can abort this process by selecting the Go Back or Back to Admin buttons. But when the interface is defined as desired and the Create button is selected, the interface will be created, the Oracle control file will be created and its location reflected on the screen, and the user will be directed to the Interface Maintenance screen.
When the definition of a new Interface is successfully started on the Create New Interface screen, the will automatically be directed to the Interface Maintenance screen to continue the definition of the Interface. The Interface Maintenance screen can also be selected from the Maintain Interface Tables screen for the user to go directly to an existing Interface for editing.
At a minimum, this screen will show the Interface Identifier, Source Database, Target Database and Target Table Name of the Interface, as well as the Connect String for the Source database. Beyond this, the values displayed on this screen will vary depending on whether this is an existing Interface, or one in the process of being defined.
The Count SQL will be displayed if defined, and is applied against the Source database to approximate the number of records retrieved by the Interface SQL. The Execute SQL button under the Count SQL will execute it against the Source database and display the results in the bottom portion of the screen. This SQL is informational only and if the Count SQL is left blank the EZETL software will default to an approximation of two records.
The Interface SQL is critical as the definition of the specific data to extract from the Source database for transformation, and will be displayed if defined. The Execute SQL button below this SQL will execute it against the Source Database and display the results in the bottom portion of the screen. This is a handy feature to verify that the SQL functions as desired.
Relevant to executing either SQL, the Connect String for the Source database is displayed for verification, and spaces are available to enter the User Code and Password for the Source database if required by security settings in the Source database.
Changes can be made to the values for the Source database, the Count SQL or the Interface SQL for the specified Interface and the Update Interface button will store the changes in the database tables.
An existing Interface can be deleted with the Delete Interface button. Navigation to the Maintain Interface Columns, Create New Interface, or Create Interface Like functions can be accomplished with the corresponding buttons. The Create Like function will begin definition of a new Interface using all the information for the Interface currently displayed on the Interface Maintenance screen. And the user can return to the Maintain Interface Tables screen with the Go Back button.
The Interface_Columns_Maintenance screen is used to map data to the destination table as defined by an Interface. This screen will show the Interface Identifier, the Source and Target database and the Target Table Name. Below this will be a grid of all the columns in the destination table, and the associated columns that can be populated in the extended table in the EZDD Database. At a minimum, each required destination data column should be mapped.
Mapping of the data is accomplished by valuing the Source_Table_Name and the Source_Column_Name for each required data element to be transformed. When the user clicks in the Source_Table_Name column, EZDD will automatically bring up a drop down menu of the tables in the Source Database from which to choose the originating data element. Once the table is selected, clicking in the Source_Column_Name column will bring up a drop down menu of all the columns in the selected table from which to choose the specific originating data element.
Each element from the Source Database that is mapped must appear in the Interface_SQL for the Interface being mapped in order to be accessible when the EZETL Tools execute.
In some cases the Source Database may contain a code that is being retrieved, but the desired value in the Target Database is a value derived from a lookup table, using the code as an index into the table. This capability is presented through the Translations capability in EZ Data Dictionary®, and the lookup table will be selected in the Translation_Identifier column of this mapping screen. When the user clicks in this column, a drop down menu will appear showing all the currently defined translations in the EZDD database. If the desired translation does not exist, the user can click on the Translations Maintenance button and choose to define a new translation.
A few other capabilities are offered from the Interface_Columns_Maintenance screen. Synch with DB will synchronize the mapping shown with the current version of the destination table in the Target Database. All mapping values will be retained, but any structure changes that have taken place in this table will be reflected in the columns for the mapping. This is especially handy if the structure for this table in the Target Database has been updated. This function updates the mapping to be consistent with the changes. Once updated, the user may want to review the changes for any additional mapping to be accomplished relevant to the changes.
Copy To Clipboard will put a copy of the mapping grid onto the clipboard for documentation or review purposes. And Interface_Maintenance will take the user back to the Interface_Maintenance screen so the SQL can be edited to ensure consistency with the columns used in the mapping.
The Translations_Maintenance screen allows the user access to edit an existing Translation used in the mapping of the Interface Columns for an Interface. The user can select the desired translation from the drop down menu for Translation_Identifier and the database used in the translation will be displayed, as well as the detailed translation information that can be edited.
The bottom of the display shows the connect string for the database used for the translation, and has places to enter the User Code and Password for this database. If the database has a User Code and Password associated with it, the user must enter these correctly in order to have the Execute SQL button work. When the user has the appropriate permissions to view the data in the underlying database, the Execute SQL button will execute the translations SQL against the specified database and show the results at the bottom of the screen.
Once the edits are accomplished as desired, the Update button will update the EZDD database with any changes that are made.
The Delete Translation button will delete the translation currently showing on this screen. The Interface Columns Maint button will take the user to that screen (potentially to continue with mapping the columns that may use this Translation). And the Add New Translation button will take the user to a screen for defining a new translation.
The Add New Translations screen enables the user to define a new translation to be used during the transformation of data from the Source Database to the Target Database. A translation uses an originating value to index into a table and emerge with a desired value. To define a translation in EZDD, the user will specify a unique Translations Identifier, and then the database where the table exists that will be used in this translation. This might be the Source Database, the Target Database, or it might be a created table that the user has put into the EZDD Database itself. Which type of database is being used is specified in Database Type.
The Translation Description is an optional value that can be used for comments or details about this Translation.
The SQL String is the critical element of the Translation that defines how to index into the table and retrieve the values.
The bottom of the display shows the connect string for the database being used for the translation, and has places to enter the User Code and Password for this database. If the database has a User Code and Password associated with it, the user must enter these correctly in order to have the Execute SQL button work. When the user has the appropriate permissions to view the data in the underlying database, the Execute SQL button will execute the Translations SQL against the specified database and show the results at the bottom of the screen.
When the Translations is adequately defined, the Add Translation button will add the information to the EZDD Database.
By default, when EZDD_Admin creates a new Interface, an Oracle Control File (*.cntl) is created to spcify how the data transformed by the Interface is to be updated to a table in an Oracle control file. If the Interface has been modified, for example by doing a Synch With DB function on the Interface Columns Maintenance screen to update the destination table structure), it may be necessary to regenerate the control file, and this function allows the user to do that.
On this screen, the user selects the Interface Identifier, and the Source Database, Target Database and Target Table Name display for verification. The location for the resulting control file displays and can be modified. The Create File button will generate the new Oracle Control File to the location specified.
If the Target Database is an SQL Server database and not an Oracle database, the user will need to create and SSIS project to define how the transformed data is to be imported into the target table. To aid in the definition of the SSIS project, this function will create an ASCII file with the structure of the target table.
On this screen, the user selects the Target Database and the Target Table to process. The location for the resulting ASCII file displays and can be modified. The Create File button will generate the ASCII file to the location specified.