By: Siddharth Mehta
Overview
SSAS Tabular mode is an actively developing area in SSAS. SQL Server 2016 introduces a lot of enhancements in tabular mode of SSAS especially in the DAX formulas. Before we start looking at different aspects of Tabular data modeling in the next chapter, let us first understand some fundamentals of SSAS Tabular mode.
Explanation
What is SQL Server Analysis Services - Tabular Mode?
- SSAS Tabular mode allows us to develop tabular models using a relational modeling approach which is easier to master as compared to the dimensional modeling approach used in SSAS multidimensional mode.
- Data sourced from source systems into the tabular model is highly compressed for optimal storage and processing. Data compression in the tabular model is generally superior to compression in Multidimensional models.
- Tabular models are deployed as tabular databases on the top of an SSAS Tabular instance.
- Tabular databases can host the data in memory (known as In-Memory mode) or can directly query the underlying source system (known as DirectQuery mode).
What is Vertipaq / xVelocity Engine?
Vertipaq in an in-memory columnstore engine introduced with SQL Server 2008 R2. In SSAS 2012, this engine was enhanced with new in-memory analytics features and other capabilities and rebranded as xVelocity. In SSAS 2016, new features and functions have been added to tabular mode.
- PowerPivot and SQL Server Analysis Services - Tabular mode (SSAS going forward) uses xVelocity engine which employs different features and algorithms for self-service and enterprise scale business intelligence respectively.
- Using SSAS and SQL Server Data Tools (SSDT), one can create Business Intelligence Semantic Models (BISM). These models can also be referred as Tabular models in case of SSAS (Tabular).
- These models can source data from multiple data sources which can be queried using Data Analysis Expressions (DAX) language.
What is Data Analysis Expressions (DAX)?
SSAS loads data from BISM models into memory (RAM) in a proprietary format, also termed as data processing. A programming language is required to query and compute this data. DAX was introduced in 2010 to serve this purpose. Below are some quick facts about DAX.
- DAX is an expression language, which in simple terms can be considered as a function library.
- DAX like any other library does not contain programming language constructs like loops, statements, etc. Though it contains functions that can iterate over data.
- DAX works only with tables and columns which constitutes a tabular data model.
- DAX is not a query language which contains typical query constructs like SELECT and WHERE clause. It contains similar functionality in the form of functions that can filter data.
- DAX contains very limited options for data modeling, but the speed of processing extremely huge volumes of data in memory compensates for the modeling limitations.
Additional Information
- Consider reading this article to gain some more understanding about the difference between multidimensional and tabular models.
