4 ETL Implementation and Customization

Source-ETL Design Considerations

Keep the following points in mind when designing and writing source-ETL for Oracle Airlines Data Model:

• You can populate the calendar data by using the calendar population scripts provided with Oracle Airlines Data Model and described in Oracle Airlines Data Model Reference.

• Populate the tables in the following order:

  1. Lookup tables

  2. Reference tables

  3. Base tables

• Analyze the tables in one category before loading the tables in the next category (for example, analyze the lookup tables before loading the reference tables). Additionally, you must analyze all of the tables loaded by the source-ETL process before executing the intra-ETL processes).

  See:

  The topic about analyzing tables, indexes and clusters in Oracle Database Administrator's Guide.

ETL Architecture for Oracle Airlines Data Model Source-ETL

ETL first extracts data from the original sources, assures the quality of the data, cleans the data, and makes the data consistent across the original sources. ETL then populates the physical objects with the "clean" data so that query tools, report writers, dashboards and so on can access the data.

The fundamental services upon which data acquisition is constructed are as follows:

• Data sourcing

• Data movement

• Data transformation

• Data loading

From a logical architecture perspective, there are many different ways to configure these building blocks for delivering data acquisition services. The major architectural styles available that cover a range of options to be targeted within a data warehousing architecture include:

• Batch Extract, Transform, and Load and Batch Extract, Load, Transform, Load

  Batch Extract, Transform and Load (ETL) and Batch Extract, Load, Transform, Load (ELTL) are the traditional architecture sin data warehouse implementation. The difference between them is where the transformation proceed in or out database.

• Batch Hybrid Extract, Transform, Load, Transform, Load

  Batch Hybrid Extract, Transform, Load, Transform, Load (ETLTL) is a hybrid strategy. This strategy provides the most flexibility to remove hand coding approaches to transformation design, apply a metadata-driven approach, and still be able to leverage the data processing capabilities of the enterprise warehouse. In this targeted design, the transformation processing is first performed outside the warehouse as a pre-processing step before loading the staging tables, and then further transformation processing is performed within the data warehouse before the final load into the target tables.

• Real-time Extract, Transform, Load

  Real-time Extract, Transform, Load (rETL) is appropriate when service levels for data freshness demand more up-to-date information in the data warehousing environment. In this approach, the OLTP system must actively publish events of interest so that the rETL processes can extract them from a message bus (queue) on a timely basis. A message-based paradigm is used with publish and subscribe message bus structures or point-to-point messaging with reliable queues.

When designing source-ETL for Oracle Airlines Data Model, use the architecture that best meets your business needs.

Creating a Source to Target Mapping Document for the Source-ETL

Before you begin building your extract systems, create a logical data interface document that maps the relationship between original source fields and target destination fields in the tables. This document ties the very beginning of the ETL system to the very end.

Columns in the data mapping document are sometimes combined. For example, the source database, table name, and column name could be combined into a single target column. The information within the concatenated column would be delimited with a period. Regardless of the format, the content of the logical data mapping document has been proven to be the critical element required to sufficiently plan ETL processes.

Designing a Plan for Rectifying Source-ETL Data Quality Problems

Data cleaning consists of all the steps required to clean and validate the data feeding a table and to apply known business rules to make the data consistent. The perspectives of the cleaning and conforming steps are less about the upside potential of the data and more about containment and control.

If there are data quality problems, then build a plan, in agreement with IT and business users, for how to rectify these problems.

Answer the following questions:

• Is data missing?

• Is the data wrong or inconsistent?

• Should the problem be fixed in the source systems?

• Set up the data quality reporting and action program and people responsibility.

Then, set up the following processes and programs:

• Set up a data quality measurement process.

• Set up the data quality reporting and action program and people responsibility.

Designing Source-ETL Workflow and Jobs Control

All data movement among ETL processes are composed of jobs. An ETL workflow executes these jobs in the proper sequence and with regard to the necessary dependencies. General ETL tools, such as Oracle Warehouse Builder, support this kind of workflow, job design, and execution control.

Below are some tips when you design ETL jobs and workflow:

• Use common structure across all jobs (source system to transformer to target data warehouse).

• Have a one-to-one mapping from source to target.

• Define one job per Source table.

• Apply generic job structure and template jobs to allow for rapid development and consistency.

• Use an optimized job design to leverage Oracle load performance based on data volumes.

• Design parameterized job to allow for greater control over job performance and behavior.

• Maximize Jobs parallelism execution.

Designing Source-ETL Exception Handling

