SAMPLE INTERVIEWS

Sample Interview 1:'

1.
In your previous project, what were your sources?
1.
we can tell
1.
oracle apps
2.
siebel
3.
Peoplesoft
4.
Jdedwards
5.
Mainframes ..etc
2.
Have you ever worked with MySequel or Essbase sources?
1.
you can tell yes or no
3.
If you have 1 fact and 4 dimension, which table would you import
first?
1.
Anything we can import
2.
If we are loading data using ETL then first we need to load
dimension tables then fact tables
4.
If you have two fact, can you join fact to fact.
1.
No .if we join fact to fact we wont get star schemas hence
we can not define content logical level
5.
In the logical layer, the logical join, what is the cardinality?
1.
It tells how the tables are related ,based on this obiee
understands fact and dimension tables
6.
How do you use the chronological key
1.
In case time based hierarchies it will do calculations based
on this key
2.
example : month level 12 +1 =1 and day level 31+1=1
7.
Cache management?
1.
Please refer class room notes
2.
8.
Have you used IBot?
1.
IBOTS and AGENTS are same ,please refer class room notes
9.
Have you ever installed and what environment?
1.
you can tell yes or no .but if you say YES you need to aware
of LINUX /windows server installation process
2.
http://rritec.blogspot.in/2014/09/obia-111181installation.html
10.
Have you ever did configurations on a linux environment?
1.
Yes or NO .if you say YES then need to answer steps of
configuration
11.
Say that you have a report that is running for long hour, let's
say 20 minutes, how do you debug it?

1.

we will take SQL query of the report and as for the query we
will apply aggregat tables /cache/hints/partition/log level/index
creation..etc
12.
What kind of security do you use?
1.
LDAP
2.
External table
13.
where you involved in code migration from one environment to
another?
1.
Yes .refer classroom notes deployment chapter
14.
Say you developed a code, how do you migrate the code changes to
production?
1.
using Catalog manger for more info refer deployment chapter
15.
Can you give me a brief background of your obiee experience.
1.
Refer project video of mine
16.
Can you explain to me about your previous project?
1.
Refer project video of mine

OBIEE INTERVIEW QUESTIONS

Handson01: Physical Layer

1.
What is connection pool
1.
Connection pool is a physical layer object ,is useful to store data base credentials like
database name, username, password..etc.

2.

2.

OBIEE oracle BI Server contacts data base using connection pool

How to import views/materialized views?

1.

While importing we need to enable views option

3.

What is the isolation level in a connection pool

It applies only For ODBC and DB2 gateways only. The value sets the transaction
isolation level on each connection to the back-end database. The isolation level setting controls
the default transaction locking behavior for all statements issued by a connection. Only one
option can be set at a time. It remains set for that connection until it is explicitly changed.
2.
The following options are available:
1.
Dirty read. Implements dirty read (isolation level 0 locking). This is the
least restrictive isolation level. When this option is set, it is possible to read uncommitted or dirty
data, change values in the data, and have rows appear or disappear in the data set before the
end of the transaction. Dirty data is data that needs to be cleaned before being queried to obtain
correct results (for example, duplicate records, records with inconsistent naming conventions, or
records with incompatible data types).
1.

Committed read. Specifies that shared locks are held while the data is
read to avoid dirty reads. However, the data can be changed before the end of the transaction,
resulting in non repeatable reads or phantom data.
3.
Repeatable read. Places locks on all data that is used in a query,
preventing other users from updating the data. However, new phantom rows can be inserted
into the data set by another user and are included in later reads in the current transaction.
4.
Serializable. Places a range lock on the data set, preventing other users
from updating or inserting rows into the data set until the transaction is complete. This is the
most restrictive of the four isolation levels. Because concurrency is lower, use this option only if
necessary.
2.

4.
How many connection Pools you used in your project
1.
It is a general question ,there is no fixed answer, however in each project we will have
minimum three connection pools
1.
One for importing tables and execute reports
2.
One for execute session initialization block sql queries
3.
One for usage tracking purpose
2.
Recommended number is between 2 to 10.but justification is important.
5.
To get data of another schema ,then what we need to enable in connection pool
1.
Required fully qualified names
2.
DB permissions

6.
1.

Can we create multiple connection pools under one database object

Yes, we can

7.
What is the difference between Duplicate and Alias in physical layer
1.
We will use Alias in physical layer to resolve below problems
1.
Self Join
2.
To maintain naming Standards in physical layer
3.
Resolve loops or closed paths
2.
Duplicate normally used in Physical layer to create a duplicate copy of existing Select
table (Opaque View) /stored Procedure object
8.
How many types of Joins are there in Physical layer?
1.
Two types
1.
Foreign Key join
1.
Equivalent to Equi join
2.
Accepts only = operator
3.
Most used join in physical layer
2.
Complex Join
1.
Equivalent to Non Equi join
2.
Accepts all operators like (=,<>,=>,,=.etc)
3.
Less used join in Physical layer
9.
Can we create self join

