Extract, Transform, And Load
Also known as ETL. The ETL processes extract data from various data sources; transform the data into a format and structure that is suitable for subsequent business purposes; and load the data into a target data store (other database, data mart, or data warehouse).
Glossary
Job History and Status
Contents
You can view information about job history and status using either of the following methods:
- Voice of Process—This page describes how you can use Voice of Process to find information about the status and processing history of Genesys Info Mart jobs.
- Genesys Info Mart Manager—For information about viewing the status and history of jobs using Genesys Info Mart Manager, see Using Genesys Info Mart Manager.
About Voice of Process
Use Voice of Process functionality to access information related to the processing history of Genesys Info Mart jobs, including to:
- Quickly check the state of Genesys Info Mart jobs.
- Track the data extraction progress of each extraction job cycle.
- Track the data transformation progress of each transformation cycle.
- Identify the job that inserted a particular row of data or made the most recent update to it.
The Genesys Info Mart database provides several service control tables, which in addition to existing administrative views, store the ETL processing history details.
Checking the state of ETL processing
The ADMIN_ETL_JOB_HISTORY administrative view is updated each time a job is executed. By monitoring this view, administrators can quickly assess the current state of all jobs.
The ADMIN_ETL_JOB_HISTORY administrative view provides the following information related to the jobs:
- Name of the job
- Time of execution
- Time of completion
- Duration
- Status (success or failure
Tracking the progress of data extraction
A row is added to the ADMIN_EXTRACT_HISTORY administrative view when Job_ExtractICON successfully completes extracting a source data table. Administrators can closely track the progress of the data extract cycle by monitoring this view.
The ADMIN_EXTRACT_HISTORY provides the following information related to the data extraction job, including:
- Name of the source table
- Number of records extracted
- Start and end time of each extraction job cycle
Tracking the progress of data transformation
Administrators can closely track the progress of the data transform cycle by viewing the contents of the CTL_TRANSFORM_HISTORY table.
The CTL_TRANSFORM_HISTORY provides the following information related to the data transformation job:
- Name of the destination table
- Number of records transformed
- Start and end time of each transformation job cycle
Identifying the job that inserted or updated data
In Info Mart’s dimensional model, every table that receives inserts has a CREATE_AUDIT_KEY service field. Every table that can also receive updates has an additional UPDATE_AUDIT_KEY service field. Both these fields contain a reference to a row in the CTL_AUDIT_LOG table.
By linking fact data to the CTL_AUDIT_LOG table, administrators can determine:
- The identifier of the job that inserted/updated the data. This information can be used to quickly locate relevant portions of the log file when troubleshooting data quality issues.
- The total number of records processed by the job.
- The range of partitions (when using partitioning) that received the new/updated data.
Additionally, fields CREATE_AUDIT_KEY and UPDATE_AUDIT_KEY can be used to identify newly arriving data for subsequent aggregation or other processing. For more information about any of the Info Mart tables, views, and fields, see the Physical Data Model for your RDBMS: