4 ETL Implementation and Customization

Source-ETL Design Considerations

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

• You can populate the calendar data by using the calendar population scripts provided with Oracle Communications Data Model and described in Oracle Communications 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 reference tables before loading the lookup 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 Communications 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's in a 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 Communications 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.

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 Communications Data Model Source-ETL

Executing the Default Oracle Communications Data Model Intra-ETL

You can execute the intra-ETL packages provided with Oracle Communications Data Model in the following ways:

• As a Workflow within Oracle Warehouse Builder as described in "Executing the INTRA_ETL_FLW Workflow from Oracle Warehouse Builder".

• Without using Oracle Warehouse Builder Workflow as described in "Manually Executing the ocdm_execute_wf.sh Script".

In either case, you can execute the intra-ETL explicitly or invoke its execution in some other program or process (for example, the source-ETL process after its successful execution) or through a predefined schedule (for example, using Oracle Job Scheduling feature and so on).

Refreshing Oracle Communications Data Model Derived and Aggregate Tables

Refreshing the relational tables and views in an Oracle Communications Data Model is a multi-step process:

1. Refresh the foundation layer of the Oracle Communications Data Model warehouse (that is, the reference, lookup, and base tables) with OLTP data by executing the source-ETL that you have written.

2. Update the parameters of the DWC_ETL_PARAMETER control table in the ocdm_sys schema. For an incremental load of an Oracle Communications Data Model warehouse, specify the values shown in the following table (that is, the beginning and end date of the ETL period).

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

   
   

   See:

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

3. Refresh the derived tables and aggregate tables which are materialized views in Oracle Communications Data Model by executing the DRVD_FLOW and AGGR_FLOW subprocess of the INTRA_ETL_FLW process flow. See "Executing the Default Oracle Communications Data Model Intra-ETL " for more information.

Refreshing Oracle Communications Data Model OLAP Cubes

On a scheduled basis you must update the OLAP cube data with the relational data that has been added to the Oracle Communications Data Model data warehouse since the initial load of the OLAP cubes.

You can execute the Oracle Communications Data Model ETL to update the OLAP cubes in the following ways

• Refresh all of the data in the warehouse by executing the Oracle Warehouse Builder Workflow INTRA_ETL_FLW in one of the ways that are described in "Executing the Default Oracle Communications Data Model Intra-ETL ".

  The OLAP Cubes are populated through OLAP_MAP which is a part of Oracle Communications Data Model intra-ETL main workflow INTRA_ETL_FLW.

• Refresh only the OLAP cube data by executing the OLAP_MAP Oracle Warehouse Builder mapping in the Oracle Warehouse Builder control center.

  Note:

  You must refresh the corresponding materialized view of the OLAP cubes you are refreshing before you execute OLAP_MAP. (For the mapping between OLAP cube and materialized views, refer to Oracle Communications Data Model Reference.

Take these steps to perform an incremental load of the analytic workspace that is part of the Oracle Communications Data Model warehouse:

1. Update the aggregate tables which are materialized views in Oracle Communications Data Model. See "Refreshing Oracle Communications Data Model Derived and Aggregate Tables" for more information.

2. Execute the intra-ETL to load the cube data in one of the ways described in "Executing the Default Oracle Communications Data Model Intra-ETL ".

3. If necessary, to recover from errors during the execution of OLAP_MAP take the following steps.

   1. Change the value of the BUILD_METHOD column of the ocdm_sys.DWC_OLAP_ETL_PARAMETER table to "C".

   2. In Oracle Warehouse Builder, rerun the OLAP_MAP map.

Refreshing Oracle Communications Data Model Data Mining Models

The MINING_FLW sub-process flow of the INTRA_ETL_FLW process flow triggers the data mining model refreshment. After the initial load of the warehouse, it is recommended to refresh the data mining models monthly. Refreshing the data models is integrated into the MINING_FLW sub-process flow. You can also manually refresh the data models.

The way you refresh a data mining model varies depending on whether you want to refresh all of the models or only one model:

• To manually refresh all mining models, call the following procedure.

  ocdm_mining.PKG_ocdm_mining.REFRESH_MODEL( MONTH_CODE,P_PROCESS_NO)
  

  This procedure performs the following tasks for each model:

  1. Refreshes the mining source materialized views based on the latest data from ocdm_sys schema.

  2. Trains each model on the new training data.

  3. Applies each model onto the new apply data set.

• To manually re-create only one mining model, you can call the corresponding procedure. For example, to re-create the churn SVM model, you can call the following procedure.

  ocdm_mining.create_churn_svm_model( MONTH_CODE );
  

  "Tutorial: Customizing the Churn Prediction Model" provides detailed instructions for refreshing a single data mining model.

Checking the Execution Plan

Use SQLDeveloper or other tools to view the package body of the code generated by Oracle Warehouse Builder.

For example, take the following steps to examine a map:

1. Copy out the main query statement from code viewer.

   Copy from "CURSOR "AGGREGATOR_c" IS …." to end of the query, which is right above another "CURSOR "AGGREGATOR_c$1" IS".

2. In SQLDeveloper worksheet, issue the following statement to turn on the parallel DML:

   Alter session enable parallel dml;
   

3. Paste the main query statement into another SQL Developer worksheet and view the execution plan by clicking F6.

   Carefully examine the execution plan to make the mapping runs according to a valid plan.

Monitoring PARALLEL DML Executions

Check that you are running mapping in parallel mode by executing the following SQL statement to count the executed "Parallel DML/Query" statement

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%'
;

If you run mapping in parallel mode, you should see "DML statements parallelized" increased by 1 (one) every time the mapping was invoked. If not, you do not see this increase, then the mapping was not invoked as "parallel DML".

If you see "queries parallelized" increased by 1 (one) instead, then typically it means that the SELECT statement inside of the INSERT was parallelized, but that INSERT itself was not parallelized.

Troubleshooting Data Mining Model Creation

After the data mining models are created, check the error log in ocdm_sys.dwc_intra_etl_activity table. For example, execute the following code.

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;

If all models are created successfully, the activity_status is all "COMPLETED-SUCCESS". If the activity_status is "COMPLETED-ERROR" for a certain step, check the ERROR_DTL column, and fix the problem accordingly.

The following examples illustrate how to troubleshoot some common error messages returned in ERROR_DTL and ACTIVITY_NAME when working with Oracle Communications Data Model:

• Example 4-2, "Troubleshooting an ORA-20991 Error for Oracle Communications Data Model"

• Example 4-3, "Troubleshooting the "Message not available ... [Language=ZHS]" Error"

• Example 4-4, "Troubleshooting an ORA-40113 Error for Oracle Communications Data Model"

• Example 4-5, "Troubleshooting an ORA-40112 Error for Oracle Communications Data Model"

• Example 4-6, "Troubleshooting an ORG-11130 Error for Oracle Communications Data Model"

Execute Calendar_Population.run('2005-01-01',10);

ocdm_mining.DMV_CUST_CHRN_SRC_PRD.chrn_ind