OBIEE Interview Questions and Answers.
These questions are related to what previously known as Siebel Analytics is now known
8) To improve performance use aggregate tables and/or caching mechanism.
1) Repository stores the Meta data information. Siebel repository is a file
. With the extension . rpd.
modeling aggregate navigation, caching, and connectivity is stored in metadata
1. Physical – Represents the data Sources
2. Business – models the Data sources into Facts And Dimension
3. Presentation – Specifies the users view of the model; rendered in Siebel answer
4) If you have 3 facts and 4 dimension and you need to join would you recommend joining fact
In the BMM layer, create one logical table (fact) and add the 3 fact table as
o connection pool is needed for every physical database.
If we used n databases in development of rpd then we will n connection pools
o It contains information about the connection to the database, not the database
o An Alias table (Alias) is a physical table with the type of Alias.
o Alias Tables can be an important part of designing a physical layer. The
” To reuse an existing table more than once in your physical layer (without having
” To set up multiple alias tables, each with different keys, names, or joins
o To help you design sophisticated star or snowflake structures in the business
model layer. Alias tables are critical in the process of converting ER Schemas to
7) How do you define the relationship between facts and dimensions in BMM layer?
o Using complex join ,we can define relationship between facts and dimensions in
o We can do comparison for certain measures ( revenue.,sales etc.. ) for current
o Identify the time periods need to be compared and then period table keys to the
previous time period.
information.
o The fact tables needs to have year ago totals.
o To use the “Time series wizard”. After creating your business model right click
the business model and click on “Time Series Wizard”.
measures that it adds to the business model.
o The Time Series Wizard prompts you to select the period table used for the
o Select the column in the period table that provides the key to the comparison
period. This column would be the column containing “Year Ago” information in
the period table.
o Select the measures you want to compare and then Select the calculations you
want to generate. For ex: Measure: Total Dollars and calculations are Change and
Percent change.
o In the General tab of the Logical table source etc you can find “Generated by
o Then you can add these comparision measures to the presentation layer for your
reports.
o Yes. We can create new logical column in BMM layer.
logical column like Total cost.
o Now in fact table source,we have one option column mapping, in that we can do
o yes we can use physical join in BMM layer.when there is SCD type 2 we need
o yes we can.When we are doing complex join in BMM layer ,there is one option
o Level-base metrics means, having a measure pinned at a certain level of the
dimension. For Example, if you have a measure called “Dollars”, you can create a
“Level Based Measure” called “Yearly Dollars” which (you guessed it) is Dollars
for a Year. This measure will always return the value for the year even if you drill
down to a lower level like quarter, month… etc. To create a level based measure,
create a new logical column based on the original measure (like Dollars in the
example above). Drag and drop the new logical column to the appropriate level in
the Dimension hierarchy (in the above example you will drag and drop it to Year
o A LBM is a metric that is defined for a specific level or intersection of levels.
o Monthly Total Sales or Quarterly Sales are the examples.
o You can compare monthly sales with quarterly sales. You can compare
o You can enable logging level for individual users; you cannot configure a
logging level for a group.
o Set the logging level based on the amount of logging you want to do. In normal
operations, logging is generally disabled (the logging level is set to 0). If you
o level of 1 or 2. These two levels are designed for use by Siebel Analytics Server
administrators.
1. In the Administration Tool, select Manage > Security.
2. The Security Manager dialog box appears.
3. Double-click the user.s user ID.
4. The User dialog box appears.
5. Set the logging level by clicking the Up or Down arrows next to the Logging
o You can use variables in a repository to streamline administrative tasks and
modify metadata content dynamically to adjust to a changing data environment.
The Administration Tool includes a Variable Manager for defining variables.
16) What are different types of variables? Explain each.
1. Repository variables
2. Session variables.
A repository variable has a single value at any point in time. There are two types
:The values are refreshed by data returned from queries. When defining
a dynamic repository variable, you will create an initialization block or use a
preexisting one that contains a SQL query. You will also set up a schedule that the
Oracle BI Server will follow to execute the query and periodically refresh the
@{biServer.variables['VariableName']}.
For using repository variables in expression,
VALUEOF(“Dynamic Initialization Block Name”.“VariableName”).
Session Variables:
Session variables are created and assigned a value when each user logs on. There
are two types of session variables:
1.System
2.Nonsystem.
System variables are session variables that the Siebel Analytics Server and
siebel Analytics Web use for specific purposes. System variables have reserved
names, which cannot be used for other kinds of variables (such as static or
dynamic repository variables, or for nonsystem session variables).
When using these variables in the Web, prefix their names with NQ_SESSION.
For example, to filter a column on the value of the variable LOGLEVEL set the
filter to the Variable NQ_SESSION.LOGLEVEL.
Nonsystem variables.
A common use for nonsystem session variables is setting user filters.
For example, you could define a nonsystem variable called SalesRegion that would
be initialized to the name of the user.s sales region. You could then set a security
filter for all members of a group that would allow them to see only data pertinent
to their region.
When using these variables in the Web, prefix their names with NQ_SESSION.
For example, to filter a column on the value of the variable SalesRegion set the
filter to the Variable NQ_SESSION.SalesRegion.
Referencing session variable:
For displaying session variables,
we should use @{biServer.variables['NQ_SESSION.VariableName']}
For using session variables in expression,
we should use VALUEOF(NQ_SESSION.“VariableName”).
17) What are the cache management? Name all of them and their uses. For
Event polling table do u need the table in your physical layer?
o Monitoring and managing the cache is cache management. There are three ways
to do that.
o Disable caching for the system.(NQSconfig file),
Cashe persistence time for specified physical tables
Setting event polling table.
o Disable caching for the system.( NQS config file :
You can disable caching for the whole system by setting the ENABLE parameter
to NO in the NQSConfig.INI file and restarting the Oracle BI Server. Disabling
caching stops all new cache entries and stops any new queries from using the
existing cache. Disabling caching allows you to enable it at a later time without
losing any entries already stored in the cache.
o Cache persistence time for specified physical tables :
You can specify a cacheable attribute for each physical table; that is, if queries
involving the specified table can be added to the cache to answer future queries.
To enable caching for a particular physical table, select the table in the Physical
layer of the Administration Tool and select the option Make table cacheable in the
General tab of the Physical Table properties dialog box. You can also use the
Cache Persistence Time settings to specify how long the entries for this table
should persist in the query cache. This is useful for OLTP data sources and other
data sources that are updated frequently, potentially down to every few seconds.
o Setting event polling table :
Siebel Analytics Server event polling tables store information about updates in the
underlying databases. An application (such as an application that loads data into a
data mart) could be configured to add rows to an event polling table each time a
database table is updated. The analytics server polls this table at set intervals and
invalidates any cache entries corresponding to the updated tables.
o For event polling table ,It is a standalone table and doesn't require to be joined
with other tables in the physical layer.
18) What is Authentication? How many types of authentication.
o Authentication is the process by which a system verifies, through the use of a
user ID and password, that a user has the necessary permissions and
authorizations to log in and access data. The Oracle BI Server authenticates each
connection request it receives.
” Operating system authentication
” External table authentication
” Database authentication
” LDAP authentication
19) What is object level security?
o There are two types of object level security: Repository level and Web level
o Repository level : In presentation layer we can set Repository level security by
giving permission or deny permission to users/groups to see particular table or
column.
o web level: This provides security for objects stored in the OBIEE web
catlog,such as dashboards, dashboards pages,folder and reports you can only view
the objects for which you are authorized. For example, a mid level manager may
not be granted access to a dashboard containing summary information for an
entire department.
20) What is data level security?
o This controls the type and amount of data that you can see in a report. When
multiple users run the same report the results that are returned to each depends on
their access rights and roles in the organization.
For example a sales vice president sees results for all regions, while a sales representative for a particular region sees only data for that region.
21) What is the difference between Data Level Security and Object Level Security?
o Data level security controls the type and amount of data that you can see in a
reports.
Object level security provides security for objects stored in the OBIEE catalog,
like dashboards, dashboards pages,folder,and reports and Presentation layer of rpt
tables and columns.
22) How do you implement security using External Tables and LDAP?
o Instead of storing user IDs and passwords in a Siebel Analytics Server
repository, you can maintain lists of users and their passwords in an external
database table and use this table for authentication purposes. The external
database table contains user IDs and passwords, and could contain other
information, including group membership and display names used for Siebel
Analytics Web users. The table could also contain the names of specific database
catalogs or schemas to use for each user when querying data
o Instead of storing user IDs and passwords in a Siebel Analytics Server
repository, you can have the Siebel Analytics Server pass the user ID and
password entered by the user to an LDAP(Lightweight Directory Access Protocol)
server for authentication. The server uses clear text passwords in LDAP
authentication. Make sure your LDAP servers are set up to allow this.
23) If you have 2 fact and you want to do report on one with quarter level and
the other with month level how do you do that with just one time dimension?
o Using level base metrics.
24) If you want to create new logical column where will you create (in repository
or Request) why?
o I will create new logical column in repository. because if it is in repository, you
can use for any report. If you create new logical column in request then it is going
to useful in that request .
25) If you have dimension table like customer, item, time and fact table like sale
and if you want to find out how often a customer comes to store and buys a
particular item, what will you do?
o write a query as
“SELECT customer_name, item_name, sale_date, sum(qty)
FROM customer_dim a, item_dim b, time_dim c, sale_fact d
WHERE d.cust_key = a.cust_key
AND d.item_key = b.item_key
AND d.time_key = c.time_key
GROUP BY customer_name, item_name, sale_date”
26) If you want to limit the users by the certain region to access only certain
data, what would you do?
o using data level security.
o Siebel Analytics Administrator: go to Manage -> Security in left hand pane u
will find the user, groups, LDAP server, Hierarchy
What you can do is select the user and right click and go to properties, you will
find two tabs named as users and logon, go to user tab and click at permission
button in front of user name you have selected as soon as u click at permission
you will get a new window with user group permission having three tabs named
as general ,query limits and filter and you can specify your condition at filter tab,
in which you can select presentation table ,presentation columns ,logical table and
logical columns where you can apply the condition according to your requirement
for the selected user or groups.
27) If there are 100 users accessing data, and you want to know the logging
details of all the users, where can you find that?
Go to in Presentation Services navigate Settings>>Administration >> manage
sessions
28) How do implement event polling table?
o Siebel Analytics Server event polling tables store information about updates in
the underlying databases. An application (such as an application that loads data
into a data mart) could be configured to add rows to an event polling table each
time a database table is updated. The Analytics server polls this table at set
intervals and invalidates any cache entries corresponding to the updated tables.
29) Can you migrate the presentation layer only to different server
o No we can’t do only presentation layer. And ask him for more information and
use one of the above answers
o Create a ODBC connection in the different server and access the layer.
o Copy the Rpd and migrate it to other server.
30) How do you create filter on repository?
o Double click on table in source folder of BMM layer .Develop condition in
Where part of content tab.
31) How do you work in a multi user environment? What are the steps?
o Create a shared directory on the network for Multi-user Development (MUD).
o Open the rpd to use in MUD. From Tools->Options, setup the MUD directory to
point to the above directory.
o Define projects within the rpd to allow multiple users to develop within their
subject area or Facts.
o Save and move the rpd to the shared directory setup in point 1.
o When users work in the MUD mode, they open the admin tool and start with
o MUD ->Checkout to checkout the project they need to work on (not use the File
open as you would usually do).
o After completely the development, user checking the changes back to the
network and merge the changes.
32) Can you bypass Oracle BI server security ?if so how?
o yes you can by-pass by setting authentication type in NQSCONFIG file in
the security section as:authentication_type=bypass_nqs.
33) Where can you add new groups and set permissions?
o you can add groups by going to manage>security>add new groups> You can
give permissions to a group for query limitation and filter conditions.
34) What are the things you can do in the BMM layer?
o Aggregation navigation,level base metrics,time series wizard,create new logical
column,complex join..etc
35) What is Ragged hierarchy? and how do u manage it
o Ragged Hierarchy is one of the different kinds of hierarchy.
o A hierarchy in which each level has a consistent meaning, but the branches have
inconsistent depths because at least one member attribute in a branch level is
unpopulated. A ragged hierarchy can represent a geographic hierarchy in which
the meaning of each level such as city or country is used consistently, but the
depth of the hierarchy varies.
o For example, a geographic hierarchy that has Continent, Country,
Province/State, and City levels defined. One branch has North America as the
Continent, United States as the Country, California as the Province or State, and
San Francisco as the City. However, the hierarchy becomes ragged when one
member does not have an entry at all of the levels. For example, another branch
has Europe as the Continent, Greece as the Country, and Athens as the City, but
has no entry for the Province or State level because this level is not applicable to
Greece for the business model in this example. In this example, the Greece and
United States branches descend to different depths, creating a ragged hierarchy.
36) What is the difference between Single Logical Table Source and Multiple
Logical Table Sources?
o If a logical table in BMM layer has only one Table as the source table then it is
Single LTS.
o If the logical table in BMM layer has more than one table as the sources to it
then it is called Multiple LTS.
o Ex: Usually Fact table has Multiple LTS’, for which sources will be coming
from different Physical tables.
37) How do you bring/relate the aggregate tables into the OBIEE Logical layer?
o One way of bringing the Aggregate Tables into the BMM layer is by bringing
them as Logical Table sources for the corresponding Fact table.
o This is done by dragging and dropping the aggregate table into the
corresponding fact table. After doing that establish the column mappings and the
set the aggregation levels.
38) How do you know which report is hitting which table, either the fact table or
the aggregate table?
o After running the report, go to “Administration” tab and go to click on “Manage
Sessions”. There you can find the queries that are run and in the “View Log”
option in the Session Management you can find which report is hitting which
table.
39) Suppose I have report which is running for about 3 minutes typically. What
is the first step you take to improve the performance of the query?
o Find the sql query of the report in Admin->manage Session-> run the sql query
on toad ->read the explain plan output ->modify the SQL based on the explain
plan output.
40) Suppose you have a report which has the option of running on aggregate
table. How does the tool know to hit the Aggregate table and for that what
the steps you follow to configure them?
o Explain the process of Aggregate navigation
41) Have you heard of Implicit Facts? If, so what are they?
o An implicit fact column is a column that will be added to a query when it
contains columns from two or more dimension tables and no measures. You will
not see the column in the results. It is used to specify a default join path between
dimension tables when there are several possible alternatives.
o For example, there might be many star schemas in the database that have the
Campaign dimension and the Customer dimension, such as the following stars:
” Campaign History star. Stores customers targeted in campaign.
” Campaign Response star. Stores customer responses to a campaign.
” Order star. Stores customers who placed orders as a result of a campaign.
In this example, because Campaign and Customer information might appear in
many segmentation catalogs, users selecting to count customers from the targeted
campaigns catalog would be expecting to count customers that have been targeted
in specific campaigns.
” To make sure that the join relationship between Customers and Campaigns is
through the campaign history fact table, a campaign history implicit fact needs to
be specified in Campaign History segmentation catalog. The following guidelines
should be followed in creating
” segmentation catalogs:
” Each segmentation catalog should be created so that all columns come from
only one physical star.
” Because the Marketing module user interface has special features that allow
users to specify their aggregations, level-based measures typically should not be
exposed to segmentation users in a segmentation catalog.
42) What is aggregate navigation? How do you configure the Aggregate tables in
OBIEE?
o Aggregate tables store pre computed results, which are measures that have been
aggregated (typically summed) over a set of dimensional attributes. Using
aggregate tables is a very popular technique for speeding up query response times
in decision support systems.
o If you are writing SQL queries or using a tool that only understands what
physical tables exist (and not their meaning), taking advantage of aggregate tables
and putting them to good use becomes more difficult as the number of aggregate
tables increases. The aggregate navigation capability of the Siebel Analytics
Server, however, allows queries to use the information stored in aggregate tables
automatically, without query authors or query tools having to specify aggregate
tables in their queries. The Siebel Analytics Server allows you to concentrate on
asking the right business question; the server decides which tables provide the
fastest answers.
43) (Assume you are in BMM layer) We have 4 dimension tables, in that, 2
tables need to have hierarchy, then in such a case is it mandatory to create
hierarchies for all the dimension tables?
o No, its not mandatory to define hierarchies to other Dimension tables.
44) Can you have multiple data sources in OBIEE?
o Yes.
45) How do you deal with case statement and expressions in OBIEE?
o use expression builder to create case when…then.. end statement
46) what is query repository tool?
o It is utility of OBIEE Admin tool(IN RPD TOOLS>>QUERY REPOSITORY)
o allows you to examine the repository metadata tool
o for example: search for objects based on name,type.
o Examine relationship between metadata objects like which column in the
presentation layer maps to which table in physical layer
47) what is JDK and why do we need it?
o Java Development Kit (JDK), A software package that contains the minimal set
of tools needed to write, compile, debug, and run Java applets.
48) Oracle doesn’t recommend Opaque Views because of performance
considerations, so why/when do we use them?
o an opaque view is a physical layer table that consists of select statement. an
opaque view should be used only if there is no other solution
49) Can you migrate the presentation layer to a different server.
o No we have to migrate the whole web & rpd files
50) How do you identify what are the dimension tables and how do you decide
them during the Business/Data modeling?
o Dimension tables contain descriptions that data analysts use as they query the
database. For example, the Store table contains store names and addresses; the
product table contains product packaging information; and the Period table
contains month, quarter, and year values. Every table contains a primary key that
consists of one or more columns; each row in a table is uniquely identified by its
primary-key value or values.
51) Why do we have multiple LTS(Logical Table Sources) in BMM layer?What
is the purpose?
o to improve the performance and query response time.
52) what is the full form of rpd?
o there is no full form for rpd as such, it is just a repository file (Rapid file Database)
53) how do i disable cache for only 2 particular tables?
o in the physical layer, right click on the table>properties there we will have the
option which says cacheable
54) How do you split a table in the rpd given the condition. ( the condition given
was Broker and customer in the same table) Split Broker and customer.
o we need to make an alias table in the physical layer.
55) Rules for inheritance for permissions and privileges
o The following list describes the rules of inheritance for permissions and privileges:
a) Any permissions or privileges granted explicitly to a user override any
permissions or privileges inherited from the application roles or
Catalog groups to which the user belongs.
b) If a user belongs to two application roles or Catalog groups and both are granted
permissions, then the least restrictive permissions are given to the user.
For example, if one application role allows Open access and another allows
Modify access, then the least restrictive access would be granted;
in this example, Open access.
Note : The exception to this is if one of the two application roles or
Catalog groups is explicitly denied the permissions,
in which case the user is denied.
c) If a user belongs to Application Role X, and Application Role X is a member
of Application Role Y, then any permissions assigned to Application Role X
override any permissions assigned to Application Role Y. The same holds
true if X and Y are Catalog groups.
For example, if Marketing has Open permissions, Marketing Administrators,
which is a member of Marketing, can have Full Control permission.
d) If a Catalog group is specified along with an application role in the
Permissions dialog in Presentation Services, then the Catalog group takes
precedence.
For example, suppose that for a certain object, the BIAdministrator role
has Read-Only permission and the Admin Catalog Group has Full Control
permission.
If a user signs in who is a member of both the BIAdministrator role and the
Admin Catalog Group, then he is granted full access to the object.
e) Explicitly denying access takes precedence over any other permissions or
privileges.
56) Why Enable Users to Act for Others?
o You can enable one user to act for another user in Oracle BI Presentation Services.
When a user (called the proxy user) acts as another (called the target user),
the proxy user can access the objects in the catalog for which the target
user has permission.
Enabling a user to act for another is useful, for example, when a manager
wants to delegate some of his work to one of his direct reports or
when IT support staff wants to troubleshoot problems with another user's objects
57) What are the Proxy Levels?
oWhen you enable a user to be a proxy user, you also assign an authority
level (called the proxy level). The proxy level determines the privileges
and permissions granted to the proxy user when accessing the catalog objects of
the target user. The following list describes the proxy levels:
Restricted — Permissions are read-only to the objects to which the target user
has access. Privileges are determined by the proxy user's account
(not the target user's account).
For example, suppose a proxy user has not been assigned the Access to
Answers privilege, and the target user has. When the proxy user is
acting as the target user, the target user cannot access Answers.
Full — Permissions and privileges are inherited from the target user's account.
For example, suppose a proxy user has not been assigned the Access to
Answers privilege, and the target user has. When the proxy user is acting as
the target user, the target user can access Answers.
When you have enabled a user to act as a proxy user, that user can display
the Act As option in the global header of Presentation Services to select the
target user to act as, provided the Act As Proxy privilege has been set.
Before a proxy user can act as a target user, the target user must have
signed into Presentation Services at least once and accessed a dashboard.
58) Creating Session Variables for Proxy Functionality
o To authenticate proxy users, you must create the following two session
variables along with their associated initialization blocks. For both variables,
you must modify the sample SQL statement according to the
schema of the database.
PROXY — Use this variable to store the name of the proxy user.
Use the initialization block named ProxyBlock and include code
such as the following:
select targetId
from Proxies
where 'VALUEOF(NQ_SESSION.RUNAS)'= targetId
and ':USER'=proxyId
PROXYLEVEL — Use this optional variable to store the proxy level,
either Restricted or Full. If you do not create the PROXYLEVEL variable,
then the Restricted level is assumed.
Use the initialization block named ProxyLevel and include code such as
the following:
select proxyLevel
from Proxies
where 'VALUEOF(NQ_SESSION.RUNAS)'=targetId
and ':USER'=proxyId.
59) Things to check to improve OBIEE performance
o -Use the log level judiciously. Switch it off for all users except Administrator
-Use filter functions instead of case statements
-Avoid cast function:
-An opaque view (a physical layer table that consists of a Select statement)
should be used only if there is no other solution. Ideally, a physical
table should be created, or alternatively a materialized view.
A traditional database view is not needed because it is identical to
the opaque view.
60) These are some key log files and config files in OBIEE 11g.
These might be frequently accessed for debugging and checking
current parameter settings.
bi_server1.log & bi_server1-diagnostic.log
(bi_server1.out & access.log are also captured in this directory)
[$FMW_HOME]/user_projects/domains/bifoundation_domain/servers/
bi_server1/logs
nqserver.log & nqquery.log
[$FMW_HOME]/instances/instance1/diagnostics/logs/
OracleBIServerComponent/coreapplication_obis1
sawlog.log
[$FMW_HOME]/instances/instance1/diagnostics/logs/
OracleBIPresentationServicesComponent/coreapplication_obips1
NQSConfig.INI & DBFeatures.INI
[$FMW_HOME]/instances/instance1/config/
OracleBIServerComponent/coreapplication_obis1
instanceconfig.xml & credentialstore.xml
[$FMW_HOME]/instances/instance1/config/
OracleBIPresentationServicesComponent/coreapplication_obips1
config.xml
[$FMW_HOME]/user_projects/domains/bifoundation_domain/
config
system-jazn-data.xml
[$FMW_HOME]/user_projects/domains/bifoundation_domain/config/
fmwconfig
61)How to stop / start individual OBIEE 11g System Components
from the OPMN or command line
o Sometimes I have a configuration change to make or need to restart a
specific system component in one of my servers, here is a list of
stop / start commands for each of the system components:
location : <Middleware Home>\instances\instance1\bin
in command prompt then run the command
opmnctl stopall to stop services .
opmnctl startall to start services
opmnctl status to view the status af all services .
BI Service:
opmnctl stopproc ias-component=coreapplication_obis1
opmnctl startproc ias-component=coreapplication_obis1
Javahost:
opmnctl stopproc ias-component=coreapplication_obijh1
opmnctl startproc ias-component=coreapplication_obijh1
Presentation Service:
opmnctl stopproc ias-component=coreapplication_obips1
opmnctl startproc ias-component=coreapplication_obips1
Scheduler Service:
opmnctl stopproc ias-component=coreapplication_obisch1
opmnctl startproc ias-component=coreapplication_obisch1
Cluster Service:
opmnctl startproc ias-component=coreapplication_obiccs1
opmnctl stopproc ias-component=coreapplication_obiccs1
If you ran into this error after stopping and restarting anyone of the
above servers
error : BEA-141281> <unable to get file lock, will retry
just Navigate to the path :
<OBIEE HOME>/C:\Middleware\user_projects\domains\
bifoundation_domain\servers\AdminServer\tmp
and delete the “AdminServer.lok” file . and you should be fine
62)Order of Authentication OBIEE 11G
OBIEE Authentication against the identity store configured in Oracle
WebLogic Server Administration Console occurs first, and if that fails,
then initialization block authentication occurs.
BI Server populates session variables using the initialization blocks in the
desired order that are specified by the dependency rules defined in the
initialization blocks. If the server finds the session variable USER,
it performs authentication against an LDAP server or an external database
table, depending on the configuration of the initialization block with
which the USER variable is associated
