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”