Business Layer 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.

The business model is used to simplify the schema structure and maps the users’ business requirement to physical data source. It involves creation of logical tables and columns in the business model. Each logical table can have one or more physical objects as sources.

There are two categories of logical tables − fact and dimension. Logical fact tables contain the measures on which analysis is done and Logical dimension tables contain the information about measures and objects in Schema.

While creating a new repository using OBIEE administration tool, once you define the physical layer, create joins and identify foreign keys. The next step is to create a business model and mapping BMM layer of the repository.

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 delete unnecessary logical object
Creating measures (Aggregations)

Create Business Layer in the Repository

To create a business layer in the repository, right-click → New Business Model → Enter the name of Business Model and click OK. You can also add description of this Business Model if you want.

Logical Tables and Objects in BMM Layer

Logical tables in OBIEE repository exist in the Business Model and Mapping BMM layer. The business model diagram should contain at least two logical tables and you need to define relationships between them.

Each logical table should have one or more logical columns and one or more logical table sources associated with it. You can also change the logical table name, reorder the objects in logical table and define logical joins using primary and foreign keys.

Create Logical Tables Under BMM Layer

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

First method is dragging physical tables to Business Model which is the fastest way of defining logical tables. When you drag the tables from the 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, it doesn’t affect objects in the physical layer.

Select physical tables/alias tables under the physical layer that you want to add to Business Model Layer and drag those table under BMM layer.

These tables are known as logical tables and columns are called Logical objects in Business Model and Mapping 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.

Go to General tab → Enter name for the logical table → Type a description of the table → Click OK

Create Logical Columns

Logical columns in BMM layer are automatically created when you drag tables from the physical layer to the business model layer.

If the logical column is a primary key, this column is displayed with the key icon. If the column has an aggregation function, it is displayed with a sigma icon. You can also reorder logical columns in the Business Model and Mapping layer.

Create a Logical Column

In BMM layer, right-click on logical table → select New Object → Logical Column → Logical Column dialog box will appear, click General tab.

Type a name for the logical column. The name of the business model and the logical table appear in the “Belongs to Table” field just below column name → click OK.

You can also apply Aggregations on the logical columns. Click Aggregation tab → Select Aggregation rule from the dropdown list → Click OK.

Once you apply Aggregate function on a column, logical column icon is changed to show Aggregation rule is applied.

You can also move or copy logical column in tables −

In the BMM layer, you can select multiple columns to move. In the Sources for moved columns dialog box, in the Action area, select an action. If you select Ignore, no logical source will be added in the Sources folder of the table.

If you click on Create new, a copy of the logical source with the logical column will be created in the Sources folder. If you select Use existing option, from the drop-down list, you must select a logical source from the Sources folder of the table.

Create Logical Complex Joins / Logical Foreign Keys

Logical tables in BMM layer are joined to each other using logical joins. Cardinality is one of the key defining parameter in logical joins. Cardinality relation one-to-many means that each row in first logical dimension table there are 0, 1, many rows in second logical table.

Conditions to Create Logical Joins Automatically

When you drag all the tables of the physical layer to business model layer, logical joins are automatically created in Repository. This condition rarely happens only in case of simple business models.

When logical joins are same as physical joins, they are automatically created. Logical joins in BMM layer are created in two ways −

Business Model Diagram (already covered while designing repository)
Joins Manager

Logical joins in BMM layer cannot be specified using expressions or columns on which to create the join like in the physical layer where expressions and column names are shown on which physical joins are defined.

Create Logical Joins/Logical Foreign keys Using Join Manager Tool

First let us see how to create logical foreign keys using Join Manager.

In the Administration Tool toolbar, go to Manage → Joins. The Joins Manager dialog box appears → Go to Action tab → New → Logical Foreign Key.

Now in the Browse dialog box, double-click a table → The Logical Foreign Key dialog box appears → Enter the name for the foreign key → From Table drop-down list of the dialog box, select the table that the foreign key references → Select the columns in the left table that the foreign key references → Select the columns in the right table that make up the foreign key columns → Select the join type from the Type drop-down list. To open the Expression Builder, click the button to the right of the Expression pane → The expression displays in the Expression pane → click OK to save the work.

Create a Logical Complex Join using Join Manager

Logical complex joins are recommended in Business Model and mapping layer as compared to the use of logical foreign keys.

In the Administration Tool toolbar, go to Manage → Join → Joins Manager dialog box appears → Go to Action → Click New → Logical Complex Join.

It will open a logical Join dialog box → Type a name for the complex join → In the table drop-down lists on the left and right side of the dialog box, select the tables that the complex join references → Select the join type from the Type drop-down list → Click OK.

Note − You can also define a table as driving table from the drop-down list. This is used for performance optimization when the table size is too large. If the table size is small, less than 1000 rows, it shouldn’t be defined as driving table as it can result in performance degradation.

Dimensions and Hierarchical Levels

Logical dimensions exist in BMM and Presentation layer of OBIEE repository. Creating logical dimensions with hierarchies allows you to define aggregation rules that vary with dimensions. It also provides a drill-down option on the charts and tables in analyses and dashboards, and define the content of aggregate sources.

