You can check the repository for errors by using the consistency checking option. Once it is done, next step is to load the repository into Oracle BI Server. Then test the repository by running an Oracle BI analysis and verifying the results.

Go to File → click on Check Global Consistency → You will receive the following message → Click Yes.

Once you click OK → Business model under BMM will change to Green → Click on save the repository without checking global consistency again.

Disable Caching

To improve query performance, it is advised to disable BI server cache option.

Open a browser and enter the following URL to open Fusion Middleware Control Enterprise Manager: http://<machine name>:7001/em

Enter the user name and password. Click Login.

On 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. Go to Apply → Activate Changes → Completed Successfully.

Load the Repository

Go to Deployment tab → Repository → Lock and Edit Configuration → Completed Successfully.

Click on Upload BI Server Repository section → Browse to open the Choose file dialog box → select the Repository .rpd file and click Open → Enter Repository password → Apply → Activate Changes.

Activate Changes → Completed Successfully → Click on Restart to apply recent changes option at the top → Click Yes.

Repository is successfully created and loaded for query analysis.

Enable Query Logging

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

Set Up Query Logging

Open the Administration tool → Go to File → Open → Online.

Online mode is used to edit the repository in Oracle BI server. To open a repository in online mode, your Oracle BI server should be running.

Enter the Repository password and user name password to login and click Open to open the repository.

Go to Manage → Identity → Security Manager Window will open. Click BI Repository on the left side and double-click on Administrative user → User dialogue box will open.

Click User tab in user dialogue box, you can set logging levels here.

In normal scenario − The user has a logging level set to 0 and the 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.

Logging Level Descriptions

To Set Logging Level

In user dialogue box, enter value for logging level.

Once you click OK, it will open the checkout dialogue box. Click Checkout. Close the Security Manager.

Go to file → Click on check-in changes → Save the repository using the Save option at the top → To take changes in effect → Click OK.

Use Query Log to Verify Queries

You can check query logs once query logging level is set by going to Oracle Enterprise Manager and this helps to verify queries.

To check the query logs to verify queries, go to Oracle Enterprise Manager OEM.

Go to diagnostic tab → click Log messages.

Scroll down to bottom in log messages to see Server, Scheduler, Action Services and other log details. Click on Server log to open log messages box.

You can select various filters − Date Range, Message types and message contains/not contains fields, etc. as shown in the following snapshot −

Once you click on search, it will show log messages as per filters.

Clicking on collapse button allows you to check details of all log messages for queries.

OBIEE Questions and Answers has been designed with a special intention of helping students and professionals preparing for various Certification Exams and Job Interviews. This section provides a useful collection of sample Interview Questions and Multiple Choice Questions (MCQs) and their answers with appropriate explanations.

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.

Example

Let us say there is a company XYZ Electronics which sells its products in many regions, countries and cities. Now the company President wants to see the total revenue at country level – one level below region and one level above cities. So total revenue measure should be summed up to the country level.

These type of measures are called level-based measures. Similarly, you can apply level-based measures on the time hierarchies.

Once the dimension hierarchies are created, level-based measures can be created by double clicking on the total revenue column in the logical table and setting the level in the levels tab.

Create Level-Based Measures

Open the repository in offline mode. Go to File → Open → Offline.

Select .rpd file and click open → Enter repository password and click Ok.

In BMM layer, right-click on Total Revenue column → New Object → Logical column.

It will open the logical column dialog box. Enter the name of logical column total revenue. Go to column source tab → Check derived from existing columns using an expression.

Once you select this option, expression edit wizard will be highlighted. In expression builder wizard, select the logical table → Column name → Total revenue from the left side menu → Click OK.

Now go to level tab in logical column dialog box → Click on logical dimension to select it as grand total under logical level. This specifies that the measure should be calculated at grand total level in the dimension hierarchy.

Once you click OK → Total Revenue logical table will appear under the logical dimension and Fact tables.

This column can be dragged to presentation layer in the subject area to be used by end users to generate reports. You can drag this column from fact tables or from logical dimension.

When you drag and drop a column from a physical table that is not currently being used in your logical table in BMM layer, the physical table containing such column gets added as a new Logical Table Source (LTS).

When in BMM layer, you use more than one table as source table, it is called multiple logical table sources. You can have a Fact table as multiple logical table sources when it uses different physical tables as source.

Example

Multiple LTS are used to convert Snowflakes schema to Star schemas in BMM layer.

Let us say you have two dimensions − Dim_Emp and Dim_Dept and one fact table FCT_Attendance in the Physical layer.

Here your Dim_Emp is normalized to Dim_Dept to implement Snowflakes schema. So in your Physical diagram, it would be like this −

Dim_Dept<------Dim_Emp <-------FCT_Attendance