Your ETL tool or your developed mapping scripts generate status and error handling tables.

As a general principle, all ETL logs status and errors into a table. You monitor execution status using an ETL tool or by querying this log table directly.

Writing Source-ETL that Loads Efficiently

Whether you are developing mapping scripts and loading into a staging layer or directly into the foundation layer the goal is to get the data into the warehouse in the most expedient manner. In order to achieve good performance during the load you must begin by focusing on where the data to be loaded resides and how you load it into the database. For example, you should not use a serial database link or a single JDBC connection to move large volumes of data. The most common and preferred mechanism for loading large volumes of data is loading from flat files.

The following topics discuss best practices for ensuring your source-ETL loads efficiently:

• Using a Staging Area for Flat Files

• Preparing Raw Data Files for Source-ETL

• Source-ETL Data Loading Options

• Parallel Direct Path Load Source-ETL

• Partition Exchange Load for Oracle Airlines Data Model Source-ETL

Performing an Initial Load of the Foundation Layer

You perform the initial load of the foundation layer using source-ETL that you create. See "ETL for the Foundation Layer of an Oracle Airlines Data Model Warehouse" for more information on creating this ETL.

Performing an Initial Load of the Access Layer

To perform an initial load of access layer of the Oracle Airlines Data Model warehouse (that is, the derived and aggregate tables, materialized views, OLAP cubes, and data mining models) take the following steps:

1. Update the parameters in DWC_ETL_PARAMETER control table in the oadm_sys schema so that the ETL can use this information (that is, the beginning and end date of the ETL period) when loading the data into the access layer.

   For an initial load of an Oracle Airlines Data Model warehouse, specify the values shown in the following table.

   Columns	Value
   PROCESS_NAME	'OADM-INTRA-ETL'
   FROM_DATE_ETL	The beginning date of the ETL period.
   TO_DATE_ETL	The ending date of the ETL period.

   
   

   See:

   Oracle Airlines Data Model Reference for more information on the DWC_ETL_PARAMETER control table.

2. Update the Oracle Airlines Data Model OLAP ETL parameters in DWC_OLAP_ETL_PARM control table in the oadm_sys schema to specify the build method and other build characteristics so that the ETL can use this information when loading the OLAP cube data.

   For an initial load of the analytic workspace, specify values following the guidelines in Table 4-1.

   Table 4-1 Values of Oracle Airlines Data Model OLAP ETL Parameters in the DWC_OLAP_ETL_PARM Table for Initial Load

   Column Name	Value
   PROCESS_NAME	'OADM-INTRA-ETL'
   BUILD_METHOD	C which specifies a complete refresh which clears all dimension values before loading.
   CUBENAME	One of the following values that specifies the cubes you want to build: ALL specifies a build of the cubes in the Oracle Airlines Data Model analytic workspace. cubename[[|cubename]...] specifies one or more cubes to build.
   MAXJOBQUEUES	A decimal value that specifies the number of parallel processes to allocate to this job. (Default value is 4.) The value that you specify varies depending on the setting of the JOB_QUEUE_PROCESSES database initialization parameter.
   CALC_FCST	One of the following values depending on whether you want to calculate forecast cubes: Y specifies calculate forecast cubes. N specifies do not calculate forecast cubes.
   NO_FCST_YRS	If the value for the CALC_FCST column is Y, specify a decimal value that specifies how many years forecast data you want to calculate; otherwise, specify NULL.
   FCST_MTHD	If the value for the CALC_FCST column is Y, then specify AUTO; otherwise, specify NULL.
   FCST_ST_YR	If the value for the CALC_FCST column is Y, then specify value specified as yyyy which is the "start business year" of a historical period; otherwise, specify NULL.
   FCST_END_YR	If the value for the CALC_FCST column is Y, then specify value specified as yyyy which is the "end business year" of a historical period; otherwise, specify NULL.
   OTHER1	Specify NULL.
   OTHER2	Specify NULL.

   
   

3. Execute the intra-ETL in one of the ways described in "Executing the Default Oracle Airlines Data Model Intra-ETL ".

EXEC PKG_INTRA_ETL_PROCESS.RUN( )

Refreshing the Foundation Layer of Oracle Airlines Data Model Warehouse

You refresh the foundation layer using source-ETL scripts that you wrote using Oracle Warehouse Builder or another ETL tool. For more information on creating source-ETL, see "ETL for the Foundation Layer of an Oracle Airlines Data Model Warehouse".

Refreshing the Access Layer of an Oracle Airlines Data Model Warehouse

