Saturday, May 26, 2012

Check Knowledge Modules (CKM)

Check Knowledge Modules (CKM)

The CKM is in charge of checking that records of a data set are consistent with defined constraints. The CKM is used to maintain data integrity and participates in the overall data quality initiative. The CKM can be used in 2 ways:
  • To check the consistency of existing data. This can be done on any datastore or within interfaces, by setting the STATIC_CONTROL option to "Yes". In the first case, the data checked is the data currently in the datastore. In the second case, data in the target datastore is checked after it is loaded.
  • To check consistency of the incoming data before loading the records to a target datastore. This is done by using the FLOW_CONTROL option. In this case, the CKM simulates the constraints of the target datastore on the resulting flow prior to writing to the target.
In summary: the CKM can check either an existing table or the temporary "I$" table created by an IKM.
The CKM accepts a set of constraints and the name of the table to check. It creates an "E$" error table which it writes all the rejected records to. The CKM can also remove the erroneous records from the checked result set.
The following figures show how a CKM operates in both STATIC_CONTROL and FLOW_CONTROL modes.
Figure 1-2 Check Knowledge Module (STATIC_CONTROL)
Description of Figure 1-2 follows
Description of "Figure 1-2 Check Knowledge Module (STATIC_CONTROL)"
In STATIC_CONTROL mode, the CKM reads the constraints of the table and checks them against the data of the table. Records that don't match the constraints are written to the "E$" error table in the staging area.
Figure 1-3 Check Knowledge Module (FLOW_CONTROL)
Description of Figure 1-3 follows
Description of "Figure 1-3 Check Knowledge Module (FLOW_CONTROL)"
In FLOW_CONTROL mode, the CKM reads the constraints of the target table of the Interface. It checks these constraints against the data contained in the "I$" flow table of the staging area. Records that violate these constraints are written to the "E$" table of the staging area.
In both cases, a CKM usually performs the following tasks:
  1. Create the "E$" error table on the staging area. The error table should contain the same columns as the datastore as well as additional columns to trace error messages, check origin, check date etc.
  2. Isolate the erroneous records in the "E$" table for each primary key, alternate key, foreign key, condition, mandatory column that needs to be checked.
  3. If required, remove erroneous records from the table that has been checked.

Reverse-Engineering Knowledge Modules (RKM)

Reverse-Engineering Knowledge Modules (RKM)

The RKM role is to perform customized reverse engineering for a model. The RKM is in charge of connecting to the application or metadata provider then transforming and writing the resulting metadata into Oracle Data Integrator's repository. The metadata is written temporarily into the SNP_REV_xx tables. The RKM then calls the Oracle Data Integrator API to read from these tables and write to Oracle Data Integrator's metadata tables of the work repository in incremental update mode. This is illustrated below:
Figure 1-1 Reverse-engineering Knowledge Modules
Description of Figure 1-1 follows
Description of "Figure 1-1 Reverse-engineering Knowledge Modules"
A typical RKM follows these steps:
  1. Cleans up the SNP_REV_xx tables from previous executions using the OdiReverseResetTable tool.
  2. Retrieves sub models, datastores, columns, unique keys, foreign keys, conditions from the metadata provider to SNP_REV_SUB_MODEL, SNP_REV_TABLE, SNP_REV_COL, SNP_REV_KEY, SNP_REV_KEY_COL, SNP_REV_JOIN, SNP_REV_JOIN_COL, SNP_REV_COND tables.
  3. Updates the model in the work repository by calling the OdiReverseSetMetaData tool.




    Source

    http://docs.oracle.com/cd/E15586_01/integrate.1111/e12645/intro.htm

What is a Knowledge Module?

What is a Knowledge Module?

 

Knowledge Modules (KMs) are code templates. Each KM is dedicated to an individual task in the overall data integration process. The code in the KMs appears in nearly the form that it will be executed except that it includes Oracle Data Integrator (ODI) substitution methods enabling it to be used generically by many different integration jobs. The code that is generated and executed is derived from the declarative rules and metadata defined in the ODI Designer module.
  • A KM will be reused across several interfaces or models. To modify the behavior of hundreds of jobs using hand-coded scripts and procedures, developers would need to modify each script or procedure. In contrast, the benefit of Knowledge Modules is that you make a change once and it is instantly propagated to hundreds of transformations. KMs are based on logical tasks that will be performed. They don't contain references to physical objects (datastores, columns, physical paths, etc.)
  • KMs can be analyzed for impact analysis.
  • KMs can't be executed standalone. They require metadata from interfaces, datastores and models.
KMs fall into 6 different categories as summarized in the table below:

Introduction to Knowledge Modules

Introduction to Knowledge Modules

 

  "What is a Knowledge Module?"

 "Reverse-Engineering Knowledge Modules (RKM)"

 "Check Knowledge Modules (CKM)"

"Loading Knowledge Modules (LKM)"

 "Integration Knowledge Modules (IKM)"

 "Journalizing Knowledge Modules (JKM)"

 "Service Knowledge Modules (SKM)"

 "Guidelines for Knowledge Module Developers"

ODI - Staging Area (or Work area)

ODI - Staging Area (or Work area)


These temporary objects are always stored in a particular schema or directory called the Work Schema (or staging area).
These work schemas should be kept separate from the schema containing the application data (Data Schema).

Example:
  • when the source is a flat file, and as such is not associated to an engine that supports operations (as concatenation, uppercase, …)
  • all components (except the triggers) of the journalizing infrastructure (like all Data Integrator temporary objects, such as integration, error and loading tables) 
     
     
     

    Objects and work tables prefix


    On the target data servers, you should almost certainly create a dedicated work schema. This will store:
    • Temporary tables and views created during the loading phase if the source data is not in the same server as the target (C$ tables)
    • Temporary tables created during the integration phase when using certain Integration Knowledge Modules (I$ tables)
    • Permanent Error tables created during Data Quality Control phase when using Check Knowledge Modules (E$ tables)
    • Permanent Journal tables, views and triggers created when the target is to be used as a source with Change Data Capture enabled by using Journalizing Knowledge Modules (J$ objects)
    On “read-only” source data servers the following objects may be created depending on the way you want to address these servers:
    • Views during Load phases, when using certain Loading Knowledge Modules
    • Error tables when performing a “static” Data Quality Control (e.g. when controlling the data of a particular source schema independently of any load job)
    • Journal tables, views and triggers when using Change Data Capture on the sources.

    Where is the Staging Area ?

    The staging area (or Work area) is first define when you create a physical schema but you can change it in the interface properties



    Source

    http://gerardnico.com/wiki/dit/odi/staging_area
     

ODI - Change Data Capture

ODI - Change Data Capture


The goal of Change Data Capture is to track change in the source data. When running integration interface, ODI-EE can reduce the volume of source data processed in the flow by extracting only the changed data.
Reducing the volume of source data is useful in many field such as:
  • synchronization
  • replication
