OBIEE Interview Question and Answers

What is Business Intelligence?

Business intelligence is the process of gathering information and knowledge through the process of analyzing business data. The major challenges for Business intelligence is to gather the necessary data about the business and enable end-users to access that knowledge easily and efficiently and in effect maximize the success of an organization

Some of the most popular business tools are Cognos,OBIEE,SAS etc

What is OBIEE?

OBIEE stands for Oracle Business Intelligence Enterprise Edition.Oracle Business Intelligence is a complete, open, and architecturally unified business intelligence system for the enterprise that delivers abilities for reporting, ad hoc query and analysis, online analytical processing (OLAP), dashboards

Difference b/w Star Schema and Snowflake Schema?

The star schema is the simplest schema. A single Fact table (center of the star) surrounded by multiple dimensional tables , It is called a star schema because the diagram resembles a star

Whereas, A Snowflake schema is a schema which consists of a single Fact table and multiple dimensional tables. A variant of the star schema where each dimension can have its own dimensions.These Dimensional tables are normalized. 

What are the different layers of repository?

Physical Layer, Business Model and Mapping Layer(BMM) , Presentation Layer.

What are the key configuration files in OBIEE?

NQSConfig.ini ,NQSCluster.ini, odbc.ini, instanceconfig.xml

What are different log files in OBIEE?

NQServer.log, NQQuery.log, NQSAdminTool.log

How can you filter/limit the rows returned from database in RPD?
with the help of where clause in content tab in logical table source we can filter the data(limit the rows)

In the BMM layer,double click on logical table sources ,on the content tab we can put a where condition in where pane.

How can you disable the cache for only particular tables?

In physical layer, double click on the table, on the general tab we have an option to make the particular table non cacheable. Uncheck the cacheable box, by default table is cacheable.

What are different ways to view the physical sql generated by an Answers report?

You can see the SQL at C:\OracleBI\server\Log\NQQuery.log 

And using administrator , Settings ->Administration ->Manage session->View log

What is column level security?

Column level security means restricting acces to OBIEE objects between different users/groups.

What is data level security?

Data level security means controls the type and amount of data users can see in a report. When multiple users run the same report results may vary, depending on the access rights user have.

What is complex join?

Complex join/logical join is used to define the relationship between two table. It is created in BMM layer.
http://startobiee.blogspot.in/2013/08/complex-and-physical-joins-in-obiee.html

What is loglevel set for production users?

Log level will be 0.

What is Alias ,usage and how to create?

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 properties from the physical table.

In physical layer, right click on table->New Object->Alias
http://startobiee.blogspot.in/2013/08/alias-in-obiee.html

What is Opaque view? How to create ? Why we should avoid them?

An opaque view is a Physical layer table that consists of a SELECT statement..

In Physical layer, Right click on table->properties->Select table type as “Select” ->then write query in Default Initialization String.

It should be used only if there is no other solution to your modeling problem. Ideally, a
physical table should be created, or alternatively a materialized view. Opaque
views prevent the Oracle BI Server from generating its own optimized SQL,
because they contain fixed SQL statements that are sent to the underlying data
source.

Can we use Complex join in physical layer?

Yes,we can use in below scenarios

1)    when we join key column of one table to non key column of other table.

2)    when the operator is other than equal to operator.

What is the default location of a repository file?

[InstalledDirectory]\OracleBI\server\Repository\

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;

If we don’t specify comment the last repository will be loaded online, so in this case SamplerRepository3.rpd will be loaded.

You have to create new logical column where will you create (in repository or dashboard) why?

We should create new logical column in repository, Because if it’s in repository, it can be used for any report. whereas, If we 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 dashboard (or request).

What are the things can be done in the BMM layer?

Hierrarchy creation, Aggregation navigation, level base metrics, time series wizard, create new logical column, complex join.

What is level-based metrics?

Level-based metrics means, having a measure pinned at a certain level of the dimension. A level base measure as name state are always calculated at a certain level of  aggregation.for example if  we have a measure called Amount Sold, we can create a Level Based Measure called CategoryAmoundSold which is Amount Sold for a Category . This measure will always return the Amount sold for the category level even if we drill down to a lower level like subcategory etc.
http://startobiee.blogspot.in/2013/08/level-based-measures-in-obiee_5.html

What are different types of variables?

In OBIEE we have two types of variables:

1. Repository variables

2. Session variables.

Repository variables:  A repository variable has a single value at any point in time. There are two types of repository variables:

Static Repository Variables: This value persists, and does not change until OBIEE Server administrator decides to change it.

Dynamic Repository Variable: 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 OBIEE Server will follow to execute the query and periodically refresh the value of the variable. 