Create logical dimension with Hierarchical level

Open the Repository in Offline mode → Go to File → Open → Offline → Select Repository .rpd file and click on open → Enter Repository password → click OK.

Next step is to create logical dimension and logical levels.

Right click on Business model name in BMM layer → New Object → Logical Dimension → Dimension with level-based hierarchy. It will open the dialogue box → Enter the name → click OK.

To create a logical level, right-click on logical dimension → New Object → Logical Level.

Enter the name of logical level example: Product_Name

If this level is Grand total level, select the checkbox and the system will set number of element at this level to 1 by default → Click OK.

If you want the logical level to roll up to its parent, select the Supports rollup to parent elements checkbox → click OK.

If the logical level is not the grand total level and does not roll up, do not select any of the checkbox → Click OK

Parent-Child Hierarchies

You can also add parent-child hierarchies in logical level by following these steps −

To define child logical levels, click Add in the Browse dialog box, select the child logical levels and click OK.

You can also right-click on logical level → New Object → Child level.

Enter the name of child level → Ok. You can repeat this to add multiple child levels for all logical columns as per requirement. You can also add Time and Region hierarchies in a similar way.

Now to add logical columns of a table to logical level → select logical column in BMM layer and drag it to logical level child name to which you want to map. Similarly you can drag all the columns of logical table to create parent-child hierarchies.

When you create a child level, it can be checked by a double-click on the logical level and it is displayed under child levels list of that level. You can add or delete child levels by using ‘+’ or ‘X’ option on top of this box

Add Calculation to a Fact Table

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

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

There are various Aggregate functions that can be used like Sum, Average, Count, Max, Min, etc.

OBIEE – Presentation Layer

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.

So, this brings us to the end of blog. This Tecklearn ‘Business and Presentation Layer of OBIEE explained’ blog helps you with commonly asked questions if you are looking out for a job in Oracle BI. If you wish to learn OBIEE and build a career in Business Intelligence domain, then check out our interactive, Oracle Business Intelligence Enterprise Edition (OBIEE) Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-business-intelligence-enterprise-edition-training-and-certification/

Oracle Business Intelligence Enterprise Edition (OBIEE) Training

About the Course

Oracle Business Intelligence Enterprise Edition (OBIEE) Training and Certification training course lets you master the Oracle Business Intelligence and Analytics platform. You will gain in-depth knowledge on OBIEE advanced features, BI dashboards, reports, etc., through hands-on training sessions. This course is meant to provide detailed knowledge of the OBIEE 12c. You will be trained in various Business Intelligence scenarios, BI life cycle, concepts of OLAP and working with charts and graphs.

Why Should you take OBIEE Training?

The average salary for “oracle business intelligence consultant” ranges from approximately $76,703 per year for Business Consultant to $113,285 per year for Full Stack Developer. -Indeed.com.
Oracle Business Intelligence has a market share of around 8% globally.
OBIEE gives you experience with next-generation solutions that can help strategize, plan and optimize business operations. Allow you to become an Oracle Certified Expert or Oracle Implementation Specialist of an in-demand solution.

What you will Learn in this Course?

Data Modelling and Data Warehousing Concepts

Introduction to Oracle Business Intelligence Enterprise Edition (OBIEE)
Data models
Data warehousing
Data modelling implications and the impact of Data modelling on business intelligence

Business Intelligence Concepts

Overview of Business Intelligence
Description of BI Stack: BI technology, BI Server and BI Scheduler
Need for reporting in business
Difference between OLTP and OLAP
Multidimensional and relational analytical processing

OBIEE Installation

OBIEE Installation (12c)
Oracle Business Intelligence Suite
Architecture of OBIEE
Key features and components

Online Analytical Processing (OLAP)

Concept of Online Analytical Processing
Significance of OLAP in business intelligence life cycle
Star Schema and Designing with Star Schema
Enterprise information model
Snow flake and constellation

Oracle BI Repository

What is Oracle Business Intelligence Repository?
Directory structure installation, services,
Analytics and interactive reporting,
Dashboard creation and multiple report creation

Business Intelligence Repository Business Model

How to build a Business Model and Mapping Layer in BI Repository
Data format, Conditional format, Removing filters,
Report Saving
Creation of new folder
Working with the Enterprise Manager
Testing and validation of the Repository
Understanding the process of Cache disabling
Dashboard prompt and filtering

Dashboard Creation

Creation and Setup of OBIEE Dashboard
Basics of OBIEEE dashboard
Deploying Dashboard Builder for building Dashboards
Edit, Share and Save options in Dashboard analysis
Process of Cache creation and clearing
ODBC functions
Logical Table Source, Summary and Detail Report

OBIEE Repository

OBIEE Repository
Creation of Test Report and adding calculations
Deployment of OBIEE analysis
Repository variables, session and presentation variables

OBIEE Security & Management

Securing the Oracle Business Intelligence Suite with Enterprise Manager
Creation of alerts and notifications
Administration, Maintenance and Grouping
Types of security in OBIEE
Task and folder level security

Differences between OBIEE 11g and 12c

Hands on Variables

New Features of OBIEE 12c

Repository Development
Catalog Development
Deployment-Online
RPD Deployment

577