Ashwin Pittampally

We have noticed that in few interfaces, the NVL function is not working as expected when “_simple_view_merging” is set to be true in the Oracle source database. This problem is encountered if the column corresponding to the first function displays the correct results, but the columns corresponding to other functions return nothing. You can see […]

Gathering Statistics of a database schema before executing an ODI load plan will improve performance by helping the Optimizer to select optimal execution plan by referring to latest statistics, instead of stale statistics. We can gather statistics of Oracle database schema at a scheduled time of day using the DBMS_SCHEDULER and DBMS_AUTO_TASK_ADMIN packages, However, if […]

We have to regularly purge the Oracle Data Integrator (ODI) log files to reduce the size of the ODI work repository and improve the performance of the ODI studio. Before purging we can export the ODI log files and archive them. If we ever want to analyze the log history, we can re-import the logs from […]

When ODI repository is configured for external authentication against WebLogic Server’s embedded LDAP server, we have to place the ODI security files ‘cwallet.sso’ and ‘jps-config-jse.xml’ at appropriate locations. When external configuration is configured for ODI repository, ‘cwallet.sso’ and ‘jps-config-jse.xml’ files will be generated and placed at <MiddleWare_Home>/user_projects/domains/bifoundation_domain/odi-client-config/embedded Steps: Copy cwallet.sso and jps-config-jse.xml from: < MiddleWare_Home>/user_projects/domains/bifoundation_domain/odi-client-config/embedded […]

This post explains the process of using ODI Export Multiple Objects utility to export selected multiple ODI objects and importing them individually. ODI introduced a new feature ‘Smart Export’ from version 11.1.16, This feature automates the process of exporting/importing all objects related to your change, As BIAPPS OOTB ODI repository contains thousands of objects smart […]

This post will explain the process to raise the custom exception in ODI. Process: Create a variable to store the custom error message Create a procedure to read the error message from the variable and raise the exception. Create a package to set the value in the custom error variable and execute the raise exception […]

This post explains the process to unload ODI session log information Ex:  Session name, run-time, number of records inserted etc. from SNP_SESSION table to a delimited file and attach it to a Load Plan success notification email. This attachment can be used to identify the Session’s which are running longer than usual and causing performance issues, […]

In BIAPPS whenever a ODI package is executed it reads  W_ETL_LOAD_DATES table to verify if a record exists with that package name. If record found package is executed in incremental mode else Full load mode. In incremental mode package will update the last_executed_date of existing record in W_ETL_LOAD_DATES table and in Full load mode package will insert […]

This post explains the process to unload an error message from ODI repository log tables SNP_LPI_STEP_LOG, SNP_LPI_EXC_LOG to .txt file and attach it to a Load Plan failure notification email. Process: Create ‘Error_Notification’ package which uses ODISqlUnload utility to unload error message from log tables and write into text file, OdiSendMail utility to send email […]

Whenever a Full load is executed, all target tables are truncated and reloaded. This can result in missing historical data in Aggregate or Snapshot tables. Once erased, such historical data cannot easily be replaced. In order to preserve Account Payables (AP) and Account Receivables (AR) Snapshot data from aging invoice tables W_AP_AGING_INVOICE_A and W_AR_AGING_INVOICE_A, OOTB […]

This post will explain steps to Start and Stop ODI Server, ODI Agent in BIAPPS using Web Logic Server Admin Console. Log into WebLogic server http://<hostname>:7001/console Click on Environments on left side under Domain Strucuture

The most common problem with ODI incremental loads: whenever a prior incremental load fails, we have to manually inactivate the load plan schedule and update the agent schedule to stop executing the next load. Once the prior incremental load’s issue is fixed and its execution completed successfully, we have to activate the load plan schedule […]