In this section, we will define the semantic layer and then describe the main role of semantic layers in BI reporting solutions.

The semantic layer acts like a translator. A translator is an expert in two languages at least. During a conversation, he or she will listen to the first person, understand what they said, and then translate the same message into a language that the other person can understand and vice versa.

The semantic layer does exactly the same thing. The end user can understand it and communicate with it, because it talks the same business language. Then, the other part translates the request into a technical language that can be submitted to the database. After that, it does the opposite by receiving the data from the database in a raw format and then translates it in a format that can be interpreted by the end user. So, it is like a man with two faces. One face is the technical face (evil one), which talks to the database, and the other one is the business user face (good one), which talks to the business end users.

The main idea of the semantic layer is to translate and simplify complex technical information in a way that business people can understand and utilize. The information is stored in a database, and it requires many technical skills to access it directly and do the required analysis. The semantic layer will isolate all technical staff from the actual business information needed, and it provides an easy way to understand and use a business model that can be accessed directly by the business to do their own reports and on-the-fly analysis.

The semantic layer will consist of two layers at least. The first one is the physical layer that will contain the technical part. In this layer, you will set up your data model by adding the required tables/views and create the proper joins between them. Usually, technical experts will be responsible for building this part as it requires technical skills. The second layer is the business model, which will be visible to the business users. This layer will contain attributes that business users can understand. Every attribute will have its own mapping to the physical layer either directly or it will be a driven attribute (calculated from other physical database columns). This will hide and isolate the complex technical part behind the frontend business model.