Integración del Modelo de Nonaka y Takeuchi:

Architecture of ODI Repositories

It is recommended that you install the ODI Master and Work repositories in an OLTP database distinct from the source or target applications.

Reverse-engineering an Oracle Schema

The Oracle JDBC driver implements most metadata APIs. You can therefore use the standard JDBC reverse-engineering with ODI. Using this method, you can capture metadata for:

• Tables, Views etc. including table comments,

• Columns including data types, length and scale, and comments

• Primary Keys

• Foreign Keys

• Check Constraints

In some cases a custom reverse-engineering approach using the RKM Oracle may be necessary. For example ODI 11g natively supports database partitions which can only be reverse-engineered using the RKM Oracle.

RKM DESCRIPTION

RKM Oracle Reverse-engineering Knowledge Module for Oracle

This RKM uses the Oracle system tables known as the Oracle Data Dictionary to extract the metadata definition of a table and its related artifacts (columns, keys, partitions etc.).

Oracle Loading Strategies

When loading an Oracle data warehouse the goal is always to load the data into the database in the most efficient manner and Oracle Data Integrator provides several Loading Knowledge Modules optimized for Oracle that will help you achieve this. Choosing the right Knowledge Module for each use case will greatly improve the efficiency of your data integration

You can refer to the following table for a list of the Loading Knowledge Modules optimized for Oracle:

LKM DESCRIPTION

LKM File to Oracle (EXTERNAL TABLE) Loads data from a file to an Oracle staging area using the EXTERNAL TABLE SQL Command.

LKM File to Oracle (SQLLDR) Loads data from a file to an Oracle staging area using the SQL*Loader command line utility. The Oracle client must be installed on the machine hosting the ODI Agent.

LKM SQL to Oracle Loads data through the agent from any SQL RDBMS to an Oracle

staging area.

LKM MSSQL to Oracle (BCP SQLLDR) Loads data from a Microsoft SQL Server database to an Oracle staging area using the BCP and SQL*Loader utilities. The utilities must be installed on the machine hosting the ODI Agent.

LKM Oracle to Oracle (DBLINK) Loads data from an Oracle source database to an Oracle staging area database using database links.

LKM Oracle to Oracle (datapump) Loads data from an Oracle source database to an Oracle staging area database using external tables in the datapump format.

There are also several additional Knowledge Modules which can be used to extract data from SAP ERP, SAP BW or Oracle BI and load the records into an Oracle database.

Using Loaders for Flat Files

When your interface contains a flat file as a source, you may want to use a strategy that leverages the most efficient loading utility available for your staging area technology rather than the standard “LKM File to SQL” which uses the ODI Agent to load the flat file data. The agent uses JDBC to write to the target, using this method would lead to significant performance issues compared to loading into Oracle using the External Table SQL command or SQL*Loader.

We advise against using the ODI Agent when loading large files and recommend the “LKM File to Oracle (EXTERNAL TABLE)” to transfer large files into an Oracle database.

Using Unload/Load for Remote Servers

When the source result set is located on a remote database server, an alternative to using the agent to transfer the data would be to unload the date into a file and then load it into the staging area.

The “LKM MSSQL to Oracle (BCP SQLLDR)” is an example of a Knowledge Module that follows these steps, it uses the BCP utility to bulk unload data from a Microsoft SQL Server database into a file and SQL*Loader to load the data back into an Oracle staging area. This Loading Knowledge Module provides the most optimal approach to extracting data out of Microsoft SQL Server and loading it into an Oracle database.

Several Knowledge Modules use a similar approach including the SAP ERP and BW LKMs.

Oracle to Oracle

There are use cases in which there is a need to move data between Oracle systems and ODI provides several ways to achieve this.

One of the best methods when it comes to loading data in such a scenario is to use Oracle Data Pump and Oracle Data Integrator provides the “LKM Oracle to Oracle (datapump)” which leverages Oracle Data Pump to export and load the data efficiently through the creation of External Tables using Oracle Data Pump on both the source and the target databases.

ODI also provides the “LKM Oracle to Oracle (DBLINK)” which has the ability to create a database link between the source and the target Oracle database to load the data.

Using Changed Data Capture

Applying a Change Data Capture (CDC) approach can significantly improve the performance of your ODI processes.

For example if you are loading your data warehouse nightly you only need to extract the data that has changed in the past 24 hours, loading the entire source data sets will result in a significant degradation of the efficiency of your ODI jobs.

Using CDC ensures that the extract from your various source systems is done incrementally. This reduces the amount of data transferred from your source systems into your target database as well as the amount of data transformed by ODI. The creation of the CDC process can be challenging and ODI simplifies it by providing Knowledge Modules which leverage many CDC mechanisms including the native database CDC capabilities.

Change Data Capture on Oracle

