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: