Using ODI Procedures to load data from Source to Target

We came across a scenario where we used ODI procedure to load data from source to target. The scenario was that we had to implement a very complex logic using sub queries in an ODI Interface. given the short timeline, we went ahead with the approach of loading the same using query in an ODI Procedure. We will discuss the same below,

An ODI Procedure has two parts, Command on Source and Command on Target,

• ODI Command on Source is where you write the source SQL query that you use to fetch data from source. 
• ODI Command on Target is where you write the insert script to load data into the target. 
• Load data from one source to another source (eg: Oracle to Teradata, MSSQL to Oracle, etc)
• The source side query can be a plain query. The number of columns in select though should match with the number columns in the insert script and their datatypes also should match. 

 eg: SELECT ROLL_NO,STUD_NAME FROM STUDENT

• The insert script should contain bind variables to select values from the source query, the insert script for the above source query would be, 

  INSERT INTO W_STUDENTS_D VALUES (:ROLL_NO,:STUD_NAME)

• Extremely inefficient compared to interfaces.
• Never preferred over interfaces. Interfaces are always faster as ODI follows all standards while generating queries. Preferred for loading of tables with less volumes of data.'
• Row by row processing happens and hence it takes longer time for data load. 

Typically the errors you get are either SQL syntax errors, Data type mismatch, column size errors, column order mismatch errors. 

All about Oracle Data Integrator(ODI) Variables

ODI variable is an ODI object that can be used to store values of string, date or number datatypes. Variables can be both static and dynamic, they can be updated at run-time using a query. These variables can be passed to other ODI objects like interfaces or procedures as filters or as column values.

• Can store String/Date/Number values.
• Values are updated during run-time.
• Values can be passed to other ODI objects like Procedures and Interfaces.
• Can either be Static/Dynamic in nature. 
• Dynamic ODI variables can be refreshed with a SQL query executed against a source database.
• Variables are commonly used for filter conditions, case conditions etc in interfaces, procedures and packages.

The scope of an ODI variable is two, 

Global: The variable can be called across projects in ODI repository.

Project: The scope of the ODI variable is limited to the particular project. 

There are three attributes to an ODI variable, 

1. Name
2. Datatype
3. Action

Action determines how values are to be stored in an ODI variable, The action types are 

Persistent: Values are stored for a particular session

Last Value: Stores the last value. 

Historize: Stores current the value and the others in history. Very useful if your variable values change frequently over time. Helps in debugging. 

An ODI variable can be called using the below syntax,

#PROJECT_NAME.Variable_Name

ODI used scenario examples, 

1. Imagine you are passing a date value dynamically from database to fetch records for ETL load. Instead of using a sub query filter, you can fetch the date value to a variable and use it in all your procedures and interfaces. 
2. Another scenario is storing JDBC url. You might be using JDBC urls at different places in an ODI instance. If hardcoded at each place, it would be hard to find and map the change wherever the same JDBC url has been used. Instead you can save the JDBC url as an ODI variable and then use the variable name wherever needed in topology. 

Oracle has a pretty neat document on variables. 

ODI Performance Tuning - Array fetch and Batch update size parameters

There are two parameters in Oracle Data Integrator topology that can be tweaked to improve performance of loading. These two parameters are, 

• Array Fetch size 
• Batch Update size

The Array Fetch size parameter can be tweaked for the source data server. This defines the number of records read from the source at a time and this data is stored in the agent. 

The Batch Update size parameter may be tweaked for the target data server. This defines the number of records fetched in one batch to be written to be server stored in the agent. 

When you increase number of records per batch, the time taken to read and write the total records will be less. This would mean you have lesser batches in total, hence improving performance. Since more data is stored in RAM, a good RAM size would be a necessity. 

Oracle recommends the following, 

• For good networks with little latency values less than 30 can be kept. However with a poor network the parameters have to be set as greater than 100. 
• The typical values as per oracle is between 50 and 500. 

Recommendation from Oracle ODI Performance tuning documentation. 

Here is a very interesting read on how this parameter if optimally set improves performance. 

ODI - Different schemas for Target and Staging(Work) Explained

It's a very common practice to keep the ODI Target and Work Schema to reside in one database. All the temporary objects created by the Knowledge modules are thus created inside the schema as the Target data store. Is this a good approach ? 

It is Oracle recommended to keep the temporary objects created by the Knowledge Modules in a separate schema rather than keeping them along with the target schema. A dedicated work schema would help you organize and distinguish your temporary objects from actual target tables. Bringing the temporary objects under one schema means that you can easily drop the objects from the work schema. 

Points to remember

• Always better to define Work Schema separately in the Topology rather than for each Interface. 

Work Schema definition in Topology.

• Always define the work schema separately from target schema for ease of organizing and managing your work schema.

You can see it here in this Oracle Data Integrator Best Practices - White Paper that clearly says to define a dedicated schema for Work/Staging.

ODI 11g - Incremental Update load using Surrogate Key [Solved]

In one of our projects we had a scenario where we had to load data from an Oracle source to an Oracle target. We faced a lot of issues during the process. Then we came across Himu's blog post on the same, which saved us a lot of hours and frustration.

Below are some key pointers to note when using Incremental Update.

• Do not make the Surrogate Key as Primary key in your Target Table.

• Make the Natural Key as the Primary Key. 

• Check Insert only for Surrogate Key and Natural Key

• Disable 'Check not null' key on Mapping editor for Surrogate Key.  