When we move these table to the BMM layer, we will create a single dimension table Dim_Employee with 2 logical sources corresponding to Dim_Emp and Dim_Dept. In your BMM diagram −

Dim_Employee <-----------FCT_Attendance

This is one approach where you can use concept of multiple LTS in BMM layer.

Specifying Content

When you use multiple physical tables as sources, you expand table sources in BMM diagram. It shows all multiple LTS from where it is picking up the data in BMM layer.

To see table mapping in BMM layer, expand the sources under logical table in BMM layer. It will open Logical table source mapping dialogue box. You can check all tables which are mapped to provide data in logical table.

Hierarchies is a series of many-to-one relationships and can be of different levels. A Region hierarchy consists of: Region → Country → State → City → Street. Hierarchies follow top-down or bottom-up approach.

Logical dimensions or dimension hierarchies are created in BMM layer. 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 members of the same type come only at single level.

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

Dimensions with Level-based Hierarchies

Level-based dimension hierarchies can also contain parent-child relationships. The common sequence to create level-based hierarchies is to start with grand total level and then working down to lower levels.

Level-based hierarchies allows you to perform −

• Level-based calculated measures.
• Aggregate navigation.
• Drill down to child level in dashboards.

Each dimension can only have one grand total level and it doesn’t have a level key or dimension attributes. You can associate measures with grand total level and default aggregation for these measures are grand total always.

All lower levels should have at least one column and each dimension contains one or more hierarchies. Each lower level also contains a level key which defines unique value at that level.

Types of Level-based Hierarchies

Unbalanced Hierarchies

Unbalanced hierarchies are those where all the lower levels don’t have the same depth.

Example − For one product, for one month you can have data for weeks and for other month you can have data available for day level.

Skip Level Hierarchies

In skip-level hierarchies, few members don’t have values at higher level.

Example − For one city, you have state → country → Region. However for other city, you have only state and it doesn’t fall under any country or region.

Dimension with Parent-child Hierarchies

In parent-child hierarchy, all the members are of the same type. The most common example of parent-child hierarchy is the reporting structure in an organization. Parent-child hierarchy is based on a single logical table. Each row contains two keys – one for the member and another for the parent of the member.

Aggregations are used to implement query performance optimization while running the reports. This eliminates the time taken by query to run the calculations and delivers the results at fast speed. Aggregate tables has less number of rows as compared to a normal table.

How Aggregation Works in OBIEE?

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, the server selects the most aggregated source to answer that query.

Adding Aggregation in a Repository

Open the Repository in an offline mode in the Administrator tool. Go to File → Open → Offline.

Import the metadata and create logical table source in BMM layer. Expand the table name and click on source table name to open logical table source dialog box.

Go to column mapping tab to see map columns in Physical table. Go to content tab → Aggregate content group by selecting the logical level.

You can select different logical levels as per the columns in fact tables like Product Total, Total Revenue, and Quarter/Year for Time as per dimension hierarchies.

Click OK to close dialog box → save the repository.

When you define Aggregate in logical fact tables they are defined as per dimension hierarchies.

Calculated measures is used to perform calculation of facts in logical tables. It defines Aggregation functions in Aggregation tab of logical column in the repository.

Create New Measure

Measures are defined in logical fact tables in repository. Any column with an aggregation function applied on it is called a measure.

Common measure examples are − Unit Price, quantity sold, etc.

Following are the guidelines to create measures in OBIEE −

• All aggregation should be performed from a fact logical table and not from a dimension logical table.

• All columns that cannot be aggregated should be expressed in a dimension logical table and not in a fact logical table.

Calculated measures can be defined in two ways in logical tables at BMM layer in Administration tool −

• Aggregations in logical tables.
• Aggregations in logical table source.

Create Calculated Measures in Logical Tables using Administration Tool

Double-click on the column name in the logical Fact table, you will see the following dialog box.

Go to Aggregation tab and select the Aggregate function from the drop-down list → Click OK.

You can add new measures using functions in Expression builder wizard in Column source. Measures represent data that is additive, such as total revenue or total quantity. Click on the save option at the top to save the repository. This is also called creating measures at logical level.

Create Calculated Measures in Logical Table Source using Administration Tool

You can define Aggregations by a double-click on Logical table source to open logical table dialogue box.

Click on Expression builder wizard to define expression.

In Expression builder, you can choose multiple options like – Category, functions, and mathematical functions.

Once you select the category, it will show the subcategories inside it. Select the subcategory and mathematical function, and click on the arrow mark to insert it.

Now to edit the value to create measures, click on source number, enter the calculated value like multiple and divide → Go to Category and select logical table → Select column to apply this multiple/division to an existing column value.

Click OK to close the Expression builder. Again click OK to close the dialog box.