Define repository in terms of OBIEE?
Repository stores the Meta data
information. The extension of the repository file is “.rpd”. With
OBIEE Server, all the rules needed for security, data modeling, aggregate
navigation, caching, and connectivity is stored in metadata repositories. Each
metadata repository can store multiple business models. OBIEE Server can access
multiple
repositories
Repository is divided into three
layer,
1. Physical – Represents the data Sources
2. Business – model the Data sources into Facts and Dimension and
apply business logic
3. Presentation – Specifies the user’s view of the data rendered in OBIEE
answers client
What is the end to end life cycle of
OBIEE?
OBIEE life cycle:
1. Gather Business Requirements
2. Identify source systems
3. Design ETL to load data to the Data Warehouse
4. Build a repository
5. Build dashboards and reports
6. Define security (LDAP or External table)
7. Based on performance, decide on aggregations and/or caching mechanism.
8. Testing and QA.
If you have 3 facts and 4 dimension
and you need to join would you recommend joining fact with fact? If no than
what is the option? Why you won’t join fact to fact?
Instead of joining fact with fact we
can create one logical table (fact) and add the 3 fact tables as logical table
source in the BMM layer.
What is connection pool and how many
connection pools did you have in your last project?Connection pool is needed
for every physical database.
It contains information about the connection to the database, not the database
itself.
Can use either shared user accounts or can use pass-through accounts
We can have multiple connection pools for each group to avoid waiting
What is the purpose of Alias Tables?An
Alias table is a physical table with the type of Alias. It is a reference to a
physical table, and inherits all its column definitions and some properties
from the physical table. A logical table source shows how the logical objects
are mapped to the physical layer and can be mapped to physical tables, stored
procedures and select statements. An alias table can be a reference to any of
these logical table source types.
Alias Tables can be an
important part of designing a physical layer. The following is a list of the
main reasons to create an alias table:
1. To reuse an existing table more than once in your physical layer (without
having to import it several times)
2. To set up multiple alias tables, each with different keys, names, or joins
3. 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 Dimensional Schemas.
How do you define the relationship
between facts and dimensions in BMM layer?Using complex join we can define
relationship between facts and dimensions in BMM layer.
Did you create any new logical
column in BMM layer, how?Yes. We can create new logical column in BMM layer.
Example: Right click on fact table> select new logical column>give name
for new logical column like Total
cost.
Can you use physical join in BMM
layer?Yes we can use physical join in BMM layer.
Can you use outer join in BMM layer?Yes
we can. When we are doing complex join in BMM layer in the join properties we
have an option to change the type of join to left outer, right outer, full
outer or inner.
What is level based metrics?Level
based metrics means, having a measure pinned at a certain level of the
dimension.
A LBM is a metric that is defined
for a specific level or intersection of levels.
Monthly Total Sales or Quarterly Sales are the
examples.
Ex: if you have a measure called
“Dollars”, you can create a “Level Based Measure” called “Yearly Dollars” which
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 in Time Dim.
What is logging level? Where can you
set logging levels?You can enable logging level for individual users.
We can 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 decide to enable logging, choose a logging level of 1 or 2. These
two levels are designed for use by Siebel Analytics Server administrators.
Set Logging Level:
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
Level field
What are different types of
variables? Explain each.There are two classes of variables:
1. Repository variables
2. Session variables
Repository variables-
A repository variable persists from the time the repository is started.
Static: This value does not change until a Siebel Analytics Server
administrator decides to change it.
Dynamic: The values of these variables change with the values returned by
queries. These variables are linked to an initialization block. An
initialization block contains a SQL query. When the repository is started the
value returned by the query in the initialization block will be assigned to the
dynamic variable.
Session Variables-
Session variables are created and assigned a value when each user logs on(when
a new session is started). There are two types of session variables:
1. System: System variables have reserved names, which cannot be used for other
kinds of variables (such as static or dynamic repository variables, or for
non-system session variables).
Ex: To filter a column on the value
of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.
2. Non-system: A common use of non-system variables is for setting user
filters.
Ex:
you could define a non-system 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. For example, to filter a column on the value of
the variable ‘SalesRegion’ set the filter to the Variable
NQ_SESSION.SalesRegion.
What is Authentication? How many
types of authentication do we have in OBIEE?Authentication is the process by
which a system verifies a user with the help of a user ID and password. It
checks if user has the necessary permissions and authorizations to log in and
access data. There are 4 main types of Authentication in OBIEE:
Operating system authentication
External table authentication
Database authentication
LDAP authentication
What is object level security?There
are two types of object level security: Repository level and Web level
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.
Web level: this provides security for objects stored in the OBIEE web catalog,
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.
What is data level security?This
controls the type or amount of data that you can see in a report. When multiple
users run the same report the results that are returned to each depend 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 data for that region.
What is the difference between Data
Level Security and Object Level Security?Data level security controls the type
and amount of data that you can see in a report. Object level security provides
security for objects stored in the OBIEE web catalog like dashboards,
dashboards pages, folder and reports.
How do you implement security using
External Tables and LDAP?
Instead of storing user IDs and passwords in a OBIEE 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.
Instead of storing user
IDs and passwords in a OBIEE Server repository, you can have the OBIEE 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.
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?Using level base metrics.
If you want to create new logical
column where will you create (in repository or dashboard) why?It would be
better if we create a new logical column in repository because if it is in
repository you can use it for any report. If you create new logical column in
dashboard then it is going to affect only those reports which are on that
dashboard. We cannot use that new logical column for other dashboards.
What is complex join, and where it
is
used?
To join a dimension table and fact
table in BMM layer we use a complex join.
If you want to limit the users by
the certain region to access only certain data, what would you do?Using data
level security.
In the Admin tool: 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.
If there are 100 users accessing
data, and you want to know the logging details of all the users, where can you find
that?Set the user’s logging level
1. In the Administration Tool, select Manage > Security.
The Security Manager dialog box appears.
2. Double-click the user’s user ID. The User dialog box appears.
3. Set the logging level by clicking the Up or Down arrows next to the Logging
Level field
How do implement event polling
table?In OBIEE Server event polling tables store information about updates in
the underlying databases. These tables are used for cache management. 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 cache entries
corresponding to the updated tables.
Define pipeline. Did you use it in your projects?
Yes, pipelines are the stages in a particular transaction, assessment, finance,
etc.
How do you work in a multi user
environment? What are the steps?Create a shared directory on the network for
Multi-user Development (MUD).
Open the rpd to use in MUD. From Tools->Options, setup the MUD directory to
point to the above directory.
Define projects within the rpd to allow multiple users to develop within their
subject area or Facts.
Save and move the rpd to the shared directory setup in point 1.
When users work in the MUD mode, they open the admin tool and start with
MUD ->Checkout to check-out the project they need to work on (not use the
File open as you would usually do).
After completing the development, users check-in the changes back to the
network and merge the changes.
Can you bypass siebel analytics
server security ?if so how?Yes we can by-pass by setting authentication type in
NQSCONFIG file in the security section as:
authentication_type=bypass_nqs.instanceconfig.xml.
What are the things you can do in
the BMM layer?We apply the business login in the BMM layer like creating new
logical columns, Aggregation navigation, level based metrics, time series
wizard, complex join.
What is the difference between
Single Logical Table Source and Multiple Logical Table Sources?If a logical
table in BMM layer has only one Table as the source table then it is Single
LTS.
If the logical table in BMM layer has more than one table as the sources to it
then it is called Multiple LTS.
Ex: Usually Fact table has Multiple LTSs, for which sources will be coming from
different Physical tables.
How do you bring/relate the
aggregate tables into the Siebel analytics Logical layer?One way of bringing
the Aggregate Tables into the BMM layer is by bringing them as Logical Table
sources for the corresponding Fact table.
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 in the content tab of the LTS.
How do you know which report is
hitting which table, either the fact table or the aggregate table?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.
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?Find the SQL query of the report in
Admin->manage Session-> view log then run the SQL query on toad if it
takes the same time even in TOAD then modify the query to reduce the query
time. If the query runs normally in TOAD then we need to make changes in the
repository.
What is aggregate navigation? How do
you configure the Aggregate tables in OBIEE?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.
Ex: If you observe that most of the queries are fired on aggregated values like
yearly or monthly data rather that day level data then to speed up the report
we can create an aggregate table containing month level and year level data and
map it to the respective logical table and set the aggregation levels in the
content tab of LTS.
We have 4 dimension tables, in that
2 tables need to have hierarchies then in such a case is it mandatory to create
hierarchies for all the dimension tables?No, it is not mandatory to define
hierarchies to other Dimension tables.
Can you have multiple data sources
in OBIEE?Yes, we can have multiple data sources of different types.
Do you know about Initialization
Blocks? Can you give me an example where you used them?Initialization blocks
are used for instantiating the value of a variable.
To create dynamic variable you have to create IB to write SQL statement. The
result of the query would be assigned as value to the variable.
What is query repository tool?It is
utility of OBIEE Admin tool
It allows you to examine the repository metadata
Examine relationship between metadata objects like which column in the
presentation layer maps to which table in physical
layer
Ex: we can search for objects based
on name, type.
Can you migrate the presentation layer
to a different server.No, we cannot migrate only one particular layer to
another server, we have to migrate the complete repository or the web catalog.
How do you identify what are the
dimension tables and how do you decide them during the Business/Data modeling?Dimension
tables contain descriptions that data analysts use as they query the database.
Ex: the Product
table contains product packaging information; and the Period table contains
month, quarter, and year values. The tables which contain numeric values used
for calculations are used as Fact tables.
Why do we have multiple LTS in BMM
layer? What is the purpose?To improve the performance and query response time.
How do i disable cache for only 2
particular tables?In the physical layer, double click on a table on the general
tab we have an option Cachable we can either enable it or disable it for that
particular table.
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.We need to create alias tables in the physical
layer and use which ever columns are required.
Some More Questions.
Here is some important interview
questions which will help to gain more knowledge in OBIEE.I have seen the below
questions in many blogs unfortunately didn’t found solutions for that.So I have
answered the below questions.Please comment your ideas so that we can rectify
the mistakes.
1. What is the default location of a
repository file?
A)C:\OracleBI\server\Repository
2. How many repository files
can be loaded to a BI Server simultaneously? Assume its a single server
with single instance of BI Server
running just to keep things easy
A)BI Server only can handle one
repository file in online mode
3. If you have more than 3
repository files mentioned in your NQSConfig.ini file as default, which one
gets loaded to the memory when the
BI Server is started?
Ex:
Star = SamplerRepository1.rpd,
DEFAULT;
Star = SamplerRepository2.rpd,
DEFAULT;
Star = SamplerRepository3.rpd,
DEFAULT;
A)Oracle BI server will not be
started.
4. How do you import Essbase
Cubes into your repository?
A.)1.Open the repository with the
admin tool.
2.Go to File>Import from
Multi-dimensional.
3.Enter your credentials and click
OK, select your cube and import.
5. Whats XMLA and where is it
used for in OBIEE context?
A.)Extended multi lanaguage analysis
,in previous version named as xmla nothing but a cube.
6. Can you change the location
of your rpd file in your OBIEE Configuration? If Yes, Where would
you mention the new location of this
rpd file for Bi Server?
A.) It cannot be changed.
7. What kind of joins would
you perform in the physical layer of the repository file when opened with
Administration tool?
A.)Physical joins and Complex joins.
8. What are the minimum
services needed to load a repository file onto memory and view a dashboard
which has reports that have been
refreshed on a scheduled basis?
A.) All services are needed.
9. Can you use an OLTP backend
database for creating a dashboard?
A.) Yes
10. How many tables are needed
in minimum to pass through the Consistency Checking in Logical
Layer (Business Mapping and Modeling
Layer) of the repository?
A.)One dimension and Fact Table
11.What is a complex join in OBIEE
and why, where is it used?
A.)The complex join in the BMM is it
will dynamically select
which Logical table sources to join together from the Logical tables.
In Pyhsical layer
12. Is it mandatory to have
hierarchies defined in your repository? If Yes, where does it help? If No,
what happens in the reports?
A.)Yes it is mandatory.
13. How do you create outer
joins in physical layer?
A.)We cannot create outer joins in
Physical alyer.
14. What does Consistency
Checking perform; What are the minimum criteria to pass consistency
checking for a given repository?
A.)Need One Dimension and fact in
BMM and appropraite folders in Presentaion layer
15.Does OBIEE store physical
sql ? How is physical sql generated in OBIEE environments?
A.)Yes it stores.We can check in
mange sessions and Nqquery log.
16. Are there any occasions
where physical sql is not generated when running against a backend
database like Oracle, SQL Server or
any other relational database?
A.It will effect due to Log level.
17.What is the best default
logging level for production users?
A.)Log level 2
18.What is the difference
between logging level 1 and 2?
A.)Level 1 Logs the SQL statement
issued from the client application and logs elapsed times for query
compilation, query execution, query cache processing, and back-end database
processing.
Logs the query status (success,
failure, termination, or timeout). Logs the user ID, session ID, and
request ID for each query.
Level 2 Logs everything logged in
Level 1.
Additionally, for each query, logs
the repository name, business model name, presentation catalog (called Subject
Area in Answers) name, SQL for the queries issued against physical databases,
queries issued against the cache, number of rows returned from each query
against a physical database and from queries issued against the cache, and the
number of rows returned to the client application.
19. What are the different
places (files) to view the physical sql generated by an Answers report?
A.) Manage sessions and NQQuery log.
20.)Where does the BI Server
logs its start, stop and restart times in the file system?
A.)In NQServer log (C:\OracleBI\server\Log)
21. You have two tables Table 1 and
Table 2 joined by a foreign key in the database? They are imported
together from the database into your
physical layer. Is this relationship still preserved in the OBIEE
physical layer?
A.) Yes,it will
22.Same as question 22 but what
happens if you import each table seperately?
A.)Keys will be affected but not the
joins.
23.If Table 1 and Table 2 are
dragged from physical layer to BMM layer, which table becomes a Fact Table and
which table becomes a Dimension Table?
A.)Table with primary key becomes
Dimension and table with foreign key becomes Fact table.
24.What if the tables (Table 1
and Table 2) are not joined, then what happens in BMM layer?
A.)Both acts like Fact table in BMM.
25.How many server instances can
coexist in an OBIEE cluster?
A.)There are two server instances:
Master server. A master server is a
clustered Oracle BI Server to which the Administration Tool connects for online
repository changes. In the NQClusterConfig.INI file, the parameter
MASTER_SERVER specifies the Oracle BI Server that functions as the master
server.
Slave server. A slave server is a
clustered Oracle BI Server that does not allow online repository changes. It is
used in load balancing of ODBC sessions to the Oracle BI Server cluster. If the
master server is ever down, the Administration Tool will connect to an
available slave server, but in read-only mode.
26.Aggregation rules are set on top
of …………… columns (Physical Columns or Logical Columns or Both)
A.)Logical Columns.
27.What are the pre-requisites for
using aggregate persistence wizard? What does aggregate
persistence wizard create and what
does it help with?
A.)Creation and initial population
of aggregates, persists them in a back-end database and configures the BI
Server metadata layer so that they’re used when appropriate.
28. Can you import data in
multiple sheets from an Excel file? How do you import data from an Excel sheet?
(Forget about csv files for now)
A.)Yes we can import data from
multiple sheets from an excel file
Steps to follow:
1.Create system DSN for Excel file
2.After creating ODBC Connection for
system DSN , Go to Administration tool > File > Import > From Database
> Select Data source > select the files which you want to import.
29. What are the uses of
“Execute Direct SQL” feature in Answers? Is it a good practice to allow this
feature for production users?
A.)Check physical connectivity to
the database and Check report or dashboard performance (Performance Tuning) ..etc….No,it
will overload Production Users.
3o. How do you disable “Execute
Direct SQL” feature for all the users of your repository?
A.)We can disable ” Execute Direct
SQL ” by the path below:
Answers > Settings >
Administration > Manage Privilege .
31. What are Chronological Keys in
OBIEE? How are they different from Logical Keys?
A.)Chronological key is the key
which uniquely identifies the data at particular level. chronological key is
mostly used in time dimensions where time series functions are used.
Where as logical key is the key which is used to define the unique elements in
each logical level.A logical level may have more than one level key. When that
is the case, specify the key that is the primary key of that level.It is used
to specify the columns which is used for drill down and which is used as
primary keys.
32.What are the different ways
to authenticate an user in OBIEE system? Can OBIEE authenticate a user passing
through multiple authentication methods?
A.)OBIEE Support four types of authentication.
1)LDAP Authentication : Users are
authenticated based on credentials stored in LDAP.This is the BEST method to do
authentication in OBIEE and it supports company’s Single Sign On (SSO)
philosophy as well.
2)External Table Authentication : If
users belongs to multiple groups ,that information is being pulled from the
data base scheme table.This usually implemented with LDAP Authentication
3)Database Authentication: The
Oracle BI Server can authenticates user based on database logins. If a user has
read permission on a specific database.Oracle BI Presentation Services
authenticates those users
4)Oracle BI Server User
Authentication: User are directly crated in OBIEE and are authenticated against
those credentials. This is the worst authentication method if company has
larger then few people using the system. Believe me Maintenance is a nightmare
here.
Two authentication methods are
usually combined together.like LDAP and external table authentication.
And as i said earlier LDAP and/or
LDAP with external table are BEST authentication(Again each case is different)
Oracle BI Server User Authentication
is not quite popular as it has its support/maintenance issue associated ,once
system grows beyond certain users
33.Does OBIEE support ragged
hierarchies? What is the procedure to import ragged hierarchies from Essbase?
A.)OBIEE 11g supports ragged
hierarchies.
34. You are trying to open a
repository using Admin tool and when you click to say “Open Online”; a dialogue
box pops up saying “Your rpd is available in read-only” mode. How can you edit
thisrepository by opening online?
A.)We can avoid this error by
deleting the .log and .sav in repository directory and restarting the services.
35.What is the default configuration
for caching in NQSConfig.ini file? How method does the OBIEE use for clearing
its cache?
A.)To disable in rpd – In
NQSConfig.INI file(OracleBI\server\Config) set ENABLE = NO under Cahce section.
If you make it as NO cahce will
never save cahce again in rpd.
36.What is MUDE/ MUD in OBIEE? On
what basis would you create projects?
A.)Oracle BI repository development
environment is not set up for multiple users. A more efficient development
environment would permit developers to modify a repository simultaneously and
then check in changes. This can be done by setting up the multi user
environment using the Oracle BI Administration Tool to support concurrent
development.
37.In MUDE, Can two resources
checkout the same project simultaneously?
A.)NO
38.What are the different documentation
mechanisms available in Admin tool? How do you create documentation for your
Answers users against all objects available in your subject areas?
A.)RPD
Documentation—Tools–>Utilities—>Repository Documentation
We can do dashboard and report documnention
by CATALOG MANAGER.
39.You are running a dashboard
report and check whether it is using cache? What are the different caches that
can be used to serve its customer faster? (remember we are not talking about
cache in BI Server only) How does a dashboard request gets served from all
available caches?
A.)To clear Cache:
if it’s presentation server then go
to Settings > Manage Session > Close all Cursors
if it’s bi server.. go to Admin
tool(rpd) > Manage > Cache > Action > Purge
To disable in rpd – In NQSConfig.INI
file(OracleBI\server\Config) set ENABLE = NO under Cahce section.
If you make it as NO cahce will
never save cahce again in rpd.
Physical
table—->general—>Cacheable
40. Is it better to cache a fact
table or dimension table or both in the BI Server level? Why? (Forgetother
caches from above question)
A.)Better to cache at fact table.
41. You recently changed the
columns of your presentation catalog as your manager wants to enforce naming
standards for all customer facing apps. What happens to all the dashboard
requests written prior ot this change? Do they function properly or do they
appear broken? If ‘Yes, they will function’ How does they work? ‘If Not,
reports appears broken’ what can you do to fix this? Give examples.
A.)If Alias table is avaliable for
Presentaion table then all the reports work fine.
42. What are the different hierarchy
types available in OBIEE? When would you use them?
A.) Level-based hierarchies
(structure hierarchies):Consists of an ordered set of two or more levels.
Dimensions with parent-child
hierarchies (value hierarchies):Consists of values that define the hierarchy in
a parent-child relationship and does not contain named levels
Ragged hierarchies:A hierarchy in
which all the lowest-level members do not have the same depth. Skip-level
hierarchy :A hierarchy in which certain members do not have values for certain
higher levels.