1.

By using Alias table , we can create self join.

10.
Can we create outer join in physical layer.
1.
In Physical Layer Outer join not possible ,however we can create outer join in BMM
layer .
11.
How to remove unused objects from physical layer
1.
It will remove objects, which we did not use in BMM layer
2.
Tools menu | Utilities | Remove unused objects from physical layer

12.
1.

How to synchronize physical layer objects

It is useful to synchronize RPD metadata with DB metadata

2.

Tools | Utilities | Update Physical Layer

13.
What is complex join, in which scenario we use it.
1.
If foreign key is not possible then we will go for complex join. For example between emp
and salgrade we will use complex join
2.
In place of non equi join ,complex join is used

14.
In foreign key join can we use other than equal to operator
1.
No
15.
What is Opaque view
1.
A SQL query or select statement is called as opaque view.
2.
If we need a new table then go for physical table (or) materialized view. In worst situation
go for opaque view.
3.
Opaque views are not supported by non relational db.(example MS Excel ,XML
,ESSBASE etc)
4.
If we need to call data base function then we have to go for Opaque View .

16.
Is deploying opaque is mandatory?
1.
No. but it is recommended due to below reason
2.
Without deploying view into database, if we use opaque view in reports then oracle BI
server needs to create complex queries. To avoid complex queries oracle recommended to deploy each
and every opaque view into database.
17.
When automatic joins will be created in physical layer.
1.
If we have primary key and foreign Key relation ship in database level and while
importing if we enable foreign key options then automatic joins will be created .
18.
What is Alias
1.
A virtual physical table (or) reference of physical table is called as alias.
2.
On one physical table we can create n no. of aliases.
3.
Whatever changes occurred in physical table that will immediately reflect in alias
(except Key definitions)
4.
It is mainly useful to resolve loops
19.
Can we create or delete a column from Alias.
1.
No
20.
How to eliminate Circular Join or loop or closed path.
1.
By Using Alias

Handson02:Business Model and Mapping Layer

1. What is the business model
1. Is the highest-level object in the BMM layer
2. Contains the business model definitions and the mappings from logical to physical
tables
3. Simplifies the physical schema
4. Captures how users think about their businesses using their own vocabulary
2. Can we create business model with single logical table, if no please let me know work
around.
1. Approach 1: Creating duplicate table in BMM layer
2. Approach 1: Creating alias and creating join between original table and alias
3. Can we create business model from multiple databases, did you created then tell me one
scenario
1. Yes
2. Scenario: when we want to compare the Actual data with target data and these data
coming from sources like oracle database and MS-Excel respectively
4. What is the Logical Table Source and when we will go for multiple logical tables
1. Logical table sources define the mappings from single logical table to one or more
physical tables.
2. A logical table contains one or more logical table source.

3. The mapping between physical columns and logical columns can manage using LTS

5.

6.
7.

8.
9.
10.

11.

4. By integrating multiple physical tables to LTS or By integrating multiple LTS to LT

we can convert snow flake schema into star schema
When we need to integrate multiple physical tables in LTS.
1. If we have snowflake schema then want to convert that into star schema then we will
go for multiple logical tables.
Did you get chance to map a logical column with multiple physical columns
1. Yes, If we want to configure aggregate tables then we need to do it .
What is logical join and why it is useful?
1. A: The join defined in the BMM Layer is called logical join
1. Logical join is used to defined driving table
2. Logical Join is used to define Inner, Right, Left and Full outer joins
3. Used to defined the cardinality
4. Logical join is useful to identify dimension and fact table
5. Logical join allows Oracle BI Server to make the best decision about the
exact physical SQL to generate based on the logical query path.
7.Can we have a table without logical join?
1. No
Can we have a dimension table without primary key?
1. No
Can we copy RPD objects within the RPD and from one RPD to another RPD?
1. Yes, we can copy RPD objects with in the RPD.
2. We can copy one RPD to another RPD but we need to copy corresponding layers
only i.e. (physical to physical, BMM to BMM and presentation to presentation)
What is Renaming wizard and which layers can be modified.

1. Renaming wizard is used to rename all objects ( example : tables and columns) of
BMM and Presentation layer
2.

Handson03:Presentation Layer
1. Can we create a subject area from multiple business models
1. No
2. In which version of OBIEE , presentation layer supporting hierarchy object
1. OBIEE 11G
3. What is Aliases
1. Keep track of any changes to Presentation layer objects

2.
4. What are nested tables
1. Prefix the name of the presentation folder to be nested with a hyphen and a
space. or type " - > "in description of the column
2. Place it after the folder in which it nests
5. Can we map a presentation column with multiple logical columns
1. we can not
6. A report as shown is using by business

