SQL WORLD

Categories: BI TutorialsSQL Interview Questions

What are OBIEE Interview Questions? | Oracle Business Intelligence Interview Questions

OBIEE Interview Questions :

In my previous article i have given the idea about different companies sql interview questions. I have written the article on BI interview questions as well as answers also.In this article i will try to give the basic as well as advanced OBIEE Interview Questions and its answer for freshers as well as experienced professionals.I studied lot of website and given different OBIEE interviews and come up with the set of important interview questions for OBIEE.

1.What is OBIEE ? (100 % asked OBIEE Interview Questions)

Answer :

OBIEE stands for Oracle Business Intelligence Enterprise Edition which is the tool used for reporting and analytics. OBIEE gives the best idea for the business users and owners about trends of business and where the business is.The current version of OBIEE is OBIEE 12 c.OBIEE tool is used for creating interactive dashboards,different types of reports which will create from multiple data sources.

There are following functionalities handled in OBIEE :

1.Creating Answers (reports)

2.Online Analytical Processing (OLAP)

3.Creating Interactive Dashboards

4.Creating Scorecards

2.Explain the architectural components of OBIEE?(100 % asked OBIEE Interview Questions)

Answer :

I have seen the OBIEE Architecture in above diagram.There are following main components of OBIEE architecture :

1.Presentation Services :

Presentation Services are used to build and view the BI Answers and analysis.

2.Oracle BI Presentation Server :

Oracle BI Presentation Server is used to processing the visualise information.

3.Presentation Catalog :

Presentation Catalog is used to store the content.

4.Oracle BI Server :

This provides efficient processing to access physical data.

3.What is mean by Repository in OBIEE ? (100 % asked OBIEE Interview Questions)

Answer :

OBIEE Repository also called as OBIEE RPD contains all metadata of BI server which is managed through administration tool.The repository always stored with .rpd extension.RPD is heart of OBIEE where actual logic is written.

OBIEE RPD is used to store information about :

1.Data Modeling

2.SQL information

3.Database Connection information

4.Caching

5.Security

6.Aggregation

4.What are different path of RPD in different OBIEE versions?

Answer :

OBIEE 10 g : BI_ORACLE_HOME/ Server/Repository

OBIEE 11 g : Oracle_Instance/bifoundation/OracleBIServerComponent/coreapplication_obisn/

OBIEE 12 C :obiee_home>/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/ customizations.

OBIEE 10 g : BI_ORACLE_HOME/ Server/Repository

OBIEE 11 g : Oracle_Instance/bifoundation/OracleBIServerComponent/coreapplication_obisn/

OBIEE 12 C :obiee_home>/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/ customizations.

5.What are different joins in OBIEE?(90 % asked OBIEE Interview Questions)

Answer :

There are following 2 joins in OBIEE :

1.Foreign key join(Equi join) : Accepts only equal to operator

2.Complex join(Non Equi join): Accepts any other operator except equal to operator.

6.What kind of sources can be connected to OBIEE?

Answer :

There are following kind of sources connected to OBIEE:

1. Relational Databases

2.Cubes

3.Flat files

These are above sources used to connect to OBIEE and modeled in OBIEE using star schema and snowflakes schema.

7.Explain End to End Architecture of OBIEE?(100 % asked OBIEE Interview Questions)

Answer :

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.

8.What is LTS? What are types of LTS?(100 % asked OBIEE Interview Questions)

Answer :

LTS stands for Logical table source.In OBIEE BMM layer when user tries to convert the snowflakes schema to star schema then the logical table source is created. There are 2 types of LTS used in OBIEE:

1.Single LTS

2.Multiple LTS

In BMM layer to convert snowflakes schema to star schema user defines multiple tables as a source table to achieve business functionality is known as logical table source.

9.What are measures in OBIEE?

Answer :

Any column with aggregations are called as measures.(Aggregations- AVG,MIN,MAX) Calculated measures are used to perform calculations in fact table.

Example of measures:

Unit price,Quantity sold

10. 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.

11.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

12.What is mean by aggregate tables in OBIEE ?

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.

13.What is Conformed Dimension in OBIEE ?

Answer :

In any case you don’t have 2 fact tables in one to one relation. To make one to one relation between two fact table one dimension table which have relation between two fact table acts as a bridge to connect two fact tables is known as conformed dimension.

14.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.

15.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.

16.Where user can check the user sessions and report duration?

Answer :

The user can check the sessions as well as report duration in OBIEE tool –>Administration –>Manage sessions.

On this tab user needs to check for the report names(CTR+F) then search report name.

17.What are different types of security mechanisms in OBIEE?

Answer :

There are two types of security mechanisms applied from OBIEE side :

1.Object Level Security

2.Data level Security

1.Object Level Security:

The Object level security is a higher level of security that allows/disallows the access for user to specific table, row or column according to his access level.

The Object level of security are permissions corresponding to the objects in the database (like database reports, dashboards, folders etc) and such permissions are set in the view level, logical level or data level.

2.Data level Security:

The Data level security level is about restrictions that are concerned about the different data that are used to generate the report of other view level information.

The Data level security mostly set in the database level and related logic table level.

18.What are different types of variables in OBIEE?

Answer :

There are following types of variables used in OBIEE:

1.Session variable

2.Repository variable

3.Presentation variables.

Repository variable : This variable is used for the whole repository.

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.

19.What are different caching techniques in OBIEE?

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.

20.Explain types of session variables?

Answer:

What are the different types of session variables that you are aware of?

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.

21.What are different log files in OBIEE?

Answer :

There are following log files :

1.NQServer.log

2.NQQuery.log

3.NSSAdmintool.log

Hope you like this article.Dont forget to share this article.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.