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.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019