Handson04:Testing / Validating Repository

1. What is global consistency check
1. Consistency check is one of the processes to check whether a repository is yielding
proper results in Answer reports. It checks whether;
1. All logical columns are mapped correctly to physical columns.
2. All logical dimension tables have a logical key.
3. Are there at least two logical tables in the business model (one is a logical
fact table and the other is logical dimension table with join condition).
4. There are no circular logical join relationships.
5. A presentation catalog exists for the business model.
2. How many messages are there in consistency check manager?
1. Upto OBIEE 11.1.1.5 version There are 3 messages in consistency check manager
such as
1. Errors
2. Warnings

3.

4.

5.
6.
7.
8.
9.

10.

3. Best Practices
2. In OBIEE 11.1.1.6 version onwards There are 3 messages in consistency check
manager such as
1. Errors
2. Warnings
In OBIEE how many servers are there
1. In OBIEE 11G we have 6 servers
1. Oracle BI Server
2. Oracle BI Presentation server
3. Oracle BI Java host server
4. Oracle Oracle BI Scheduler server
5. Oracle BI Cluster controller server
6. Web logic Server (newly added in OBIEE11G)
What is OPMNCTL and how many servers are integrated in it.
1. The Oracle Process Manager and Notification Server Control
1. Oracle BI Server
2. Oracle BI Presentation server
3. Oracle BI Java host server
4. Oracle Oracle BI Scheduler server
5. Oracle BI Cluster controller server
NQSCONFIG.INI and NQSERVER.log files are corresponding to which server.
1. OBI server
Can we load multiple RPDs in Oracle BI server
1. No, we cant
Can we upload RPD without using EM?
1. We cant
How many log levels are available
1. Totally 8 levels (0 to 7)
What are the log levels in development , test and production, you used in your project.
1. In Development and Test we will use log level as 2
2. In production we will use log level 0
All query logs will be stored in which file and what is that file physical path

1. NQQUERY.log file
2. C:\OBIEE_OBIEE\instances\instance1\diagnostics\logs\OracleBIServerComponent\
coreapplication_obis1
11. In log level 1 can we see physical SQL query.
1. No, we can not see the physical SQL query it shows logical SQL query.

2. If you set logging level as 2 then it gives all log information of level 1 along with
physical SQL query
12. Global consistency check is not throwing any errors, then can we assume RPD is correct
1. Syntax wise it is correct .Business rules wise we may have some mistake 1.

Handson05: .Adding Multiple Physical Table Sources

1. Table 1 and Table 2 have no direct join in physical layer. Can we map both in single LTS
No .
2. Detail table and aggregate tables will be added as two LTS to LT. Is it right?
Yes, It is right .
3. Using this concept we can convert Normalized/snow flake schema into denormalized / Star
schema,
is
it
right
?
Yes we can do using below methods
1. By integrating multiple physical tables into LTS
2. By integrating Multiple physical tables as multiple LTS to LT .

Handson06: Calculations
1. How many types of calculations available in BMM layer.
We can create three types of calculations
1. Creating calculation based on logical columns
2. Creating calculation based on physical columns
3. Creating calculation using Calculation Wizard
2. What
scenarios
automatically
handled
Below

two

scenarios handled by

using

calculation

wizard.

using calculation wizard

1. To
handle NULL
2. To handle Divide by Zero values
3. A calculation which is taking longer time then ,where we need to calculate

values

Request ETL team to calculate .

4. A calculation which is taking lesser time and reusable then ,where we need to calculate
If a calculation is reusable , simple and takes less run time then calculate in RPD .
5. A calculation which is taking lesser time and not reusable then ,where we need to calculate
If a calculation is taking less time and not reusable then do the calculations in the report only.
6. What is CAST() function?
This function changes the data type of an expression or a null literal to another data type.For
example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a
datetime literal). The following are the supported data types to which the value can be
changed:
CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE,
TIME, TIMESTAMP, BIT, BIT VARYING

Use CAST to change to a DATE data type. Do not use TO_DATE.

Example
CAST(hiredate AS CHAR(40))
Please refer help document for all four below evaluate functions

7. I have two columns in two tables, one column data type is varchar and another one is
number. Then can we create foreign key join in physical layer.
Yes By using CAST() function ,we can achieve it
8. What is evaluate function

