OBIEE components are mainly divided into two types of components −

• Server Components
• Client Components

Server components are responsible to run OBIEE system and client components interact with user to create reports and dashboards.

Server Components

Following are the server components −

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

Oracle BI Server

This component is the heart of OBIEE system and is responsible to communicate with other components. It generates queries for report request and they are sent to database for execution.

It is also responsible for managing repository components which are presented to the user for report generation, handles security mechanism, multi user environment, etc.

OBIEE Presentation Server

It takes the request from users via browser and passes all requests to OBIEE server.

OBIEE Application Server

OBIEE Application Server helps to work on client components and Oracle provides Oracle10g Application server with OBIEE suite.

OBIEE Scheduler

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

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

Client Components

Following are some client components −

Web-based OBIEE Client

Following tools are provided in OBIEE web-based client −

• Interactive Dashboards
• Oracle Delivers
• BI Publisher
• BI Presentation Service Administrator
• Answers
• Disconnected Analytics
• MS Office Plugin

Non-Web based Client

In Non-Web based client, following are the key components −

• OBIEE Administration − It is used to build repositories and has three layers − Physical, Business, and Presentation.

• ODBC Client − It is used to connect to database and execute SQL commands.

Schema is a logical description of the entire database. It includes the name and description of records of all types including all associated data-items and aggregates. Much like a database, DW also requires to maintain a schema. Database uses relational model, while DW uses Star, Snowflake, and Fact Constellation schema (Galaxy schema).

Star Schema

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 to create reports using BI reporting tools.

Dimensions in Star schemas contain a set of attributes and Fact tables contain foreign keys for all dimensions and measurement values.

In the above Star Schema, there is a fact table “Sales Fact” at the center and is joined to 4 dimension tables using primary keys. Dimension tables are not further normalized and this joining of tables is known as Star Schema in DW.

Fact table also contains measure values − dollar_sold and units_sold.

Snowflakes Schema

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.

Only difference between a Star and Snowflakes schema is that dimension tables are further normalized. The normalization splits up the data into additional tables. Due to normalization in the Snowflake schema, the data redundancy is reduced without losing any information and therefore it becomes easy to maintain and saves storage space.

In above Snowflakes Schema example, Product and Customer table are further normalized to save storage space. Sometimes, it also provides performance optimization when you execute a query that requires processing of rows directly in normalized table so it doesn’t process rows in primary Dimension table and comes directly to Normalized table in Schema.

Granularity

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 be stored in a fact table. In date dimension, the granularity level could be year, month, quarter, period, week, and day.

The process of defining granularity consists of two steps −

• Determining the dimensions that are to be included.
• Determining the location to place the hierarchy of each dimension of information.

Slowly Changing Dimensions

Slowly changing dimensions refer to changing value of an attribute over time. It is one of the common concepts in DW.

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 −

At a later date, he has 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.

There are three ways to solve this type of problem −

Solution 1

The new record replaces the original record. No trace of the old record exists.

Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

• Benefit − This is the easiest way to handle the Slowly Changing Dimension problem as there is no need to keep track of the old information.

• Disadvantage − All historical information is lost.

• Use − Solution 1 should be used when it is not required for DW to keep track of historical information.

Solution 2

A new record is entered into the Employee dimension table. So the employee, Andy, is treated as two people.

A new record is added to the table to represent the new information and both the original and new record will be present. The new record gets its own primary key as follows −

• Benefit − This method allows us to store all the historical information.

• Disadvantage − Size of the table grows faster. When the number of rows for the table is very high, space and performance of table can be a concern.

• Use − Solution 2 should be used when it is necessary for DW to keep historical data.

Solution 3

The original record in Employee dimension is modified to reflect the change.

There will be two columns to indicate the particular attribute, one indicates original value and other indicates the new value. There will also be a column that indicates when the current value becomes active.

• Benefits − This does not increase the size of the table, since new information is updated. This allows us to keep historical information.

• Disadvantage − This method doesn’t keep all history when an attribute value is changed more than once.

• Use − Solution 3 should only be used when it is required for DW to keep information of historical changes.

Normalization

Normalization is the process of decomposing a table into less redundant smaller tables without losing any information. So Database normalization is the process of organizing the attributes and tables of a database to minimize data redundancy (duplicate data).

Purpose of Normalization

• It is used to eliminate certain types of data (redundancy/ replication) to improve consistency.

• It provides maximum flexibility to meet future information needs by keeping tables corresponding to object types in their simplified forms.

• It produces a clearer and readable data model.

Advantages

• Data integrity.
• Enhances data consistency.
• Reduces data redundancy and space required.
• Reduces update cost.
• Maximum flexibility in responding to ad-hoc queries.
• Reduces the total number of rows per block.

Disadvantages

Slow performance of queries in database because joins have to be performed to retrieve relevant data from several normalized tables.

You have to understand the data model in order to perform proper joins among several tables.

Example

In the above example, the table inside the green block represents a normalized table of the one inside the red block. The table in green block is less redundant and also with less number of rows without losing any information.

Dimensional modeling provides set of methods and concepts that are used in DW design. According to DW consultant, Ralph Kimball, dimensional modeling is a design technique for databases intended to support end-user queries in a data warehouse. It is oriented around understandability and performance. According to him, although transaction-oriented ER is very useful for the transaction capture, it should be avoided for end-user delivery.

Dimensional modeling always uses facts and dimension tables. Facts are numerical values which can be aggregated and analyzed on the fact values. Dimensions define hierarchies and description on fact values.

Dimension Table

Dimension table stores the attributes that describe objects in a Fact table. A Dimension table has a primary key that uniquely identifies each dimension row. This key is used to associate the Dimension table to a Fact table.

Dimension tables are normally de-normalized as they are not created to execute transactions and only used to analyze data in detail.

Example

In the following dimension table, the customer dimension normally includes the name of customers, address, customer id, gender, income group, education levels, etc.

Fact Tables

Fact table contains numeric values that are known as measurements. A Fact table has two types of columns − facts and foreign key to dimension tables.

Measures in Fact table are of three types −

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

Example

This fact tables contains foreign keys for time dimension, product dimension, customer dimension and measurement value unit sold.

Suppose a company sells products to customers. Every sale is a fact that happens within the company, and the fact table is used to record these facts.

Common facts are − number of unit sold, margin, sales revenue, etc. The dimension table list factors like customer, time, product, etc. by which we want to analyze the data.

Now if we consider the above Fact table and Customer dimension then there will also be a Product and time dimension. Given this fact table and these three dimension tables, we can ask questions like: How many watches were sold to male customers in 2010?

Difference between Dimension and Fact Table

The functional difference between dimension tables and fact tables is that fact tables hold the data we want to analyze and dimension tables hold the information required to allow us to query it.

Aggregate Table

Aggregate table contains aggregated data which can be calculated by using different aggregate functions.

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement.

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.

Example

You save tables with aggregated data like yearly (1 row), quarterly (4 rows), monthly (12 rows) and now you have to do comparison of data, like Yearly only 1 row will be processed. However in an un-aggregated table, all the rows will be processed.

Select Avg (salary) from employee where title = ‘developer’. This statement will return the average salary for all employees whose title is equal to ”Developer”.

Aggregations can be applied at database level. You can create aggregates and save them in aggregate tables in the database or you can apply aggregate on the fly at the report level.

Note − If you save aggregates at the database level it saves time and provides performance optimization.

Presentation layer is used to provide customized views of Business model in BMM layer to users. Subject areas are used in Presentation layer provided by Oracle BI Presentation Services.

There are various ways you can create subject areas in Presentation layer. Most common and simple method is by dragging Business Model in BMM layer to Presentation Layer and then making changes to it as per requirement.

You can move columns, remove or add columns in presentation layer so it allows you to make changes in a way that the user shouldn’t see columns that has no meaning for them.

Create Subject Areas/Presentation Catalogues and Presentation Tables in Presentation Layer

Right-click on Presentation area → New Subject Area → In General tab enter the name of subject area (Recommended similar to Business Model) → Click OK.

Once Subject area is created, right click on subject area → New Presentation table → in General tab, Enter name of presentation table → OK (Add number of presentation tables equal to number of parameters required in the report).

Click the Permissions tab → Permissions dialog box, where you can assign user or group permissions to the table.

Delete a Presentation Table

In the Presentation layer, right-click on subject Area → Presentation Catalog dialog box, click the Presentation Tables tab → Go to Presentation Tables tab, select a table and click Remove.

A confirmation message appears → Click Yes to remove the table or No to leave the table in the catalog → Click OK.

Move a Presentation Table

Go to Presentation Tables tab by a right-click on Subject Area → In the Name list, select the table you want to reorder → Use drag-and-drop to reposition the table or you can also use the Up and Down buttons to reorder the tables.

Presentation Columns Under Presentation Table

The name of presentation columns are normally same to the logical column names in the Business Model and Mapping layer. However, you can also enter a different name by unchecking Use Logical Column Name and the Display Custom Name in the Presentation Column dialog box.

Create Presentation Columns

The most simple way to create columns under Presentation tables is by dragging the columns from logical tables in BMM layer.

Select the objects under logical tables in BMM and drag them to Presentation tables under subject area (Use Ctrl key to select multiple objects for dragging). Repeat the process and add the logical columns to the remaining presentation tables.

Create a New Presentation Column −

Right-click on Presentation table in the Presentation layer → New Presentation Column.

Presentation Column dialog box appears. To use the name of the logical column, select the Use Logical Column checkbox.

To specify a name that is different name, uncheck the Use Logical Column check box, and then type a name for the column.

To assign user or group permissions to the column, click Permissions → In the Permissions dialog box, assign permissions → click OK.

Delete a Presentation Column

Right-click on presentation table in the Presentation layer → Click on Properties → Click on the Columns tab → Select the column you want to delete → Click Remove or press the Delete key →Click Yes.

To Reorder a Presentation Column

Right-click on presentation table in the Presentation layer → Go to Properties → Click the Columns tab → Select the column you want to reorder → Use drag-and-drop or you can also click Up and Down button → Click OK.

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 such as −

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

The BI Server can access multiple repositories. OBIEE Repository can be accessed using the following path −

BI_ORACLE_HOME/server/Repository -> Oracle 10g
ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/coreapplication_obisn/-> Oracle 11g

OBIEE repository database is also known as a RPD because of its file extension. The RPD file is password protected and you can only open or create RPD files using Oracle BI Administration tool. To deploy an OBIEE application, the RPD file must be uploaded to Oracle Enterprise Manager. After uploading the RPD, the RPD password then must be entered into Enterprise Manager.

Designing an OBIEE Repository using Administration Tool

It is a three layer process − starting from Physical Layer (Schema Design), Business Model Layer, Presentation Layer.

Creating the Physical Layer

Following are the common steps involved in creating the Physical Layer −

• Create physical joins between the Dimension and Fact tables.
• Change the names in the physical layer if required.

The physical layer of repository contains information about the data sources. To create the schema in the physical layer you need to import metadata from databases and other data sources.

Note − Physical layer in OBIEE supports multiple data sources in a single repository – i.e. data sets from 2 different data sources can be performed in OBIEE.

Create a New Repository

Go to Start → Programs → Oracle Business Intelligence → BI Administration → Administration Tool → File → New Repository.

A new window will open → Enter the name of Repository → Location (It tells the default location of Repository directory) → to import metadata select radio button → Enter Password → Click Next.

Select the connection type → Enter Data Source name and User name and password to connect to data source → Click Next.

Accept the meta types you want to import → You can select Tables, Keys, Foreign Keys, System tables, Synonyms, Alias, Views, etc. → Click Next.

Once you click Next, you will see Data Source view and Repository view. Expand the Schema name and select tables you want to add to Repository using Import Selected button → Click Next.

Connection Pool window opens up → Click OK → Importing window → Finish to open the repository as shown in the following image.

Expand the Data Source → Schema name to see the list of tables Imported in Physical Layer in the new Repository.

Verify Connection and Number of Rows in Tables Under Physical Layer

Go to tools → Update all rows counts → Once it is completed you can move the cursor on the table and also for individual columns. To see Data of a table, right-click on Table name → View Data.

Create Alias in Repository

It is advisable that you use table aliases frequently in the Physical layer to eliminate extra joins. Right-click on table name and select New Object → Alias.

Once you create an Alias of a table it shows up under the same Physical Layer in the Repository.

Create Primary Keys and Joins in Repository Design

Physical Joins

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

You can also use a physical join in BMM layer, however, it is very rarely seen. The purpose of using a physical join in BMM layer is to override the physical join in the physical layer. It allows users to define more complex joining logic as compared to physical join in the physical layer so it works similar to complex join in the physical layer. Therefore, if we are using a complex join in the physical layer for applying more join conditions, there is no need to use a physical join in BMM layer again.

In the above snapshot, you can see a physical join between two table names − Products and Sales. Physical Join expression tells how the tables should be joined with each other as shown in the snapshot.

It is always recommended to use a physical join in the physical layer and complex join in BMM layer as much as possible to keep Repository design simple. Only when there is an actual need for a different join, then use a physical join in BMM layer.

Now to join tables while designing Repository, select all the tables in the Physical layer → Right-click → Physical diagram → Selected objects only option or you can also use Physical Diagram button at the top.

Physical Diagram box as shown in the following image appears with all the table names added. Select the new foreign key at the top and select Dim and Fact table to join.

Foreign Key in Physical Layer

A Foreign key in the physical layer is used to define Primary key-Foreign key relation between two tables. When you create it in the physical diagram, you have to point first the dimension and then the fact table.

Note − When you import tables from schema into RPD Physical Layer, you can also select KEY and FOREIGN KEY along with the table data, then the primary key-foreign key joins are automatically defined, however it is not recommended from performance point of view.

The table you click first, it creates one-to-one or one-to-many relationship that joins column in first table with foreign key column in the second table → Click Ok. The join will be visible in Physical Diagram box between two tables. Once tables are joined, close the Physical diagram box using ‘X’ option.

To save the new Repository go to File → Save or click the save button at the top.

Creating Business Model and Mapping Layer of a Repository

It defines the business or logical model of objects and their mapping between business model and Schema in the 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.

Following are the steps to build the Business Model and Mapping layer of a repository −

• 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 objects
• Creating measures (Aggregations)

Create a Business Model

Right-click on Business Model and Mapping Space → New Business Model.

Enter the name of Business Model → click OK.

In the physical layer, select all the tables/alias tables to be added to Business Model and drag to Business Model. You can also add tables one by one. If you drag all the tables simultaneously, it will keep keys and joins between them.

Also note the difference in icon of Dimension and Fact tables. Last table is Fact table and top 3 are dimension tables.

Now right-click on Business model → select Business Model diagram → Whole diagram → All tables are dragged simultaneously so it will keep all joins and keys. Now double click on any join to open the logical join box.

Logical and Complex Joins in BMM

Joins in this layer are logical joins. It doesn’t show expressions and tells the type of join between tables. It helps Oracle BI server to understand the relationships between the various pieces of the business model. When you send a query to Oracle BI server, the server determines how to construct physical queries by examining how the logical model is structured.

Click Ok → Click ‘X’ to close the Business model diagram.

To examine logical columns and logical table sources, first expand the columns under tables in BMM. Logical columns were created for each table when you dragged all tables from the physical layer. To check logical table sources → Expand the source folder under each table and it points to the table in the physical layer.

Double-click the logical table source (not the logical table) to open the logical table source dialog box → General tab → rename the logical table source. Logical table to physical table mapping is defined under “Map to these tables” option.

Next, Column mapping tab defines the logical column to physical column mappings. If mappings are not shown, check the option → Show mapped columns.

Complex Joins

There is no specific explicit complex join like in OBIEE 11g. It only exists in Oracle 10g.

Go to Manage → Joins → Actions → New → Complex Join.

When complex joins are used in the BMM layer, they act as placeholders. They allow the OBI Server to decide on which are the best joins between fact and dimension logical table source to satisfy the request.

Rename Logical Objects Manually

To rename logical table objects manually, click the column name under the Logical table in BMM. You can also right-click on column name and select option rename, to rename the object.

This is known as manual method to rename objects.

Rename Objects Using the Rename Wizard

Go to Tools → Utilities → Rename Wizard → Execute to open the rename wizard.

In the Select Objects screen, click Business Model and Mapping. It will show Business Model name → Expand Business Model name → Expand logical tables.

