Overview
SQL Server Analysis Services (SSAS) Tabular mode databases can be queried in two forms – In-memory and DirectQuery. In-memory mode runs within memory and enables superior speed data access to tabular models from reporting clients like Excel and PowerView. For tabular models that are hard to accommodate in memory, DirectQuery mode facilitates direct data access from back-end sources. One thing common between these two modes is the query mechanism using Data Analysis Expressions (DAX). The latest version of SSAS Tabular supports MDX queries as well.
Data Analysis Expressions (DAX) is perceived to be a query language for Tabular SSAS, Power BI Desktop and PowerPivot. Technically, DAX is a library of functions and operators that can be used to develop a formula that can be used with the Vertipaq Engine. The similarity of DAX formulas with Excel formulas helps developers to familiarize themselves with the syntax of DAX comparatively faster.
Explanation
As with every programming language / library, it has its own set of functions, data types, operators and scoping mechanisms. Generally one who works with data would have come across some form of SQL at some point of time in his/her career. Assuming that you have a basic understanding of SQL, we will start the tutorial with the basics of the Vertipaq Engine which powers Tabular SSAS. We will cover the terminologies and a few basic constructs of DAX. Once understood, we will look at different ways of selecting, joining, navigating, sorting, validating and calculating data using DAX.
- Basics
- Introduction to Vertipaq Engine and DAX
- Tabular SSAS and DAX Terminologies
- Data types and operators
- Selecting data
- Formulas and Contexts
- Evaluate clause
- Filter clause
- Joining data
- Summarize
- AddColumns
- Navigating and Sorting data
- Parent-child Functions
- RANKX function for advanced sorting
- Validating data
- Information functions
- Calculations and Dates
- Calculate and CalculateTable Function
- Date and Time Intelligence Functions
In this tutorial we will step through each of the above mentioned topics with example code-snippets on a number of DAX constructs. So let’s get started with the basics of DAX and the Vertipaq Engine.
