OBIEE interview questions and answers
BI Tutorials

50 Important OBIEE Interview Questions with answers | OBIEE Interview Questions and Answers

Amit S February 19, 2018

OBIEE Interview Questions and Answers :

In my previous article i have given the idea about different OBIEE interview questions as well as OBIEE tutorials.In this article i will try to give all OBIEE interview questions and answers.These interview questions are useful for OBIEE developers as well as RPD developers.Following are set of OBIEE interview questions and answers :

1.What Is Obiee? ( 100% asked OBIEE interview questions and answers )

Answer :

Obiee stands for Oracle Business Intelligence Enterprise Edition (OBIEE). It is a business intelligence system for the enterprise that delivers abilities for reporting, online analytical processing (OLAP), ad hoc query and analysis dashboards, and scorecards.Now a days OBIEE reporting is used for mobility and mobility reporting.

 

2.Explain OBIEE Architecture?

Answer:

1.Presentation server : The presentation server is responsible to convert the request comes from end user to logical SQL and sends it to Oracle BI Server.

2.Oracle BI server :The main task of Oracle BI server is converting the logical sql to Physical sql so that database will understand the sql. Oracle BI server converts the logical sql to physical sql so that database syntactically identifies the query and send it to the Database.

3.Database :  Database fetches the required result.

 

3.Explain How OBIEE works?

Answer:

1.Presentation server : The presentation server is responsible to convert the request comes from end user to logical SQL and sends it to Oracle BI Server.

2.Oracle BI server :The main task of Oracle BI server is converting the logical sql to Physical sql so that database will understand the sql. Oracle BI server converts the logical sql to physical sql so that database syntactically identifies the query and send it to the Database.

3.Database :  Database fetches the required result.

 

4.Tell us main features of OBIEE?

Answer:

There are following main features of OBIEE :

1.Hierarchy Drilling

2.Scheduled Report Generation

3.Graphical Reporting (Charts, Pivots, Gauges, )

4.Ad Hoc Analysis

5.Global support and development capability

 

5.Explain End to End Lifecycle of OBIEE?( 100% asked OBIEE interview questions and answers )

Answer:

 

The end to end life cycle of OBIEE is:

  • Collecting Business Requirements
  • Identify source systems
  • Design ETL to load data to the Data Warehouse
  • Creating repository
  • Create dashboards and reports
  • Setting up security (LDAP or External table)
  • Decide on aggregations or caching mechanism based on performance
  • Testing and QA.

6.What is use of ODBC client?

Answer:

ODBC Client is used to connect to the database and execute SQL commands.

 

7.What Kind Of Sources Can Be Connected To Obiee?

Answer:

The OBIEE can connect most of databases like,

Sources like Relational Databases, Cubes or Flat files can be connected to OBIEE with following constraints:

The sources must be modeled as snowflakes or star schema for efficient result In the case of multiple sources connecting directly in OBIEE, it has to be used.

8.Explain what does OBIEE repository contains?

Answer:

OBIEE repository contains metadata of different databases.It contains following information:

    • Data Modeling
    • SQL Information
    • Caching
    • Connectivity Information
    • Aggregate Navigation
    • Security

9.What Are The Three Layers Of Obiee Repository?( 100% asked OBIEE interview questions and answers )

Answer:

The three layers of OBIEE repository are:

Physical Layer (Schema Design): It contains information about the data sources

Business Model Layer: It outlines the business or logical model of objects and their mapping between business model and Schema in the physical layer

Presentation Layer: Presentation layer is used to provide customized views of Business Model in Business Model layer to users.

 

10.What is use of physical layer in RPD?

Answer:

Physical layer is used for:

1.Importing data

2.Creating Aliases

3.Building physical joins

4.Setting up connection pool and its properties

5.Enabling/ Disabling cache for individual table

 

11.What is mean by LTS in OBIEE?( 100% asked OBIEE interview questions and answers )

Answer:

LTS stands for Logical table source.LTS or Logical Table Source is created when you drag and drop a column from a table that is not presently being used in your logical table. A physical table containing such column is referred as LTS.

 