These changes are captured by Oracle Data Integrator and transformed into events that are propagated throughout the information system.
Changes tracked by Changed Data Capture constitute data events. The ability to track these events and process them regularly in batches or in real time is key to the success of an event-driven integration architecture.
Changed Data Capture is performed by journalizing models. Journalizing a model consists of setting up the infrastructure to capture the changes (inserts, updates and deletes) made to the records of this model's datastores.
Oracle Data Integrator supports two journalizing modes:
  • Simple Journalizing tracks changes in individual datastores in a model.
  • Consistent Set Journalizing tracks changes to a group of the model's datastores, taking into account the referential integrity between these datastores. The group of datastores journalized in this mode is called a Consistent Set.
     
     
     
     

    Publish-and-subscribe model

    Changed Data Capture uses a publish-and-subscribe model. This model works in three steps:
    • An identified subscriber, usually an integration process, subscribes to changes that might occur in a datastore. Multiple subscribers can subscribe to these changes.
    • The Changed Data Capture framework captures changes in the datastore and then publishes them for the subscriber.
    • The subscriber—an integration process—can process the tracked changes at any time and consume these events. Once consumed, events are no longer available for this subscriber.

    Process

    ODI-EE processes datastore changes in two ways:
    • Regularly in batches (pull mode)—for example, processes new orders from the Web site every five minutes and loads them into the operational datastore (ODS)
    • In real time (push mode) as the changes occur—for example, when a product is changed in the enterprise resource planning (ERP) system, immediately updates the on-line catalog.

    The Journalizing Components

    The journalizing components are:
    • Journals: Where changes are recorded. Journals only contain references to the changed records along with the type of changes (insert/update, delete).
    • Capture processes: Journalizing captures the changes in the source datastores either by creating triggers on the data tables, or by using database-specific programs to retrieve log data from data server log files. See the documentation on journalizing knowledge modules for more information on the capture processes used.
    • Subscribers: CDC uses a publish/subscribe model. Subscribers are entities (applications, integration processes, etc) that use the changes tracked on a datastore or on a consistent set. They subscribe to a model's CDC to have the changes tracked for them. Changes are captured only if there is at least one subscriber to the changes. When all subscribers have consumed the captured changes, these changes are discarded from the journals.
    • Journalizing views: Provide access to the changes and the changed data captured. They are used by the user to view the changes captured, and by integration processes to retrieve the changed data.
    These components are implemented in the journalizing infrastructure.

    Simple vs. Consistent Set Journalizing

    Simple Journalizing enables you to journalize one or more datastores. Each journalized datastore is treated separately when capturing the changes.
    This approach has a limitation, illustrated in the following example: Say you need to process changes in the ORDER and ORDER_LINE datastores (with a referential integrity constraint based on the fact that an ORDER_LINE record should have an associated ORDER record). If you have captured insertions into ORDER_LINE, you have no guarantee that the associated new records in ORDERS have also been captured. Processing ORDER_LINE records with no associated ORDER records may cause referential constraint violations in the integration process.
    Consistent Set Journalizing provides the guarantee that when you have an ORDER_LINE change captured, the associated ORDER change has been also captured, and vice versa. Note that consistent set journalizing guarantees the consistency of the captured changes. The set of available changes for which consistency is guaranteed is called the Consistency Window. Changes in this window should be processed in the correct sequence (ORDER followed by ORDER_LINE) by designing and sequencing integration interfaces into packages.
    Although consistent set journalizing is more powerful, it is also more difficult to set up. It should be used when referential integrity constraints need to be ensured when capturing the data changes. For performance reasons, consistent set journalizing is also recommended when a large number of subscribers are required.

    Implementation

    Tracking changes

    ODI-EE provides two methods for tracking changes from source datastores to the Changed Data Capture framework:
    • triggers
    • and relational database management system (RDBMS) log mining.
    The triggers method creates triggers on the source tables to track changes as data is inserted, updated, or deleted. This method can be implemented on most RDBMS, but it can have an impact on the transactional performance of the source systems.
    The second method involves mining the RDBMS logs, which are the internal change history of the database engine. This method has no effect on the system’s transactional performance; it is database-specific. This method is supported out-of-the-box for:
    • Oracle Database (through the Log Miner feature)
    • and IBM DB2.
    The Changed Data Capture framework used to manage changes is generic and open. The change tracking method can be customized, and any third-party change provider can be used to load the framework with changes.

    Processing the change

    Developers define the declarative rules for the captured changes within the integration processes in the ODI-EE Designer graphical user interface—without having to code. With the Designer, customers declaratively specify set-based maps between sources and targets, and then the system automatically generates the data flow from the set-based maps. The technical processes required for processing the changes captured are implemented in Knowledge Modules.

    Ensuring Data Consistency

    Changes frequently involve several datastores at one time. For example, when an order is created, updated, or deleted, it involves both the orders table and the order lines table. When processing a new order line, the new order to which this line is related must be taken into account.
    ODI provides a mode of tracking changes, called Consistent Set Changed Data Capture, for this purpose. This mode allows you to process sets of changes that guarantee data consistency.




    Source:

    http://gerardnico.com/wiki/dit/odi/cdc
     

Components of an Interface

The interfaces use the following components:
  • Datastores defined in the models as sources and target of the loading process.
  • Knowledge Modules to generate the appropriate processes.
  • Variables and Sequence to store values or counters in the expressions.
  • Users functions to ease the transformation rule coding.
     

    Flow and knowledge modules


    The flow is the set of loading and integration strategies for mapped data, based on knowledge modules as :
    • Loading strategy: Loading Knowledge Module (LKM)
    • Integration strategy: Integration Knowledge Module (IKM)
    • Control strategy: Check Knowledge Module (CKM). The flow control strategy lets you define the method used to check the flow before the insertion in the target. 
       
       
       
      Source 
       
      http://gerardnico.com/wiki/dit/odi/interface
     

ODI Main key words

Target Datastore - The target datastore is the element that will be loaded
by the interface.
· Datasets - One target is loaded with data coming from several datasets.
Set-based operators (Union, Intersect, etc.) are used to merge the
different datasets into the target datastore.
· Diagram of Source Datastores - A diagram of sources is made of source
datastores – possibly filtered – related using joins. The source diagram
also includes lookups to fetch additional information for loading the target.
· Mapping - A mapping defines the transformations performed on one or
several source columns to load one target column. These transformations
are implemented in the form of SQL expressions.
· Staging Area - The staging area is a logical schema into which some of
the transformations (joins, filters and mappings) take place. It is by default
the same schema as the target’s logical schema. It is possible to locate
the staging area on a different location (including one of the sources).
· Flow - The flow describes how the data flows between the sources, the
staging area if it is different from the target, and the target as well as
where joins and filters take place. The flow also includes the loading and
integration methods (Loading and Integration Knowledge Modules - LKM,
IKM).
· Control - An interface implements two points of control. Flow control
checks the flow of data before it is integrated into the target. Post-
Integration control performs a static check on the target table at the end of
the interface (Check Knowledge Module - CKM).


