Setting an ODI variable value to another ODI variable

Suppose I have two ODI variables var1 and var2, I want to assign var2 the value of var1.
Basically, my code should mean : var2 = var1;
But how do I do this in ODI ?
This one is a bit tricky since ODI does not allow writing '#variable' in the set variable option in package. It will throw a syntax error.
The trick here is : in the 'Refreshing' tab for var2 you should write the query like this
select #ProjectName.var1 from dual
Now, the query validation will fail, but dont worry, if you execute it by pressing the refresh button, it will execute successfully and you will be able to see the value in 'History' tab

Cheers !

  

ODI 11.1.1.7 : Works with JDK 7 only !!!

ODI 11.1.1.7 has been released and it can work normally with JDK 7 only. Although ODI will install successfully with JDK/JRE 1.6, the certification matrix says its compatible only with JDK 7.

I was trying to work with the ODI SDK of 11.1.1.7 and everything worked fine until I encountered 'NoClassDefFound' exception. Reason : I was runnning a JVM of 1.6,

As a caution, never try to install ODI 11.1.1.7 to work with Oracle EPM 11.1.2.2, since EPM 11.1.2.2 does not support JDK 7.

Better have a look at certification matrix before installing version 11.1.1.7

Best of Luck !!!

Doing Static Control Check in a ODI Package

This is something like finding a cheat code while playing video games.

The problem was that I wanted to check a source table for constraints (static control check if you may). 

Normally it is done using the 'Check' option on the data-store, but what if I want to automate it in a package and don't want to go right-clicking the datastores everytime ?

To know what static control is, search ODIExperts blog or John Goodwin's blog. No point in re-inventing the wheel :)

I just thought of dragging the table (data store) onto the package area and voila !!, got the option to perform the check
 Following are the constraints that I wanted to check in a package

Then I dragged the table directly onto the Package diagram 

If you see the properties pane, you will be able to select the 'Datastore check' which will basically execute the constraints check on the table 

ODI Myth #1 : Cannot be used for bulk loading or very high volumes of Data

I always get into a debate where people say that ODI doesn't do bulk integration or very high volume of data integration that smoothly as compared to its competitors. So is it really true ? I deal with this in sections

1. Native Essbase Data load vs. Oracle Data Integrator Load
Native Essbase load (using Load rules and EAS console) is faster than integration done using Oracle Data Integrator. But the amount of flexibilty that ODI provides in terms of mappings and transformations sometimes outweighs the lesser load time taken by native technique. Also, the use or selection of Staging area is crucial in deciding the load time. Using Sunopsis memory engine (Java heap in other words) as Staging drastically increases the load time, sometimes even failing the process. Staging area should always be a good relational database or if possible, the source itself.

2. Relational Databases
Having worked on integration projects involving pure relational tables and lots of data. I have come to know that there are few things that decide how good or bad the load times are going to be

1. Selection of Staging area
2. Deciding which transformation is executed where
3. Selection of the right Knowledge module
4. Table joins and lookups.
4 And ofcourse, how good the source, target and staging area relational databases are in terms of memory and processing speeds

The best part is that ODI gives a vast amount of flexibility when dealing the question of 'how to load data most efficiently'. It is through trial and errors that we arrive at the best possible solution. So, in my opinion, ODI is not that bad when it comes to loading high volumes of data.

Viewing Repository Connection Details in ODI

Recently I faced with a unique problem when trying to upgrade repositories from version 10 to version 11. For some reason the upgrade utility wasn't able to find the connected Work Repositories and the older version of ODI was messed up so it wasnt opening.

To view all the details of Schemas and Connections made, you need to look at the SNP_CONNECT table of master repository. It contains all the information of the physical connections that can be crucial when recovering corrupted repositories.

Attaching an image of SNP_CONNECT table (reversed in ODI)

OLTP vs.OLAP

Transactional Data : In a very vague sense, whatever data that acts as an evidence for showing something has happened is transactional data. It is also called Operational data. For example : A Car manufacturing company keeps a record of all the cars manufactured, all cars sold, inventories ordered, transportation etc in a set of tables which is collectively called a Database. 

This kind of data is used by Store managers, dealers to manage inventories, control production, track status of any order etc on a day to day basis.  Note the word used here is 'day to day basis'. In such a scenario, the smallest things are tracked. For example, if a customer has ordered a car, he can request for its status from the dealer, the dealer can then track the status from the plant manager, the plant manager can look at the status whether it the car is in paint shop or getting its engine. 

Now lets ask a simple question to ourselves : Will the CEO of this Automobile company ever look at these databases or Tables ???

Answer is NO. He is not interested in individual customers or cars. His job would be to look at the bigger picture, see how many cars the company is selling over a period of time, say a year or two years. His job is to observe the trends of customer, e.g. How the demand for SUVs is declining while Small Cars is growing, can we launch a new car ??, How much budget is to be allocated for advertising etc. etc.

 

This need for analysing data in a broader sense has grown over the years. Managers usually will look at data that might be as old as ten years (or more) in some cases. Data Mining is a concept that was born out of this need.

OLTP : In the older ages, Transactional Data was processed (processed : Insert/Update/Select) across geographies using customised Java Programs which would then be put across the web so that they could be accessed Online. This realization is often termed as Online Transaction Processing or OLTP. In its simplest form, buying anything on eBay or pulling out cash from ATM is OLTP. 

OLTP Requirements : OLTP  essentially needs inserts and updates be as fast as possible, in transactional systems, data is more inserted, updated and deleted and less 'Selected'.  Imagine your ebay purchase or ATM machine taking hours to process. Nobody would do online transactions then. 

Database design is very crucial keeping the insert and update frequency in mind. E-R diagrams/ Data Modeling methods are used for arriving at an efficient design.

 

OLTP Today : Many off-the-shelf OLTP applications are used by clients instead of designing custom Java applications, e.g. SAP ERP systems, Oracle's JD Edwards etc. This has drastically reduced the development and implementation time.

OLAP :  (Online Analytical Processing) Analytical data is often collected from the transactional systems. But does that mean you pull data from transactional system and put it as is with all the joins and constraints untouched ?? No, Whenever data is analysed, we use 'Select' query more and do insert/updates less. And faster selection of data means faster analysis and faster decision making. This need gives rise to the concept of Data Warehousing. A Warehouse is a place where all the data that is necessary for decision making is kept at one single place, and is arranged in such a way that the retrieval time is as minimal as possible. The best method to organize such a warehouse is to de-normalize all the tables of a transactional database and keep it in one huge single table.
How a Data Warehouse is organized is a different subject matter. But the universal truth is that a Warehouse should have minimum retrieval time else the design is a failure. Analysis means generating reports, trends etc from a set of data which can be used for making decisions. Before the arrival of Datawarehousing Applications custom Java programs were designed and built by companies.

OLAP Requirements : Lots and Lots of Space, Warehouse design must be such that 'Selection' is faster and Update/Insert may or may not be faster.

OLAP Today : Hyperion Essbase is one of the most widely used Data Warehouse application. Oracle 11g also has Warehouse building abilities. Again, using these off-the-shelf applications greatly reduce design and implementation time 

OLTP vs. OLAP

- Faster Insert/Update vs. Slower Insert/Update 

- Slower Selection vs. Faster Selection

- No need to keep track of Slowly changing Dimensions vs. Slowly Changing Dimensions are very important

- Normalized vs. De-normalized (although they are normalized to some extent)

Can Oracle Data Integrator be used for both Transactional and Analytical needs  ?

Yes, ODI can be used for Integration tasks of transactional systems as well as building Warehouses from Transactional systems. 

Importing Knowledge Modules in Synonym Mode

Although this is already covered in my previous post, I felt this thing needs special attention.
KMs cannot be migrated directly in Synonym mode, and importing in Duplication mode might have some problems if the interfaces using it are imported in Synonym mode. Simply because the ID of KM referenced in the interface and the ID of KM imported in Duplication mode wont match.
See the steps below to see how KMs are migrated in SYNONYM mode

1. Locate the Knowledge Module XML file on your file system and rename its prefix to “TRT_” instead of “KM_”

2. Right-click on any Folder of your Project and select “Import…> Import Procedure...” menu

3. Select “Synonym Mode INSERT_UPDATE”, go to the directory where your KM is stored on file system and select the KM, then click OK.

"An Internal Error occured whilst opening the Diagram"

Prior to version 11.1.1.6, this error occurs when you click on the mapping tab of an interface.
"An Internal Error occured whilst opening the Diagram"
Restoring an old backup wont work. The work around for this as suggested by Oracle (Note 1279132.1) is given as follows. 

1. Find  the internal ID of the Interface (click on the Version Tab of Interface)
2. Execute the query on the Work Repository

delete from SNP_TXT

where I_TXT in (
                 select I_TXT_XMI_DATA
                 from   SNP_DIAGRAM
                 where  I_POP = InternalID;

delete from SNP_DIAGRAM
where where I_POP = InternalID;

commit;

Best of Luck !!!

Migration Issues in ODI and the dark world of Internal IDs

Almost everyone who has tried to Export and Import ODI objects individually in two different environments has faced error of this kind
“Interface 101555 references a table ID (190555) that does not exist in you repository”
So what is this ID exactly ?
This comes straight from the horse's mouth (Read Oracle)

Each Object has a unique ID associated with itself.  This ID contains the Workrepository ID of the work repository to which it belongs to. At creation time, every work repository has an ID called the “Work

Repository ID”. This ID must be unique across all work repositories of an Oracle DI installation.

Objects IDs within a given work repository are constructed as follows:

[UniqueNumber][WorkID]where 

Where:

·   [UniqueNumber]  is a unique ID in the current work repository, issued from the table of IDs

                (SNP_ID)

·   [WorkID] is the ID of the current work repository

For example, if you create the 20th Interface in the work repository ID 229, its unique ID would be

20229. The next Interface in the same repository will have ID 21229. Every time a new object ID is

constructed the last unique number for this object is incremented in the table SNP_ID. In our example,

this table will contain 21 in the field related to the Interfaces unique number.

All objects created within the same repository have the same 3 last digits. This mechanism is

implemented for 2 reasons:

1. Avoid any ID conflicts when exporting and importing from one work repository to another

2. Understand the provenance of every object, simply by looking at its Internal ID. The 3 last

digits always refer to the repository where it was created

Object Dependencies:

ODI Objects are dependent on each other. For example, An Interface contains a Source Table and a Target table belonging to same or different models. Hence it is imperative to have the objects migrated in proper sequence. For example, if an Interface object is migrated without migrating the Source and Target Models (and the tables within it), the migration will fail. It is necessary to understand which objects are being used by the object that needs to be migrated.

Objects that are used by another object (e.g. Package containing multiple interfaces, procedures etc) are referred using the unique IDs of the component objects. For example, a Package will contain references of the component interfaces and procedures used in it. These references are nothing but the Internal IDs of the component objects. 

Retaining original IDs while migration:

Objects can be imported in Duplication mode or Synonym mode.

Duplication Mode : A new unique ID of the object is created when importing it. This method is not recommended since this method can cause conflicts while migrating ojects that are dependent on each other.

Synonym Mode: Original IDs are retained while importing. The advantage of this mode for importing is that there are no ID conflicts generated.

All objects are to be migrated in Synonym mode. The use of INSERT/UPDATE or INSERT_UPDATE modes are explained in latter section

Order of Migration:

Objects should be migrated (imported) in the following order assuming that Technology and Contexts have been imported already

1.     Model Folder

2.     Model

3.     Interface/Procedures

4.     Packages

5.    Scenarios 

Steps for Importing Objects:

Now that you have exported all relevant objects from the Target Repository, you can import them

safely in the new Repository. To do so, you can follow this procedure:

1.Connect to new Work Repository

2. Import the Model Folders in the order in which they appear in Target Repository. For example,

if you have exported 3 Model Folders “MFOLD1/MFOLD2/MFOLD3” without their child

components, you need to import MFOLD1 before MFOLD2 and so forth. Make sure to set the

import mode to:

a. “Synonym Mode INSERT” if your Model Folder does not exist in the new repository

b. “Synonym Mode UPDATE” if your Model Folder already exists in the new repository

c. DO NOT USE “Synonym Mode INSERT_UPDATE” if your Model Folder already

exists.

3. Import your Models in “Synonym Mode INSERT_UPDATE”. If a Model depends on another

Model, for example through Foreign Key references between datastores of the 2 models,

make sure to import the referenced Model first.

4. Import your Project that was exported without its child components and make sure to use:

a. “Synonym Mode INSERT” if the Project does not exist

b. “Synonym Mode UPDATE” if the Project already exists

c. DO NOT USE “Synonym Mode INSERT_UPDATE” if your Project already exists.

5. Import Project Folders in the order in which they appear in Bangkok’s Repository. For

example, if you have exported 3 Folders “/RELEASE2/EXIMBILL/STG_TO_TRG” without their

child components, you need to import “RELEASE2” before “EXIMBILL” and so forth. Make

sure to set the import mode to:

a. “Synonym Mode INSERT” if your Folder does not exist in the new repository

b. “Synonym Mode UPDATE” if your Folder already exists in the new repository

c. DO NOT USE “Synonym Mode INSERT_UPDATE” if your Folder already exists.

6. Import your Knowledge Modules as follow:

Import Export Best Practices for SCB 17/17

a. Locate the Knowledge Module XML files on your file system and rename their prefix to

“TRT_” instead of “KM_”

b. Right-click on any Folder of your Project and select “Import…> Import Procedure..”

menu

c. Select “Synonym Mode INSERT_UPDATE”,

d. go to the directory where your KMs are stored on your file system

e. Select your KMs, then click OK.

7. Import your Variables, Sequences and Functions in “Synonym Mode INSERT_UPDATE”

8. Finally, import your Interfaces in “Synonym Mode INSERT_UPDATE”

Generating Integration Reports from ODI

The most boring part of any project is documenting your work. In Integration projects it gets even worse, trust me ;)

But there is a nice feature ODI provides to make our life easy. See the image below.

Right click on FirstFolder->Print->Print(options)

By clicking on any of these options you can publish a pdf file of the object group.

To specify the directory and pdf viewer path go to