12.What is mean by session variables in OBIEE?

Answer :

A session variable can be a system variable and non-system variable. It is initialized at login time for each user. System variable uses NQ_SESSION.(system reserved variable). Examples of non-system variables are user defined filters etc.

 

13.What are different log files in OBIEE?

Answer :

Different log files in OBIEE includes,

1.NQServer.log

2.NQQuery.log

3.NQSAdminTool.log, etc.

 

14.Which are configuration files in OBIEE?

Answer:

Key Configuration Files in OBIEE are,

1.NQSConfig.ini

2.NQSCluster.ini

3.odbc.ini

4.instanceconfig.xml

 

15.What are different security providers used by OBIEE?

Answer:

There are following security providers used by OBIEE:

1.Authentication provider to authenticate users.

2.Policy store provider is used to access privileges on all applications except for BI Presentation Services.

3.Credential store provider is used to store credentials used internally by the BI application.

 

16.What is main difference between Logical Table and Logical Table Source?( 100% asked OBIEE interview questions and answers )

Answer:

The difference between logical table source and the logical table is that a logical table consists of one or more logical table source. The mapping between  logical column and physical columns are done in this element.

17.How Many Server Instances Can Coexist In An Obiee Cluster?

Answer:

Maximum 16 server instances coexist in an OBIEE cluster.

18.Is it possible to create outer join in OBIEE?

Answer:

In an OBIEE physical layer, it is not possible to create the Outer join. You will create the join type in the Business Layer.

19.Can anyone bypass server authentication in OBIEE? If yes,How?

Answer:

To bypass the server authentication in NQSConfig.ini and instance config.xml, BYPASS SERVER AUTHENTICATION = YES.

20.Explain about IBOTs?

Answer:

Ibot is a scheduling agent, used to schedule reports to be sent across various devices such as email, pager, mobile, other devices, etc.

21.What are different ways to get Physical SQL from OBIEE server?

Answer:

To get real physical SQL sent by OBIEE to the database, check following details.

1.Verify the value of your session variable LOGLEVEL (5 is fine)

2.Go into Administration -> Manage sessions

3.Find your query (at the bottom of the list), and click on “View log.”

4.Below the logical SQL, you will see the physical SQL.

22.What is fact table in SQL?( 100% asked OBIEE interview questions and answers )

Answer:

A Fact table consists of two types of columns

    1. Facts and
    2. Foreign key to dimension tables

23.How to hide columns from the user in OBIEE?

Answer:

To hide certain columns from a user:

1.Do not add the column in the report

2.Do not add the column in the presentation layer

 

24.How user will change the port of OBIEE?

Answer:

You can change port of Obiee by changing its port in instanceconfig.xml

25.Explain The Architecture Of Obiee 11g And Function Of Each Components?

Answer :

OBIEE Comprises Presentation Services,Oracle BI Server:

The user constructs sql and passes it to the Analytic Engine and then the Oracle BI (Analytic Engine) describes the physical sql to the Datasources and recover the data back to the Engine and presents to the presentation Services .

26.What are different ways to extract SQL from OBIEE?

Answer:

    1. Change the request and click Advanced in that you get xml code and also the actual sql.
    2. In the catalog Manager click Tools-Create Report . In the Create Report Window –> Click Request SQL and save the sql to the physical path in your PC.
    3. Enable Loglevel to 2 in the OBIEE 11g Administration Tool from Manage-> Security and enable the log level to 2 by clicking properties for the user, then go to the NQQuery.log in BI_HOME/OracleBI/Server/Logs.You will find the SQL for that User.
    4. By clcking Administration->Manage sessions-> view sql.

27.How one can sort the reports in OBIEE 11G?

Answer:

Click on modify and then click on sort (order by icon) on the relevant column in the criteria pane.

28.What is write back in obiee?

Answer:

We can give a column as updatable and then view the reports,this option is called write back option.

29.Can user execute the direct SQL in OBIEE? If yes how?

Answer:

