Building BMM (Business Model and Mapping) Layer in OBIEE 12c Repository
In previous tutorial we have learnt till Physical layer in OBIEE. In this tutorial we will focus on BMM Layer of OBIEE.
The Business Model and Mapping layer organizes information by business model (sort of folder). Each business model contains logical tables. Logical tables are composed of logical columns. Logical tables have relationships to each other expressed by complex logical joins. The relationship between logical columns can be hierarchical, as defined in business model hierarchies. Logical tables map to the source data in the Physical layer. The mapping can include complex transformations and formulas.
During the design process of your logical model, you have to define:
- the fact table with measures
- the dimension table
- the hierarchy (of Dimension in OBI term).
Building a Business Model
- Right-click the white space in the Business Model and Mapping layer and select New Business Model to open the Business Model dialog box.
- Enter Supermarket in the Name field. Leave Disabled checked.
Building logical tables, columns, and sources
- In the Physical layer, select the alias tables
- You must include the table with the foreign keys if you want to preserve the keys and joins from the Physical layer.
- Drag the selected alias table from the Physical layer to the Supermarket business model in the BMM layer.
Note: The dimension tables have the same icon, whereas the F6 Sales table has an icon with a # sign, indicating it is a fact table.
If you want to see the logical joins then:
- Right-click the Supermarket business model and select Business Model Diagram > Whole Diagram to open the Business Model Diagram.
Below picture will appear showing the Joins between Fact and Dimension Tables.
If you want to see the Join Condition between any of the two Tables:
- Double-click any one of the joins in the diagram to open the Logical Join dialog box. In this example the join between D10 Product and F6 Sales Day is selected.
Important Point about Logical Joins:
- Notice that there is no join expression.
- Joins in the BMM layer are logical joins.
- Logical joins express the cardinality relationships between logical tables and are a requirement for a valid business model.
- Specifying the logical table joins is required so that Oracle BI Server has necessary metadata to translate logical requests against 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.
- When a query is sent to Oracle BI Server, the server determines how to construct physical queries by examining how the logical model is structured.
- Click OK to close the Logical Join dialog box.
- Click the X to close the Business Model Diagram.
Building Measures in OBIEE
- Double-click the Sales Value logical column to open the Logical Column dialog box.
- Click the Aggregation tab.
- Change the default aggregation rule to Sum.
- Click OK to close the Logical Column dialog box.
- Repeat the steps to define the SUM aggregation rule for the other measure logical column.
- Notice that the icon has changed for the Sales Value logical column indicating that the aggregation rule has been applied.
Examining Logical Source
- Expand the Sources folder for the D1 Brand logical table. Notice there is a logical table source, D1 Brand.
- This logical table source maps to the D1 Brand alias table in the Physical layer.
- Double-click the D1 Brand logical table source (not the logical table) to open the Logical Table Source dialog box.
- On the Column mapping tab, notice that logical column to physical column mappings are defined.
- If mappings are not visible, select Show mapped columns.
- Click OK to close the Logical Table Source dialog box. If desired, explore logical table sources for the remaining logical tables.
- Expand the D1 Brand logical table.
- Notice that logical columns were created automatically for each table when you dragged the alias tables from the Physical layer to the BMM layer.
In Next Tutorial we will learn about Presentation Layer in OBIEE.