Session Variables: Session variables are created and assigned a value when each user logs on. So, every login has its own session variable.When a user begins a session, the Oracle BI Server creates a new instance of a session variable and initializes it. There are two types of session variables:

System Session Variables: System variables are session variables that the OBIEE Server and OBIEE Web use for specific purposes. Example, USER, LOGLEVEL, GROUP etc.

Non-system Session Variables: A system variable that the administrator creates. Example, A non-system variable called Sales Region that would be initialized to the name of the user’s sales region.

How to create any new logical column in BMM layer?

Right click on fact table ->new logical column->give name for new logical column

like Total sales.

How to create outer join in BMM layer?

While creating logical join between tables you can select outer join type.

What is logging level? Where can you set logging levels?

It is a parameter which control the number of information that you will retrieve in the log file from nothing (level 0 – no log found) to a lot of information (level 5).

You can enable logging level for individual users, you cannot configure a logging level for a group.
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 OBIEE 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.

Description of Logging Levels

Log Level 0	No Logging
Log Level 1	Logs the SQL statement issued from the client application 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
Log 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
Log Level 3	Logs everything logged in Level 2 Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails
Log Level 4	Logs everything logged in Level 3 Additionally, logs the query execution plan.
Log Level 5	Logs everything logged in Level 4 Additionally, logs intermediate row counts at various points in the execution plan.

How many dimension can we create on a table?

One Dimension

What is Authentication? How many types of authentication ? 

In the OBIEE context, Authentication means validating the user while logging in the OBIEE application.  When a user logs in the OBIEE application a request is sent to the BI Server asking that whether this user is a valid user or not. When BI Server validates the user, then only the user is able to login in the application.

OBIEE Support four types of authentication
External table authentication,
Database authentication,
LDAP authentication,

Oracle BI Server User Authentication.

What is Authorization?
Authorization means a user is authorized to view what all objects. Example, User A might be authorized to view only particular set of reports and dashboards based on the security applied.

How many layouts/view are available in OBIEE?

Majorly we have four layouts

Compound Layout,

Table Layout,

Graph Layout,

Pivot layout.

What are AGO and TODATE function?

AGO gives you, for example, the value of sales one month ago or one quarter ago, whilst TODATE gives you the total of sales month-to-date, or quarter-to-date, or year-to-date.
http://startobiee.blogspot.in/2014/07/time-series-functions-in-obiee.html

Where dashboards get stored?

Under _portal folder at Shared folder.

Difference in Navigation and Drill down and Drill through?

Drill down means drill down from one level to lower level of the same hierarchy  

Drill through means form one level of a hierarchy to another level from another hierarchy. (For instance, from a Date Dimension level date to the Time Dimension level hour)

Navigation means move from one report to another report.

What is Logical table sources (LTS) ?

Logical table source define the mappings from a single logical table to one or more physical tables. A logical table may contains one or more logical table source.

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, whereas, 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 LTS, for which sources will be coming from different Physical tables.

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?

BI server, Presentation server; Java Host for charts; Scheduler for iBots.

How many minimum tables are needed to pass through the Consistency Check of the repository?

Two,One Dim and One Fact

How to give admin privileges in dashboard?

Settings -> Administrator -> Manage privileges -> Manage dashboards.

What is Initialization Blocks?

Initialization blocks are used to initialize dynamic repository variables, system session variables, and non-system session variables. For example, the NQ_SYSTEM initialization block is used to refresh system session variables. Init blocks are used for instantiating a session when a user logs in.

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.

You can see the SQL in the C:\OracleBI\server\Log\NQQuery.log.

What is Cache? How does it works?

When data is accessed, a copy is stored in separate memory called cache , Next time CPU looks for information, it first checks the cache. If data is there called a hit , retrieval is faster from the  cache memory .If it is not a hit, then it accesses system memory, puts a copy of the new data in the cache, and processes the information. Disk caching and memory caching significantly improves the overall speed of the computer.

What is Cache management? What are possible cache methods in OBIEE?

Monitoring and managing the cache is cache management.

1) No Cache: You can disable caching for the whole system by setting the ENABLE parameter to NO in the NQSConfig.INI file and restarting the OBIEE Server. Disabling caching stops all new cache entries and stops any new queries from using the existing cache.

2) Manual Cache Management(Using Cache Manager)  : Go to Administration Tool-> Manage->cache

3) Automatically(Using Event Table): OBIEE 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.

4) Programmatically(Purging and Maintaining Cache Using ODBC Procedures) : 

Call SAPurgeAllCache(), Call SAPurgeCacheByTable(), Call SAPurgeCacheByQuery()

Please share your feedback and keep me posted.
have a good day!!

Thanks