ETL that populates the foundation layer of an Oracle Communications Data Model warehouse (that is, the base, reference, and lookup tables) with data from an operational system is known as source-ETL.
You can populate the foundation layer of an Oracle Communications Data Model warehouse in the following ways:
If an application adapter for Oracle Communications Data Model is available for the system from which you want to populate the foundation layer of an Oracle Communications Data Model warehouse, you can use that adapter to populate the foundation layer.
Write your own source-ETL scripts using Oracle Data Integrator or another ETL tool and then use those scripts to populate the foundation layer.
If an Application Adapter for Oracle Communications Data Model is available for the application that populates your Operational system, you use that adapter to populate the foundation layer of your Oracle Communications Data Model warehouse.
If you are not using an application adapter, you must write your own source-ETL scripts using Oracle Data Integrator or another ETL tool or mapping tool.
The following topics provide general information about writing source-ETL:
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:
Lookup tables
Reference tables
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 Also:
The topic about analyzing tables, indexes, and clusters in Oracle Database Administrator's Guide
ETL (or EL-T, that is, Extract, Load and Transform) 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 of the 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. In such cases, the staging area can be used as a real-time Operational Data Store, at least for the source concerned, and aggregation could run directly from the Operational Data Store (operational system) to the Access layer, or to the presentation layer in specific cases.
When designing source-ETL for Oracle Communications Data Model, use the architecture that best meets your business needs.
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.
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.
There are several potential data quality issues, related to each other, that the staging area needs to handle:
Data Validity: Is the data content and type sufficient to be usable, and as expected (and "profile" in case one uses this advanced option)?
Data Accuracy: correct addresses, correct with respect some "true" standard (or as such defined).
Data Completeness: is all the required data there? What to do when data is missing? What represents the minimum set of required data?
Data Consistency: that is, consistency of the data between the various sources and what rules one applies for inconsistencies.
Data Latency: A sub-part of data consistency, but treated separately because of its importance: when does data arrive, over which period and in which one can we combine, which one not?
Data Reasoning: This is more at reporting level but can be applied at the staging level: Does the data I see make sense from a business perspective? Can I really combine the data as an end-user would expect?
As a consequence, a multi-layer staging is generally required or expected.
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.
All data movement among ETL processes are composed of jobs. An ETL workflow executes these jobs in the proper sequence and with 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.
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.
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.
Using a Staging Area for Flat Files
The area where flat files are stored before being loaded into the staging layer of a data warehouse system is commonly known as staging area. The overall speed of your load is determined by:
How quickly the raw data can be read from staging area.
How quickly the raw data can be processed and inserted into the database.
Recommendations: Using a Staging Area
Stage the raw data across as many physical disks as possible to ensure that reading it is not a bottleneck during the load.
Also, if you are using the Exadata Database Machine, the best place to stage the data is in an Oracle Database File System (DBFS) stored on the Exadata storage cells. DBFS creates a mountable cluster file system which can you can use to access files stored in the database. Create the DBFS in a separate database on the Database Machine. This allows the DBFS to be managed and maintained separately from the data warehouse.
Mount the file system using the DIRECT_IO option to avoid thrashing the system page cache while moving the raw data files in and out of the file system.
Preparing Raw Data Files for Source-ETL
In order to parallelize the data load Oracle Database must be able to logically break up the raw data files into chunks, known as granules. To ensure balanced parallel processing, the number of granules is typically much higher than the number of parallel server processes. At any given point in time, a parallel server process is allocated one granule to work on. After a parallel server process completes working on its granule, another granule is allocated until all of the granules are processed and the data is loaded.
Recommendations: Preparing Raw Data Files for Source-ETL
Follow these recommendations:
Deliminate each row using a known character such as a new line or a semicolon. This ensures that Oracle can look inside the raw data file and determine where each row of data begins and ends in order to create multiple granules within a single file.
If a file is not position-able and seek-able (for example the file is compressed or zip file), then the files cannot be broken up into granules and the whole file is treated as a single granule. In this case, only one parallel server process can work on the entire file. In order to parallelize the loading of compressed data files, use multiple compressed data files. The number of compressed data files used determines the maximum parallel degree used by the load.
When loading multiple data files (compressed or uncompressed):
Use a single external table, if at all possible
Make the files similar in size
Make the size of the files a multiple of 10 MB
If you must have files of different sizes, list the files from largest to smallest. By default, Oracle assumes that the flat file has the same character set as the database. If this is not the case, specify the character set of the flat file in the external table definition to ensure the proper character set conversions can take place.
Source-ETL Data Loading Options
Oracle offers several data loading options:
External table or SQL*Loader
Oracle Data Pump (import and export)
Change Data Capture and Trickle feed mechanisms (such as Oracle GoldenGate)
Oracle Database Gateways to open systems and mainframes
Generic Connectivity (ODBC and JDBC)
The approach that you take depends on the source and format of the data you receive.
Recommendations: Loading Flat Files
If you are loading from files into Oracle you have two options: SQL*Loader or external tables.
Using external tables offers the following advantages:
Allows transparent parallelization inside the database.You can avoid staging data and apply transformations directly on the file data using arbitrary SQL or PL/SQL constructs when accessing external tables. SQL Loader requires you to load the data as-is into the database first.
Parallelizing loads with external tables enables a more efficient space management compared to SQL*Loader, where each individual parallel loader is an independent database sessions with its own transaction. For highly partitioned tables this could potentially lead to a lot of wasted space.
You can create an external table using the standard CREATE TABLE statement. However, to load from flat files the statement must include information about where the flat files reside outside the database. The most common approach when loading data from an external table is to issue a CREATE TABLE AS SELECT (CTAS) statement or an INSERT AS SELECT (IAS) statement into an existing table.
Parallel Direct Path Load Source-ETL
A direct path load parses the input data according to the description given in the external table definition, converts the data for each input field to its corresponding Oracle data type, then builds a column array structure for the data. These column array structures are used to format Oracle data blocks and build index keys. The newly formatted database blocks are then written directly to the database, bypassing the standard SQL processing engine and the database buffer cache.
The key to good load performance is to use direct path loads wherever possible:
A CREATE TABLE AS SELECT (CTAS) statement always uses direct path load.
A simple INSERT AS SELECT (IAS) statement does not use direct path load. In order to achieve direct path load with an IAS statement you must add the APPEND hint to the command.
Direct path loads can also run in parallel. To set the parallel degree for a direct path load, either:
Add the PARALLEL hint to the CTAS statement or an IAS statement.
Set the PARALLEL clause on both the external table and the table into which the data is loaded.
After the parallel degree is set:
A CTAS statement automatically performs a direct path load in parallel.
An IAS statement does not automatically perform a direct path load in parallel. In order to enable an IAS statement to perform direct path load in parallel, you must alter the session to enable parallel DML by executing the following statement:
alter session enable parallel DML;
Partition Exchange Load for Oracle Communications Data Model Source-ETL
A benefit of partitioning is the ability to load data quickly and easily with minimal impact on the business users by using the EXCHANGE PARTITION command. The EXCHANGE PARTITION command enables swapping the data in a nonpartitioned table into a particular partition in your partitioned table. The EXCHANGE PARTITION command does not physically move data, instead it updates the data dictionary to exchange a pointer from the partition to the table and vice versa.
Because there is no physical movement of data, an exchange does not generate redo and undo. In other words, an exchange is a sub-second operation and far less likely to impact performance than any traditional data-movement approaches such as INSERT.
Recommendations: Partitioning Tables
Partition the larger tables and fact tables in the Oracle Communications Data Model warehouse.
Note:
The assumption being made in this example is that the data integrity was verified at date extraction time. If you are unsure about the data integrity, omit the WITHOUT VALIDATION clause so that the Database checks the validity of the data.
Example 4-1 Using Exchange Partition Statement with a Partitioned Table
Assume that there is a large table called Sales, which is range partitioned by day. At the end of each business day, data from the online sales system is loaded into the Sales table in the warehouse.
The following steps ensure the daily data gets loaded into the correct partition with minimal impact to the business users of the data warehouse and optimal speed:
Create external table for the flat file data coming from the online system
Using a CTAS statement, create a nonpartitioned table called tmp_sales that has the same column structure as Sales table
Build any indexes that are on the Sales table on the tmp_sales table
Issue the EXCHANGE PARTITION command.
Alter table Sales exchange partition p2 with
table top_sales including indexes without validation;
Gather optimizer statistics on the newly exchanged partition using incremental statistics.
The EXCHANGE PARTITION command in this example, swaps the definitions of the named partition and the tmp_sales table, so the data instantaneously exists in the right place in the partitioned table. Moreover, with the inclusion of the INCLUDING INDEXES and WITHOUT VALIDATION clauses, Oracle swaps index definitions and does not check whether the data actually belongs in the partition - so the exchange is very quick.