OBIEE Interview Questions

• It is used for Reporting and Data Analysis.
• It provides a Central Repository with data integrated from one or more sources.
• It stores current and historical data.

A Transactional system is designed for known workloads and transactions like updating a user record, searching a record, etc. however a Data Warehouse transactions are more complex and present a general form of data.

A Transactional system contains the current data of an organization and Data warehouse normally contains the historical data.

Transactional system supports parallel processing of multiple transactions. Concurrency control and recovery mechanisms are required to maintain consistency of the database.

An Operational database query allows to read and modify operations (delete and Update), while an OLAP query needs only read only access of stored data (Select statement).

Data Warehousing involves data cleaning, data integration, and data consolidations.

• Data Mart
• Online Analytical Processing (OLAP)
• Online Transaction Processing (OLTP)
• Predictive Analysis

Data Mart is simplest form of Data Warehouse and it normally focus on a single functional area, such as sales, finance or marketing. As Data Mart usually focus on single system so they get data only from few data sources.

Indexes − OLTP system has only few indexes while in an OLAP system there are many indexes for performance optimization.

Joins − In an OLTP system, large number of joins and data is normalized however in an OLAP system there are less joins and de-normalized.

Aggregation − In an OLTP system data is not aggregated while in an OLAP database more aggregations are used.

Additive − Measures that can be added across any dimension.

Non Additive − Measures that cannot be added across any dimension.

Semi Additive − Measures that can be added across some dimensions.

Common aggregate functions include −

• Average()
• Count()
• Maximum()
• Median()
• Minimum()
• Mode()
• Sum()

These aggregate tables are used for performance optimization to run complex queries in a Data Warehouse.

In a Star Schema, there are multiple dimension tables in de-normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose. These Schemas are multidimensional structures which are used further to create reports using BI reporting tools.

In a Snowflakes Schema, there are multiple dimension tables in normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose.

Granularity in a table represents the level of information stored in the table. High granularity of data means that data is at or near the transaction level, which has more detail. Low granularity means that data has low level of information.

A fact table is usually designed at a low level of Granularity. This means that we need to find the lowest level of information that can store in a fact table.

In date dimension the Granularity level could be year, month, quarter, period, week, and day.

Slowly Changing Dimensions refer to changing value of an attribute over the time. It is one of common concept in a Data Warehouse.

Example

Andy is an employee of XYZ Inc. He was first located in New York City in July 2015. Original entry in the Employee lookup table has the following record −

Employee_IdNameLocation

10001LocationAndyLocationNew York

At a later date, he has been relocated to LA, California. How should XYZ Inc. now modify its employee table to reflect this change?

This is known as "Slowly Changing Dimension" concept.

OBIEE stands for Oracle Business Intelligence Enterprise Edition is set of Business Intelligence tools and is provided by Oracle Corporation. It enables user to delivers the robust set of reporting, ad-hoc query and analysis, OLAP, dashboard, and scorecard functionality with a rich end-user experience that includes visualization, collaboration, alerts and many more options.

• Oracle BI (OBIEE) Server
• Oracle Presentation Server
• Application Server
• Scheduler
• Cluster Controller

It is responsible to schedule jobs in OBIEE repository. When you create repository, OBIEE also create a table inside repository which saves all schedule related information. Also to run agents in 11g, this component is mandatory.

All jobs which are scheduled by Scheduler can be monitored by job manager.

ODBC stands for Open Database Connectivity and is a Universal data Connector.

OCI stands for Oracle Call Interface and is used to connect Oracle data source.

OBIEE repository contains all metadata of the BI Server and is managed through the administration tool. It is used to store information about the application environment like −

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

The BI Server can access multiple repositories.

When you create a Repository in OBIEE system, physical join is commonly used in Physical layer. Physical joins helps to understand how two table should be joined to each other. Physical joins are normally expressed with the use of Equal operator.

Yes but it is rarely seen.

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

To execute SQL, click direct database request below the subject area and you can execute Direct SQL in OBIEE.

To improve query performance, we disable BI server cache option.

Open a browser and enter the below URL to open Fusion Middleware Control Enterprise Manager −

http://<machine name>:7001/em

Enter user name and password and click on login.

In the left side, expand Business Intelligence → coreapplication → Capacity Management tab → Performance

Enable BI Server Cache section is by default checked → Click on Lock and Edit Configuration → Close.

Now deselect cache enabled option → It is used to improve query performance → Apply → Activate Changes → Completed Successfully.

As mentioned in previous article, Business Layer defines the business or logical model of objects and their mapping between business model and Schema in Physical layer. It simplifies the Physical Schema and maps the user business requirement to physical tables.

The Business Model and Mapping layer of OBIEE system Administration tool can contain one or more business model objects. A business model object defines the business model definitions and the mappings from logical to physical tables for the business model.

Steps involved in defining Business Layer −

• Create a Business Model
• Examine Logical Joins
• Examine Logical Columns
• Examine Logical Table Sources
• Rename Logical Table Objects Manually
• Rename Logical Table Objects Using the Rename Wizard and Deleting Unnecessary Logical Object
• Creating Measures (Aggregations)

There are two ways of creating Logical tables/objects in BMM layer −

First one is dragging Physical tables to Business Model which is the fastest way of defining Logical tables. When you drag the tables from Physical layer to BMM layer, it also preserves the joins and keys automatically. If you want you can change the joins and keys in Logical tables and it doesn’t effect objects in Physical layer.

Second method is to create a logical table manually −

In the Business Model and Mapping layer, right-click the business model → select New Object → Logical Table → Logical Table dialog box appears.

You can check the repository for errors by using the consistency checking option.

Go to File → click on Check Global Consistency → Yes

You can setup query logging level for individual users in OBIEE. Logging level control the information that you will retrieve in log file.

In normal scenario −

• User has a logging level set to 0 and Administrator has a logging level set to 2.
• Logging level can have values starting from Level 0 to level 5.

Level 0 means no logging and Level 5 means maximum logging level information.

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.

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.

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.

There are two types of dimensional hierarchies that are possible −

• Dimensions with level based hierarchies
• Dimension with Parent-child hierarchies

In Level based hierarchies, members can be of different types and member of same type comes only at single level.

In Parent-child hierarchies, all members are of same type.

Level based measures are created to perform calculation at a specific level of aggregation. They allow to return data at multiple levels of aggregation with one single query. It also allows to create share measures.

When you execute a query in OBIEE, BI server looks for the resources which has information to answer the query. Out of all available sources, server selects the most aggregated source to answer that query.

In OBIEE there are two types on variables that are commonly used −

• Repository Variables
• Session Variables

Apart from this you can also define Presentation and Request variables.

A Repository variable has a single value at any point of time. Repository variables are defined using Oracle BI Administration tool. Repository variables can be used in place of constants in Expression Builder Wizard.

There are two types of Repository variables −

• Static Repository Variables
• Dynamic Repository Variables

Static Repository variables are defined in variable dialogue box and their value exists until they are changed by Administrator.

Static repository variables contain default initializers that are numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as Date, Time, etc.. You cannot use any other value or expression as the default initializer for a static repository variable.

Dynamic repository variables are same as static variables but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You can also set up a schedule that the Oracle BI Server will follow to execute the query and refresh the value of the variable periodically.

When the value of a dynamic repository variable changes, all cache entries associated with a business model are deleted automatically.

Session variables are similar to dynamic repository variables and that they obtain their values from initialization blocks. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

OBIEE Dashboard is a tool that enables end users to run ad-hoc reports and analysis as per business requirement model. Interactive dashboards are pixel perfect reports which can be directly viewed or printed by end users.

OBIEE Dashboard is part of Oracle BI Presentation layer services. If your end user is not interested in seeing all the data in the dashboard, it allows you to add prompts to the dashboard that allows user to enter what he wants to see. Dashboards also allows end users to select from Drop-down lists, multi-select boxes and selection of columns to display in the reports.

Dashboard Alerts

Oracle BI Dashboard also allows you to set up alerts to sales executives that comes up on the Interactive Dashboard whenever company’s projected sales is going to be below forecast.

Filters are used to limit the results that are displayed when an analysis is run, so that the results answer a particular question. Based on the filters, only those results are shown that matches the criteria passed in the filter condition.

Filters are applied directly to attribute columns and measure columns. Filters are applied before the query is aggregated and affect the query and thus the resulting values for measures.

Example − Suppose you have a list of members in which the aggregate sums to 100. Over the time, more members meet the set filter criteria, which increases the aggregate sum to 200.

A Prompt is a special type of filter that is used to filter analyses embedded in a dashboard. The main reason to use a dashboard prompt is that it allows the user to customize analysis output and also allows flexibility to change parameters of a report.

The prompt created at the dashboard level is called a Named prompt. This Prompt is created outside of a specific dashboard and stored in the catalog as a prompt. You can apply a Named prompt to any dashboard or dashboard page that contains the columns, mentioned in the prompt. It can filter one or any number of analyses embedded on the same dashboard page. You can create and save these named prompts to a private folder or a shared folder.

A Named prompt always appear on the dashboard page and user can prompt for different values without having to rerun the dashboard.

A named prompt can also interact with selection steps. You can specify a dashboard prompt to override a specific selection step.

Inline prompts are embedded in an analysis and are not stored in the Catalog for reuse. An Inline prompt provides general filtering of a column within the analysis, and depending on how it is configured.

Inline Prompt work independently from a dashboard filter, which determines values for all matching columns on the dashboard. An inline prompt is an initial prompt. When the user selects the prompt value, the prompt field disappears from the analysis.

A column prompt is the most common and flexible prompt type. A column prompt enables you to build very specific value prompts to either stand alone on the dashboard or analysis or to expand or refine existing dashboard and analysis filters. Column prompts can be created for hierarchical, measure, or attribute columns at the analysis or dashboard level.

Connection pool is needed for every physical database and it contains information about the connection to the database. We had multiple connection pools to save time of users.

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.

OBIEE security is defined by use of a role based access control model. Security in OBIEE is defined in terms of Roles that are aligned to different directory server groups and users.

Security structure defines with below components −

• The directory Server User and Group managed by the Authentication provider.
• The application roles managed by the Policy store provide.

Security is normally defined in terms of Application roles that are assigned to directory server users and groups.

Example − the default Application roles are BIAdministrator, BIConsumer, and BIAuthor.

In OBIEE 10g, most of OBIEE administration tasks were mostly performed either through the Administration tool, the web-based Presentation Server administration screen, or through editing files in the filesystem. You had around 700 or so configuration options spread over multiple tools and configuration files, with some options like users and groups were embedded in unrelated repositories (the RPD).

In OBIEE 11g, all administration and configuration tasks are moved into Fusion Middleware Control also called as Enterprise Manager.

Both table will work as Fact table.

No

You can assign permissions in one of the following ways −

To application roles − Most recommended way of assigning permissions and privileges.

To individual users − This is difficult to manage where you can assign permissions and privileges to specific users.

To Catalog groups − It was used in previous releases for backward compatibility maintenance.