SUMMARY

This section explains how to work with objects in the Business Model and Mapping (BMM) layer which is the backbone of an OBIEE application. It also explains other BMM layer concepts like logical tables, joins, and columns.

This section comprises following topics:

Building the BMM layer of a Repository

BMM layer – Physical layer Relation

1. BMM Layer

BMM layer defines the business or logical model of the data. In BMM layer the actual modelling will be started according to the user requirement. Once we create the presentation layer then we do a consistency check. Anytime we create changes in the repository, we do consistency check that tries to find any error in your modelling or something which is not according to the OBIEE principle through a warning or error. If it is error definitely we have to come with the solution to get rid of the error in order to deploy the rpd.

• The BMM layer specifies the mapping between the business model and physical layer schemas.

• Business models are always dimensional, unlike physical layer objects that reflect the organization of the data sources.

• This layer can contain one or more business models. Each business model consists of logical dimensions, facts, hierarchies and metrics. For e.g. the time dimension can have different levels or hierarchies like month, quarter or year.

Note: Oracle recommends is even though the hierarchy is flat, also then you better create a hierarchy so that the join conditions will be properly ordered. 

• BMM layer-Physical layer relation

Business model objects map to the source data objects in the physical layer. Typically, business models map to physical schemas, logical tables map to physical tables, logical columns map to physical columns and so on.

Mapping between business model objects and physical objects do have one-to-one and many-to-many relationship. So one BMM layer can correspond to many physical objects.

One column in the BMM layer can refer multiple attributes in the physical layer

• Objects in BMM layer

• The BMM layer contains the business model definitions as per the user requirement.

• It is the highest level object in the BMM layer.

• Simplifies the physical schema.

• Captures how user thinks about their businesses by their own vocabulary.

1. In the Admin Tool, right-click in the BMM layer below any objects.

2. Select New Business Model from shortcut menu.

3. Provide a name for the business model.

4. New business models are disabled by default. If you want to make the presentation layer available for queries, select disabled.

5. Specify description of the business model which is optional.

6. Click OK.

After you create a business model, you can create business model objects by dragging and dropping objects from the physical layer.

• Creating Logical table and Columns

When you create the logical table in the BMM layer it will be always displayed with the icon . The logical fact table can be represented by with # symbol included.

• Logical Table

• Represent fact or dimension data.

• Can be created automatically by dragging tables from the physical layer.

Note: You must include the table with foreign keys if you want to preserve the keys and joins from the physical layer

• If a table does not exist in your physical schema, the logical table can be created manually by right-clicking the business model and selecting new object-> Logical Table.

• If the table is a lookup table, select the option Lookup table. A Lookup table stores multilingual data corresponding to the rows in the base tables.

• Can be modified without affecting the physical layer objects. Dimension table should have a key whether the fact table contains metrics.

Logical Table Sources (LTS)

• LTS defines the mapping between BMM layer to the physical layer.LTS will correspond to physical table definitely. For e.g. the tables dim-time and dim-products can correspond to multiple physical tables but one LTS corresponds to one physical table.

• Logical tables can, and routinely do, have many physical table sources. For e.g. revenue information may come from multiple data sources like for e.g., manually entered spreadsheet in a business.

• A single logical column can map to many physical columns from multiple physical tables including aggregate tables.

• You may have three different business units (each with its own order system) from which you get the revenue information and each business unit revenues goes to different table.

LTS: Column Mapping

• Double-click a logical table source to open the LTS dialog box

• Click the column mapping tab to build, view, or modify logical-to-physical column mapping

• A mapping can be used to specify transformations that occur between the physical layer and the BMM layer

  • The transformations can be simple, such as changing integer datatype to a character type

  • The transformations can be complex, such as applying a formula to find a percentage of sales per unit of population.

• The data type of a logical column is determined by its logical table source mappings.

• To map logical columns to physical columns

• In BMM layer of the Admin Tool, double –click LTS.

• The logical column can also be mapped to physical column. LTS relates to one physical table while one logical column relate to one or many physical columns.

• Logical columns can be created automatically by dragging tables from the physical layer to the BMM layer

• You can manually create logical columns that involve calculations based on other logical columns. To create a logical column manually, right-click a logical table and select New Object->Logical Column

• A single logical column may map to many physical columns in the physical layer. For example, a logical column may map to both physical column in a detailed table and a physical column in aggregate table

Logical Primary Key

After creating tables in the Business Model and Mapping layer, you specify a primary key for each dimension table. Logical dimension tables must have a logical primary key.

• Logical primary keys define unique identifiers for logical tables. For a business model to be valid, each logical dimension table must have a logic primary key.

• Logical fact table do not require keys for a business model to be valid. Logical primary keys consist of one or more logical columns.

• The logical key defines the lowest level (most detailed level) of information of any source in the logical table.

• After creating tables in the BMM layer, you can manually specify a primary key for each table by right-clicking the table and selecting New Object->Logical Key, or double-clicking a table and clicking the key

• Every logical column should have unique key otherwise the rpd will be invalid. Because everything is logical and nothing exists physically then it will be difficult for OBIEE server to identify the granularity and joint condition index.

Logical Joins

• Logical joins express the cardinality relationships between logical tables and are a requirement for the valid business model.

• Specifying the logical table joins is required so that Oracle BI server has the necessary metadata to translate logical requests again the business model into SQL queries against the physical data sources.

• Logical joins help Oracle BI server understand the relationships between the various pieces of the business model.

• Creating Logical Joins

In OBIEE10g there are two types of joins namely simple join and complex join. Simple join is used for physical tables whereas complex join is used for logical tables.

1. Click the New Join button on the Administration Tool toolbar:

2. In the Business Model Diagram, left-click the first table in the join (the table representing many in the one-to-many join) to select it.

3. Move the cursor to the table to which you want to join (the table representing one in the one-to-many join), and then left-click the second table to select it.The Logical Join dialog appears.

4. To specify a driving table for the key, select a table from the Driving table list, and an applicable cardinality.

Measures

Measures are the facts that a business uses to evaluate its performance in a business model.

A measure column is a logical column with an aggregate function. Any column with an aggregation rule is a measure. Examples include Revenue or Units Sold.

Measures need to be defined in a logical fact and to be computed correctly from the base physical data when Oracle BI server receives a logical query from end-user tool.

Selvi