OBIEE Repository Development Basics | Business Model Mapping Layer development | BMM Layer | Logical layer in OBIEE

Business Model Mapping Layer development(BMM Layer) :

In previous article we got idea of basics of RPD development as how to develop RPD Physical layer;In this article i will try to explain how to develop business model layer which is heart of OBIEE reporting.Business model mapping layer will convert actual business model to technical model and using this layer user will add the actual business logic.In Business mode layer we will simplify the table name,column names and business requirements such that business users will understand the requirements. BMM layer is also called as Logical Layer/Business layer.There are 90 percent of RPD work is done in BMM Layer. Business model is one business area or subject area or data mart which gives the idea of one of the business functionality. As an example Customer management module is one business area or business model of a particular business.

Business model layer is layer where the physical schemas are simplified and recognized to form basis of users view of data.

 

Implementation of Business Model :

There are following steps we are using for creation of Business Model in BMM Layer:

1.Create a Logical Business Model

2.Create Logical tables and columns

3.Define Logical joins

4.Modify Logical tables and columns

5.Defining Measures.

Step 1 : Create a Logical Business Model 

The Business model mapping layer is the second layer of OBIEE RPD which contains the data mart which contains one of the functionality of the business. To create business model in RPD just right click on BMM layer (anywhere in the space of BMM layer) and select the option as New Business Model.Data Mart is also called as Subject area or business area.

Business Model

After creation of Business Model you need to deselect the disabled option and enable the business model. Example of Business model is ‘Customer Management’ is one of the business model.

Step 2 :Create Logical tables and columns

Logical tables are tables in BMM layer which are directly connected or  mapped to physical tables in physical layer.Business model diagram should contain at least 2 tables to define relationship between them.Basically we are using the logical tables to convert the complex physical layer snowflakes schema in to star schema. There are 2 ways to create logical tables in BMM layer :

Method 1 : Dragging Physical tables to BMM Layer 

This is one of the fastest way to define logical tables.User needs to directly drag the physical table from physical layer and drop it to the BMM layer.It preserves joins and keys automatically.One of the disadvaantage of this method is it preserves all columns also,so if user needs to do report development for only few columns then he/she needs to drop unused columns.

Method 2 : Create Logical Table Manually

The second method is creating the logical table manually.Follwing steps are useful to create Logical table manually

Click on BMM Layer –>New Object –> Logical Table

Enter the name of Logical table and click on OK

After creating the logical tables next step is to create logical columns.To create logical columns there are 2 methods.

Method 1 : Logical columns comes with dragging the physical table

Logical columns are automatically created while user drags physical table from Physical layer to BMM layer.If the logical column has primary key then you will able to see the key icon for that column.If the column has aggregation function you will able to see column with sigma.You can also reorder logical columns in BMM layer.

Method 2 : Logical columns creation manually

User can create logical colums manually by following steps.

Right click on Logical table–>Select new object –>Logical column–>General tab

Type Logical Column name

You will see Belongs to table field where you will select mapping

Click on OK

Step 3: Define Logical Joins

Logical joins are joins which are used to convert the physical layer snowflakes schema diagram in to star schema diagram.Logical joins are nothing but the joins which we have mentioned in physical layer;but we can not mention the type of joins on physical layer.So we need to define logical joins.

Need of Logical join :

1. Cardinality not possible in physical layer

2. Mentioning driving table is also not possible in Physical layer

3. Identification of Fact and Dimension tables

4. Optimized SQL Query creation

Before going for logical join creation we need to know about 2 new concepts

1.Cardinality

2.Driving table

  • Cardinality :

Cardinality is nothing but the uniqueness of data values contained in the column. There are 4 types of cardinalities and which identifies in OBIEE as follows:

1.One to One Cardinality  (N to N)

2.One to Many Cardinality (N to (0,1))

3.Many to One ((0,1) to N)

4.Many to Many (0,1) to(0,1)

  • Driving table :

Driving table is a small table which has less number of records.This approach is used for performance optimization of the reports.

Conditions to create Logical joins automatically :

When you drag all physical tables from physical layer to business model layer all logical joins are automatically created.Logical joins in BMM layer can not be specified using expressions or columns like physical join.User will able to change the joining type to Left outer or right outer or full outer join in BMM layer.

Using Join Manager :

Using following steps for creating joins using join manager :

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.

 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.

Step 4 : Modify Logical Tables and columns

User can modify the logical tables and columns .User can add logical table sources in the logical table.User will able to add new expressions in logical table by creating new columns.User can rename logical tables or columns.

Step 5 :Defining Measures 

Measures are columns which has mapped with one aggregated rules.User can define the aggregate columns in the BMM layer.Measures are used in Performance optimization of the reports.

Steps to define measures :

1.Goto table and select the column –> Goto Aggregation tab

2.Select aggregate function from Drop down list and click on OK

There are various aggregate functions can be used like Average,Sum,Count,Max,Min.

 

Points to Remember :

You need to remember the following points for creation of BMM layer :

1.Create Logical Business Model

2.Create Logical Tables

3.Define Logical Joins

3.1.Cardinality Concept

3.2.Fact-Dimension Identification

3.3.Driving Table

3.4. Types of Joins

4.Modify Logical tables and joins

5.Define Measures.

 

Hope this article will help you for creating RPD.Please dont forget to comment in comment section.

About admin 138 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

3 Trackbacks / Pingbacks

  1. Table partition | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  2. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  3. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples

Leave a Reply

Your email address will not be published.


*