ODI->User Parameters-> Default PDF generation directory 

and change the value

To open the file automatically set the path of PDF Viewer

Cheers !

The odiRef.getObjectName() function

Many a times it happens that a column value in a table (source or target) needs to be looked up in another table which is not present in the interface as a source. And there is no way you can join it with your source table. So how do we dynamically pull a record using a select query on the target or say, filters ?

Enter the odiRef.getObjectName() function
This function returns the object name, in our case, the table name that we need to dynamically pull data from.
Example:
Suppose you have a table called LOOKUP_TAB (KEY|VALUE format) from which you need to pull a record and insert it in a target column
In the mapping, you can write a query directly as

(select "value" from <%=odiRef.getObjectName( "L" ,"LOOKUP_TAB","D")%> where "key"='the_key')
Make sure you put this query in braces

At runtime, the schema name is automatically prefixed to the table, now, which schema will it take ?
Answer is the staging schema, or if 'staging schema different from target' is unchecked, it becomes the target schema.
This function comes in very handy for lookups ... you can also hard-code the schema and table name while writing the query like this (select "value" from Staging_schema."LOOKUP_TAB" where "key"='the_key')

But everytime the staging schema name changes, you will have to edit the interface hence odiRef.getObjectName() is a good way to do that.

ODI Best Practices Link

Follow this link if you have any interview or want to know the best practices of ODI :)

http://www.oracle.com/technetwork/middleware/data-integrator/overview/odi-bestpractices-datawarehouse-whi-129686.pdf

How to test ODI Interfaces quickly and efficiently ?

Testing an ODI interface, especially when there are tens of tables joined at the source can get very tedious at times. Finding simple errors like missing parenthesis or missing expressions can lead to headaches or sleepless nights.
I am putting some of my experiences here, and suggesting a few ways to make life better.

1. Make sure Source data is not having whitespaces before you start developing, if source tables are not trustworthy, use the LTRIM and RTRIM functions

2. Unix based text files have different line feed characters than Windows based, always make sure you know the source.

3. Sometimes the data might contain hidden characters , example, when you retrieve a number 'value' say 10, it will show you 10, but use the function TO_NUMBER(value) and it will give you an error saying invalid number, why ?? because there is a LF character ASCII code 13 after 10 which is not visible to naked eye.

4. Do not develop the interface completely and test its working in one go, there are so many things that might have gone wrong, but ODI will give only one error that it encounters, best way is to run the interface after every mapping (or maybe two) and you will get the errors then and there, although time consuming, it works.

5. Use the Active Mapping checkbox often while testing, disable a mapping and check if interface runs, the enable the mapping and check what happens. This helps in tracking erroneous mappings faster.

6. Always create a copy (duplicate) when doing major changes to interface, we tend to forget what was done before

7. Set 'Delete Temporary Objects' to false, this helps in having a look at what happens behind the curtains, and its an effective way to find out errors

8. Run the code generated by ODI at runtime (in operator window) in SQL developer or similar tools, ODI will not give you the exact line where error is encountered, but SQL editors give.

Hope these pointers help, will add more as I experience

How many schemas can be used in an ODI interface ?

This question might sound strange, but my experience with people has led to explaining this fact in my blog.
So, tables are contained within a schema, for convenience sake, lets assume everything is done in Oracle SQL. I have two schemas as Source : Source1 and Source2, one staging : Staging and a target schema Target.

Case 1: Can I use tables in Source1 and Source2 joined by some clause as Sources ?
Yes, if tables are joined in source, it doesnt matter what schemas they belong to.
Staging can be set as target or defined as something other than target

Case 2: I need to use table1 from Source1 as source, I will need table2 from source2 while looking up a certain value while inserting in target but, essentially table1 and table2 wont be joined by any logical condition and Staging area is different from target
Now, things get a little tricky here.
Any table X that is referenced in ODI interface should satisfy one of the criteria to work successfully
1. It should be used as a source
2. If  X is used along with another table as Source, X and the other table should be joined
3. If X is not present in Source but needed for some reasons, the staging schema should be the same as the schema to which X belongs, and table X needs to be explicitly called with its schema name (e.g. Select * from StagingSchema.TableX ) and this mapping must be executed on staging area.
(In the next article, I will be explaining the use of 'odiRef.getObjectName' for this purpose)

Case 3: Source1 used as source, Source2 needed but not joined and Staging area same as target.
In this case, the staging schema is that of target i.e. Target
A workaround for this case is to bring the contents to Source2.Table2 into Target.TempSource2 (replicate the table of Source2 schema in Target schema and use it) But there is additional dependency of creating a table and pulling data into another one, which adds to the confusion

So, the moral of the story is : Whenever doing integrations make sure that Source Schemas are organized in such a way that they can be safely used without conflict, or, in other words, the datapoints must be designed smartly.

Cheers!