ODI Interview Questions and Answers

What is Oracle Data Integrator (ODI)?

Oracle acquired SUNOPSIS with its ETL tool called "Sunopsis Data Integrator" and renamed to  Oracle Data Integrator (ODI) is an E-LT (Extract, Load and Transform) tool used for high-speed data movement between disparate systems.

The latest version, Oracle Data Integrator Enterprise Edition (ODI-EE 12c) brings together "Oracle Data Integrator" and "Oracle Warehouse Builder" as separate components of a single product with a single licence.

Explain what is ODI?why is it different from the other ETL tools.

ODI stands for Oracle Data Integrator. It is different from another ETL tool in a way that it uses E-LT approach as opposed to ETL approach. This approach eliminates the need of the exclusive Transformation Server between the Source and Target Data server. The power of the target data server can be used to transform the data. i.e. The target data server acts as staging area in addition to its role of target database. 

while loading the data in the target database (from staging area) the transformation logic is implemented. Also, the use of appropriate CKM (Check Knowldege Module) can be made while doing this to implement data quality requirement.

What is E-LT? Or What is the difference between ODI and other ETL Tools?

E-LT is an innovative approach to extracting, loading and Transforming data. Typically ETL application vendors have relied on costly heavyweight , mid-tier server to perform the transformations required when moving large volumes of data around the enterprise. 

ODI delivers unique next-generation, Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs, even across heterogeneous systems by pushing the processing required down to the typically large and powerful database servers already in place within the enterprise.

Components of Oracle Data Integrator?

"Oracle Data Integrator" comprises of:

- Oracle Data Integrator + Topology Manager + Designer + Operator + Agent

- Oracle Data Quality for Data Integrator

- Oracle Data Profiling

What systems can ODI extract and load data into?

ODI brings true heterogeneous connectivity out-of-the-box, it can connect natively to Oracle, Sybase, MS SQL Server, MySQL, LDAP, DB2, PostgreSQL, Netezza.

It can also connect to any data source supporting JDBC, its possible even to use the Oracle BI Server as a data source using the jdbc driver that ships with BI Publisher

What are Knowledge Modules?

Knowledge Modules form the basis of 'plug-ins' that allow ODI to generate the relevant execution code , across technologies , to perform tasks in one of six areas, the six types of knowledge module consist of:

• Reverse-engineering knowledge modules are used for reading the table and other object metadata from source databases
• Journalizing knowledge modules record the new and changed data within either a single table or view or a consistent set of tables or views
• Loading knowledge modules are used for efficient extraction of data from source databases for loading into a staging area (database-specific bulk unload utilities can be used where available)
• Check knowledge modules are used for detecting errors in source data
• Integration knowledge modules are used for efficiently transforming data from staging area to the target tables, generating the optimized native SQL for the given database
• Service knowledge modules provide the ability to expose data as Web services

ODI ships with many knowledge modules out of the box, these are also extendable, they can modified within the ODI Designer module.

Does my ODI infrastructure require an Oracle database?No, the ODI modular repositories (Master + and one of multiple Work repositories) can be installed on any database engine that supports ANSI ISO 89 syntax such as Oracle, Microsoft SQL Server, Sybase AS Enterprise, IBM DB2 UDB, IBM DB2/40.

Does ODI support web services?

Yes, ODI is 'SOA' enabled and its web services can be used in 3 ways:

• The Oracle Data Integrator Public Web Service, that lets you execute a scenario (a published package) from a web service call
• Data Services, which provide a web service over an ODI data store (i.e. a table, view or other data source registered in ODI)
• The ODIInvokeWebService tool that you can add to a package to request a response from a web service

what is the ODI Console?
ODI console is a web based navigator to access the Designer, Operator and Topology components through browser. 

suppose I having 6 interfaces and running the interface 3 rd one failed how to run remaining interfaces?
If you are running Sequential load it will stop the other interfaces. so goto operator and right click on filed interface and click on restart. If you are running all the interfaces are parallel only one interface will fail and other interfaces will finish. 

what is load plans and types of load plans?
Load plan is a process to run or execute multiple scenarios as a Sequential or parallel or conditional based execution of your scenarios. And same we can call three types of load plans , Sequential, parallel and Condition based load plans. 

what is profile in ODI?
profile is a set of objective wise privileges. we can assign this profiles to the users. Users will get the privileges from profile

How to write the sub-queries in ODI?
Using Yellow interface and sub queries option we can create sub queries in ODI. or Using  VIEW we can go for sub queries Or Using ODI Procedure we can call direct database queries
in ODI.

How to remove the duplicate in ODI?
Use DISTINCT in IKM level. it will remove the duplicate rows while loading into target. 

Suppose having unique and duplicate but i want to load unique record one table and duplicates one table?
Create two interfaces or once procedure and use two queries one for Unique values and one for duplicate values. 

how to implement data validations?
Use Filters & Mapping Area AND Data Quality related to constraints use CKM Flowcontrol.

How to handle exceptions?
Exceptions In packages advanced tab and load plan exception tab we can handle exceptions.

In the package one interface got failed how to know which interface got failed if we no access to operator?
Make it mail alert or check into SNP_SESS_LOg tables for session log details.

How to implement the logic in procedures if the source side data deleted that will reflect the target side table?
User this query on Command on target Delete from Target_table where not exists (Select 'X' From Source_table Where Source_table.ID=Target_table.ID).

If the Source have total 15 records with 2 records are updated and 3 records are newly inserted at the target side we have to load the newly changed and inserted records
Use IKM Incremental Update Knowledge Module for Both Insert n Update operations.

Can we implement package in package?
Yes, we can call one package into other package.

How to load the data with one flat file and one RDBMS table using joins?
Drag and drop both File and table into source area and join as in Staging area.

If the source and target are oracle technology tell me the process to achieve this requirement(interfaces, KMS, Models)

Use LKM-SQL to SQL or LKM-SQL to Oracle , IKM Oracle Incremental update or Control append.

what we specify the in XML data server and parameters for to connect to xml file?

File name with location :F and Schema :S this two parameters

How to reverse engineer views(how to load the data from views)?
In Models Go to Reverse engineering tab and select Reverse engineering object as
VIEW.

Start/Stop OBIEE 11g Services in Linux

Starting Oracle BI 11g Services:

Start Admin Server
switch to the user who owns BI installation.
cd /u01/Oracle/Middleware/user_projects/domains/bifoundation_domain/bin
nohup ./startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=welcome1 &
tail -f nohup.out

start Node manager
cd /u01/Oracle/Middleware/wlserver_10.3/server/bin
nohup ./startNodeManager.sh > NOD.out &
tail -f NOD.out

Start managed Server
cd /u01/Oracle/Middleware/wlserver_10.3/common/bin
./commEnv.sh
cd /u01/Oracle/Middleware/user_projects/domains/bifoundation_domain/bin
./setDomainEnv.sh
nohup ./startManagedWebLogic.sh bi_server1 t3://servername:7001 > bis1_startup.log &
tail -f bis1_startup.log
nohup ./startManagedWebLogic.sh odi_server1 t3://servername:7001 > bis2_startup.log &
tail -f bis2_startup.log

start OPMN
cd /u01/Oracle/Middleware/instances/instance1/bin
./opmnctl startall
./opmnctl status

Stopping Oracle BI 11g Services

Stop OPMN
cd /u01/Oracle/Middleware/instances/instance1/bin
./opmnctl stopall
./opmnctl status

Stop Managed server
cd /u01/Oracle/Middleware/user_projects/domains/bifoundation_domain/bin
./stopManagedWebLogic.sh bi_server1 t3://servername:7001 weblogic welcome1
./stopManagedWebLogic.sh odi_server1 t3://servername:7001 weblogic welcome1

Stop Admin Server
cd /u01/Oracle/Middleware/user_projects/domains/bifoundation_domain/bin
sh stopWebLogic.sh

Cheers!
Bose

Adding Database Hints to your reports to improve OBIEE performance

1. There is no option to add database hints at the presentation level (i.e. Answers), however, you can set 'hints' in the repository for each physical table. You can do this by opening up the properties of the physical table and entering the hint (without '/*' and ' */') in the "Hint" field.

If you need different hints on the same physical table, then you can create 'aliases' of the physical table and model them separately.

2. In the connection pool, you can issue an 'ALTER SESSION' command to change a database parameter on log in.

3. You can use the 'Direct Database Request' functionality. That way, a user with the appropriate privileges can construct the request using SQL, and you can add hints to the SQL.  The drawback of this solution is that any user that needs to run the request will need to be granted the "Execute Direct Database Requests" privilege in the OBI Presentation Server Catalog ('Administration > Manage Privileges > Answers'). Also, the OBI Server Metadata "Security Manager > Permissions > Query Limits > Execute Direct Database Requests" option must be set to 'Allow' as well.

Happy Blogging!
Cheers!
Bose 

Trellis View in OBIEE 11g

What Is Trellis View:
Trellis view is a new view type that presents multi-dimensional data laid out in a set of cells in a grid, with each cell displaying a subset of data shown as numbers or as graphs. The trellis view allows users to display multiple views all at once for quick comparison, and to display data that reveals trends.

Types:
1. Simple Trellis View
2. Advanced Trellis View
 
Simple Trellis view:
Simple Trellis. A simple trellis displays a core inner graph multiplied across row sets and column sets, rendering many small multiples that are ideal for comparing and contrasting.

Step1:
Just drag some sample columns and measures in the criteria

Step2: 
Just check the results coming in table or Pivot Table View. In the result view go to Trellis -> select Simple

Step3:
Edit the default Trellis view and arrange the column like shown in the screenshot below

Step4:
Just click 'Done' with the above screen after the column arrangement and customization. we will get the below simple Trellis View. Save to finish the completing simple Trellis View. 

Advanced Trellis View:
 An advanced trellis displays a grid of small spark graphs that are ideal for monitoring trends and spotting patterns in a data set.
 
An simple trellis view is only allow us to create with single style for the selected columns and rows. But Advanced type is having the visualization option which will allow us to create separate visualization for each measure.

Go to New View -> Select Trellis -> Select Advanced. Arrange the columns that which way you want to see results.
Here I have selected Sparkle Bar for 'Actual Unit Price' and Number for Actual Target Price.
 

 Once with the editing save the View. The result should be like below:

Cheers!
Bose

How do we know that SMTP server port is open for BI Scheduler Configuration?

Introduction

Most of time we are struggling to to set up Email server for Oracle BI Scheduler. So This post describes to easily know that the SMTP server which we are trying to configure is open or not. If it open, we could able to setup server and can receive email but if it is blocked, we will get authentication error.

Many email clients and services use port 25 for SMTP to send out emails. However an ISP (Internet Service Provider) may block port 25 in order to prevent spamming by its customers. This article will show you how you can check to see if Port 25 is blocked on your network.

Before we start

Go to command prompt on your computer. If you need help with how to enable telnet in our local windows machine follow the below step to enable telnet in your local machine.

Step1: 

Go to Control Panel -> Programs -> Program and Features -> Select 'Turn windows featues on or off'

 Step2: 
We will be prompted to new windows, From there check 'Telnet Client' and click 'OK'

The configuration might take some minutes. 

Now telnet client is successfully enabled in your local machine to check the SMTP Server.

Instructions

1. Go to the command prompt and Type the following command: 

2. View Results:
   If Port 25 is not blocked, you will get a successful 220 response (text may vary).

If Port 25 is blocked, you will get a connection error or no response at all.

Tip: You can use this same technique to check if Port 587 is blocked as well.  Just use the following command:

OBIEE 11g Log Files and Configuration

·     Specifying Query Log Maximum Log Age for all log files

This post best describes what are all the log file we have to consider for OBIEE 11g. We could check and configure both online as well online.

Using Enterprise Manager:

----------------------------------------------------------------------------------------------------

The right way to check them is using the Enterprise Manager (EM) Console page.

EM Console. Login to the URL http://servername:7001/em and navigate to: 

Farm_bifoundation_domain-> Business Intelligence-> coreapplications-> Diagnostics-> Log Messages

You will find the available files:

• Presentation Services Log
• Server Log
• Scheduler Log
• JavaHost Log
• Cluster Controller Log
• Action Services Log
• Security Services Log
• Administrator Services Log

If we are looking at the ‘Log Configuration’ tab which will allow to do the following changes

·     Specify Maximum file Size for all log files

·     Specifying Maximum Log Age for all log files

·     Specifying Query Log Maximum file size for all log files

However, we could also directly browse them on the hard disk in the below specified locations. 

The log files for OBIEE components are under <OBIEE_HOME>/instances/instance1/diagnostics/logs.  Specific log files and their location is defined in the following table:

Log File Name	Physical Path
Installation log	<OBIEE_HOME>/logs
NQQuery Log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
NQSServer Log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
ServerName_NQSAdminTool Log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
ServerName_NQSUDMLExec log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
servername_obieerpdmigrateutil log (Migration log)	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
sawlog0 log (presentation)	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1
JH  log (Java Host)	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIJavaHostComponent\coreapplication_obijh
WebcatUpgrade Log (Web Catalog Upgrade)	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1
NQScheduler  Log (Agents)	<OBIEE_HOME>/instances/instance1/diagnostics/logsOracleBISchedulerComponent/coreapplication_obisch1
NQCluster  Log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIClusterControllerComponent\coreapplication_obiccs1
ODBC log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OracleBIODBCComponent/coreapplication_obips1
OPMN Log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OPMN/opmn
Debug log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OPMN/opmn
LogQuery log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OPMN/opmn
Service log	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OPMN/opmn
OPMN Out	<OBIEE_HOME>/instances/instance1/diagnostics/logs/OPMN/opmn
Upgrade Assistant log	<OBIEE_HOME>Oracle_BI1/upgrade/logs

Till Next Post, Cheers! Bose

Opening Catalog Manager in Windows for Linux OBIEE 11g

This post describes how to open Catalog Manager in Windows for the Linux OBIEE 11g. 
In this situtation, OBIEE 11g is installed in Linux machine and in some extent we could launch catalog manager in linux server but most of the time we could not have access to server and even if we have that to can access only via VNC or some of third party server. 

So If we want to open catalog through catalog manger, It is possible to open using our catalog manager object which is installed in our local windows machine. 

All we know the default location of the catalog manager

OS	Path
Windows	ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\ catalogmanager\runcat.cmd
Linux	ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/ catalogmanager/runcat.sh

Online / Offline

Many of the operations that you can perform using Catalog Manager are available in both online mode and offline mode. A few operations are available in only one mode or the other. Generally, the operations available in:

• Online mode are read-only operations and write operations that do not affect the entire catalog, such as setting permissions for an object.
• Offline mode include most of the operations available in online mode and write functions that affect the entire catalog, such as searching for and replacing catalog text

Note: Using catalog Manager in OBIEE client, we have the privilege to catalog only in online as we couldn't directly point physical to open in offline mode.

Here is the steps how to open repository online using Administration Tool in our local machine.

Step1: 
Start -> All Programs -> click 'Catalog Manager' from OBIEE Client

Step2:

Now Catalog Manager windows will be opened. From there click File -> Open Catalog

Step3:

In the next step, we will be prompted to enter catalog path, weblogic username and password to open catalog online. Enter all the detail and click 'OK'

Link: http://hostname:9704/analytics/saw.dll

User Name: Weblogic

Passowrd: Admin123

Now we could see the catalog is opened in catalog manager.

Step4:

As per my work I have assign privileges for some users to My Folder. So right click on 'My Folder' from the left side pane of catalog manager and select 'Permission'

Step5:

In the next sceen we could see what are the users having access to My Folder and from that selecting 'All' and then click 'List' will list out all the other user. so from the user list we could add some user to the left side pane and the click 'OK' to apply the changes.  

Now close catalog manager and check with privileges of the new users which you altered.

Cheers!
Bose

Opening Repository Online Using Administration Tool in Windows for Linux OBIEE

This post describes how to open repository online using OBIEE 11g Administration Tool in Windows for the Linux OBIEE. 
In this situtation, OBIEE 11g is installed in Linux machine and all we know that Administration Tool will not work in Linux. 

For the major development, we could develop reository through installed OBIEE Client in our local machine and could upload the same repository to linux server through enterprise manager. But for every minor changes, imports we no need to develop the same in local machine. We could directly open repository online using our Administration Tool from our local machine.

Here is the steps how to open repository online using Administration Tool in our local machine.

Step1:
Go to Control Panel -> Administration Tool -> ODBC Source  and then select 'System DSN' tab

 Step2:
Click 'Add' from the above select 'Oracle BI Server1' driver and then finish with the below screen

 Step3:
The above step prompt the 'Oracle BI Server DSN Configuration' window.
       1. Enter DSN Name
      2. Enter OBIEE Server Host Name or IP Address
and then click 'Next'

 Step4:
In the next step click Login ID and Password. After entering weblogic username and password click 'Next'

 Step5:
With the below screen just click 'Finish' and complete the DSN configuration.

 After sucessful creation We could see DSN Name which we have created under System DSN

Verfication:
Now Open Administration Tool from our OBIEE Client installed in our Local machine and click open online. Now we could see the DSN which we have created in ODBC Sources. 

Here enter Repository Password and weblogic Username and Passworkd

 We could see repository opened online in Administration Tool

Points to remember:
To create the connection we have to be in local network where OBIEE Server installed or have to be connected with network atleast through VPN.

in The Next Post read How to open catalog manager for the changes for the same Env

Cheers! 
Bose

Windows 64bit Informatica 9.0.1 Client Complete Installation

This article provides complete step by step instruction for installation and configuration of Informatica PowerCenter 9.x. This includes the installation Informatica Client.

Continuation of the previous Informatica 901 Server Installation post, we can take this forward to know how to install Informatica 9.0.1 client.

Step1: Double Click on “install.exe” 

Step2: Installation wizard Starts. Click “Next”. 

Step3: Installation wizard Starts. Choose the installation type as in the below image. Click “Next”. 

 

Step4: Installation Pre-requisites will be shown before the installation starts as below. Click “Next”.

Step5: Choose the client tools you need. Only PowerCenter Client is mandatory. Click “Next”. 

Step6: Choose the client installation directory. Click “Next”.  

Step7: Pre-installation summery will give the items installed during the installation process. Click “Install”. 

Step8: Installation Begins. It takes one or two minutes to complete this step. I

Step9: Installation is complete and you get the post-installation summary. Click “Done”, and you are done with Informatica 9.0.1 Client Installation.

With the above step you have successfully installed Informatica 9.0.1 Client installation successfully.

Happy Blogging!!

Bose