By clicking Direct Database Request below the subject area in we can execute Direct SQL in OBIEE.

30.How to create interactive dashboards in OBIEE?

Answer:

By clicking on Administration and Manage dashboards and by adding column selector we can create interactive Dashboard.

31.Can Report created with using 2 subject areas? How?

Answer:

From the Criteria Pane of the Report Created from First Subject Area come to the bottom of the page and click combine request. By this we can create report from two subject areas.

32.How We Can Port Changes For Dashboards, Reports, Rpd From Development To Production?

Answer :

For the RPD we can use the Merge option in Admin Tool and for dashboards and reports we can use Content Accelerator Framework.

33.What is mean by aggregate tables in OBIEE ?( 100% asked OBIEE interview questions and answers )

 Answer:
Aggregate table contains aggregated data which can be calculated by using different aggregated functions like count,avg,min,max.e.t.c.Aggregated tables are most widely used tables in OLAP database.Aggregate functions are functions where the values of table or column are grouped together and form a single value. Following are some aggregate functions:

1.Average

2.Count

3.MAX

4.MIN

5.Median

6.SUM

Using the above aggregate functions the data will be inserted in aggregate table.The aggregate tables are used for performance optimization and data is coming fast using aggregate table.Aggregations applied on database level improves the performance of the query as the query not hits directly on table it will hit on aggregate table and fetches data.

Real Example:

If table contains the data of year 2016 and 2017 ,User wants a actual count of records in the table monthly,Quarterly and yearly.Then We need to make aggregate table which contains count of records  monthly,For Quarterly we need to create other table and push the quarterly count in that table.And we need to use that tables in the reports so that report performance will improve drastically.

 

34.What Are The Different Types Of Variables In Obiee 11g? Explain?

Answer :

There are two types of variables in OBIEE 11g.

  1. Repository variable: This variable is used for the whole repository.
  2. Session variable: session variable are of two types: system variable and non system variable.

System variable uses NQ_SESSION. Examples of non system variables are user defined filters.

35.How Will You Enable Or Disable Caching In The System Level And Table Level?

Answer :

In the NQSConfig.ini file use ENABLE under CACHE Section for System Level For tables, if we want to enable the cache at table level , open the repository in offline mode This should be different from the current repository and click enable or disable the cache

36.What are Hierarchies? Explain with different types?

Answer :
Hierarchies is series of many to one relationships which has different levels.
There are 2 types of Hierarchies :
1.Level Based Hierarchies
2.Parent Child Hierarchies
37.How Will You Go About Adding Additional Column To The Repository In The Presentation Layer?

Answer :

Check if the table is already existing if so add in physical layer ,then click on BMM and then Presentation layer, then reload server metadata ,then it will be visible to all users.

38.How Will Effect The Changes For A Report , If For Certain Users Only The Column Heading In The Report Should Be Changed?

Answer :

Using session variables for that user.

39.What is mean by opaque views?Why it is used?Answers:

When handling complex requirement is not possible in data modeling from RPD side in obiee then user needs to create direct select statement and use it in to RPD is known as Opaque view.It is not recommended to use Opaque views as it degrades the performance of the report.

40.What Is A Table Alias In Obiee 11g? Where And How Will You Create It?

Answer :

Table alias is used for creating self joins. Table alias can be created by right clicking the table in the physical layer then click alias.

41.Have You Created Hierarchy In Obiee 11g. If So Where And How?( 100% asked OBIEE interview questions and answers )

Answer :

Yes, we can create hierarchy in BMM Layer of OBIEE in dimensions for the dimension tables. This can be done by right clicking the dimension table and click create dimension and then we can manually define the hierarchy and its levels.

42.What Is Level Based Metrics? How We Can Create It?( 100% asked OBIEE interview questions and answers )

Answer :

Level-base metrics means, having a measure held at a certain level of the dimension. Examples Monthly Total Sales or Quarterly Sales To create a level based measure, create a new logical column based on the original measure .Drag and drop the new logical column to the appropriate level in the Dimension hierarchy.

43.What is implicit fact column?

