Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Semantic Layer

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.

One Response to “Semantic Layer”

  1. […] BI Platform Semantic Layer […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s