EVALUATE
This function passes the specified database function with optional referenced columns as
parameters to the back-end data source for evaluation. This function is intended for scalar
calculations, and is useful when you want to use a specialized database function that is not
supported by the Oracle BI Server, but that is understood by the underlying data source.
The embedded database function may require one or more columns. These columns are
referenced by %1 ... %N within the function. The actual columns must be listed after the
function.
The ability to use EVALUATE is disabled by default. To enable support for this function,
change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion
Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition
for more information.
Syntax
EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])
Where:
db_function is any valid database function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result. Use this
parameter whenever the return data type cannot be reliably predicted from the input
arguments. However, do not use this parameter for type casting; if the function needs to
return a particular data type, add an explicit cast. You can typically omit this parameter when
the database-specific function has a return type not supported by the Oracle BI Server, but is
used to generate an intermediate result that does not need to be returned to the Oracle BI
Server.
column1 through columnN is an optional, comma-delimited list of columns.
Examples
This example shows an embedded database function.

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase
Functions
The following examples use the EVALUATE_AGGREGATE and EVALUATE functions. Note
that expressions are applied to columns in the logical table source that refers to the physical
cube.Use EVALUATE_AGGREGATE to implement custom aggregations. For example, you
may want to compare overall regional profit to profits for the top three products in the region.
You can define a new measure to represent the profits for top three products resulting in the
Logical SQL statement:
SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2),
%3)',
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic
The Oracle BI Server generates the following expression for the custom aggregation:
member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,
[Measures].[Profit]),[Measures].[Profit])'
Use the EVALUATE function on projected dimensions to implement scalar functions that are
computed post-aggregation. EVALUATE may change the grain of the query, if its definition
makes explicit references to dimensions (or attributes) that are not in the query.
For example, if you would like to see the Profits for the top five products ranked by Sales
sold in a Region, after creating the applicable measure, the resulting Logical SQL statement
is as follows
SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products,
Sales), Profits FROM SampleBasic
The Oracle BI Server generates the following expression to retrieve the top five products:
set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'
9. Please explain how to add or subtract date columns.

We cannot perform an arithmetic operation(plus ,minus

....etc) on a DATE or
TIMESTAMP. These are considered Non-numeric types. To calculate difference between two
dates, OBIEE provides a TIMESTAMPDIFF function. The syntax for using the function is:
TIMESTAMPDIFF(interval, first_date, second_date)
First_date and second_date have to be valid values in the TIMESTAMP type.
Interval is a specified value. Valid values for INTERVAL are:

SQL_TSI_SECOND
SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR
If the dates are not in a valid TIMESTAMP format, use CAST function to convert values into TIMESTAMP
type. See example below:
TIMESTAMPDIFF(SQL_TSI_DAY,CAST(VALUEOF("MY_DATE_COLUMN")as
CURRENT_DATE)

TIMESTAMP),

We can also use TIMESTAMPADD function to add an interval to a date.

Example:

TIMESTAMPADD(SQL_TSI_DAY,

10. Please

7,

TIMESTAMP

explain

Insert

'2011-11-01

12:00:00')

Filter

function.

Filter

dialog

Use this dialog to add a SQL FILTER function to a column formula in the "Edit Column Formula dialog:
Column
Formula
tab".
For

more
"Editing

information,

the

Formula

for

see:
a

Column"

Components
Filter

Function

Displays

the

SQL

FILTER(expr

Area

function

in

USING

the

form:

filter_expressions)

Where:
expr is an expression that contains at least one measure column. For example, the expression "sales +
1" is allowed if "sales" is a measure column. The expression "productid" is not allowed if "productid" is a
scalar
attribute.
filter_expressions is a Boolean expression (evaluates to TRUE or FALSE) and does not contain any
measure
columns.
Also,
this
expression
cannot
contain
any
nested
queries.
Note:
If you selected some text in the Formula area before clicking Filter, then it is incorporated in the
inserted SQL function, replacing the expression token (expr). For example, if you had selected "Sales
Measures".Dollars before clicking Filter, then the SQL function would look like this: FILTER("Sales
Measures"."Dollars"
USING
filter_expressions).
A

complex

filter

statement

might

replace

the

filter_expressions

token

as

follows:

FILTER("Sales Measures".Dollars USING ((Periods."Year" = '1999') AND ((Markets.District =

'CINCINNATI
DISTRICT")
OR
(Markets.District
=
'DENVER
DISTRICT'))))

Even

we

can

11. Please

use

in

explain

RPD

Lookup

Lookup

too

:)

function.
Functions

Multilingual schemas typically store translated fields in separate tables called lookup tables. Lookup
tables contain translations for descriptor columns in several languages, while the base tables contain the
data in the base language. Lookup is when a query joins the base table and lookup table to obtain the
translated values for each row in the base table. A LOOKUP function is typically used in the Business
Model and Mapping layer as an expression in a translated logical table column.
See the following sections in Oracle Fusion Middleware System Administrator's Guide for Oracle
Business
Intelligence
Enterprise
Edition
for
full
information:
"Supporting
"About

Multilingual
the

LOOKUP

Data"
Function

Syntax

Handson07: Hierarchies
1.
2.
3.
4.
5.

What is hierarchy
Types Of hierarchies
What is level based hierarchy
What is parent child hierarchy
What is unbalanced or ragged and skipped hierarchy

Syntax"