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"
0 comments:
Post a Comment