Process Overview
The following step sequence is usually performed when creating an interface,
and can be used as a guideline to design your first interfaces:
1. Create a New Interface
2. Define the Target Datastore
3. Define the Datasets
4. Define the Source Datastores and Lookups
5. Define the Mappings
6. Define the Interface Flow
7. Set up Flow Control and Post-Integration Control
8. Execute the Integration Interface

Developing a Knowledge Module in Oracle Data Integrator


Developing a Knowledge Module in Oracle Data Integrator

Learn how to build your very own Integration Knowledge Module for fast time-to-production in data integration design.
By Uli Bethke
Published June 2009
Knowledge modules (KMs) in Oracle Data Integrator are components that implement reusable transformation and ELT (extract, load, and transform) strategies across different technologies. One example of this is extracting data through change data capture from Oracle Database 10g and loading the transformed data into a partitioned fact table in Oracle Database 11g, or creating timestamp-based extracts from a Microsoft SQL Server database and loading this data into a Teradata enterprise data warehouse.
The power of KMs lies in their reusability and flexibility—for example, you can develop and implement a loading strategy for a fact table and then, with a click of a mouse, apply this loading strategy to all of your fact tables. If you adapt your ELT strategy to changing business requirements and modify the associated KM, all interfaces that use the KM inherit the changes. You can mix and match different programming languages, types, and styles (native RDBMS SQL, scripting languages such as Jython or JavaScript, or even Java). This is what makes these KMs extremely flexible. This innovative approach is called Oracle Data Integrator Declarative Design and can be up to 10 times faster than traditional ETL approaches.
Oracle Data Integrator ships with various predefined KMs. You can directly use these in your ELT interfaces (the equivalent of an Oracle Warehouse Builder mapping) or adapt them to your specific requirements. Oracle Data Integrator implements five different types of KMs. Each of them covers one phase in the transformation process from source to target. The three most important types of modules are the integration knowledge module (IKM), the loading knowledge module (LKM), and the check knowledge module CKM.
In this article, we will create a new IKM. Our requirement for the IKM is to load the partitioned Sales fact table in the Sales History (SH) sample schema. In order to improve load performance of a fact table, we make the foreign key constraints and the bitmap indexes unusable for the duration of the load. Postload, these will be re-enabled. As part of the IKM, we will also include an optional step that will allow us to enable extended trace for the duration of the interface execution.

Prerequisites and Resources