Refreshing the access layer of an Oracle Airlines Data Model is a multi-step process. You can do a full incremental load of the access layer all at one time by executing the PKG_INTRA_ETL_PROCESS package as described in"Executing the Default Oracle Airlines Data Model Intra-ETL ", or you can refresh the data sequentially:

1. Refreshing the Access Layer Relational Tables in the Oracle Airlines Data Model

2. Refreshing Oracle Airlines Data Model OLAP Cubes

3. Refreshing Oracle Airlines Data Model Data Mining Models

In either case, you can manage errors during the execution of the intra-ETL as described in "Managing Errors During Oracle Airlines Data Model Intra-ETL Execution".

Monitoring the Execution of the Intra-ETL Process

Two oadm_sys schema control tables, DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY, monitor the execution of the intra-ETL process. These tables are documented in Oracle Airlines Data Model Reference.

Each normal run of the PKG_INTRA_ETL_PROCESS package (as opposed to an error-recovery run) performs the following steps:

1. Inserts a record into the DWC_INTRA_ETL_PROCESS table with a monotonically increasing system generated unique process key, SYSDATE as process start time, RUNNING as the process status, input date range in the fields FROM_DATE_ETL and TO_DATE_ETL.

2. Invokes each of the individual PKG_INTRA_ETL_PROCESS procedures in the appropriate order of dependency. Before the invocation of each program, the procedure inserts a record into the Intra-ETL Activity detail table DWC_INTRA_ETL_ACTIVITY with a system generated unique activity key, the process key value corresponding to the Intra-ETL process, individual program name as the Activity Name, a suitable activity description, SYSDATE as activity start time, RUNNING as the activity status.

3. Updates the corresponding record in the DWC_INTRA_ETL_ACTIVITY table for the activity end time and activity status after the completion of each individual ETL program (either successfully or with errors. For successful completion of the activity, the procedure updates the status as 'COMPLETED-SUCCESS'. If an error occurs, the procedure updates the activity status as 'COMPLETED-ERROR', and also updates the corresponding error detail in the ERROR_DTL column.

4. Updates the record corresponding to the process in the DWC_INTRA_ETL PROCESS table for the process end time and status, after the completion of all individual intra-ETL programs. If all the individual programs succeed, the procedure updates the status to 'COMPLETED-SUCCESS', otherwise it updates the status to 'COMPLETED-ERROR'.

You can monitor the execution state of the intra-ETL, including current process progress, time taken by individual programs, or the complete process, by viewing the contents of the DWC_INTRA_ETL_PROCESS and DWC_INTRA_ETL_ACTIVITY tables corresponding to the maximum process key. Monitoring can be done both during and after the execution of the intra-ETL procedure.

Recovering an Intra ETL Process

To recover run of the PKG_INTRA_ETL_PROCESS package:

1. Identify the errors by looking at the corresponding error details that are tracked against the individual programs in the DWC_INTRA_ETL_ACTIVITY table.

2. Correct the causes of the errors.

3. Re-execute the PKG_INTRA_ETL_PROCESS package.

The procedures in the PKG_INTRA_ETL_PROCESS package identify whether it is a normal run or recovery run by referring the DWC_INTRA_ETL_ACTIVITY table. During a recovery run, PKG_INTRA_ETL_PROCESS executes only the procedures needed for recovery. For example, in the case of a derived population error as a part of the previous run, this recovery run executes the individual derived population programs which produced errors in the previous run. After their successful completion, the run executes the aggregate population programs and materialized view refresh in the appropriate order.

In this way, the intra-ETL error recovery is almost transparent, without involving the data warehouse or ETL administrator. The administrator must only correct the causes of the errors and re-invoke the PKG_INTRA_ETL_PROCESS package. PKG_INTRA_ETL_PROCESS identifies and executes the programs that generated errors.

Troubleshooting Intra-ETL Performance

To troubleshoot the performance of the intra-ETL:

• Check the execution plan as described in "Checking the Execution Plan".

• Monitor parallel DML executions as described in "Monitoring PARALLEL DML Executions".

• Check that data mining models were created correctly as described in "Troubleshooting Data Mining Model Creation".

column name format a50
column value format 999,999
SELECT NAME, VALUE 
FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
  OR UPPER (NAME) LIKE '%PARALLELIZED%'
  OR UPPER (NAME) LIKE '%PX%'
;

set line 160
col ACTIVITY_NAME format a30
col ACTIVITY_STATUS format a20
col error_dtl format a80
select activity_name, activity_status,  error_dtl from dwc_intra_etl_activity;