http://www.sqlservercentral.com/blogs/salvoz-sql/2013/01/02/querying-and-optimizing-dax/

Printed 2014/08/28 09:58AM

Querying and Optimizing DAX

By Jennifer Salvo, 2013/01/02

Over the past few weeks, I’ve been watching some videos from this year’s PASS Summit.  One of the presentations I watched is ‘Querying and Optimizing DAX’ (BIA-321-S) by Alberto Ferrari.  I took some notes during this presentation and am sharing them here.

DAX as a Query Language

MDX Calculated Members versus DAX Query Measures

Optimizing Tabular versus Multidimensional

DAX Query Architecture

DAX Query Evaluation Flow

SQL Server Profiler

Storage Engine Example Query

EVALUATE

ROW(“Sum”,

               SUMX(‘Internet Sales’,

               [Sales Amount] / [Order Quantity]

               )

  )

Note: ROW returns a table with one row. SUMX does leaf level calculation.

Translated to:

SELECT

       SUM(

          [Internet Sales].[SalesAmount]

           /

         [Internet Sales].[OrderQuantity]

)

FROM

[Internet Sales];

This can be calculated in the Storage Engine because it is a simple division.

Formula Engine and Storage Engine Example Query

EVALUATE

     ROW(“Sum”,

                SUMX(‘Internet Sales’,

                            IF([Sales Amount] > 0,

                                          [Sales Amount] / [Order Quantity]

                                        )

                              )

                   )

Translated to:

SELECT

          SUM([CallbackDataID(

                        IF([Sales Amount] > 0,

                               [Sales Amount] / [Order Quantity]}

                         )

                 )]

                  (   

                    PFDATAID( [Internet Sales].[OrderQuantity] ),

                    PFDATAID( [Internet Sales].[SalesAmount])

                   )

            )

FROM [Internet Sales];

This is too complex for the Storage Engine and involves the Formula Engine as well.  Storage Engine and Formula Engine work in unison to calculate the results, indicated by the CallbackDataID.

Slow Measures and Filters Example Query

DEFINE

MEASURE ‘Internet Sales’[Sales] =

CALCULATE (ROUND (SUM (‘Internet Sales’[Sales Amount] ), 0 ) )

MEASURE ‘Internet Sales’[YTD Sales] =

TOTALYTD ( [Sales] , ‘Date’[Date] )

MEASURE ‘Internet Sales’[QTD Sales] =

TOTALQTD ( [Sales], ‘Date’[Date] )

EVALUATE

FILTER (

     ADDCOLUMNS (

          CROSSJOIN (

                      VALUES (‘Date’[Calendar Year] ),

                      VALUES(‘Date’[Month]),

                      VALUES(‘Date’[Month Name])

            ),

           “Sales”, [Sales],

           “YTD Sales”, [YTD Sales],

           “QTD Sales”, [QTD Sales]

       ),

      NOT ISBLANK( [Sales] )

)

ORDER BY ‘Date’[Calendar Year], ‘Date’[Month]

Issues:

Re-Structure the Query to do the filtering before the computation of YTD Sales and QTD Sales:

DEFINE

MEASURE ‘Internet Sales’[Sales] =

CALCULATE (ROUND (SUM (‘Internet Sales’[Sales Amount] ), 0 ) )

MEASURE ‘Internet Sales’[YTD Sales] =

TOTALYTD ( [Sales] , ‘Date’[Date] )

MEASURE ‘Internet Sales’[QTD Sales] =

TOTALQTD ( [Sales], ‘Date’[Date] )

EVALUATE

ADDCOLUMNS (

     FILTER(

          CROSSJOIN (

                   VALUES (‘Date’[Calendar Year] ),

                  VALUES(‘Date’[Month]),

                 VALUES(‘Date’[Month Name])

          ),

         NOT ISBLANK( [Sales] )

     ),

“Sales”, [Sales],

“YTD Sales”, [YTD Sales],

“QTD Sales”, [QTD Sales]

)

ORDER BY ‘Date’[Calendar Year], ‘Date’[Month]

This query runs 7x’s faster than the other!

Joins are Expensive

Cache Usage

General Guidelines

Top Three Fears of UDM Developers.  How Does Tabular Compare?

Final Thoughts


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.