This article is based on Oracle Database 11g (11.1.0.7.0) and Oracle Data Integrator 10.1.3.4.0.
I assume that you have already set up an Oracle Data Integrator master and work repository. See "Oracle Data Integrator: Getting Started with an ETL Project" (PDF) for details.
Here are some more helpful resources:
  • "Oracle Data Integrator Best Practices for a Data Warehouse" (PDF)
  • "Oracle by Example Series for Oracle Data Integrator"
  • "Oracle Data Integrator Substitution Methods Reference" (PDF)
  • Finally, you can download biweekly Webcasts delivered by Oracle Data Integrator product management.
  • The completed IKM can be downloaded here. To import the IKM, save it to your file system and import it with the Oracle Data Integrator Designer module.

    Create Source Schema

    First of all, we will create a new schema and a new table. This will be our datasource. We will also insert two records into this table. In a real-world scenario, you would more than likely have a lot more data, but for demonstration purposes these two records will suffice. Log in as user sys via SQL+ and execute the following statements:
    SQL> CREATE USER sales_source IDENTIFIED BY sales_source;
                                       
    
    
    User created.
                                       
    
    
    SQL> GRANT CONNECT TO sales_source;
                                       
    
    
    Grant succeeded.
                                       
    
    
    SQL> GRANT RESOURCE TO sales_source;
                                       
    
    
    Grant succeeded.
                                       
    
    
    SQL> CREATE TABLE sales_source (
      2     PROD_ID NUMBER NOT NULL ,
      3     CUST_ID NUMBER NOT NULL ,
      4     TIME_ID DATE NOT NULL ,
      5     CHANNEL_ID NUMBER NOT NULL ,
      6     PROMO_ID NUMBER NOT NULL ,
      7     QUANTITY_SOLD NUMBER NOT NULL ,
      8     AMOUNT_SOLD NUMBER NOT NULL
      9  ) ;
                                       
    
    
    Table created.
                                       
    
    
    SQL> INSERT INTO sales_source VALUES (13,987,TO_DATE('01/01/1999','DD/MM/YYYY'),3,999,1,500);
                                       
    
    
    1 row created.
                                       
    
    
    SQL> INSERT INTO sales_source VALUES (13,987,TO_DATE('01/04/1999','DD/MM/YYYY'),3,999,1,500);
                                       
    
    
    1 row created.
                                       
    
    
    SQL> COMMIT;
                                       
    
    
    Commit complete.
                                    
    Next we grant our target schema user SH execute privileges on the dbms_monitor package. The dbms_monitor package will allow us to launch extended trace. (See this Oracle Magazine article for more information on dbms_monitor.)
    SQL> grant execute on dbms_monitor to sh;
    
    Grant succeeded.
    
    Finally, we create a user for the Oracle Data Integrator work schema. For some of the data validation and transformation operations, Oracle Data Integrator needs to create (temporary) work objects. It is best practice to have these objects in a separate schema to avoid confusion between data warehouse objects and the temporary work objects.
    SQL> CREATE USER odi IDENTIFIED BY odi;
                                       
    
    
    User created.
                                       
    
    
    SQL> GRANT CONNECT,RESOURCE TO odi;
                                       
    
    
    Grant succeeded.
                                    

    Create Development Context

    Contexts are used to group physical resources together in the same environment. Typical contexts are Development, User Acceptance Test (UAT), Production, and so on.
    We create contexts with the Topology Manager. For the purpose of this article, we will just create a Development context. In Topology Manager, click the Context tab and then click the Insert Context icon. This will bring up the Definition tab for the context. Populate the fields as shown in the figure below.
    Figure 1
    We will now create the physical and logical architecture for our source schema (SALES_SOURCE). First we create the physical architecture for the source schema. In Topology Manager, select the Physical Architecture tab and expand Technologies. Right-click Oracle and select Insert Data Server.
    Figure 2
    For the name of the data server, we follow the naming guidelines in the Oracle Data Integrator Best Practices for a Data Warehouse guide: <TECH_NAME>_<SRV_NAME> (in uppercase). So we will name the data server ORCL_SALES_SOURCE_DEV. We populate the other fields on the Definition tab as shown in the figure below.
    Figure 3/
    Change to the JDBC tab and populate as shown in the figure below. You need to replace <host> with the server network name or IP address, <port> with the listener port number (usually 1521), and <sid> with the instance service name (Oracle SID).
    Figure 4
    Click the Test button to make sure that the data entered is correct and then click OK. On the following screen, select the SALES_SOURCE schema from the Schema (Schema) drop-down menu. Select ODI as the Schema (Work Schema). Remember that this is the schema in which Oracle Data Integrator creates temporary work objects. Leave the default values for the other fields (as shown in the figure below). Click OK.
    Figure 5
    Ignore the following warning message:
    Figure 6
    In the previous steps, we created the physical schema. We will now create the logical schema for the ORCL_SALES_SOURCE_DEV data server. In Topology Manager, click the Logical Schema tab. Expand Technologies, right-click Oracle, and select Insert Logical Schema. Again we will follow the naming convention from the best-practice guide: <TECH_NAME>_<SCHEMA_NAME> (in uppercase). So we will name the logical schema ORCL_SALES_SOURCE.
    For the Development context, select ORCL_SALES_SOURCE_DEV.SALES_SOURCE from the Physical Schema drop-down menu. Click OK.
    Figure 7

    Create Physical and Logical Architecture for SH Schema

    Repeat the steps from the previous section to set up the physical and logical schema for the SH schema. Adapt names, usernames, passwords, and so on to reflect the properties of the SH schema.

    Reverse-Engineer the SALES_SOURCE and SH Models into Oracle Data Integrator Designer Module

    We use the Oracle Data Integrator Designer Module to reverse-engineer the metadata of source and target schemas. Log into Designer, click the Models tab, and click the Insert Model Folder icon (as shown in the figure below).
    Figure 8
    Name the new model EDW and click OK. Next, right-click the new EDW folder and select Insert Model.
    On the Definition tab, type in SALES_SOURCE for the Name. Select Oracle from the Technology drop-down menu and ORCL_SALES_SOURCE from the Logical Schema drop-down menu.
    Figure 9
    Change to the Reverse tab and select Development from the Context drop-down menu. Leave the default values for the other fields.
    Figure 10
    Next click the Reverse button to reverse-engineer the SALES_SOURCE model. The reverse-engineering process may take a minute or two to complete. The next step will be to reverse-engineer the SH schema. Create the SH model in the EDW model folder by following the steps we have discussed for creating the SALES_SOURCE model.

    Create IKM to Load Fact Table

    In this section, we will create a new IKM that implements best practice methods for loading a fact table in a data warehouse. We will set up optional tracing and disable foreign keys and indexes preload. Once we have loaded the fact table, we will re-enable foreign keys and indexes and also disable tracing.
    To start off with, we highlight the Projects tab and create a new project by clicking the Insert Project icon.
    Figure 11
    We name this project EDW. Next we will create a new IKM.
    Expand the EDW project and KMs folders, right-click the Integration folder, and select Insert Integration (IKM).
    We name our new IKM "IKM Oracle Append (Disable FKs & Indexes)." This is in line with the best practice for naming KMs: <Type of Module> <Technology(ies)> <Load Strategy> (<Details of implementation>). Also select Oracle from the Target Technology drop-down menu.
    Figure 12
    Via the Details tab, we define the individual steps of the KM. As you will see later, execution of each step can be made optional when run in an interface. The figure below displays the completed IKM.
    Figure 13
    We will now go through most of the above steps. All of the above steps are written with a mixture of SQL and the Oracle Data Integrator substitution API.

    Start Trace

    We use the dbms_monitor package to enable tracing for our interface. Note that we need to call the session_trace_enable procedure from within a PL/SQL block.
    Figure 14
    As you can see from the figure below, we want to make tracing wait events optional. For that purpose, we have defined an option named TRACE_WAITS. We can reference this option via the getOption substitution API method. Also, we have defined a default value of false for the Option. As such, tracing is disabled by default when the IKM is executed in an interface. We can later override this value when executing the IKM via an interface.
    Figure 15
    From the Options tab, we can see that we do not always enable tracing (the Always Execute check box is not selected). Whether tracing is enabled is determined by the value of the Trace option.
    Figure 16
    By default the Trace option is set to No. We can later override this in the interface.
    Figure 17

    Drop Staging Table

    Dropping the staging table will get rid of records from a previous ETL run. We use the odiRef.getTable substitution API method to get the name of the staging table. The staging table is referenced as INT_NAME. Note that we have selected the Ignore Errors check box. In case we run the ETL for the first time, the staging table has not yet been created. If we try to drop a nonexistent table Oracle will return an error message. To prevent the ETL from stopping an execute if it comes across an error, we select the Ignore Errors check box for this step. This will save us from writing lengthy error handling procedures. (Note that instead of dropping the staging table, we could also have truncated it.)
    Figure 18

    Create Staging Table

    Once we have dropped the staging table and deleted any old records, we can recreate it.
    Figure 19
    We set the PCTFREE attribute of the table to the value of the PCTFREE option. By default, the value is 0. Because we are only inserting records into this table and never update them, we can make use of all of the space on the data blocks of this table.
    Figure 20

    Insert into Staging Table

    We use a mix of SQL and the Oracle Data Integrator substitution API to load the Sales staging table into our Oracle Data Integrator work schema from the SALES_SOURCE schema.
    Figure 21

    Disable FK Constraints

    We disable the foreign keys of the Sales table before we load the data into it. For large volumes of data, this will speed up the load process. This could be very important in performance-critical environments such as near-real-time data warehouses.
    Oracle Data Integrator includes a mechanism in KMs that allows us to use implicit cursors to perform an action for every row returned by a SQL SELECT statement. To do so, you simply need to specify the SELECT statement in the Source tab of the KM step and the action code in the Target tab. This is a brilliant feature, because it will save you from manually coding a cursor in PL/SQL and as a result will save you a lot of development time.
    Under the Command on Source tab, we specify the following:
    Figure 22
    We query the user_constraints table in the Oracle Data Dictionary to return a list of all the foreign key constraints on the target table (the Sales table in the SH target schema). In the Command on Target tab we can reference the target table and constraint name by prefixing them with the # sign and disable the foreign key constraints.
    Figure 23
    Note that I ran an extended trace against the implicit cursor. For each constraint on the Sales target table, Oracle Data Integrator sent an ALTER TABLE... statement to the database server. Because there are five foreign keys on the Sales table, we end up with five ALTER TABLE... statements.

    Make BIX Unusable

    In this step, we disable the bitmap indexes of the Sales fact table. This will speed up the loading of our fact tables. We only disable the bitmap indexes on those table partitions into which we load data. In our example, these will be the partitions for Q1 and Q2 1999.


    As in the previous step, we once more use an implicit Oracle Data Integrator cursor to implement this step. For the Command on Source tab we use a mix of SQL and Oracle Data Integrator substitution API methods to retrieve the distinct time_ids inserted into the staging table. Based on these time_ids, we can determine the partitions to which they relate in the target table. The odiRef.getTable("L","INT_NAME","W")substitution API method will retrieve the staging table for us.
    Figure 24
    In the Command on Target window, we specify the syntax to disable index partitions on the target table.
    Figure 25
    In the next couple of steps, we insert records into the target table, re-enable foreign key constraints, make bitmap indexes reusable, and disable tracing. For more details on these individual steps, I have made an export of the KM. (Recall that at the beginning of this article, you saw a link for downloading the IKM, with instructions on how to import it into Oracle Data Integrator.) Note that, in a real-world data warehouse scenario, we would have added a few more steps to the IKM, such as gathering statistics on the target table, handling and recycling constraint violations, doing surrogate key lookups, and so on.

    Create Interface

    An interface is an Oracle Data Integrator object that enables the loading of one target datastore (Oracle Data Integrator�speak for a table) with data transformed from one or more source datastores. The related object in Oracle Warehouse Builder is a mapping. However, thanks to the concept of reusable KMs, it is a lot quicker to create an Oracle Data Integrator interface than it is to develop an Oracle Warehouse Builder mapping.
    Before we start creating the interface, we need to import two more KMs: one for extracting the data from the source system (LKM), and a KM that will check for constraint violations. We will need both of them in our interface.
    To import a KM log in to Designer, click the Projects tab, and expand Knowledge Modules. Right-click Loading (LKM) and select Import Knowledge Modules. Browse to the Import Export folder (<odi home>\oracledi\impexp) and select LKM SQL to Oracle. Click OK.
    Figure 26
    Next we import the CKM. Go through the same steps as above and this time select CKM Oracle. Click OK.
    Figure 27
    Now we are finally ready to create the interface. Expand the EDW project and also the First Folder. Right-click Interfaces and select Insert Interface. According to the best practice guide, the naming convention for interfaces is Int.<TARGET_TABLE> <DETAILS>. We will name our interface Int.SALES FACT LOAD. Also set the Optimization Context to Development from the drop-down menu.
    Figure 28
    Change to the Diagram tab. From the Models tab, drag and drop the sales_source datastore from the Sales_Source model to the Sources area. Drag and drop the Sales table from the SH model to the Target Datastore area. You are then prompted to perform an automatic mapping. Confirm this with Yes.
    Figure 29
    Next make sure that no errors were detected in your mapping by clicking the Errors button.
    Figure 30
    Next click the Flow tab. In the screen that opens, click the SALES_SOURCE datastore and from the LKM Selection drop-down menu select the LKM SQL to Oracle.
    Figure 31
    Next click the Target+Staging Area. Select our IKM Oracle Append (Disable FKs & Indexes) from the IKM Selection drop-down menu. In the Option area at the bottom of the screen, highlight the Trace Option and set it to Yes. This will override the default value for tracing, and as a result tracing will be enabled in the execution of our interface.
    Figure 32
    Next click the Controls tab, and from the Select your KM drop-down menu select CKM Oracle.
    Figure 33
    We are now ready to execute our interface and test our KM. Click the Execute button, and in the pop-up menu that appears select Development as the Context. Finally, click OK.
    Figure 34
    To verify the execution status of our interface, we need to log in to the Oracle Data Integrator Operator module. In the Operator module, we can view execution statistics and errors from all angles and at all levels of detail. For example, from the execution stats below we can see that the step that took the longest to execute was Enable FK constraints. This took six seconds. We can also see that we inserted two records into the staging table and two records into the fact table.
    Figure 35



    Source:


    http://www.oracle.com/technetwork/articles/bethke-odi-090881.html

