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.

Once in a while, there might be a need to change some of the passwords of the system users in OBIA. These could include the BIAppsSystemUser or the database passwords for the BIAPPS warehouse. BIAPPS 10.2 version users can follow the instructions as mentioned in this MOS tech note. OBIA 11g: How to change the system account passwords in BI Applications (Doc ID 2264922.1) If you are on older versions of BIAPPS, you can refer to this document instead OBIA 11g: Installing Oracle BI Applications 11.1.1.8.1 - Troubleshooting and Frequently Asked Questions [1997293.1]

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 id S

1.We need to populate male and female alternatively For Example: Above records need to Populate as below: Ans: 2.Find all the students whose marks greater than the average marks in each subject within a class For Example: Expected Output: Output: 3.      Find the data given below, and display the data with cumulative values CREATE TABLE MONTHLYVOLUME   (YEAR NUMBER(4),   MONTH CHAR(3),   SALES NUMBER(3)); / INSERT INTO MONTHLYVOLUME VALUES (2007,'JAN',11); INSERT INTO MONTHLYVOLUME VALUES (2007,'FEB',9); INSERT INTO MONTHLYVOLUME VALUES (2007,'MAR',7); INSERT INTO MONTHLYVOLUME VALUES (2007,'APR',5); INSERT INTO MONTHLYVOLUME VALUES (2007,'MAY',14); INSERT INTO MONTHLYVOLUME VALUES (2007,'JUN',6); INSERT INTO MONTHLYVOLUME VALUES (2007,'JUL',8); INSERT INTO MONTHLYVOLUME VALUES (2007,'AUG',17); INSERT INTO MONTHLYVOLUM