Saturday, May 26, 2012

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.

ODI Interview questions and answers

  Oracle Data Integrator (ODI) is  - product from Sunopsis acquired by Oracle in 2006 and now part of Oracle Fusion Middleware Family.
1. ODI is built on E-LT(Extract , Load and Transform) Architecture.
2. Oracle Data Integrator 10g (10.1.3.5.0) suite includes three products
a) Oracle Data Integrator
b) Oracle Data Quality and
c)  Oracle Data Profiling
3. ODI uses Database as ETL (Extract, Transform & Load) engine thus eliminates requirement of proprietary ELT engine
4. Oracle Data Integrator Enterprise Edition (ODIEE) is combination of ODI (Oracle Data Integrator) and OWB (Oracle Warehouse Builder)


 Q.Explain what is ODI?why is it different from the other ETL tools.

ODI stands for Oracle Data Integrator. It is different from another ETL tool in a way that it uses E-LT approach as opposed to ETL approach. This approach eliminates the need of the exclusive Transformation Server between the Source and Target Data server. The power of the target data server can be used to transform the data. i.e. The target data server acts as staging area in addition to its role of target databasel. While loading the data in the target database (from staging area) the transformation logic is implemented. Also, the use of appropriate CKM (Check Knowldege Module) can be made while doing this to implement data quality requirement.


Q.How will you bring in the different source data into ODI?

you will have to create dataservers in the topology manager for the different sources that you want.

Q.How will you bulk load data?


In Odi there are IKM that are designed for bulk loading of data.

Q.How will you bring in files from remote locations?


We will invoke the Service knowledge module in ODI,this will help us to accesses data thought a web service.

Q.How will you handle dataquality in ODI?

There are two ways of handling dataquality in Odi....the first method deals with handling the incorrect data using the CKM...the second method uses Oracle data quality tool(this is for advanced quality options).



Oracle Data Integrator (ODI) consists of following products
1. Repository - is relational Databases to store objects used/configured or developed by ODI. There are two type of Repository Master Repository (one and only one) and Work Repository (one or more)
a) Master Repository - There is only one Master repository and used to store security information, topology information (servers..) and versions of the Objects. All Modules (designer, operator, topology & security) have access to master repository.
b) Work Repository- Work related objects (project objects) are stored in Work Repository like Models, Projects and run-time information. There could be multiple work repository per installation and all linked to single master repository. Work Repository is accessed by Designer/Operator Module and run time agent.

2. Graphical Modules -
a) Designer (designer.sh|bat)- All project development takes place in this module and this is the place where database and application metadata are imported and defined.
b) Operator (operator.sh|bat) - usually to monitor production ODI instance and shows execution logs, rows processed and execution statistics
c) Topology Manager (topology.sh|bat)- To register servers, schema and agents in master repository.
d) Security Manager (security.sh|bat) - To manage user profiles and their access privileges.

3. Runtime Component / Scheduler Agent - scheduler agent coordinates execution of scenarios. Scheduler Agent retrieves code from execution repository and then requests database server, scripting engine or operating system server to execute that code.

4. Metadata Navigator (MN)- is Web (JSP/Servlet) application (available as oracledimn.war) that enables access to repository through Web Interface (Web Browser). MetaData Navigator (MN) requires Application Server and you deploy MN application oracledimn.war on pre-installed application server (Tom Cat, OAS, WebLogic). This is optional component.





ODI Architecture

ODI Architecture

  1. Understand ODI Architecture.
  2. Understand Components that make up ODI.
  3. Understand what are ODI repositories?
Architecture Overview:
What is Oracle Data Integrator?
  • Data integration product.
  • ODI is a development platform. (Business Rule Driven , E-LT approach)
  • Simple and faster.
  • Based on Metadata – Centralized Repository.
Oracle Data Integrator is an integration platform. Simply put, it is used to move and transform information across the information system. Oracle Data Integrator is also a development platform for integration processes. It is unique in two respects:
  • It uses an approach driven by business rules. In this approach, you focus your effort on the business side of integration, and not on the technical aspects.
  • It uses the E-LT approach. Oracle Data Integrator does not execute the integration processes itself at run time, but orchestrates a process which leverages existing systems.
Oracle Data Integrator is based on metadata. That is, descriptive information about the information system and its contents. This metadata is stored in a centralized metadata repository. These elements combined mean that, Oracle Data Integrator AIP enables “Simply Faster Integration.

ODI Architecture


The central component of the architecture is the repository. This stores configuration information about the IT infrastructure, the metadata for all applications, projects, scenarios, and execution logs. Repositories can be installed on an OLTP relational database. The repository also contains information about the Oracle Data Integrator infrastructure, defined by the administrators.
Administrators, developers, and operators use different Oracle Data Integrator Graphical User Interfaces to access the repositories.
Security and Topology are used for administering the infrastructure, Designer is used for reverse engineering metadata and developing projects, and Operator is used for scheduling and operating run-time operations.
At design time, developers work in a repository to define metadata and business rules. The resulting processing jobs are executed by the Agent, which orchestrates the execution by leveraging existing systems. It connects to available servers and requests them to execute the code. It then stores all return codes and messages into the repository.
It also stores statistics such as the number of records processed, the elapsed time, and so on.
Several different repositories can coexist in a single IT infrastructure. In the graphic in the previous page, two repositories are represented: one for the development environment, and another one for the production environment. The developers release their projects in the form of scenarios that are sent to production.
In production, these scenarios are scheduled and executed on a Scheduler Agent which also stores all its information in the repository. Operators have access to this information and are able to monitor the integration processes in real time.
Business users, as well as developers, administrators and operators, can get Web-based read access to the repository. The Metadata Navigator application server links the Oracle Data Integrator Repository to any Web browser, such as Firefox or Internet Explorer
ODI Components