Tuesday, March 20, 2012

Oracle Data Integrator (ODI) - Frequently Asked Questions (FAQ)

1) What is Oracle Data Integrator (ODI)?
Oracle acquired Sunopsis in 2006 and with it "Sunopsis Data Integrator".

Oracle Data Integrator (ODI) is an E-LT (Extract, Load and Transform) tool used for high-speed data movement between disparate systems.

The latest version, Oracle Data Integrator Enterprise Edition (ODI-EE) brings together "Oracle Data Integrator" and "Oracle Warehouse Builder" as separate components of a single product with a single licence.


2) What is E-LT?
E-LT is an innovative approach to extracting, loading and Transforming data. Typically ETL application vendors have relied on costly heavyweight , mid-tier server to perform the transformations required when moving large volumes of data around the enterprise.

ODI delivers unique next-generation, Extract Load  and Transform (E-LT) technology that improves performance and reduces data integration costs, even across heterogeneous systems by pushing the processing required down to the typically large and powerful database servers already in place within the enterprise.


3) What components make up Oracle Data Integrator?
"Oracle Data Integrator" comprises of:

        - Oracle Data Integrator + Topology Manager + Designer + Operator + Agent
        - Oracle Data Quality for Data Integrator
        - Oracle Data Profiling


4) What is Oracle Data Integration Suite?


Oracle data integration suite is a set of data management applications for building, deploying, and managing enterprise data integration solutions:
  • Oracle Data Integrator Enterprise Edition
  • Oracle Data Relationship Management
  • Oracle Service Bus (limited use)
  • Oracle BPEL (limited use)
  • Oracle WebLogic Server (limited use)
Additional product options are:

  • Oracle Goldengate
  • Oracle Data Quality for Oracle Data Integrator (Trillium-based DQ)
  • Oracle Data Profiling (Trillium based Data Profiling)
  • ODSI (the former Aqualogic Data Services Platform)

5) What systems can ODI extract and load data into?

ODI brings true heterogeneous connectivity out-of-the-box, it can connect natively to Oracle, Sybase, MS SQL Server, MySQL, LDAP, DB2, PostgreSQL, Netezza.

It can also connect to any data source supporting JDBC, its possible even to use the Oracle BI Server as a data source using the jdbc driver that ships with BI Publisher


6) What are Knowledge Modules?

Knowledge Modules form the basis of 'plug-ins' that allow ODI to generate the relevant execution code , across technologies , to perform tasks in one of six areas, the six types of knowledge module consist of:
  • Reverse-engineering knowledge modules are used for reading the table and other object metadata from source databases
  • Journalizing knowledge modules record the new and changed data within either a single table or view or a consistent set of tables or views
  • Loading knowledge modules are used for efficient extraction of data from source databases for loading into a staging area (database-specific bulk unload utilities can be used where available)
  • Check knowledge modules are used for detecting errors in source data
  • Integration knowledge modules are used for efficiently transforming data from staging area to the target tables, generating the optimized native SQL for the given database
  • Service knowledge modules provide the ability to expose data as Web services
ODI ships with many knowledge modules out of the box, these are also extendable, they can modified within the ODI Designer module.


7) How do 'Contexts' work in ODI?

ODI offers a unique design approach through use of Contexts and Logical schemas. Imagine a development team,  within the ODI Topology manager a senior developer can define the system architecture, connections, databases, data servers (tables etc) and so forth.

These objects are linked through contexts to 'logical' architecture objects that are then used by other developers to simply create interfaces using these logical objects, at run-time, on specification of a context within which to execute the interfaces, ODI will use the correct physical connections, databases + tables (source + target) linked the logical objects being used in those interfaces as defined within the environment Topology.


8) Does my ODI infrastructure require an Oracle database?
No, the ODI modular repositories (Master + and one of multiple Work repositories) can be installed on any database engine that supports ANSI ISO 89 syntax such as Oracle, Microsoft SQL Server, Sybase AS Enterprise, IBM DB2 UDB, IBM DB2/40.


9) Where can I get more information on ODI?
The OTN Data integration home page : http://www.oracle.com/us/products/middleware/data-integration/index.html


10) Does ODI support web services?

Yes, ODI is 'SOA' enabled and its web services can be used in 3 ways:
  • The Oracle Data Integrator Public Web Service, that lets you execute a scenario (a published package) from a web service call
  • Data Services, which provide a web service over an ODI data store (i.e. a table, view or other data source registered in ODI)
  • The ODIInvokeWebService tool that you can add to a package to request a response from a web service 

11) Where does ODI sit with my existing OWB implementation(s)?
As mentioned previously, the ODI-EE licence includes both ODI and OWB as separate products, both tools will converge in time into "Oracle’s Unified Data Integration Product".

Oracle have released a statement of direction for both products, published January 2010:

http://www.oracle.com/technology/products/oracle-data-integrator/sod.pdf

OWB 11G R2 is the first step from Oracle to bring these two applications together, its now possible to use ODI Knowledge modules within your OWB 11G R2 environment as 'Code Templates', an Oracle white paper published February 2010 describes this in more detail:

http://www.oracle.com/technology/products/warehouse/pdf/owb-11gr2-code-template-mappings.pdf


12) Is ODI Used by Oracle in their products?

Yes there are many Oracle products that utilise ODI, but here are just a few:
  • Oracle Application Integration Architecture (AIA)
  • Oracle Agile products
  • Oracle Hyperion Financial Management
  • Oracle Hyperion Planning
  • Oracle Fusion Governance, Risk & Compliance
  • Oracle Business Activity Monitoring
Oracle BI Applications also uses ODI as its core ETL tool in place of Informatica , but only for one release of OBIA and when using a certain source system.

Future plans are to have ODI fully available through the OBIA offering.