Business Model and Mapping Layer is the semantic layer in BI metadata. Why do we need it? What if we do not have this layer?
1. Provide the physical (Relational) to dimensional mapping
Although we already know which tables are dimension tables and which tables are fact tables in the data warehouse, if you are directly reporting from the OLTP model, the logical mapping is where you are defining the relational to dimensional mapping.
The data model diagrams from the BMM layer are star schema.
2. Snow flake schema is transformed to Star Schema in the BMM layer
This is accomplished by defining multiple tables within a LTS. A LTS can map the attributes from the parent tables to form the denormalization.
Another way to view this feature is that it allows the SQL trimming so the unnecessary table joins will not be used in the query. If you do not model this type of relationship in BI metadata but define the relationship inside the database view, these table will always be seen in the query even though you are not selecting any data.
3. Helper tables are hidden in the BMM Layer
If you are using helper table to adjust the grain of the data, the helper tables are implemented in the physical layer and joined in the physical layer, but their existence will be hide in the BMM Layer.
4. Derived metrics are defined in the BMM Layer
You can define the formula in the LTS mapping or in the logical attribute definitions.
If the metric definition are defined as logical attribute, they are independent from the source model.
5. A common information model which is independent from the source schema
Actually, the universal data warehouse is also designed with this source agnostic nature in mind.
However, the BI tool is designed with this assumption and can be used for this purpose.
In the real life, this is very difficult. The semantic layer of each source systems are known to the system users. Creating a “common” one is actually creating “another” one.
Reports and Dashboards are created against the presentation layer. You can use the same presentation catalog even though the source database are different.
6. Dimension Hierarchies and Levels are defined in BMM Layer
You need to first analyze the requirement about how data are commonly aggregated and design the model upfront.
There is a technical implication about this feature. You can thus define the aggregate tables and the rollup dimensions. Unlike the Oracle database server that can perform the SQL rewrite by redirecting the query to Material View, BI server uses this multiple LTSes mapped in the logical layer to generate different queries based on the level the data is being selected. This is a performance optimization.
Also, with the aggregate persistence and the summary adviser features, The rollup dimension tables and aggregate tables can be generated via the BI metadata. You do not really need to define these by yourselves
7. Translation Lookup – for Performance Optimization
You can define the lookup column and the lookup table for supporting translations.
Lookup is defined as a very special type of join that is evaluated only on the aggregated data.
Although, in OBIEE, this feature is defined in the LTS layer (using physical lookup) or in the Business Model layer as a logical lookup table, there is really no logical dependence that presents us to defining it at the physical layer.
8. Defining the aggregation rule
Not all numbers can be added up. For examples, the temperature of each place cannot be added up. The max, the min, or the average of temperature of multiple places are more meaningful.
9. Conformance across tables
A logical table may have multiple LTSes which point to different physical tables. When a query is generated with different fact tables, the data are shown side by side via a full outer join.
The truth is that this is a rarely used feature in my expereince.
10. Supporting UNION via fragments
The standard way to support UNION is to create fragments in the BMM layer.
You can declare that multiple LTSes are groups as fragments. When the query against the logical table, the data from multiple sources will be pulled and the results will be shown together.
This can be done at the reporting layer at the presentation catalog though.