Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server DAX Tutorial Overview



By:
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.

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.

  1. Basics
    • Introduction to Vertipaq Engine and DAX
    • Tabular SSAS and DAX Terminologies
    • Data types and operators
  2. Selecting data
    • Formulas and Contexts
    • Evaluate clause
    • Filter clause
  3. Joining data
    • Summarize
    • AddColumns
  4. Navigating and Sorting data
    • Parent-child Functions
    • RANKX function for advanced sorting
  5. Validating data
    • Information functions
  6. 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.






More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools