SQL Server DAX Basics an Introduction

By: Siddharth Mehta

Overview

Data Analysis Expressions (DAX) is generally used with SQL Server Analysis Services - Tabular mode. In this chapter we will learn about some basics of DAX as well as the underlying engine that powers DAX.

Explanation

Before we start understanding the key terms, it is highly recommended to have some basic experience of SSAS Tabular databases. This would make understanding and working with DAX easier.

What is SQL Server Analysis Services - Tabular Mode?

SQL Server Analysis Services is available in different server modes like Multidimensional and Data Mining, Tabular and PowerPivot.

• SSAS Tabular mode allows development of tabular models using a relational modeling approach which is comparatively easier to master, 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 a tabular model is generally superior to compression in Multidimensional models.
• Tabular models are deployed as tabular databases on the top of a SSAS Tabular instance.
• Tabular databases can host the data in memory (known as In-Memory mode) or can directly query the underlying source system to remove data management on the source system (known as DirectQuery mode).

What is the Vertipaq Engine?

Vertipaq in an in-memory columnstore engine introduced with SQL Server 2008 R2. In SQL Server 2012, this engine was enhanced with new in-memory analytics features and other capabilities and rebranded as xVelocity.

• 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 to as Tabular models in the case of SSAS (Tabular)

• These models can source data from multiple data sources which can be queried using the 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 for 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.