Answer :

The implicit fact column is the one that is created due to the join, combinations from the different data or columns from the different tables. There may not be the exact data in form of the column in the result or even in the physical layer of database. These are implied from different data and can just be a temporary layer which help us in getting the required result.

44.What are different caching techniques in OBIEE?( 100% asked OBIEE interview questions and answers )

Answer :

1. The cache management can be done in three different ways:
Cache disabled: The NQConfig.INI file has the ENABLE parameter that can be set to NO. This disables the cache for the server. After the change the server needs a restart. The disable cache will prevent any queries to use/update cache. This may lead to slower performances, but will be useful in the case of lesser accesses that are being expected. Enabling of the cache will not be concerned about the cache updates and synchronization.
2. Caching set for each physical table: The admin tool has the option “Make cache-abble” for the tables. You can click that and go into the general settings. This has the cache persistence in the option. This will make the queries to and fro from the physical table to use the cache. This can also be set according to specific time interval. This will help you in getting a good interactive response on the view layer. Any table that is frequently updated can have this setting on for reasonable amount of time.
3. Event Polling data: The event polling data table keeps track of the different updates that are done on the different tables. The application is going to updated this event polling table for each query of update into the physical table. This stand-alone table can get the required frequency statistics for each table to maintain the cache policies as required.

45.How Can We Use Saved Filters And Where We Will Save The Filters?Answer :

Shared Folders-XYZ Folder- It have two folders

  • Prompts
  • Reports

46.What Is Guided Navigation?Answer :

Guided navigation is a feature of Oracle BI Interactive Dashboards that enables the content and layout of Oracle BI Interactive Dashboard to change dynamically based on changes in the information being analyzed. Specifically, sections in a dashboard page can be set up and only appear when there is interesting information of the data.

47.What are the different types of session variables that you are aware of?( 100% asked OBIEE interview questions and answers )

There are 2 different types of session variables
1. System variables: The system variables are the ones that are used by the analytics server and web to manage and coordinate the sessions and related data. Such names cannot be used for non system variables or other repository variables. You can try naming such variables with a prefix to identify their nature and content. This will make it easier for classification within each session.
2. Non-System variables- The non system variables are always used to have user-defined aspects of data stored at a specific place. If you have a data called “numberOfPersons” in a specific data from the user space, you can then use this to classify the entire database according to the numberOfPersons etc.
When using the variables from the Analytics Webserver, you can just prefix the variable name with NQ_SESSION.(Varaiblename) to narrow down the scope to the present session.

48.What Is A Bridge Table ?Answer :

If we want to connect two tables where there is no relationship we can use a thrid bridge table for connecting them which will have same columns in both tables.

49.How Time Based Triggered Report Can Be Generated?

Answer :

Using Scheduler – ibots we can generate time based triggered report.

50.What are steps to create measures in OBIEE?

Answer :
Any measure can be created at 2 levels of OBIEE i.e. logical table and logical table source.
Step 1 :
Double click on column name in logical fact table /  LTS
Step 2 :
Go to Aggregation tab and select aggregate function from drop down.
Step 3 :
Click on expression builder wizard to define expression.
These are some important OBIEE interview question and answers.Hope everyone like this article.Kindly comment in comments section.

 

 

 

 

 

 

 

 

 

 

25 Replies to “50 Important OBIEE Interview Questions with answers | OBIEE Interview Questions and Answers”

  2. Dear sir,

    First of all thanks for your interview FAQ’S.
    Am searching job in OBIEE for interview they are asking scenario based questions so if you have any thing like could you please send it to my id pls..
    Rajesh1nellore@gmail.com

    Thank u so much sir…

  4. Thank you so much for the questions and answers, these prove to be a great help.
    By any chance do you have a pdf of these.

  10. Hi Amit,

    Very Helpful content! Thanks for this.

    Can you please send me some more questions on OBIEE so that in one go I can prepare for Interview.

    Email: shefali.agrawal3393@gmail.com

    Thanks in Advance 🙂

    Regards,
    Shefali

Comments are closed.