TRAINING (OBI-5) - OBIEE ADMIN TOOL - Part3 (Building Business Model and Mapping Layer)

BUSINESS MODEL & MAPPING LAYER

• BMM Layer is also called as logical or business layer.
• BMM layer is the second layer in the repository development.
• BMM layer contains ‘n’ number of business models also known as subject area or data mart. 
• BMM layer objects map to source data objects in the physical layer.
• In BMM layer physical schema and objects are simplified to represent the user's view of the data i.e. technical terminology converted into business terminology. Eg: a column name EXPND going to be converted into Expenditure.
• Maximum effort in rpd development is spend in this layer (approx 80% of overall rpd development)

Business Model Mappings

BMM & Physical layer objects mappings are typically not one-to-one:

• One Business Model may map to multiple/different data sources.
• One Logical Tables may map to multiple/different physical tables.
• One Logical Columns may map to multiple/different physical columns.
• One Logical Table Source may map to many physical sources

Business Model and Mapping (BMM) Layer Objects
BMM layer contains business model objects.



1.) Business Model

 

• Highest level object in the BMM layer.
• Contain the business model definitions and the mappings from logical to physical tables.
• Simplifies the physical schema.
• Object names are more business specific rather then technical.

2.) Logical Display Folder




3.) Dimension Hierarchies




4.) Logical Tables

• Can be created:
• Automatically by dragging tables from Physical layers
• Manually by right clicking business model and selecting New Object > Logical Tables
• Can be modified without affecting physical layer objects.
• Can be of two categories.
• Logical Dimension TableRepresent Dimension data

          

• Logical Fact Table
  Represent Fact data

              

5.) Logical Table Sources

• Defines the mapping from a Logical Table to a Physical Table
• Logical tables may have multiple logical table sources
• One Logical Table Source may map to many physical sources

• Logical Table Source: Column Mappings
  Double click the Logical table Source > Click the Column Mapping tab to build, view or modify logical to physical column mappings.
  
  
  
  

6.) Logical Columns

• Represent the business view of the data
• May map to many column in the Physical layer
• May be defined by other logical columns
• Can be created:
• Automatically by dragging tables or columns from the physical layer
• Manually by right clicking a logical table and selecting New Object > Logical Column
  
  

7.) Logical Primary Keys

• Define unique identifiers for logical tables.
• Are required for each logical dimension table for a valid repository.

8.) Measures (columns with a scale icon)

• Are the facts a business uses to evaluate its performance
• Are calculations that define measurable quantities
• Are created on logical columns in the fact table
• Have a defined aggregation rule

9.) Logical Joins

• Express the cardinality relationship between logical tables and are required for a valid business model.
• Logical Join forms the foundation for BI server to understand the relationship between various objects of Business Model and how to construct the physical queries at run time.
• Logical SQL won't generate with
• Arrow mark at Dimension table end or at one's end (in one to many relationship). 
• Logical join has no key relationship or expression defined as compared to physical join.

Why are joins defined again in Logical layer, when the joins are already defined at physical layer?
Since in Physical layer we can't define
1.) Driving Table (Which table will drive the query execution)
3.) Type of join (Inner, Left Outer, Right Outer, Full Outer Join)
2.) Cardinality (Relationship between tables 1-1, 1-M etc)
Also
4.) In BMM layer BI Admin tool can't understand if the table is dimension or fact table without join and assign dimension icon to all the tables until join is not defined. Once join is defined Logical fact tables icon is with # symbol and Logical dimension table without # symbol.
5.) Logical SQL is created on the basis of BMM Joins used for optimizing query which is used by BI sever.

Driving Table - Always select driving table which have less number of rowsas it helps in performance of SQL execution. Generally dimension tables contains less number of records, and is selected in Logical join as Driving table.

How to build Logical Layer:

1. Create the logical business model
Refer above notes.

2. Create the logical tables and columns
Refer above notes.

3. Define the logical joins

How to view existing Logical Joins in Logical layer:
Select the Business Model in BMM Layer > Right Click and go to Business Model Diagram > Whole Diagram > You should now be able to see the logical joins already created



Double click the arrow should open the Logical join properties.

How to create Logical joins in Logical Layer: 
> Select the Business Model in BMM Layer
> Right Click and go to Business Model Diagram
> Whole Diagram
> Click on new join icon  on toolbar
> Drag the Join Tool from D1 Manish First Logical table EMP to D1 Manish Second Logical table DEPT table ( Arrow mark at Dimension table end or at one's end (in one to many relationship))
> Click OK



Note that Logical join has no key relationship or expression defined as compared to physical join.

4. Modify the logical tables and columns
Rename, Reorder, Add, Delete logical tables, columns, sources.

5. Define the measures
Right Click the Logical Column and select Properties > Aggregation > Selection one of the Default Aggregation Rule from drop down > Click on OK > Logical Column icon changes to Scale representing a Measure.