The Business Intelligence Semantic Model In SQL Server

The Business Intelligence Semantic Model is the latest model created by Microsoft that supports all the applications in BI stacks. Additionally, the intent of such a model is to provide efficient, rich, and scalable analytical capabilities. The important highlight of the BISM model is its integration of data from heterogeneous data sources like LOB applications, relational databases, and unconventional sources like cloud, Excel, or text files. Further, the BISM comes with a three-layer architecture: the data model, business logic and queries, and data access

Data Model:

Now, it’s the choice of the developers or analysts working in BISM architecture to choose either a multidimensional data model or a tabular one. Additionally, the integration of the model can also be in an Analytical Services server or PowerPivot.

Business Logic and Queries:

Yet another choice for the analysts and developers to choose between the Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX).

MDX – The MDX is based on the multidimensional concepts that are like query language for online analytical processing (OLAP). Further, the OLAP uses a database management system. And the language can also be used for calculation purposes like spreadsheet formulas.

DAX – The DAX is based on the tabular concepts which are a native query language for Microsoft PowerPivot, Power BI and SQL Server Analytical Services (SSAS). Some of the similar formulas used in the Excel is as well used in the DAX for formulating purposes. The purpose of DAX is to facilitate with simple, reliable and flexible feature while encroaching the PowerPivot and SSAS tabular models.

Data Access Layer:

The data access layer merges the data from multiple data sources such as relational databases like the DW, files line of business (LOB) applications. The data can be either server locally or can be passthrough directly from the data sources. Further, serving locally is called the “Cached mode” and passthrough is called the “pass-through mode”.

Cached Mode: The cached mode can use either of the two storage engines. One that comes with the MOLAP that supports the multidimensional model and the other latest engine is “VertiPaq”.

Pass-through Mode: By the name just accept the data without saving the data from the data source. However, the ROLAP and DirectQuery is the complementing model to the cached mode counterpart. 

Interview questions you may be interested:

"Difference Between Clustered Index And Non-Clustered Index In SQL Server"

"Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever"