5 ETL Implementation and Customization

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 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 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

Understanding the INTRA_ETL_FLW Process Flow

The INTRA_ETL_FLW process flow consists of the following subprocesses and includes the dependency of individual sub-process flows and executes them in the proper order:

1. DRVD_FLW

   This sub-process flow contains all the Oracle Warehouse Builder mappings for populating derived tables based on the content of the base, reference, and lookup tables. This process flow allows mappings to be executed concurrently.

   This sub-process uses the following technologies:

   • MERGE clause. This statement is used for initial load and for incremental load. It determines if the data to be inserted or updated into target table based on checking key columns with incoming data and provides a much superior performance compared to an INSERT and UPDATE combination.

   • TABLE function. This function is used mainly for derived tables (and some source data) in order to avoid needing a separate staging table. The absence of a staging table provides a performance boost in the ETL operation and simplifies database maintenance

   • Pipeline clause. This clause is used in the TABLE function for incrementally returning the result set for further processing as soon as they are created. Therefore, the use of this clause avoids bulk return which makes the complete process much faster than using a normal return.

2. AGGR_FLW

   This sub-process flow contains PL/SQL code that uses the partitions change tracking strategy to refresh all the aggregate tables which are materialized views in the Oracle Communications Data Model.

   This subprocess uses the following technologies:

   • Materialized View. A materialized view is used to hold the aggregation data. Whenever this is refreshed then the modified data get reflected in the corresponding aggregate table and this leads to a significant increase in the query execution performance. Moreover usage of materialized view allows Oracle Communications Data Model to make use of the Oracle Query Rewrite feature for better SQL optimization and hence improved performance.

   • FAST Refresh. This refresh type is used to refresh the aggregates with only the incremental data (inserted and modified) in base and derived tables after the immediately previous refresh and this incremental refresh leads to much better performance and hence shorter intra-ETL window.

3. MINING_FLW

   This sub-process flow triggers the data mining model refresh.

The INTRA_ETL_FLW process flow also includes the OLAP_MAP mapping. OLAP_MAP invokes the analytic workspace build function of the PKG_OCDM_OLAP_ETL_AW_LOAD package to load data from Oracle Communications Data Model aggregate materialized views into the Oracle Communications Data Model analytical workspace and calculates the forecast data. The PKG_OCDM_OLAP_ETL_AW_LOAD package reads OLAP ETL parameters from the DWC_OLAP_ETL_PARAMETER control table in the ocdm_sys schema to determine the specifics of how to load the data and perform the forecasts.

Performing an Initial Load of an Oracle Communications Data Model Warehouse

Performing an initial load of an Oracle Communications Data Model is a multistep process:

1. Load the reference, lookup, and base tables Oracle Communications Data Model warehouse by executing the source-ETL that you have written using the guidelines given in "Writing Source-ETL for Oracle Communications Data Model".

2. Load the remaining structures in the Oracle Communications Data Model, by taking the following steps:

   1. Update the parameters in DWC_ETL_PARAMETER control table in the ocdm_sys schema so that the ETL can use this information (that is, the beginning and end date of the ETL period) when loading the derived and aggregate tables and views.

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

      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.

   2. Update the Oracle Communications Data Model OLAP ETL parameters in DWC_OLAP_ETL_PARAMETER control table in the ocdm_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 5-1.

      Table 5-1 Values of Oracle Communications Data Model OLAP ETL Parameters in the DWC_OLAP_ETL_PARAMETER Table for Initial Load

      Column Name	Value
      PROCESS_NAME	'OCDM-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 Communications Data Model analytic workspace. cubename[[|cubename]...] specifies one or more cubesto 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 Communications Data Model Intra-ETL ".

Managing Errors During Oracle Communications Data Model Intra-ETL Execution

This topic discusses how you can identify and manage errors during intra-ETL execution. It contains the following topics:

• Monitoring the Execution of the Intra-ETL Process

• Recovering an Intra ETL Process

• Troubleshooting Intra-ETL Performance

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;

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

ocdm_mining.DMV_CUST_CHRN_SRC_PRD.chrn_ind