Select all the columns under the logical table to rename using the Shift key → Click Add. Similarly, add columns from all other logical Dim and Fact tables → click Next.

It shows all logical columns/tables added to wizard → Click Next to open Rules screen → Add rules from the list to rename like : A;; text lower case and change each occurrence of ‘_’ to space as shown in the following snapshot.

Click Next → finish. Now, if you expand Object names under logical tables in Business model and Objects in the physical layer, objects under BMM are renamed as required.

Delete Unnecessary Logical Objects

In the BMM layer, expand Logical tables → select objects to be deleted → right-click → Delete → Yes.

Create Measures (Aggregations)

Double-click on the column name in the logical Fact table → Go to Aggregation tab and select the Aggregate function from the dropdown list → Click OK.

Measures represent data that is additive, such as total revenue or total quantity. Click on save option at top to save the repository.

Creating the Presentation Layer of a Repository

Right-click on Presentation area → New Subject Area → In the General tab enter the name of subject area (Recommended similar to Business Model) → Click OK.

Once subject area is created, right click on subject area → New presentation table → Enter the name of the presentation table → Click OK (Add number of presentation tables equal to number of parameters required in the report).

Now, to create columns under Presentation tables → Select the objects under logical tables in BMM and drag them to Presentation tables under subject area (Use Ctrl key to select multiple objects for dragging). Repeat the process and add the logical columns to the remaining presentation tables.

Rename and Reorder Objects in Presentation Layer

You can rename the objects in Presentation tables by a double-click on logical objects under subject area.

In General tab → Deselect the check box Use Logical column name → Edit the name field → Click OK.

Similarly, you can rename all the objects in the Presentation layer without changing their name in BMM layer.

To order the columns in a table, double-click on the table name under Presentation → Columns → Use up and down arrows to change the order → Click OK.

Similarly, you can change objects order in all presentation tables under Presentation area. Go to File → Click Save to save the Repository.

Check Consistency and Load the Repository for Query Analysis

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

Once you click OK → Business model under BMM will change to Green → Click 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 and click Login.

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

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

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

Loading the Repository

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

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

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

Repository is successfully created and loaded for query Analysis.

OBIEE stands for Oracle Business Intelligence Enterprise Edition, a set of Business Intelligence tools and is provided by Oracle Corporation. It enables the user to deliver 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.

Key Points

• OBIEE provides robust reporting which makes data easier for business users to access.

• OBIEE provides a common infrastructure for producing and delivering enterprise reports, scorecards, dashboards, ad-hoc analysis, and OLAP analysis.

• OBIEE reduces cost with a proven web-based service-oriented architecture that integrates with existing IT infrastructure.

• OBIEE enables the user to include rich visualization, interactive dashboards, a vast range of animated charting options, OLAP-style interactions, innovative search, and actionable collaboration capabilities to increase the user adoption. These capabilities enable your organization to make better decisions, take informed actions, and implement more-efficient business processes.

Competitors in the Market

The main competitors of OBIEE are Microsoft BI tools, SAP AG Business Objects, IBM Cognos and SAS Institute Inc.

As OBIEE enables the user to create interactive dashboards, robust reports, animated charts and also because of its cost-effectiveness, it is widely used by many companies as one of main tool for Business Intelligence solution.

Advantages of OBIEE

OBIEE provides various types of visualizations to insert in dashboards to make it more interactive. It allows you to create flash reports, report templates, and ad-hoc reporting for end users. It provides close integration with major data sources and can also be integrated with third party vendors like Microsoft to embed data in PowerPoint presentations and word documents.

Following are the key features and benefits of OBIEE tool −

How to Sign in to OBIEE?

To sign into OBIEE, you can use web URL, user name and password.

To sign into Oracle BI Enterprise Edition −

Step 1 − In the Web browser address bar, enter URL to access OBIEE.

The “Sign In page” is displayed.

Step 2 − Enter your user name and password → Select the language (You can change the language by selecting another language in the User Interface Language field in the My Account dialog Preferences tab”) → Click on Sign In tab.

It will take you to the next page as per configuration: OBIEE homepage as shown in the following image or to My Dashboard page/Personal Dashboard or a Dashboard specific to your job role.