ODI Mapping Quick edit screenshot - Refer for first four points

In the above diagram ROW_WID is the surrogate key and ROW_ID the natural key.

• Make the Natural Key as the Update Key in flow tab.

This worked for us. Let us know if this helps. 

Do check out the Mhimu's blog post at https://mhimu.wordpress.com/2009/05/04/odi-incremental-update-and-surrogate-key-using-database-sequence/

ODI 11g Standard Sequence not Incrementing [Solved]

Issue: ODI 11g Interface with ODI sequence to generate surrogate key does not increment correctly. As seen in the screenshot below.

ODI Standard Sequence

The issue is that when you try to use the Standard Sequence it only populates one number in the column as shown below,

ODI Sequence increment issue - Illustrated.

Cause: The cause of the issue was that the agent was not processing target row by row.

Resolution:

The resolution is to follow a set of guidelines as follows,

• Select an IKM which supports row by row processing using the ODI agent. We used the IKM SQL to SQL Control Append.

Change to an IKM that supports row processing by Agent.

• Set Flow Control attribute to False in the IKM.
• Make sure the column populated with Sequence is NOT checked for NOT NULL condition. 

This worked well for us. Try out and let us know. 

Note: The best and optimal way is to use a native database sequence. Even ODI documentation recommends the same. 

[ODI 11g] Check ODI Installables is 32bit or 64bit version

Scenario: We were trying to install OBIA Apps and instead of installing a 64bit version of ODI 11g, a 32bit version was installed. We had a tough time identifying the version of the ODI Installables from the filename.

So this is how we understood which version of ODI we were trying to install,

During the initial step of setup where you have to give the JDK path, If your setup.exe file proceeds when you give a 32 bit JDK, then your ODI is 32bit and if it works with only 64bit JDK then it's a 64bit version of ODI.

Another way to identify the version of ODI 11g version is to check where the Oracle Inventory log files are located at. If the log files are under Program Files/Oracle/Inventory folder then the installation is 64-bit, In the other case if the installation is 32-bit then the log files would be under Program Files(x86)/Oracle/Inventory. 

 ODI 11g Installation Log file location displayed during configuration.

Oracle BI Applications hardware requirements document

Scenario: We were looking out for a document to get the minimum recommended hardware configuration for OBIA 11g. Since this was for a demo purpose we required to install OBIA Apps and target database/data warehouse in the same node. 

This is from a technical note we came across in Oracle Support.

OBIA Apps 11g

Target Database

Link to the technical note: - https://goo.gl/M38toV 

[Solved] ODI-26039 - java.sql.SQLException: Object already exists

Scenario: Creating an XML Data Server in ODI Studio > Topology > Techonologies > XML and testing connection twice or deleting and creating the same XML Data Server in that same ODI Studio session

Error Description: ODI-26039 Connection Failed. java.sql.SQLException: object name already exists.

ODI Error Object Name Already Exists

Cause: This is because the first time you created the XML Data Server or tested the connection ODI created and stored the objects for this particular XML file in it's JVM.

Resolution: Well this is embarrassing. Hope ODI resolves this in the future. You have to close ODI Studio and open a new session and try again.

ODI XML Data server Successful connection

Courtesy: oracle.developer-works

[ODI 11g] [Solved] Error 26178 Unable to Connect to Repository User

Scenario: Creation of Master Repository in ODI and have to create a connection to the database to connect to the Master Repostiory schema.

Error Description: ODI-26178: Unable to connect to the Repository user. Listener refused the connection with following error. ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Connection Error at ODI Repository Creation

Cause: This is either because you don't have an SID with the same name in your database server or you are trying to connect with a service name.

Resolution: Check whether the string after 1521: in our case as you see in the screenshot above is orcl.0.2.15. Now go to app/product/dbhome/network/admin and open the tnsnames.ora check the connection detail and check whether this particular string is the SID or the Service Name. This was the service name in our case and if it's a Service Name then you have to modify your connection string as below,

jdbc:oracle:thin:@HOSTADDRESS:PORTNO/SERVICE_NAME

See that instead of PORTNO:SERVICE_NAME we have used PORTNO/SERVICE_NAME. Now try connecting again you will see that the connection proceeds.

[ODI 11g] AppCrash error during ODI Installation [Solved]

We were trying to setup a VM ODI Instance on a Windows 2008 Server R2 when we came across this error below.

APPCRASH error - ODI Installation

I was at the step where I run try to run the setup.exe file from command prompt followed by the location for JDK. See below

Command Prompt command to run setup.exe

Cause: This is because the Java folder path has spaces in between.

Resolution: This can easily be resolved by moving the jdk folder to a path without spaces, simply put it outside the program files folder to C: Drive

If the first command in the above CMD doesn't work for you try out the second. 

[ODI 11g] Create Master Repository in ODI 11g

Note: Master Repository can be created both manually and by running RCU(Repository creation utility)

SQL Script to create Database Schema

CREATE USER SNPM1 IDENTIFIED BY ADMIN123 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

GRANT CONNECT, RESOURCE TO SNPM1;

COMMIT;

Creating Master Repository from ODI

Provide details for the newly created schema and also provide a user with DBA privileges. Use a duplicate user with DBA privileges and not SYS user.

 

Test the connection and go to next screen,

Then in the next proceed with Internal password storage,

Click Finish. 

ODI will then start creating your work repository,

Log is also available of the creation process. 

After the process is complete log in and test master repository connection,