In this post we will look at an important best practice for a data warehouse; documenting the semantic layer.

A quick review of the key aspects of a data warehouse will be helpful. Bill Inmon the father of the data warehouse, simply states that it is “a subject oriented, nonvolatile, integrated, time variant collection of data in support of management’s decisions” [1]. Supporting business decisions; this is what really separates a data warehouse from a generic database. Which leads us to our thoughts on the semantic layer, which is crucial to translate raw data into business meaning.

Accessing the raw tables in a data warehouse can be confusing for an end user (this rarely happens, right?). A semantic layer shields a user from raw data field names and allows the navigation and creation of reports based on data in the warehouse. This critical concept can often get overshadowed by focusing on the latest database tools and technology. However without a solid semantic layer, developers, users and especially non-technical business leaders can’t make sense of what is in the warehouse.

• For example a raw data attribute from the warehouse:
  DIM_CUST_RISK_SCORE_LKP.RISK_AVG_PD = .25

A data savvy user examining this information directly in the warehouse would see a table called DIM_CUST_RISK_SCORE_LKP and a field called RISK_AVG_PD with a value of “.025”. What exactly does this mean? A user may have some context, especially with some institutional knowledge, but much is left to guess work.

• With a semantic layer the same data attribute can be presented as:
  ACME Customer’s Average Risk of Default = 25%

This example shows how important it is to translate raw data warehouse information into something business users can understand. Now onto the best practice suggestion:

• Document the location(s) of the semantic layer
• Ensure semantic values are easy to update
• Just like a Slowly Changing Dimension (SCD), keep a history of your semantic values.

The location of this semantic information can be in the warehouse itself, which is the traditional approach, or in a BI tool.  Keeping the semantic layer in a BI tool can become complicated, especially when more than one tool is used.  In this case the semantic layer becomes distributed because each BI tool will often have its own way of creating and maintaining the semantic layer.

In summary, understanding the value of the semantic layer, and documenting it properly is a good best practice to ensure that business data is presented clearly to users.

[1] https://en.wikipedia.org/wiki/Bill_Inmon