The four Oracle Data Integrator GUIs—Designer, Operator, Topology Manager, and Security Manager, are based on Java. They can be installed on any platform that supports Java Virtual Machine 1.4, including Windows, Linux, HP-UX, Solaris, pSeries, and so on.
Designer is the GUI for defining metadata, and rules for transformation and data quality. It uses these to generate scenarios for production, and is where all project development takes place. It is the core module for developers and metadata administrators. Operator is used to manage and monitor Oracle Data Integrator in production. It is designed for production operators and shows the execution logs with errors counts, the number of rows processed, execution statistics, and so on. At design time, developers use Operator for debugging purposes.
Topology Manager manages the physical and logical architecture of the infrastructure. Servers, schemas, and agents are registered here in the Oracle Data Integrator Master Repository. This module is usually used by the administrators of the infrastructure.
Security Manager manages users and their privileges in Oracle Data Integrator. It can be used to give profiles and users access rights to Oracle Data Integrator objects and features. This module is usually used by security administrators. All Oracle Data Integrator modules store their information in the centralized Oracle Data Integrator repository.
ODI Run Time Components

At run time, the Scheduler Agent orchestrates the execution of the developed scenarios. It can be installed on any platform provided that it supports a Java Virtual Machine 1.4 (Windows, Linux, HP-UX, Solaris, pSeries, iSeries, zSeries, and so on).
Execution may be launched from one of the graphical modules, or by using the built-in scheduler. Thanks to Oracle Data Integrator’ E-LT architecture, the Scheduler Agent rarely performs any transformation itself. Normally, it simply retrieves code from the execution repository, and requests database servers, operating systems or scripting engines to execute it. When the execution is completed, the scheduler agent updates logs in the repository, reporting error messages and execution statistics.
The execution log can be viewed from the Operator graphical module. It is important to understand that although it can act as a transformation engine, the agent is rarely used this way in practice. Agents are installed at tactical locations in the information system to orchestrate the integration processes and leverage existing systems. Agents are lightweight components in this distributed integration architecture
Metadata Navigator
Metadata Navigator is a J2EE application that provides Web access to Oracle Data Integrator repositories. It allows the users to navigate projects, models, logs, and so on. By default, it is installed on Jakarta Tomcat Application Server.
Business users, developers, operators and administrators use their Web browser to access Metadata Navigator. Via its comprehensive Web interface, they can see flow maps, trace the source of all data and even drill down to the field level to understand the transformations that affected the data.
It is also possible to trigger and monitor processing jobs from a Web browser through Metadata Navigator

Components – A global view


By putting these pieces together, you now have a global view of the components that make up Oracle Data Integrator: the graphical components, the repository, the Scheduler Agent, and finally Metadata Navigator.
ODI Repository


The Oracle Data Integrator Repository is composed of a master repository and several work repositories. These repositories are databases stored in relational database management systems. All objects configured, developed, or used by the Oracle Data Integrator modules are stored in one of these two types of repository. The repositories are accessed in client/server mode by the various components of the Oracle Data Integrator architecture.
There is usually only one master repository, which contains the following information:
· Security information including users, profiles, and access privileges for the Oracle Data Integrator platform.
· Topology information including technologies, definitions of servers and schemas, contexts and languages.
Old versions of objects. The information contained in the master repository is maintained with Topology Manager and Security Manager. All modules access the master repository, as they all need the topology and security information stored there.
The work repository is where projects are worked on. Several work repositories may coexist in the same Oracle Data Integrator installation. This is useful, for example, to maintain separate environments or to reflect a particular versioning life cycle.
A work repository stores information for:
· Data models, which include the descriptions of schemas, data store structures and metadata, fields and columns, data quality constraints, cross references, data lineage, and so on
· Projects, which include business rules, packages, procedures, folders, knowledge modules, variables and so on
Execution, which means scenarios, scheduling information and logs
The contents of a work repository are managed with Designer and Operator. It is also accessed by the agent at run time.
When a work repository is only used to store execution information (typically for production purposes), it is called an execution repository. Execution repositories are accessed at run time with Operator and also by agents. An important rule to remember is that all work repositories are always attached to exactly one master repository
Example of Repository Setup


This diagram gives an overview of a typical repository architecture where development, testing and production are carried out in separate work repositories. When the development team finishes working on certain projects, it releases them into the master repository. The testing team imports these released versions for testing in a separate work repository, thus allowing the development team to continue working on the next versions. When the test team successfully validates the developed items, the production team then exports executable versions (called scenarios) into the final production work repository. This repository structure corresponds to a simple development-test-production cycle

ODI Architecture and approach

Oracle Data Integrator provides a new declarative design approach to defining data transformation and integration processes, resulting in faster and simpler development and maintenance. Based on a unique “E-LT” architecture