TechWare

We always recommend that customers use the ODI SDK to access the information stored in the ODI repository: the repository structure evolves from version to version, and the SDK shelters developers from these structural changes. This said, many developers still prefer to write their own SQL queries to read from the repositories directly, even if this may mean a rewrite of these queries with each repository upgrade. To help customers that prefer the SQL route, our support team has put together documents that describes the repositories structure for ODI versions 11.1.1.7, 12.1.2, 12.1.3 and ODI 12.2.1 To find these documents, login to  http://support.oracle.com , and look for  Doc ID 1903225.1  :  Oracle Data Integrator 11g and 12c Repository Description

If you look at the ODI code base for OBIA (all 11.X versions) before every SDE mapping step or SIL mapping steps. We will see two sets of steps. Initialize steps (Before the map is run) Indexes are dropped Finalize steps (After the map is run) Indexes are created Stats are gathered When we add a new index to a table in DW schema or when we delete an index, we do not have to add CREATE INDEX, DROP INDEX or GATHER STATS steps to the ODI code base. There are hooks in the code to take care of these steps irrespective of the number of indexes. Index maintenance steps can be achieved by a couple of simple steps in Designer without having to write a single line of code. Let's start by understanding what are the steps involved in adding an index to a table. Step 1: Create indexes on the database Step 2: Reverse engineer the model (typically named Oracle BI Applications) Customized Reverse-Engineering using OBIA specific RKM Step 3: Modify options on the index....

When you run an ODI ETL Load plan, it takes certain time. At times, customers would like to understand a split of the time taken by the load plan and analyze the top running maps to see if they can be optimized to run faster. In the first part in this series, we help you understand how you can easily analyze the time taken by the load plan and  identify the top running maps . Using ODI Studio, a customer can load at the Load Plan logs and see the time taken by each phase or if required, each step in the load plan. However since there is no easy option to sort by the time taken, it can sometimes be painful to look at the complete logs from ODI Studio to easily identify the top running maps. The following backend queries(to be run against the ODI repository schema) can help you see the same details that you see in ODI studio and yet analyze them better. Query to get the overall load plan run timings for an instance --List of Load Plan runs for a load plan instance i...

Oracle Data Integrator (ODI) is a very powerful product when handled correctly. Unfortunately, some mistakes may lead to dramatic results in Integration projects. This post compiles the Top 10 best practices that avoid the most common mistakes seen in integration projects made with Oracle Data Integrator. Best Practice #1 – Understand and Use Correctly Topology and Contexts The ODI topology and the contexts are one of the most powerful feature for running your design-time or run-time artifacts in various environments. In ODI, all developments as well as executions are performed on top of a  Logical Architecture  (Logical schemas, logical agent), that resolves, in a given  Context  to a  Physical Architecture  (real/physical source/targets data servers/schemas and ODI run-time agents). Contexts allow you to switch the execution of the artifacts from one environment (context) to another. Now, read the previous paragraph again. Make sure you got the ...

You can get any part of data and any sorted order you want with Analytic functions. Its syntax is OVER ((PARTITION BY … ORDER BY …)). Here is some samples with analytic functions I have used. First sample is getting number of (COUNT) employees by departments and ordered by (ROW_NUMBER ) departments. select     A. DEPTNO ,  row_number  ()   over   (partition   by   A. DEPTNO  order   by   A. EMPNO )  DEPT_EMP_ROWNO ,   A. EMPNO ,  row_number  ()   over   (order   by   A. DEPTNO ,   A. EMPNO )  EMP_ROWNO ,   count   (  *  )   over   (partition   by   A. DEPTNO )  DEPT_TOTAL_EMP_CNT ,   count   (  *  )   over   ()  TOTAL_EMP_CNT ,   count   (distinct   A. DEPTNO )   over   ()  TOTAL_DEPT_CNT from    SCOTT . EMP  A order   by ...