When it comes to detecting changes occurring in an Oracle database ODI offers several options:

• CDC using Oracle GoldenGate

• CDC using Oracle Streams

• CDC using database triggers

The Oracle Journalization Knowledge Modules (JKMs) automatically create the underlying infrastructure required by the ODI CDC framework so that end users do not have to worry about it. For example, the Oracle JKMs have the ability to configure Oracle GoldenGate or the Oracle Streams infrastructure when required.

You can refer to the following table for a list of the JKMs for Oracle provided with ODI:

JKM DESCRIPTION

JKM Oracle to Oracle Consistent (OGG) Creates and manages the ODI CDC framework infrastructure when using Oracle GoldenGate for CDC.

JKM Oracle 10g Consistent (Streams) Creates the journalizing infrastructure for consistent set journalizing on Oracle 10g tables, using Oracle Streams.

JKM Oracle 11g Consistent (Streams) Creates the journalizing infrastructure for consistent set journalizing on Oracle 11g tables, using Oracle Streams.

JKM Oracle Consistent Creates the journalizing infrastructure for consistent set journalizing on Oracle tables using triggers

JKM Oracle Consistent (Update Date) Creates the journalizing infrastructure for consistent set journalizing on Oracle tables using triggers based on a Last Update Date column on the source tables

JKM Oracle Simple Creates the journalizing infrastructure for simple journalizing on Oracle tables using triggers

It is also possible to leverage other mechanism in ODI to detect changes like timestamps, dates or sequence numbers.

There are many more JKMs provided with ODI, you can refer to the ODI Documentation for a complete list of the JKMs.

Oracle Integration Strategies

The Integration Knowledge Modules optimized for the Oracle database are implementing many of the best practices to insert data into an Oracle data warehouse:

- Direct Path loading is applied whenever possible using the /*+ APPEND */ hint

- Additional optimizer hints such as PARALLEL which specifies the degree of

parallelism for a given database operation can be specified via the OPTIMIZER_HINT option of the IKMs

- The ODI Staging Area tables are created by default using NOLOGGING to ensure

best performance

- The MERGE SQL statement can be leveraged using the “IKM Oracle Incremental

Update (MERGE)”. MERGE combines an INSERT and an UPDATE in one single optimized SQL command

- ODI gathers tables statistics to ensure that the Oracle database Cost Based

Optimizer (CBO) selects the most optimal execution plan over time

- Indexes are created to speed up the joins and filters execution. End users can choose

improve the efficiency of joins between large fact tables and smaller dimension tables in a star schema.

You can refer to the following table for a list of the IKMs that can be used with an Oracle database:

IKM DESCRIPTION

IKM SQL Control Append Integrates data in any ISO-92 compliant database target table in

TRUNCATE/INSERT (append) mode.) Data quality can be checked. Invalid data is rejected in the “E$” error table and can be recycled.

IKM Oracle Incremental Update Set-based incremental update for Oracle IKM Oracle Slowly Changing Dimension Slowly Changing Dimension Type II for Oracle IKM Oracle Incremental Update

(MERGE)

Integrates data in an Oracle target table in incremental update mode, using a MERGE statement

IKM Oracle Multi Table Insert Integrates data from one source into one or many Oracle target tables in append mode, using a multi-table insert statement (MTI).

IKM Oracle AW Incremental Update Integrates data in an Oracle target table in incremental update mode and is able to refresh a Cube in an Analytical Workspace.

It is also possible to extend the ODI Knowledge Modules with your own best practices when needed. Thanks to the reusability of the KMs it is simple to ensure that best practices are enforced and utilized by each developer.

Defining a Data Quality Strategy

Loading data that is not clean in your data warehouse can have a major impact on the overall performance of your ETL processes. Primary key or foreign key violations, business rules that are not met, null values that are not expected can waste time and resources if the appropriate Data Quality framework is not in place within your data integration process.

Oracle Data Integrator provides an out of the box framework to handle Data Quality errors occurring while data is being loaded into a database. Business rules and constraints are defined at the metadata level and those validation rules are checked against the data prior to being integrated into the target tables. The constraints can be enabled or disabled when required. The errors that are captured are routed into error tables managed by ODI that can be reviewed through the Designer Navigator as well as the ODI Console.

“CKM Oracle” is the recommended Check Knowledge Module for checking data integrity constraints defined on a given Oracle table.

Setting up Agents in an Oracle environment

Where to Install the Agent(s)?

A typical data warehouse implementation can have one or more ODI agent in production. In an Oracle environment, the agent is usually installed on the host machine that is used to load data in the data warehouse. The agent requires a connection to the source databases or files, and triggers the appropriate load utilities.

Starting from ODI 11g it is also possible to install the ODI agents in WebLogic Server to achieve High-Availability (HA) through a clustered deployment.