SQL WORLD

Categories: BI Tutorials

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

Interview

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.Tell us about OBIEE? ( 100% asked OBIEE interview questions and answers )

Answer :

OBIEE also known as Oracle Business Intelligence Enterprise Edition (OBIEE). It is a business intelligence(BI) tool for creating the non real time and real time reports , online analytical processing (OLAP), ad hoc query and analysis dashboards, and scorecards.Obiee way for Oracle Trade Agility Enterprise Copy (OBIEE). It is a business wit plan for the enterprise that transfers talents for reporting, connected to the internet analytical treat (OLAP), provisional query and analysis instrument panels, and scorecards.Now moment of truth OBIEE newsgathering is used for flexibility and flexibility 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.

3.Database : Database fetches the required result.

4. Can you tell us about the main features of OBIEE?

answer:

The main functions of OBIEE are:

1. Hierarchical drilling

2.Generation of scheduled reports

3. Graphical reporting (charts, pivots, indicators, etc.)

4. Ad hoc analysis

5. Global support and development opportunities

5. Explain the life cycle of OBIEE from start to finish? (100% asked questions and answers in the OBIEE interview)
answer:
The complete life cycle of OBIEE is:

Capture business requirements. Identify source systems. Design ETL to load data into a data warehouse. Create a repository. Create dashboards and reports. Configure security (LDAP or external table). Decide on an aggregation or caching mechanism based on performance testing and quality control.

6. What is an ODBC client used for?
answer:
The ODBC
client allows you to connect to your database and execute SQL commands.

7.OBIEE can connect most databases, e.g.

Sources such as relational databases, cubes or flat files can be connected to OBIEE with the following restrictions:

Sources should be modeled like a snowflake or star chart to achieve an effective result. This should apply to multiple sources that link directly to OBIEE.

8. Describe the contents of the OBIEE repository. Answer:

The OBIEE repository holds the metadata from many databases.It includes the following details: a data model SQL Connections for Information Caching aggregated information navigation security 9. What Are The Obiee Repository’s Three Layers?100 percent of the OBIEE interview questions and response

Answer: The three OBIEE repository layers are: Information regarding the data sources is contained in the Physical Layer (Schema Design). Business Model Layer: It describes the logical or business models of things and how they are mapped to the physical layer’s Schema. The purpose of the presentation layer is to give users personalised perspectives of the business model in the business model layer. 10. How does the physical layer function in RPD? Answer:

Uses for the physical layer include:

1. Data import

2. Making Aliens

3. constructing physical joints

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

answer:

LTS stands for Logical Table Source. An LTS source or logical table is created when you drag and drop a column from a table that is not currently used in the logical table. A physical table that contains such a column is called an LTS.

12.What do session variables mean in OBIEE?

answer:

A session variable can be a system variable and a non-system variable. It starts when every user logs in. The system variable uses NQ_SESSION. (Variable reserved for the system). Examples of non-system variables include custom filters, etc.

13. How are the log files different in OBIEE?

answer:

The various log files in OBIEE include:

Server 1.NQ.Rivista

2.NQQuery.log

3.NQSAdminTool.log etc.

14.What are the configuration files in OBIEE?

answer:

The

most important configuration files in OBIEE are:

1.NQSCconfig.Dix

2.NQSCluster.ini

3.odbc.ini

4.Instantjaconfig.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.

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.

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.

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.