MDX and DAX topics

DAX Query Plan, Part 2, Operator Properties

2012-01-31T23:38:00.000-08:00

Last time we learned that DAX Query Plans are tree structures formatted as indented text with each text line representing a single operator node in a tree. A text line begins with an operator name followed by a colon and then properties of the operator. Today we study the operator properties. You will see that for the four types of operators, ScaLogOp, RelLogOp, LookupPhyOp, and IterPhyOp, each type has a fixed set of common properties, and an individual operator may contain extra properties to provide supplemental information. We’ll focus on the semantics of the common properties in this post.

List of Columns

In a DAX Query Plan, a list of columns is shown as a list of comma-delimited column numbers in a pair of parentheses plus a list of comma-delimited fully-qualified column names in another pair of parentheses, see Figure 1. In a degenerate case, two pairs of empty parentheses, ()(), represent an empty list. Note that some properties, like LookupCols and IterCols, are not shown in the plan when they contain no columns, but other properties, like DependOnCols and RequiredCols, are always shown even when their list of columns is empty.

Column numbers are helpful when you need to disambiguate two separate references to the same column. When you execute Query 1 against the tabular AdventureWorks database, the logical plan, shown in Figure 2, assigns different numbers to column ‘Date’[Month]: number 1 refers to the column in the outer table scan and number 2 refers to the column in the inner table scan. Column numbers are not chosen to be globally unique, but rather unique within a local context.

// Query 1
define measure 'Internet Sales'[Total Sales Amount] = Sum([Sales Amount])
evaluate
  calculatetable(
    addcolumns(
      values('Date'[Month]),           -- outer scan
      "YTD",
      calculate([Total Sales Amount],
          filter(
            All('Date'[Month]),        -- inner scan
            'Date'[Month]              -- refer to inner scan
            <=
            earlier('Date'[Month])     -- refer to outer scan
          )
      )
    ),
    'Date'[Calendar Year] = 2003
  )

Common Properties of Logical Plan Nodes

Here are the properties common to all scalar logical operators (ScaLogOp):

· DependOnCols

Marks columns from the left-sideof a tree on which the current logical operator depends. The current operatormay return a different value for each distinct combination of values of DependOnCols. Some table scanningfunctions, e.g. AddColumns and Filter, create a row context using itsleft child subtree and then evaluate the value of its right child subtree inthis context. This creates a dependency of the right child subtree on somecolumns from the left child subtree. DependOnColscaptures this correlation between the two sides of a tree. Figure 3 shows anexample where a ScaLogOp subtree onthe right depends on some columns from two RelLogOpsubtrees on the left. At the bottom level of a tree, DependOnCols are established by either an explicit reference to acolumn on the left or by a leaf table scan that joins directly or indirectly(through SetFilter arguments of Calculatefunction) to columns on the left. DependOnColsare then propagated up through intermediate parent nodes to the root node of theright subtree. Since DAX automatic cross-table filtering rules can be trickysometimes, beginners can use this property to help them figure out whethertheir measures have the correct dependencies on external row contexts.

· Data type

One of the six data types DAXsupports. Values returned by the operator must be either of this data type orbe the BLANK value.

· DominantValue

Captures the sparsity of a scalarlogical operator. When DominantValueis NONE, the operator is dense, otherwise, it is sparse. When a scalar subtreeis sparse, DAX Formula Engine may pick a physical plan that can be orders ofmagnitudes faster than a naïve physical plan. For example, if the predicatechild operator of a Filter operatorhas a DominantValue of FALSE, DAXFormula Engine can construct an iterator physical plan for the predicatesubtree that automatically skips large chunks of rows which would otherwisereturn FALSE and be thrown away any way by the Filter operator. For userscoming from MDX background, this reminds them of the huge performancedifference between block mode vs. cell-by-cell mode. The technique to derivethe sparsity of a scalar subtree is very sophisticated and beyond the scope ofthis post. It’s enough to know that a sparse scalar operator is the key togreat performance in many common query patterns.

Here are properties common to all relational logical operators (RelLogOp):

· DependOnCols

Identical to the same namedproperty of ScaLogOp. The currentoperator may return a different table for each distinct combination of valuesof DependOnCols.

· Range of column numbers

Although a relation may containmany columns in its heading, DAX Formula Engine is smart enough to derive theminimal subset of columns, see RequiredColsproperty, which are needed to answer a query. To save space, DAX Query Plandoes not list all columns in the relation header, instead, it assignscontinuous column numbers to all columns in the relation header and only shows<beginning column name>-<ending column name> as a part of the plan.Note that this property may be missing when a relation has no column at all.

· RequiredCols

This is the union of DependOnCols and the subset of columnsfrom the relation header which are needed to answer a query. For example, whenyou examine the logical plan, shown in Figure 4, which corresponds to Query 2,you can see that only one column, [Sales Amount], among 129 columns is arequired column. In case you are wondering why ‘Internet Sales’ table has 129columns, you can find the answer in oneof my earlier posts.

// Query 2
define measure 'Internet Sales'[Total Sales Amount] = Sum([Sales Amount])
evaluate row("x", [Total Sales Amount])

Common Properties of Physical Plan Nodes

In a physical plan tree, an iterator operator supplies rows of column values to other nodes. When those rows are fed to a lookup operator, it can return a scalar value from each input row. When the rows are fed to another iterator operator, it can output any number of rows of its own columns for each input row. Therefore, both iterators and lookups share the same input property, LookupCols, but they produce different outputs. Let’s use the physical plan tree, shown in Figure 5, captured from Query 1 to illustrate the common properties of physical operators.

Here are the properties common to all lookup physical operators (LookupPhyOp):

· LookupCols

Columns supplied by an iteratorwhose values are used to calculate a scalar value. In Figure 5, lookupoperators 1 and 2 read their input values from iterator 3; their output valuesare later on used by their parent operator, LessThanOrEqualTo,to calculate a Boolean value.

· Data type

One of the six data types DAXsupports. Values returned by the operator must be either of this data type orbe the BLANK value.

Here are the properties common to all iterator physical operators (IterPhyOp):

· LookupCols

Identical to the same namedproperty of LookupPhyOp. In Figure 5,iterator 5, which doesn’t have the LookupColsproperty, hence a pure iterator, supplies column 2 to iterator 4 as its LookupCols property which in turnproduces output column 1.

· IterCols

Columns output by the iterator.

It is interesting to learn that a DAX iterator can be a pure iterator, when it only has the IterCols property, or a table-valued function as in T-SQL Apply operator when it has both LookupCols and IterCols properties, or a pure row checker when it only has the LookupCols property. In the last case, the iterator serves the purpose of removing unwanted rows from other iterators.

For many DAX operators, common properties are all they offer. But some DAX operators output additional properties to provide more information about themselves. I am not going to go into details about all those operator-specific properties today because it would drag this blog on far too long. I’ll only describe the proprietary properties of one physical operator Spool_IterOnly and postpone the discussion of private properties of other operators in future blogs when we get to study individual operators.

Special Properties of Physical Operator Spool_IterOnly<>

Spool_IterOnly is a pure iterator that draws its rows from an in-memory spool which is built through some other means. DAX Formula Engine builds different flavors of in-memory spools, therefore Spool_IterOnly along with several other physical operators built from spools put the name of the spool in a pair of angle brackets <> as a part of their names. This is partly caused by the fact that spools are not first class citizens in query plan trees as of SQL Server 2012. As a result, some spool specific properties are added directly to physical operators built on top of the spool. Below is one line I extracted from Figure 5 with Spool_IterOnly specific properties highlighted in bold face. They tell us that there are 12 records in the spool and there are 240 key columns (most of which are compressed to 0 bit hence record size is not as wide as it seems) and no value columns.

Spool_IterOnly<Spool>: IterPhyOp IterCols(1)('Date'[Month]) #Records=12 #KeyCols=240 #ValueCols=0

Summary

Today we studied properties of operator nodes in DAX Query Plan trees. We have learned that some properties are common to all operators of one type and some properties are specific to a particular operator. While I described in details those common properties, I just cited one example of operator specific properties. Now that we have covered the basics of DAX Query Plans, we will be able to explore ways to take advantage of them to investigate performance issues in future posts. When we run into a specific operator of importance, I’ll explain its associated properties at that time.

DAX Query Plan, Part 1, Introduction

2011-12-30T22:22:00.000-08:00

SQL Server 2012 Analysis Services RC0 introduced a new SQL Server Profiler event class, DAX Query Plan, under the Query Processing event category. This is an advanced and rich new event class, but there has been no official document yet. Nonetheless, it has already attracted the attention of some users who are pushing us to release more information as soon as possible. While waiting for an official document to come out, I’ll try to find some spare time to temporarily fill the gap by providing some background information on this event class in a series of blog posts. As always, my goal is to provide accurate information with sufficient technical details. There are plenty of other BI professionals who are eager to help average users to learn this feature through intuitive, practical examples. I’ll be using the tabular model AdventureWorks for SQL Server 2012 RC0 when I need to demonstrate different aspects of DAX query plans through examples.

The DAX Query Plan event class has four event subclasses:

DAX VertiPaq Logical Plan
DAX VertiPaq Physical Plan
DAX DirectQuery Algebrizer Tree
DAX DirectQuery Logical Plan

Trace events of subclasses 1 and 2 are fired when a tabular database is in VertiPaq mode. Trace events of subclasses 3 and 4 are fired when a database is in DirectQuery mode. Since most tabular databases are likely to run in VertiPaq mode, I’ll focus my discussions on the first two types of events.

Logical Plans and Physical Plans

DAX Formula Engine evaluates a DAX expression in multiple stages and generates several tree data structures along the way, see Figure 1. The new trace event outputs two of the trees to help users investigate logic or performance issues. This is a great leap forward from the dark days of debugging MDX expressions. Logical plan trees show the primitive operations that make up the higher level user functions. The powerful yet sometimes mysterious automatic cross-table filtering becomes explicit in logical plan trees. Properties related to the sparsity of a scalar subtree tell you why DAX Formula Engine chooses one execution plan over another. If poor performance is caused by the Formula Engine, physical plan trees can help you locate the expensive sub-expressions that caused the problem.

Format of the Query Plans

Let’s first study the general structure and format common to both types of plan trees. Send the following DAX query to the tabular AdventureWorks database:

define measure 'Internet Sales'[Total Sales Amount] = Sum([Sales Amount])
evaluate
  filter(
    addcolumns(
      crossjoin(
        values('Date'[Calendar Year]),
        values('Product Category'[Product Category Name])
      ),
      "Total Sales Amount", [Total Sales Amount]
    ),
    not isblank([Total Sales Amount])
  )

When you execute this query, the logical plan tree and the physical plan tree are shown in Figures 2 and 3 respectively.

As you can see, each plan tree is output as a multi-line text. Each line represents a single operator node in the tree. The hierarchical structure of a tree is maintained by indentation. Child nodes show up indented below their parent nodes. Sibling nodes have the same level of indentation under their parent. Each line begins with the name of the operator followed by a colon and properties of the operator starting with the operator type.

Types of Operators

There are two types of logical plan nodes and two types of physical plan nodes as shown in the table below. We’ll spend a lot more time in future posts drilling into the details of various operators and their properties.

Plan Type	Operator Type	Description
Logical Plan	ScaLogOp	Scalar Logical Operator Outputs a scalar value of type numeric, string, Boolean, etc.
Logical Plan	RelLogOp	Relational Logical Operator Outputs a table of columns and rows.
Physical Plan	LookupPhyOp	Lookup Physical Operator Given a current row as input, calculates and returns a scalar value.
Physical Plan	IterPhyOp	Iterator Physical Operator Given a current row as an optional input, returns a sequence of rows.

Number of Trace Events per Query

Each time the DAX Formula Engine is called to evaluate a DAX expression, a pair of DAX Query Plan events are generated. Therefore, a DAX query (Evaluate statement) triggers exactly two events: a logical plan event and a physical plan event. But an MDX query may produce any number of pairs of events depending on how many times the MDX Formula Engine has to call into the DAX Formula Engine. At the time of this writing, DAX Formula Engine cannot call back into MDX Formula Engine, although this may change in the future.

Event Trigger Points

Ideally, the DAX Formula Engine should generate both the logical plan and the physical plan before any query execution happens so users can capture the plans without being blocked by potentially long-running operations. But this is not the case in the current implementation. Logical plans are built in two stages. The first stage is quick and light-weight, but the second stage may need to execute a portion of the tree therefore potentially expensive. Unfortunately the logical plan event is fired after the second stage is completed, so sometimes users may have to wait for certain time-consuming operations to finish before they can capture the logical plan event. But in most cases, constructing and simplifying a logical plan is a quick process. On the other hand, building a physical plan can often involve expensive operations. Although currently the trace event only shows two types of physical plan nodes: lookup and iterator, there is actually a third type of plan node: spool. A spool plan is when an operator materializes its result in memory by executing its entire subtree. A physical plan tree may contain many nodes built from spools, each of which requires partial execution of a subtree before the entire physical plan tree is fully constructed. In particular, all leaf level nodes which require fetching data from the VertiPaq Engine currently always build spools to store VertiPaq results, therefore, users can see the physical plan event only after all VertiPaq queries have completed.

The new DAX Query Plan trace event can assist you in writing efficient DAX expressions and troubleshooting problematic DAX behavior. How you use them is up to you, but first you need to understand the information contained within the plans and how to interpret it. Today we have gone over the basics such as types of plans, format of text, types of plan nodes, and when and how frequently the events are fired. Next time we are going one step further to examine the various properties of plan nodes.

Avoid Pitfalls of Fact Data Prefetching

2011-11-15T17:08:00.001-08:00

As mentioned in Chris Webb’s article Query Performance Tuning in Microsoft Analysis Services and SQL Server CAT team’s white paper SQLServer Best Practices Article: Identifying and Resolving MDX Query PerformanceBottlenecks in SQL Server 2005 Analysis Services,MDX Formula Engine (FE) may request more data from the Storage Engine (SE)upfront so that following SE queries can be answered directly from the SE cache.In order to retrieve the right amount of data, FE uses some clever heuristicsto construct the prefetch query. While the underlying design delivers greatperformance most of the time, sometimes the resulting query prefetches too muchdata. In this blog post I am going to show you a pathological case where aprefetch query seems to completely ignore the slices in the original MDX querythereby scan all partitions instead of just the needed ones. I will explain whyit happens so you understand what’s going on in the engine if you ever run intosuch cases and deal with it accordingly.

To illustrate the problem, I am going to run a series of MDXqueries against the AdventureWorks sample databasefor Microsoft SQL Server 2008R2. In between queries, I send ClearCache commandsto eliminate inter-query interference. For each MDX query, we are going toobserve the sequence of SE queries through the Query Subcube Verbose profiler trace events. Instead of showing thecomplete output of Query Subcube Verboseevents, I am going to extract only those attributes in the [Date] dimensionwhich are relevant to our discussion. As well known in the MDX community, AnalysisServices engine internally uses subcubes to represent queries, therefore I willuse the terms query and subcube interchangeably throughout the post.

Now, let’s run our first MDX query to retrieve the values ofmeasure [Internet Sales Amount] for all days in January 2007.

Query 1:

// January2007

select [Internet Sales Amount] on 0,

head(descendants([Date].[Calendar].[Calendar Year].&[2007],[Date].[Calendar].[Date]), 31) on 1

from [Adventure Works]

Since measure [Internet Sales Amount] is defined as ameasure expression, there are three QuerySubcube Verbose events, one for the [Exchange Rates] measure group, two forthe [Internet Sales] measure group, although only one of them hits thepartitions, the other is just an artifact of measure expression evaluation.

Here are the attributes corresponding to levels in the[Calendar] hierarchy extracted from the trace events:

Dimension 7 [Date] (0 * 8 0 6 0 0 0 0 00 20 4 0 0 0 0 0 0 0 0)

[Date]:*

[Calendar Quarter]:[Q1CY 2007]

[Calendar Semester]:[H1CY 2007]

[Month Name]:[January2007]

[Calendar Year]:[CY2007]

The SE query asks for all days in January 2007, which matchexactly the days listed on Axis 1 of the MDX query, with the added benefit ofnot listing each day individually. There is no prefetch query in this casewhich makes sense as the original query is asking for all days in January 2007that matches exactly the SE query.

Now let’s add one more day to the query.

Query 2:

// January2007 + February 1 2007

select [Internet Sales Amount] on 0,

head(descendants([Date].[Calendar].[Calendar Year].&[2007],[Date].[Calendar].[Date]), 32) on 1

from [Adventure Works]

From thecaptured profiler trace below

You canextract the following information form the three queries sent to the [InternetSales] measure group:

Non-cache data

Dimension 7 [Date] (0 * 8 0 6 0 0 0 0 00 20 4 0 0 0 0 0 0 0 0)

[Date]:*

[Calendar Quarter]:[Q1CY 2007]

[Calendar Semester]:[H1CY 2007]

[Month Name]:[January2007]

[Calendar Year]:[CY2007]

Non-cache data

Dimension 7 [Date] (0 * 8 0 6 0 0 0 0 00 21 4 0 0 0 0 0 0 0 0)

[Date]:*

[Calendar Quarter]:[Q1CY 2007]

[Calendar Semester]:[H1CY 2007]

[Month Name]:[February2007]

[Calendar Year]:[CY2007]

Cache data

Dimension 7 [Date] (0 582 8 0 6 0 0 0 00 0 21 4 0 0 0 0 0 0 0 0)

[Date]:[February1, 2007]

[Calendar Quarter]:[Q1CY 2007]

[Calendar Semester]:[H1CY 2007]

[Month Name]:[February2007]

[Calendar Year]:[CY2007]

Again there is no prefetch query for January 2007 but FEdoes prefetch all days in February 2007 first before getting the value ofFebruary 1, 2007 from cached data.

Now let’s add one more day again to the query.

Query 3:

// January2007 + February 1 2007 + February 2 2007

select [Internet Sales Amount] on 0,

head(descendants([Date].[Calendar].[Calendar Year].&[2007],[Date].[Calendar].[Date]), 33) on 1

from [Adventure Works]

Below is the profiler trace events and relevant informationextracted from them:

Non-cache data:

Dimension 7 [Date] (0 * * 0 * 0 0 0 0 00 * * 0 0 0 0 0 0 0 0)

[Date]:*

[Calendar Quarter]:*

[Calendar Semester]:*

[Month Name]:*

[Calendar Year]:*

Cache data:

Dimension 7 [Date] (0 * 8 0 6 0 0 0 0 00 20 4 0 0 0 0 0 0 0 0)

[Date]:*

[Calendar Quarter]:[Q1CY 2007]

[Calendar Semester]:[H1CY 2007]

[Month Name]:[January2007]

[Calendar Year]:[CY2007]

Cache data:

Dimension 7 [Date] (0 + * 0 * 0 0 0 0 00 * * 0 0 0 0 0 0 0 0)

[Date]:+

[Calendar Quarter]:*

[Calendar Semester]:*

[Month Name]:*

[Calendar Year]:*

One EventSubclass = Non-Cache data query prefetches fact data into SE cache which isused to answer the subsequent two SE queries shown as EventSubclass =Cache data, one for all days in January 2007, the other for the first twodays of February 2007. But why did the prefetch query ask for data for alldates? Why did we lose all slices in the [Date] dimension? This is really badas it would have caused all partitions to be scanned. The answer lies in theimprecise nature of the algorithm used to construct the prefetch subcube.

In order tobuild the prefetch SE subcube, FE keeps track of all relevant MDX sets in a stack-likedata structure called Sonar registry. Later on each MDX set in the Sonarregistry is used to build a collection of Sonar subcubes. Perfmoncounter MDX\Total Sonar subcubesdisplays the total number of Sonar subcubes generated inside FE.

By default,the conversion from an MDX set to its collection of Sonar subcubes is not aprecise process. The resulting Sonar subcubes will cover the original MDX setbut may include additional members on some attributes. The Cache Ratio connection string property controls how a Sonar subcubeattribute is expanded to include more members than what the original setcontains. The actual algorithm is very complex. I am only going to show here agreatly simplified version that illustrates the main idea of the algorithm.

Algorithm 1: Build Sonar subcubes from anMDX set

Input: anMDX set S, for simplicity, assume S is single-grained

For each hierarchyH in S

Since S is single-grained, let L_s be thelevel of H where members of S reside

For each level L in H starting from thetopmost level down to L_s

Identify all members on L with good coverage of S

A member M has good coverage of Sif the ratio of the number of its descendants in S to the number of all its descendants on level L_s is greaterthan or equal to the Cache Ratio

Add M to the list of slicers on level L

For each level with a nonempty list ofslicers

Create a Sonar subcube with granularitieson attributes corresponding to the levels of H from the topmost level down to L_s

Apply the list of slicers to thesubcube

Output: thecollection of generated Sonar subcubes

First, applyAlgorithm 1 to the [Calendar] set inQuery 1

head(descendants([Date].[Calendar].[CalendarYear].&[2007], [Date].[Calendar].[Date]), 31)

you get the following sequence of slices encoded in internaldata IDs and eventually FE finds a single good cover slice on the month level.

Sonar [Calendar] [1 * * * * *] ratio:31/1188

Sonar [Calendar] [1 4 * * * *] ratio: 31/365

Sonar [Calendar] [1 4 6 * * *] ratio: 31/181

Sonar [Calendar] [1 4 6 8 * *] ratio: 31/90

Sonar [Calendar] [1 4 6 8 20 *] ratio: 31/31 ß good cover

Theresulting Sonar subcube is

SonarSubcube 1

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
2007	H1 CY 2007	Q1 CY 2007	January 2007	*

Here and inthe following I only show the important attributes in the Sonar subcube whichcorrespond to levels of the [Calendar] hierarchy. Next, apply Algorithm 1 to the [Calendar] set in Query 2

head(descendants([Date].[Calendar].[CalendarYear].&[2007], [Date].[Calendar].[Date]), 32)

FE finds twogood cover slices, one on the month level and the other on the date level.

Sonar [Calendar] [1 * * * * *] ratio:32/1188

Sonar [Calendar] [1 4 * * * *] ratio: 32/365

Sonar [Calendar] [1 4 6 * * *] ratio: 32/181

Sonar [Calendar] [1 4 6 8 * *] ratio: 32/90

Sonar [Calendar] [1 4 6 8 20 *] ratio: 31/31 ß good cover

Sonar [Calendar] [1 4 6 8 21 *] ratio: 1/28

Sonar [Calendar] [1 4 6 8 21 582] ratio: 1/1 ß good cover

The twoSonar subcubes created are:

SonarSubcube 2.1

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
2007	H1 CY 2007	Q1 CY 2007	January 2007	*

SonarSubcube 2.2

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
2007	H1 CY 2007	Q1 CY 2007	February 2007	February 1, 2007

Finally,apply Algorithm 1 to the [Calendar]set in Query 3

head(descendants([Date].[Calendar].[CalendarYear].&[2007], [Date].[Calendar].[Date]), 33)

FE findsthree good cover slices, one on the month level and two on the date level.

Sonar [Calendar] [1 * * * * *] ratio:33/1188

Sonar [Calendar] [1 4 * * * *] ratio: 33/365

Sonar [Calendar] [1 4 6 * * *] ratio: 33/181

Sonar [Calendar] [1 4 6 8 * *] ratio: 33/90

Sonar [Calendar] [1 4 6 8 20 *] ratio: 31/31 ß good cover

Sonar [Calendar] [1 4 6 8 21 *] ratio: 2/28

Sonar [Calendar] [1 4 6 8 21 582] ratio: 1/1 ß good cover

Sonar [Calendar] [1 4 6 8 21 583] ratio: 1/1 ß good cover

Again, FEcreated two Sonar subcubes.

SonarSubcube 3.1

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
2007	H1 CY 2007	Q1 CY 2007	January 2007	*

SonarSubcube 3.2

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
*	*	*	*	+

Note that Sonar Subcube 2.2 retains slices on alllevels of the [Calendar] hierarchy while SonarSubcube 3.2 only has a slice on the lowest level of the [Calendar]hierarchy. This is an artifact of the current implementation. When multiplecovering members of a hierarchy are added to a Sonar subcube, only a singleslice is created at the lowest level of the hierarchy.

FE usesSonar subcubes in several different places, including constructing a largerevaluation node when FE is executing in cell-by-cell mode. In the simple casewhere an MDX query can be answered directly from SE, Sonar subcubes are used toconstruct SE queries.

Algorithm 2: Evaluate cell values

Input: acell iterator and the current Sonar registry

For eachcell C

Find the Sonar subcube S that contains C

Build an evaluation node E for S

Execute E. In the simplest case, create SE subcube S_SE from S andsend S_SE to SE.

Lookup value of C from cached result in E

Output: cellvalues

Anotherusage of Sonar subcubes is to build a prefetch subcube from a given SE querysubcube. The full algorithm is again very complex with exceptions and specialhandling of arbitrary shapes or unary operators, etc. Here I include asimplified version to illustrate the essence of the algorithm:

Algorithm 3: Build a prefetch subcube for aquery subcube

Input: a SE querysubcube S_query and the currentSonar registry

Initializethe prefetch subcube S_prefetch asa clone of S_query,

For each Sonar subcube S_sonar in theSonar registry

For each attribute A_prefetchin S_prefetch

Find the corresponding attribute A_sonar in S_sonar

Skip A_sonar if it isnot a granularity attribute

If A_prefetch is agranularity attribute

If the slice of A_sonar is asuperset of the slice of A_prefetch

Copy the slice of A_sonar to A_prefetch

Else

If the slice of A_sonar includes all members

Make A_prefetch a granularity attribute

Output: S_prefetch if it is different from S_query

In Query 1, Sonar Subcube 1 is equivalent to the SE query subcube therefore noprefetch subcube is needed. In Query 2,the first SE query subcube matches SonarSubcube 2.1 so again no prefetch subcube is needed. But the second SE querywould copy the * from [Date] in SonarSubcube 2.1 therefore produce a prefetch subcube that fetches all days inFebruary. The * in Sonar Subcube 2.1 reallymeans all days in January 2007 but Algorithm3 treats each attribute independently and is oblivious to the fact that[Date]:* is cross-filtered by slices on other attributes. But the end result ofprefetching all days in February 2007 isn’t too bad.

PrefetchSubcube 1

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
2007	H1 CY 2007	Q1 CY 2007	February 2007	*

But when wecome to Query 3, there is a * oneach attribute from either Sonar Subcube3.1 or Sonar Subcube 3.2, we endup with a prefetch subcube that has * on all its attributes.

PrefetchSubcube 2

Calendar Year	Calendar Semester	Calendar Quarter	Month Name	Date
*	*	*	*	*

Nowadays weare seeing more and more implementation of systems with multiple terabytes ofdata in Analysis Services. The above example of overly aggressive prefetchingof fact data is unacceptable in such environments. You can disable prefetchingaltogether by setting connection string property Disable Prefetch Facts=true or you can play with the Cache Ratio connection string property toinfluence the Sonar subcubes generated by FE. But either approach is likely tohave negative performance impact that would require a lot of testing to confirmtheir overall benefit against a certain query set. If you can control the typesof queries in your deployment, it is better to replace queries that fall intothis pitfall with ones that produce good Sonar subcubes in the default settings.Using our example, since we know that there are 90 days in the first quarterand the default Cache Ratio is 0.5, wecan simply ask for 45 days instead of 33 days to produce a single good cover atquarter level.

Query 4:

// January2007 + February 1-14 2007

select [Internet Sales Amount] on 0,

head(descendants([Date].[Calendar].[Calendar Year].&[2007],[Date].[Calendar].[Date]), 45) on 1

from [Adventure Works]

The capturedprofiler trace confirmed our conjecture as it shows a single SE query to the[Internet Sales] measure group to retrieve all days in the first quarter of2007.

Dimension 7 [Date] (0 * 8 0 6 0 0 0 0 00 * 4 0 0 0 0 0 0 0 0)

[Date]:*

[Calendar Quarter]:[Q1CY 2007]

[Calendar Semester]:[H1CY 2007]

[Month Name]:*

[Calendar Year]:[CY2007]

Three Strategies of Evaluating the MDX Aggregate Function

2011-10-22T17:26:00.000-07:00

In the past two months, the Analysis Services development team was preoccupied with wrapping up SQL Server Denali development. I was racing against time to put finishing touches on some exciting new DAX features that I will write in more details once Denali goes public. It didn’t help that PASS 2011 fell on the same week that developers were allowed to make a final batch of code changes before the checkin bar would be lifted prohibitively high. All the flurry of activity forced me to postpone writing blogs on a couple of topics queued up over the past year. Now that things have quieted down a lot on the Denali front, the development team immediately switched gear to work on the next batch of important features requested by many customers, like supporting DAX queries against multidimensional cubes. Over the next few months before Denali hits the stores, I’ll try to finish up several MDX topics that I had put on the backburner due to Denali crunch time.

Before I get to the main subject of today’s post, I’d like to say a few words on PASS Summit 2011 that happened here at Seattle about a week ago. AS team, and Microsoft at large, always encourages product developers to interact with customers through venues like PASS so that we get a chance to see firsthand how the products we have built impact people’s lives. I was gratified to see that Kasper received a round of applause when he gave the audience a glimpse of DAX query plans, a feature I personally fought very hard to be included in Denali. Obviously the room was filled with MDX users who have waited for years an MDX query plan feature. One MVP told me that she thinks MDX is a beautiful language. That was the first time I heard the word beautiful to be associated with MDX, what I had previoulsy heard were all in line with hard or difficult. I have also heard touching stories about how brothers both turned into MDX experts and that a mother passed her MDX knowledge to her daughter. Who could have thought that MDX can promote family bonding? I could use stories like this to lift my spirit after finishing another grueling product release cycle at Microsoft.

Without further delay, let’s jump into the MDX Aggregate function which is the focus of this post. Many users think of Aggregate as a smart function that dynamically chooses an aggregation type based on the current measure. But this way of thinking only works when the current measure is a physical measure that has an additive aggregation type like Sum, Min, Max, or Count. What should MDX formula engine do when the current measure is a calculated measure which doesn’t have a default aggregation type? Often times the formula engine simply gives up and returns an error, as seen in this blog. Even when the formula engine is able to pick a calculation strategy, it often has limitations that may surprise you when you move beyond basic scenarios. In this blog post I’ll describe three execution plans the formula engine uses to evaluate the Aggregate function and the decision logic employed to pick the winning strategy. The information provided here is valid as of SQL Server 2008R2.

Here are the three possible execution plans for calculating the value V of

Aggregate(«Set»[, «Numeric Expression»])

in the context of the current cell C₀whose subspace is S₀

1. The basic plan.

Many people think of Aggregate as a generic form of Sum, Min, Max, or Count with a similar execution strategy.

Let Agg be the aggregation function derived from the current measure in S₀

Set V = NULL

For each tuple t in «Set»

Apply t to S₀ to build a new subspace S₁

If «Numeric Expression» is present

Set V = Agg(V, value of «Numeric Expression» in S₁)

Else

Set V = Agg(V, value property of S₁)

The formula engine chooses this strategy when the current measure in S₀ is a basic physical measure or is an alias to a basic physical measure, as shown below.

2. The switching-solve-order plan.

The basic plan stops working when the current measure is a non-trivial calculated measure since the formula engine cannot extract the Agg function any more. One way to work around the problem is to switch the solving order of the AGGREGATE function and the calculated measure. Mosha alluded to this strategy in his comment to Thomas Ivarsson’s post.

Let «Measure Expression» be the expression of the current measure in S₀

Set V = Calculate the value of «Measure Expression» in S₀

The thinking behind this strategy is that by evaluating «Measure Expression» first, it will eventually lead to a physical measure that has a good Agg function. Afterwards, the Aggregate calculation can be evaluated using the basic plan. This strategy essentially moves an otherwise higher-priority Aggregate calculation behind lower priority calculated measures.

For example, when I slightly change the expression for calculated measure [x] to be more than just a physical measure name, the formula engine would switch the solving orders of [x] and [y] to produce the same result as in the basic plan.

But this strategy falls apart when the calculated measure is more than just a single expression.

If you create [x] and [y] in the cube script in the following fashion,

CREATE MEMBER CURRENTCUBE.Measures.[x] AS NULL;

Measures.[x] = [Measures].[Internet Sales Amount];

CREATE MEMBER CURRENTCUBE.[Date].[Calendar Year].[y] AS NULL;

[Date].[Calendar Year].[y] =

AGGREGATE({[Date].[Calendar Year].&[2007], [Date].[Calendar Year].&[2008]});

you will get the following surprising query result.

This is because the formula engine can only retrieve the original expression of the calculated measure but is unable to take into account scope assignments which also affect the value of the calculated measure. In my opinion, this strategy is more or less a hack as it doesn’t provide a coherent solution under all circumstances.

3. The set-in-the-where-clause plan.

Neither of the above strategies works when the current measure is a physical measure with a non-additive function, such as semi-additive measures or distinct count measures. To make the function return meaningful result, we borrowed a page from another hacky feature of MDX: multi-select through query-scope calculated member using Aggregate function. The formula engine rewrites the incoming multi-select query into an equivalent query by putting the set argument extracted from the Aggregate function into the where clause.

As it turned out, the same strategy can be used as a generic solution for evaluating the Aggregate function. In 2008R2, this is used for non-additive physical measures.

Construct a new subspace S₁ by adding «Set» as a slice to S₀

If «Numeric Expression» is present

Set V = Calculate the value of «Numeric Expression» in S₁

Else

Set V = Calculate the value property of S₁

If you are familiar with DAX, this strategy is similar to the Calculate function in DAX: it transforms the current subspace by applying the set argument as a filter and then evaluates the «Numeric Expression» in the new subspace.

Conclusion

As you can see from today’s discussion, current implementation of the Aggregate function in the face of a calculated measure has limitations and inconsistencies. It may cause confusions among users who saw that basic scenarios worked and then ventured into more advanced usages. Therefore we are considering to give users an option to switch to the set-in-the-where-clause plan where currently the switching-solve-order plan is used.

Interaction between MDX Subselect and Calculation

2011-08-17T15:49:00.000-07:00

As Mosha Pasumansky has described in his blog AS2008MDX: subselects and CREATE SUBCUBE in non-visual mode, an MDX subselect performs two functions:

Limits each axis set through dimension autoexists.
Applies visualtotals to cell values if there are no coordinate overwrites.

The second point deserves more elaboration especially when there are MDX calculations involved. In this blog post, I will explain how subselect visualtotals works in MDX formula engine and how the presence of MDX calculations can disable visualtotals by overwriting subselect attributes. Let’s start by looking at a series of questions recently raised by an AS customer. Since the original questions were based on customer’s database, I have adapted them to using AdventureWorks.

1. A customer puzzle

First run the following MDX query to find out the original values of USA, CA, and WA.

Since the value of CA is $5,714,257.69, if we query USA while subselecting by CA, the visual totaled value of USA is $5,714,257.69 as well.

Case 1

Now if you insert a seemingly innocuous calculation into the cube script that simply says USA = USA,

the value of USA suddenly goes back to its original value.

Case 2

What the user really wanted was to define a calculation that calculates USA as the sum of CA and WA.

But he was not able to get visualtotals when he subselected CA. The value of USA is the sum of the values of CA and WA instead of just the value of CA.

Case 3

The user further experimented by assigning a constant value to WA. When he put the WA assignment before the USA assignment,

USA value was still the sum of the values of CA and WA.

Case 4

When the user moved the WA assignment after the USA assignment,

he suddenly got visualtotals on USA!

The above results confused quite a few MDX users.

2. How does subselect visualtotals work anyway?

2.1. Subselect filters without calculation

You can rest assured that there is a consistent logic underneath the above perplexing results. Before we study the interaction between subselect and calculation, let’s first go back to the basics to see how subselect visualtotals is attained when there is no calculation at all.

I’ll start with a quick review of some fundamentals of the formula engine. Every MDX query is split into one or more single-granularity queries. A single-granularity query is represented by a cube subspace, also known as subcube. The formula engine constructs evaluation nodes from those single-granularity subspaces. For every evaluation node, the formula engine determines which calculations apply to its subspace and generates a list of calculation items, one for each applicable calculation. In the future, you will see that calculation item is also called evaluation node item. But I will stick with calculation item in this post. Every calculation is associated with a subspace at the granularities where the calculation is defined. For example, the left side of the trivial assignment USA = USA defines a subspace with granularity at Country level. The formula engine then constructs a subspace for each calculation item by combining the evaluation node subspace with the calculation subspace. The process of constructing an item subspace is too complex to be covered here, but the resulting subspace is always at or below the granularities of the evaluation node subspace. That’s because a calculation simply doesn’t apply to an evaluation node if the subspace of the former is not strictly at or below the granularities of the subspace of the latter. For example, an assignment at the Country level does not affect a query at the City level. The diagram below illustrates an evaluation node and its calculation items.

In the simplest case when there is no calculation like calculated member, scope assignment, unary operator, etc., the formula engine creates a single, special calculation item, called DetailData, which fetches data from the storage engine. You can imagine that the subspace associated with any DetailData calculation item is always at the lowest granularities for the measures to be fetched. Any subselect filters are then used to filter out unwanted members from the DetailData subspace. Afterwards, when the formula engine aggregates measure values from the DetailData subspace into the evaluation node subspace, you get visualtotals since data has been filtered at the lowest level. In practice, the formula engine does not construct DetailData subspace at the lowest granularities since the storage engine can do more than just retrieving and filtering data at leaf levels, it can also aggregate physical measure values at higher granularities. So the DetailData subcubes sent to the storage engine, as shown in SQL Profiler’s Query Subcube trace events, are typically at higher granularities but include any subselect filters as subcube slices which will filter data at leaf levels.

2.2. Subselect filters with calculations.

As we just said, when there is no applicable calculation, visualtotals is achieved by applying filters to the DetailData subspace and then aggregating measure values into a higher granularity subspace. The same logic extends naturally to cases when calculations come into the picture. Unlike DetailData items, the subspace of an arbitrary calculation is not necessarily at the lowest granularities any more. When building the subspace of the calculation item for a calculation, the formula engine examines each subselect attribute one by one, applies a subselect filter only if the attribute is at or above the granularities of the subspace. So if a subspace is at quarter level, subselect filters at quarter or year levels will be applied to the subspace but subselect filters at month or day levels are skipped. The diagram below illustrates how a subselect partially filters a subspace.

In practice, every time the formula engine constructs a new subspace, be it for an evaluation node or a calculation item, it always applies the subset of subselect filters which are at or above the granularities of the new subspace.

2.3. Attributes overwritten by calculations

An MDX calculation is not only defined by the subspace to which it applies, but also by its MDX expression. When the formula engine evaluates an MDX expression, it often transforms one subspace into another by overwriting some of the attributes. For example, MDX tuple expression ([Retailer Sales Amount], [Date].[Fiscal Year].[FY 2011]) changes one subspace to another by overwriting the [Measures] attribute and the [Fiscal Year] attribute and its related attributes. More complex MDX calculation expressions are parsed into formula trees. Subexpressions within a formula tree like tuple expressions or set expressions overwrite attributes in the original subspace to produce new subspaces.

While the formula engine navigates from one subspace to another, through evaluation nodes and calculation items, it keeps track of all attributes that have been overwritten by calculation expressions so far. When it is time to apply subselect filters to a newly constructed subspace, the formula engine skips all overwritten attributes. This rule sometimes leads to unintuitive results, like the ones we saw earlier in section 1. In case 1, the dummy calculation USA = USA overwrites attributes in the [Customer Geography] hierarchy, later on the DetailData subspace is no longer filtered by the California slice on the [State-Province] attribute, the final value for USA ends up being the sum of values of all states.

The rationale behind this rule is that if a user defines a simple calculated measure like ([Internet Sales Amount], [Fiscal Year].[FY 2009]), they really want to see the sales in 2009 when they add this measure to a report even though the report may have a filter on fiscal years 2010 and 2011. If the formula engine had applied the subselect filter after the calculated measure has changed the subspace to fiscal year 2009, user would have got empty result back since the intersection between {2009} and {2010, 2011} yields empty slice on the [Fiscal Year] attribute.

2.4. Answers to puzzle questions

Since calculation USA = CA + WA in case 2 and case 3 overwrites attributes in the [Customer Geography] hierarchy as well, the same rule kicks in to prevent the subselect filter on [State-Province] from being applied, hence no visualtotals for USA. The diagram below illustrates that in cases 1 through 3, overwritten attributes prevents subselect visualtotals.

But what about case 4 when WA = 1000000 is moved after USA = CA + WA? Why did we get visualtotals in that case? This is actually nothing more than a trick question to test how well you know solving orders in MDX. Now that calculation WA = 1000000 has a higher calculation pass value than calculation USA = CA + WA, the latter calculation is not used at all. The diagram below shows that since calculation does not overwrite any attributes, subselect filters still apply.

2.5. Caveats

Note that the rule discussed in 2.3 only applies to subselect visualtotals. MDX VisualTotals function is not affected by overwritten attributes in the same way. The discrepancy between the two styles of visualtotals is due to historical reasons where VisualTotals function had to maintain backward compatibility with SQL Server 2000 behavior.

Also note that the current implementation has complications when an MDX query mixes subselect with set in the where clause. So it’s generally not a good idea to use both features together in the same query.

3. What if a subselect is arbitrarily shaped?

So far we have been applying subselect filters one attribute at a time. We have learned that for a given subspace, some subselect filters can be applied if they are at or above granularity and not overwritten, others cannot be applied if they are below granularity or have been overwritten. This is not an issue when each subselect attribute can be applied independently. What if there is a correlation between two subselect attributes? In AS jargon, what if a subselect is arbitrarily shaped but only a subset of the attributes can be applied to a subspace? In this case, the formula engine projects the subselect onto the applicable filter attributes and then restricts the subspace with the projected set. But if a partial projection is caused by some of the filter attributes overwritten by calculations, the formula engine raises an error instead. For example, insert a dummy calculation WA = WA into the cube script that overwrites the [State-Province] attribute. Issue a query Select USA From (Select {(CA, 2009), (WA, 2008)} From AW) which contains an arbitrary shape subselect and you will get the error Expression cannot be resolved in the context of an arbitrary shape. That’s because the [Fiscal Year] attribute of the arbitrary shape set applies to the subspace but the [State-Province] attribute of the arbitrary shape set does not apply as it has been overwritten by the dummy calculation.

4. Summary

Today we have learned how MDX formula engine implements subselect visualtotals. In general, subselect filters newly constructed subspaces for attributes at or above granularities. The DetailData calculation item can be thought of as working at the leaf levels therefore all subselect filters apply. Other calculation items may work at higher levels hence only a subset of subselect filters are used to restrict their subspaces. Attributes overwritten by calculations are not filtered by subselect.

Under the Covers: MDX IF Statement

2011-06-27T18:32:00.000-07:00

It is a well-known best practice in the MDX community to avoid run-time checks by choosing SCOPE over IIF function, and for the same reason, the IF statement. But what is the actual performance impact when you have no choice but to use IIF function or IF statement? I have described in details the block mode algorithm for IIF function in one of my previous blog posts. The other day, Teo Lachev asked whether he needed to worry about performance if he used IF statement in his cube script. In particular, Teo wanted to know if there was any performance impact on other calculations which kick in when the condition of IF statement is false. In my post about IIF function, I mentioned that IF statement is internally rewritten as IIF function calls. Today I am going to add a bit more details on how the rewriting is done and what limitations can prevent the rewriting from happening. What is described here applies equally to the CONDITION clause in a CREATE CELL CALCULATION statement since there is no internal difference between the two MDX features.

Rewrite to IIF function calls

Let’s consider a simple case where two calculations apply to the same subspace S₀, as shown in Figure 1. The calculation, Calc1, wrapped in IF statement has higher priority over the other calculation, Calc2. For example, Calc1 may be at a higher calculation pass than Calc2. When comparing two scope specifications, MDX calculation engine does not take into account conditions of IF statements although they appear to be part of the scope definitions. Instead, conditions in IF statements are evaluated at run-time.

In case you wonder why Calc1 and Calc2 have their own subspaces S₁ and S₂ even though the evaluation node already has a subspace S₀, that’s because the scope of a calculation can be at lower granularity than the subspace of an evaluation node. For example, a query may ask for results at the year level, but there is a calculation at month level. In this case, the calculation is needed to answer the query but the subspace for the evaluation node, which is the same as the subspace of the query, and the subspace of the calculation will be at different granularities.

Figure 2 shows how the IF statement is translated to IIF function calls which are evaluated at run-time. DisjointTest is an internal function that takes as input a given cell and returns true if the cell is not covered by one of the higher priority calculations. In our example, when S₁ and S₂ are at the same granularity, DisjointTest degenerates into NOT Condition, which returns true when the condition of IF statement returns false. When S₂ has lower granularity than S₁, DisjointTest first finds a cell in S₁ that covers the given cell in S₂ and then evaluates NOT Condition in the context of the covering cell.

So unlike IIF(Condition, Calc1, Calc2), where the Condition is evaluated in one subspace, the condition of IF statement is replicated and evaluated in all subspaces of lower priority calculations which apply to the given evaluation node. Consequently, the condition of IF statement will be evaluated in many more cells than the original subspace, especially when the subspaces of lower priority calculations are at lower granularities.

Exceptions

The internal rewriting to IIF does not happen in S₂ when Calc2 is a semi-additive measure, a unary operator, or a storage engine query. In those cases, Calc2 is evaluated in a larger subspace not constrained by the opposite condition of the IF statement. This is typically not a problem when Calc2 is to simply fetch data from the storage engine except for really large cubes when fetching the extra data requires a lot of disk IOs. In the other two cases, S₂ becomes inexact since now Calc2 is evaluated in more cells than it should. An inexact subspace increases the chance of the MDX calculation engine choosing cell-by-cell mode when it builds the calculation subtree starting from S₂.

Performance Considerations for Recursive Calculations in MDX (Part 2)

2011-05-31T23:07:00.000-07:00

Last time I discussed MDX engine limitations in dealing with recursive calculations. Today I want to describe two more situations where users may run into bad performance when writing recursive calculations in MDX.

Pseudo Infinite Recursion

Last time I recommended users to avoid subspace overlap over the changing attribute of the recursion, in most cases, an attribute in the Date/Time dimension. That’s because MDX calculation engine may choose a cell-by-cell execution plan when it detects sideways recursion. But performance can get a lot worse than simply executing in cell-by-cell mode if MDX calculation engine falsely reports an infinite recursion even though it is actually a sideways recursion. When a calculation applies to two subspaces on the callstack, MDX calculation engine checks for potential infinite recursion. When the two subspaces have overlapping regions, MDX calculation engine checks to see if any cell in one subspace is mapped to itself in the other subspace. This requires MDX calculation engine to keep track of how each MDX expression on the callstack transforms one subspace into another. While there are extensive logic inside MDX formula engine to analyze most common MDX expressions, there are still some MDX expressions which are treated as a black box by the formula engine. Moreover, if one of the subspaces on the callstack is a single-cell subspace, MDX calculation engine switches to lazy execution mode for that expression, and treats the calculation in that subspace as opaque as well. During infinite recursion detection, an opaque calculation on the callstack would force MDX calculation engine to assume the worst and to raise an infinite recursion error even though it is not true.

Error handling is one of the most expensive operations in MDX calculation unless the error simply aborts the entire query. This is especially true in block mode. MDX calculation engine is not sure whether the error happens in one of the cells in the subspace or the error applies to the entire subspace. As a result, parent evaluation node which intercepts the error will abandon all intermediate results and start all over by falling back to cell-by-cell mode and recalculating cell values one by one. For the same reason, if an infinite recursion error is raised due to overlapping region and opaque MDX expression anywhere on the callstack, error handling logic at upper level would force a recomputation of cell values one at a time. This is helpful since if the infinite recursion is not real, it won’t happen again as soon as the changing attribute is reduced to a single member. However, the unwinding process happens one level at a time. If the recursion loop is long, recomputations of cell values at all intermediate levels are wasted effort, see Figure 2.

The false infinite recursion error is repeatedly raised until the beginning of the recursion loop is reached. Only when MDX calculation engine splits the subspace at the beginning of the recursion into individual cells will the false infinite recursion error stops being raised, as by now the beginning subspace would have a single member on the changing attribute and the ending subspace would have a different member on that attribute. To make matters worse, MDX calculation engine has a preference for block mode. Even though a parent level is forced into cell-by-cell mode to avoid false infinite recursion, a child level may still make the same mistake if somehow the subspace is enlarged by some calculation to include more members on the changing attribute. If you see a steady increase of performance counter MDX\Total Recomputes along with the creation of large numbers of evaluation nodes of various kinds, your query may have entered such a vicious cycle.

Impact on Calculation Caches

Aggressively caching intermediate results is one of the key reasons for MDX execution engine to deliver great query performance. MDX execution engine maintains a complex system of various types of caches for different purposes. When a MDX query requires heavy calculation, caches for evaluation nodes tend to play a decisive role in good query performance. An evaluation node is a subspace along with query plans built for all applicable calculations plus, optionally, data caches holding the calculation results. Unlike cached results of storage engine queries which correspond to leaf nodes in an MDX evaluation tree, formula engine evaluation nodes can be at much higher level or even be the root node of an evaluation tree. Hitting or missing an evaluation node at high level in the cache will make a dramatic difference in performance for calculation intensive queries.

MDX calculation engine keeps separate caches for cell-by-cell evaluation nodes and for bulk-mode evaluation nodes. In this section I am going to discuss the impact of recursive calculation on bulk-mode evaluation node caches.

The evaluation node cache mentioned above builds hash tables and indexes to facilitate insertions and lookups of cache entries. The hash function used by the hash table calculates hash values based on the group-by attributes of a subspace, plus some other information which is unimportant for the sake of this discussion. The hash table uses linked lists to implement separate chaining for collision resolution. Since every evaluation node ever created is inserted into the cache, the cache becomes crowded very fast. When a linked list becomes too long, evaluation nodes are evicted from the cache, as reflected by the performance counter MDX\Number of evictions of evaluation nodes.

Recursion can easily generate a large number of evaluation nodes. Starting from the original query subspace, the evaluation tree can grow quite big as recursion grows deeper and deeper. This in turn puts heavy pressure on evaluation node caches. To exacerbate the situation, the hash function mentioned previously skips group by attributes in parent child dimensions, as a result, subspaces with a lot of parent child dimensions have a much higher chance of hash collision. On the other hand, recursive calculations tend to show up in financial cubes which usually contain a lot of parent child dimensions. So recursion in a cube with a lot of parent child dimensions has a high chance of eviction of cached evaluation nodes and a low chance of hitting a previously built evaluation node.

You can increase the maximum length of all linked lists by increasing the values of private server configuration properties CalculationLRUMinSize and CalculationLRUMaxSize as the eviction threshold is dynamically calculated but always falls in the interval [CalculationLRUMinSize, CalculationLRUMaxSize]. Increasing the size of the linked lists only allows more evaluation nodes to stay in the cache. You also have to increase the value of private server configuration property CalculationCacheRegistryMaxIterations so that those entries are actually examined during cache lookup.

Summary

In this blog post, we explored two more scenarios in which the presence of recursive MDX calculations can negatively impact query performance. MDX calculation engine may raise false infinite recursion errors when the changing attribute has more than one member in the subspace and when there are a mix of block mode evaluation nodes and cell-by-cell mode evaluation nodes along the recursion path. Although not a definitive diagnosis, you can watch the performance counter Total recomputes to get an indication that unwarranted errors are causing the query slowdown. As recommended in my previous post, keeping a single member on the changing attribute will prevent this issue.

We often find recursive calculations in financial cubes, which also tend to have many parent child dimensions. The combination of the two is cache unfriendly for bulk mode evaluation nodes. Here is one way to help you identify this problem. Assume there is a recursive calculation based on the [Month] attribute. First issue a query to calculate the value in January. After the query finishes, issue a query to calculate the value in February. After that, issue a query to calculate the value in March, so on so forth. If each query comes back fast in this fashion of successive calculations but a query to calculate the value in December is slow when starting in cold cache mode, this is an indication that your recursive calculation can benefit from hitting previously cached results but the large number of evaluation nodes generated by a deep recursion is evicting good cached results. Sometimes increasing the maximum size of hash collision chain of the evaluation node cache may help.

Most of these problems arise because block mode evaluation can be improved in terms of infinite recursion detection and sideways recursion handling. While that may happen in a future release of Analysis Services, a potential workaround is to force a pure cell-by-cell mode for all calculations. SQL Server 2000 performed calculations in cell-by-cell mode only. Many cubes designed back in SQL Server 2000 days worked well enough in that mode with acceptable and predictable query performance. If none of my other recommendations work for you and you suspect that pure cell-by-cell mode may be what you need, you can contact Microsoft Customer Support and Services to explore such a possibility.

Performance Considerations for Recursive Calculations in MDX

2011-04-27T17:16:00.000-07:00

The other day, while investigating a customer performance problem, Chris Webb came up with a sequence of very simple and targeted MDX queries against SQL Server 2008 Adventure Works database that clearly illustrate some of the pitfalls people may encounter when they write recursive MDX formulas. Today we explore how MDX formula engine generates execution plans for those queries and discuss some engine limitations in this area that may have big impact on query performance.

Chris Webb’s Queries

Chris defined a test measure, [rtest], that recursively counts the number of days from the current date back to 07/01/2001. His first query calculates [rtest] against all customers with a slice on date 07/31/2001. The query finished in about one second to return 118,484 cells.

Query #1

WITH MEMBER MEASURES.rtest AS
IIF (
    [Date].[Date].CURRENTMEMBER IS [Date].[Date].&[20010701]
    , 1
    , 1 + (MEASURES.rtest, [Date].[Date].CURRENTMEMBER.PREVMEMBER)
)
SELECT
  MEASURES.rtest ON 0,
  [Customer].[Customer].[Customer].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].&[20010731])

Next Chris trimmed down the number of customers to be slightly less than 50% of all customers and ran the query again. The query finished a little bit faster than the first one as expected.

Query #2

WITH MEMBER MEASURES.rtest AS
  IIF (
    [Date].[Date].CURRENTMEMBER IS [Date].[Date].&[20010701]
    , 1
    , 1 + (MEASURES.rtest, [Date].[Date].CURRENTMEMBER.PREVMEMBER)
  )
SELECT
  MEASURES.rtest ON 0,
  HEAD ([Customer].[Customer].[Customer].MEMBERS, 9241) ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].&[20010731])

But adding one more customer to the second query suddenly increased query execution time to 20 seconds. The obvious question is what is so significant about querying 50% or more of total customers.

Query #3

WITH MEMBER MEASURES.rtest AS
IIF (
    [Date].[Date].CURRENTMEMBER IS [Date].[Date].&[20010701]
    , 1
    , 1 + (MEASURES.rtest, [Date].[Date].CURRENTMEMBER.PREVMEMBER)
)
SELECT
MEASURES.rtest ON 0,
HEAD ([Customer].[Customer].[Customer].MEMBERS, 9242) ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].&[20010731])

Chris then tried to remedy the situation by cleverly adding a helper measure, [rtest2], that pre-calculates [rtest] in the range of desired dates in the hope that calculating values of later dates can hit caches of values of earlier dates. I omitted several intermediate queries that led Chris to this idea. When he tried out his idea against the problem query, execution time was cut down to about 12 seconds, an improvement from 20 seconds.

Query #4

WITH MEMBER MEASURES.rtest AS
IIF (
    [Date].[Date].CURRENTMEMBER IS [Date].[Date].&[20010701]
    , 1
    , 1 + (MEASURES.rtest, [Date].[Date].CURRENTMEMBER.PREVMEMBER)
)
MEMBER MEASURES.rtest2 AS
IIF (
    ISEMPTY(
      SUM(
        [Date].[Date].&[20010701] : [Date].[Date].CURRENTMEMBER,
        MEASURES.rtest
      )
    )
    , null
    , MEASURES.rtest
)
SELECT
MEASURES.rtest2 ON 0,
HEAD ([Customer].[Customer].[Customer].MEMBERS, 9242) ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].&[20010731])

But the same trick didn’t help the original query, which now takes about 38 seconds to finish.

Query #5
WITH MEMBER MEASURES.rtest AS
IIF (
    [Date].[Date].CURRENTMEMBER IS [Date].[Date].&[20010701]
    , 1
    , 1 + (MEASURES.rtest, [Date].[Date].CURRENTMEMBER.PREVMEMBER)
)
MEMBER MEASURES.rtest2 AS
IIF (
    ISEMPTY(
      SUM(
        [Date].[Date].&[20010701] : [Date].[Date].CURRENTMEMBER,
        MEASURES.rtest
      )
    )
    , null
    , MEASURES.rtest
)
SELECT
MEASURES.rtest2 ON 0,
[Customer].[Customer].[Customer].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].&[20010731])

Block Mode vs. Cell-by-Cell Mode

As it turned out, queries #1 and #2 run fast because they execute in block mode. On the other hand, queries #3, #4, and #5 all execute in cell-by-cell mode. But how do I know that? Well, I admit that Analysis Services product team owes the MDX community good diagnostic features, such as MDX query plans, to easily identify such problems. Before such features become available, we have to make do with what we have today, namely PerfMon counters and SQL Profiler trace events. In this case, PerfMon counters can be very revealing. If you start PerfMon and add the counter MSAS 2008:MDX/Total flat cache inserts, rerun queries #2 and #3 with cache cleared, you will see that the counter stays at 0 during query #2 but jumps to 286,502 at the end of query #3. Flat cache is one of the many data caches maintained by MDX formula engine and is used to store single-cell calculation results. The large number of inserts into this cache during query #3 indicates that the query runs in cell-by-cell mode. I'd like to add that cell-by-cell mode can be reflected in other PerfMon counters under a different circumstance. Just because Total flat cache inserts is zero, does not necessarily mean query is in block mode.

Sideways Recursion and Inexact Subspace

Now we know adding one more customer to query #2 tips MDX formula engine over the edge into cell-by-cell mode, but what is so special about the threshold of 50% of members in an attribute set? When MDX formula engine constructs a query subspace, it does not always construct the exact subspace as specified by the MDXMDX formula engine has an easy way to indicate that all customers are included in the subspace. Later operations like determining overlapping regions between two subspaces or detecting whether a cell belongs to a subspace become fast and efficient. But the real benefit of this expansion is to fetch more fact data into detail data cache, so that following queries have a much higher chance of hitting a cache entry. An obvious downside of this expansion is the possibility of fetching too much data. If there are four years of data in the cube and a query selects two years, AS engine ends up retrieving all four years of data. A much more serious problem with this expansion is when there are calculations applicable to the subspace. A single extra member may introduce an unwanted calculation that kills performance. The 50% threshold is arbitrary and is controlled by private server configuration flag QueryOptimizerRatio. Now change the ratio from -1 to 0.7 and rerun query #3, the query finishes instantly. You should know that Microsoft does not support customers who temper with private server configuration settings without authorization from Microsoft Customer Service and Support.

So MDX formula engine expanded the subspace for query #3 to include all customers, the expanded subspace ended up being the same as the one in query #1, but why was query #3 so much slower than query #1? In addition to expanding the subspace in query #3, MDX formula engine also marks the query subspace as inexact for the obvious reason that the subspace contains more customers than requested. Inexact subspaces have several repercussions on building query plans. Loosely speaking, whenever MDX formula engine runs into a potentially expensive operation and when one of the parent subspaces is inexact, it is likely to fall back to cell-by-cell plan as the expensive operation may be unnecessarily introduced by the extra members added.

The next concept to explain is sideways recursion. A recursion happens when the same calculation shows up twice on the stack of current calculations. When the two subspaces to which the calculation is applied have the same granularity and there is no slice change from a regular member to a calculated member or vice visa, the recursion is called sideways recursion. So if one subspace is at All level on an attribute, but the other subspace is at leaf level, the recursion is not a sideways recursion.

When MDX formula engine detects a sideways recursion in the context of an inexact subspace, it chooses a cell-by-cell execution plan. There are a couple of reasons for making such a choice. In addition to query execution, MDX formula engine is also responsible for detecting infinite recursions. Detecting infinite recursion in block mode is more complicated than in cell-by-cell mode. Even when a calculation shows up twice on the stack of current calculations and the two subspaces to which the calculation is applied have overlapping regions, it does not necessarily entail an infinite recursion. There have been cases where it takes a long time to detect an infinite recursion in block mode and it turns out that time is wasted as the subspace is enlarged through expansion. So currently MDX formula engine resorts to cell-by-cell mode when it encounters sideways recursion and when one of the parent subspaces is inexact. If you want to keep all subspaces exact throughout the execution process to overcome this constraint, you can change another private server configuration property, SpaceDecomposition, from default value of 8 to 9. You can try the new value and rerun query #3 again to see instant result. Why isn’t this default setting? Wouldn’t precise subspaces be a good thing all the time? The Analysis Services product team tried to make the switch during SQL 2008 development but found that precise subspaces hurt performance much more often than helping. Precise subspaces tend to carry a lot of large slices and often arbitrary-shaped slices. Arbitrary-shaped slices are a well-known reason of MDX query performance degradation. More importantly, precise subspaces make it hard to hit caches. Extensive caching of intermediate results is one of the secret recipes of good MDX query and calculation performance.

Recursion and Subspaces Overlapping on Shifted Attributes

Although sound promising in concept, queries #4 and #5 do not perform as well as queries #1 and #2 since they also run in cell-by-cell mode. But the subspace for query #5 is precise, why does it still enter cell-by-cell mode? The execution plan for query #5 enters cell-by-cell mode for a different reason.

Chris introduced the following helper expression

SUM(
    [Date].[Date].&[20010701] : [Date].[Date].CURRENTMEMBER
    ,MEASURES.rtest
)

in the hope that MDX calculation engine will calculate [rtest] on 07/01/2001 first, and then each following day will hit the cached result of the previous day. But MDX formula engine always tries to execute in block mode first. So when constructing the subspace for the evaluation of the scalar argument of SUM, the engine adds all dates from the set argument to the subspace. The diagram below shows the important subspaces constructed when executing query #5.

The steps below illustrate the import stages during query #5 evaluation.

Construct subspace 1 from MDX query.
Build query plan for calculation rtest2 in subspace 1.
        Build query plan for sub-expression Sum(07/01/2001 : 07/31/2001, rtest)
        Construct subspace 2 by adding MDX set 07/01/2001 : 07/31/2001 to subspace 1.
        Build query plan for calculation rtest in subspace 2.
                Build query plan for sub-expression (rtest, Date.PrevMember).
               Construct subspace 3 by shifting all dates in subspace 2 to the previous date.
                Build query plan for calculation rtest in subspace 3.
                Detect recursion and overlapping dates. Choose cell-by-cell plan.

When MDX formula engine detects recursion and the two subspaces have overlapping regions, it is potentially an infinite recursion. But the overlapping dates have been shifted, so whether or not there is infinite recursion depends on the MDX operation that performed the shifting. It is obvious in this case there is no infinite recursion. But MDX formula engine has to handle the general case when there are a series of shifting operations between the two subspaces. To play it safe, the engine chooses cell-by-cell execution plan again.

Why is choosing cell-by-cell mode bad for performance in this case? Doesn’t the recursive formula force the engine to evaluate [rtest] one day at a time anyway? That’s true. But there are also 118,484 customers in the subspace. Right now cell-by-cell is a global decision. While going cell-by-cell over days is not a bad thing in this case, going over customers one by one is. Ideally, we want the formula engine to go block on [Customer] attribute, but cell-by-cell on [Date] attribute.

Another downside of choosing cell-by-cell mode in subspace 3 is that the decision will propagate back to all parent calculations as well. MDX formula engine cannot take advantage of sparsity of underlying data when calculation is in cell-by-cell mode. Since building execution plan for [rtest] in subspace 3 is a part of building the overall execution plan for [rtest2] in subspace 1, choosing cell-by-cell plan in subspace 3 ends up forcing a bad plan for [rtest2] in subspace 1 as well.

We don’t have this problem in queries #1 and #2 since there is only a single date in the subspace. When constructing a new subspace, [Date].PrevMember shifts the current date to the previous date. The newly constructed subspace has a different date from all previously constructed subspaces, therefore, there is no risk of infinite recursion. As a result, the execution plan stays in block mode.

Deep Recursion and CLR Assembly

The next topic is unrelated to Chris Webb’s queries but still important for recursions in MDX. Each additional recursive step consumes more space on the stack. A very deep recursion will eventually use up all space on the stack. When that happens, MDX formula engine creates a fiber and switches execution on to the new fiber. But it is not always safe to call managed code from within a fiber since you may get stack overrun problem. In SQL Server 2008 R2, MDX formula engine raises an error when this happens. But the error does not abort the current query to return to user immediately. Most errors cause MDX formula engine to switch to cell-by-cell mode in case some cells contain errors but others don’t. So instead of seeing a query fail quickly, you may end up waiting a long time for it to fail eventually. Note that VBA library is registered as a CLR assembly hosted by Analysis Services, so calling VBA functions in MDX recursion may trigger the fiber exception and cause performance problems. Also note that some VBA functions have been implemented directly in Analysis Services, therefore are safe to use inside recursion. Setting private server configuration flag AllowCLRStoredProcedureCallsInFiberMode to 1 would prevent the exception from being raised.

Conclusions

So far, we have discussed three important performance limitations imposed by MDX formula engine when it encounters recursions. We have learned that to achieve better performance when writing recursive MDX formulas, you should:

Don’t write recursive formulas unless really needed. Non-recursive MDX formulas are not subject to the limitations discussed in this post.
Keep the subspaces precise. Try to stay with queries that produce nice, rectangular-shaped query spaces which don’t trigger the 50% rule. Playing with SpaceDecomposition is an option but you need approval from Microsoft Customer Service and Support to use it in production environment. Note that always enforcing precise subspaces is likely to have an overall negative impact on query performance.
Avoid subspace overlap over shifted attributes. Most recursions happen along the time line. Keeping a single date in the subspace is one way to guarantee that subspaces don’t overlap.
Avoid calling into CLR assembly when recursion is very deep.

As always, performance advices apply to particular versions of a product. The recommendations given in this post apply to all currently supported versions of Analysis Services with the latest one being 2008 R2. The Analysis Services product team will continue to evolve the product and some of the limitations are likely to be lifted in future releases. When you test your recursive calculation, you should start at the beginning and gradually increase recursion depth. Observe whether query time increases at worst linearly as recursion depth increases. Make sure you test the case when the recursion is the deepest as needed by the user.

The Logic behind the Magic of DAX Cross Table Filtering

2011-03-21T23:59:00.000-07:00

Automatic cross filtering between columns of the same table or related tables is a very powerful feature of DAX. It allows a measure to evaluate to different values for different cells in a pivot table even though the DAX expression for the measure does not change. Filter context is the underlying mechanism that enables this magic behavior. But it is also a very tricky concept that even befuddles some DAX experts. Marco Russo and Alberto Ferrari have introduced DAX filter context in Chapter 6 of their book Microsoft PowerPivot for Excel 2010. Marco has also blogged about how Calculate function works. Recently I have run into many questions from advanced DAX users which tell me that people are still confused about how filter context works exactly. And this will be the subject of today’s post.

This post assumes that you already have basic knowledge about measures, row context, filter context, and DAX functions Calculate, Values, All, etc.

A level 200 pop quiz on DAX

If you think you already know how filter context works, let me ask you a couple of level 200 questions on DAX to see if you can explain the nuances of some DAX expressions. If you don’t feel like being challenged now, it is still beneficial to read the questions so you have some examples to better understand the following sections. The questions are based on the data model inside the publicly available sample PowerPivot workbook Contoso Samples DAX Formulas.xlsx. You can download the sample workbook to try out the formulas yourself if you want to, but it is not required to answer the questions.

Question #1.

People have heard that fact tables are automatically filtered by slices on dimension tables, but not the other way around, or in more general terms, if there is a relationship from table A to table B, A is automatically filtered by any slices on columns of B but B is not automatically filtered by any slices on columns of A. So if you select

DimProductSubcategory[ProductSubcategoryName] = “Air Conditioners”

on a pivot table slicer, measure

CountRows(DimProduct)

returns 62 as DimProduct is limited to air conditioners. On the other hand, if you select

DimProduct[ProductLabel] = “0101001”,

measure

CountRows(DimProductSubcategory)

returns 44 instead of just 1 although only a single product is selected. To filter DimProductSubcategory by the selected product label, you can define a measure as

Calculate(CountRows(DimProductSubcategory), DimProduct)

which returns 1. So it seems like when you explicitly add DimProduct as a setfilter argument of Calculate, DimProductSubcategory will be filtered by DimProduct. But if I define a measure as

Calculate(CountRows(DimProductSubcategory), Values(DimProduct[ProductLabel]))

to explicitly add the column that I know having a slice from the pivot table to the Calculate function , the measure formula returns 44 again. So what makes setfilter expression DimProduct work but Values(DimProduct[ProductLabel]) not work even though the filter only comes from [ProductLabel] column? If you think you have to add foreign key DimProduct[ProductSubcategoryKey] to the filter context in order for DimProductSubcategory to be filtered by DimProduct, you can try

Calculate(CountRows(DimProductSubcategory), Values(DimProduct[ProductSubcategoryKey]))

but it still returns 44. If you have enough patience, you can use Values function to explicitly add all 33 columns in DimProduct one by one as setfilter arguments to Calculate function and you still will get 44 back. So what is the difference between table expression DimProduct and the enumeration of all 33 columns in that table?

Question #2.

There are 2556 records in DimDate table, therefore if you add a measure with expression

CountRows(DimDate)

to a pivot table without any filters, the measure value would be 2556. Now if you add a second measure with expression

Calculate(CountRows(DimDate), FactSales)

to the same pivot table, the measure value would be 1096 since DimDate table is filtered by FactSales table and only dates with sales records are included. But if you add a third measure with expression

Calculate(CountRows(DimDate), All(FactSales))

to the pivot table, the measure value becomes 2556 again. Since this pivot table has no filters anywhere, shouldn’t FactSales and All(FactSales) return the same table? Now add a fourth measure with expression

Calculate(CountRows(DimDate), Filter(All(FactSales), true))

to the pivot table, the measure value becomes 1096 again. All three setfilter arguments return exactly the same table, why would we get back different results?

With these questions in mind, let’s examine the logic foundation upon which the magic world of DAX is built. At the end of the post, you will be able to find a logical explanation to all these seemingly inconsistent results.

The expanded view of a DAX base table

The best way to understand DAX cross table filtering is to think of each base table as extended by its related tables. When a relationship is created from table A to table B, the new A, which is really A left outer join B, includes both columns of A and columns of B. So in DAX, a table reference FactSales really refers to

FastSales

LOJ DimProduct LOJ DimProductSubcategory LOJ DimProductCategory

LOJ DimStore LOJ DimGeography LOJ DimDate LOJ DimChannel LOJ DimPromotion,

where LOJ means left outer join. This interpretation makes it easy to understand some other DAX syntax. For example, in DAX expression

Filter(FactSales, Related(DimProduct[ProductLabel]) = “0101001”),

Related(DimProduct[ProductLabel]) refers to the value of column DimProduct[ProductLabel] in the extended FactSales table. As a second example, DAX expression

AllExcept(FactSales, DimProduct[ProductLabel])

returns a table with all columns of extended FactSales table except for column DimProduct[ProductLabel].

Build initial filter context

DAX filter context is a stack of tables. At the beginning, the stack is empty. Given a pivot table, a filter context is initially populated by adding slicers and page filters. For each cell in a pivot table, current members of row labels and column labels also add filters to filter context. Other pivot table operations like visual totals add to initial filter context as well but I will keep things simple here. At this point, we have set up an initial filter context in which the measure expression of the current cell is to be evaluated.

Measure invocation

If SumOfSales is the name of a measure and Sum(Sales[Amount]) is its DAX formula, DAX expression

[SumOfSales]

is equivalent to

Calculate(Sum(Sales[Amount]))

and DAX expression

[SumOfSales](Date[Year] = 2001, Store[Country] = “USA”)

is equivalent to

Calculate(Sum(Sales[Amount]), Date[Year] = 2001, Store[Country] = “USA”).

So the syntax sugar which makes a measure name look like a function name is just a clever way to add tables to filter context before evaluating the expression associated with the measure. Since invoking a measure implicitly calls Calculate, from now on I’ll just focus on Calculate function as the same rules apply equally to measures.

Add tables to filter context

Calculate function performs the following operations:

1. Create a new filter context by cloning the existing one.

2. Move current rows in the row context to the new filter context one by one and apply blocking semantics against all previous tables.

3. Evaluate each setfilter argument in the old filter context and then add setfilter tables to the new filter context one by one and apply blocking semantics against all tables that exist in the new filter context before the first setfilter table is added.

4. Evaluate the first argument in the newly constructed filter context.

If a new table is added to filter context and it has blocking semantics against some tables already in the filter context, the affected tables are checked one by one, all common columns with the new table are marked as blocked on the existing table.

Let’s look at an example. Assume the current filter context has two filters: one filter is Date[Year] = 2011, the other filter is Store[Country] = “Canada”. We want to evaluate the following expression in the context

AverageX(Distinct(Date[Month]), Calculate(Sum(Sales[Amount]), Store[Country] = “USA”)).

The first argument of AverageX sets a month in row context. When it comes to Calculate, it first removes the month from row context and adds it to filter context, it does not block anything since there is no [Month] column in existing filters. Next Calculate adds Store[Country] = “USA” to filter context which blocks existing filter Store[Country] = “Canada”. When Sum(Sales[Amount]) is evaluated, Sales table is filtered by the current month in 2011 and stores in USA.

Targets of filter context

After so much effort populating and modifying a filter context, when will the filters be applied? In DAX, the filters in a filter context apply to following DAX table expressions:

1. A table expression that is simply a table reference, such as FactSales.

2. Values(Table[Column]).

3. Distinct(Table[Column]).

In cases of 2 and 3, the Table is filtered by filter context and then distinct values of [Column] are extracted from the filtered table.

So if your expression is

Calculate(SumX(Filter(FactSales, [SalesQuantity] > 1000), [SalesAmount]), Date[Year] = 2011),

the filter context only restricts FactSales and has no effect whatsoever on other parts of the formula. If you image every DAX formula is represented as a tree of parent and child function calls, a filter context is built at the top or in the middle of the tree but takes effect at leaf level table nodes.

Note that DAX function Sum(T[C]) is just a shorthand for SumX(T, [C]), the same is true for other aggregation functions which take a single column reference as argument. Therefore the table in those aggregation functions is filtered by filter context.

Apply filters to a target table

Finally we have identified a target table and are ready to apply filters from filter context. For each filter table in the filter context, we check to see if there are any common columns between the target table and the unblocked columns of the filter table. If there is at least one common column, the target table is semi-joined with the filter table, or in SQL-like terms

SELECT *

FROM TargetTable AS t

WHERE EXISTS

(

SELECT *

FROM FilterTable AS f

WHERE t.CommonColumns = f.CommonColumns

)

Each filter table is applied to the target table independently, so the target table is filtered by all relevant filters.

All, AllExcept, AllNoBlankRow

So far I have said that each setfilter argument of Calculate function returns a table which is added to filter context. Well, that is true as long as the setfilter is not one of the All functions. The All functions should really be renamed as BlockColumns when they are used as setfilter arguments. If one of the All functions is used as the top-level function of setfilter, it only blocks common columns of earlier tables but does not add itself to filter context.

In all other places, including as a sub-expression of a setfilter but not at the top level, All functions behave like any other DAX table expressions and always return a table. One special feature of All functions is that the Table argument inside All(Table), All(Table[Column]), AllExcept(Table, …), AllNoBlankRow(Table), etc. is not filtered by the current filter context.

Pop quiz answers

Answer to question #1.

When the initial filter context contains column DimProduct[ProductLabel], table DimProductSubcategory is not filtered as it does not have that column.

Now look at the next formula

Calculate(CountRows(DimProductSubcategory), DimProduct).

The setfilter argument DimProduct is filtered by [ProductLabel], and then table DimProductSubcategory is filtered by table DimProduct since they both share the columns from table DimProductSubcategory and table DimProductCategory.

Move onto the next two formulas

Calculate(CountRows(DimProductSubcategory), Values(DimProduct[ProductLabel]))

Calculate(CountRows(DimProductSubcategory), Values(DimProduct[ProductSubcategoryKey]))

Both setfilter arguments are a single column table and the column comes from table DimProduct. Since table DimProductSubcategory does not have any column from DimProduct, it is not filtered by filter context. For the same reason, you can add any columns from DimProduct to the filter context and none of them would impact DimProductSubcategory.

Answer to question #2.

In the first formula

Calculate(CountRows(DimDate), FactSales)

Both table DimDate and table FactSales share columns from DimDate, so DimDate is filtered by FactSales.

In the second formula

Calculate(CountRows(DimDate), All(FactSales))

All(FactSales) blocks any columns from FactSales, but since the filter context is empty, it has no effect. When DimDate is evaluated, filter context is still empty.

In the third formula

Calculate(CountRows(DimDate), Filter(All(FactSales), true))

The All function is not at the top level of setfilter argument, table Filter(All(FactSales), true) is added to filter context, table DimDate is filtered by filter context for the same reason as in the first formula.

MDX Overwrite Semantics and Complex Attribute Relationship

2011-02-22T20:58:00.000-08:00

The past month has been extremely busy for me so I didn’t get to write more blogs. Today we’ll resume the exploration of MDX calculation engine. In this post I am going to describe a common mistake made by people when writing MDX calculations that can be very hard to diagnose.

WARNING: The MDX overwrite behavior I am going to describe in this post applies to overwriting to a physical member or the [All] member. Overwriting to a calculated member has its own set of rules which are not covered here.

An Example

Let’s start with a simple example using Adventure Works DW 2008 database. First create a calculated measure, [m], that returns [Internet Sales Amount] for the first quarter of fiscal year 2004.

create member [Adventure Works].[Measures].[m] as

([Internet Sales Amount], [Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2004])

Next send a query to check the value of [m].

select [m] on 0

from [Adventure Works]

$2,744,340.48

Now send another query calculating [m] but start with different calendar quarters.

select [m] on 0,

[Date].[Calendar].[Calendar Quarter].members on 1

from [Adventure Works]

	m
Q3 CY 2001	(null)
Q4 CY 2001	(null)
Q1 CY 2002	(null)
Q2 CY 2002	(null)
Q3 CY 2002	(null)
Q4 CY 2002	(null)
Q1 CY 2003	(null)
Q2 CY 2003	(null)
Q3 CY 2003	$2,744,340.48
Q4 CY 2003	(null)
Q1 CY 2004	(null)
Q2 CY 2004	(null)
Q3 CY 2004	(null)
Q4 CY 2006	(null)

Why don’t we see the value, $2,744,340.48, in all cells? Didn’t the MDX formula of [m] change whatever calendar quarter to the first quarter of fiscal year 2004? The answer lies in MDX overwrite semantics and attribute relationship of the [Date] dimension.

Examine the Example Based on MDX Overwrite Rules

Before we delve into details, let’s establish some terminologies first. MDX attribute relationship defines functional dependency between two attributes, e.g.

[Calendar Quarter] → [Calendar Semester] → [Calendar Year].

If A → B, we say B is related to A and A is relating to B. Therefore, [Calendar Semester] and [Calendar Year] are related attributes of [Calendar Quarter], and reversely [Calendar Quarter] and [Calendar Semester] are relating attributes of [Calendar Year]. On the other hand, [Fiscal Week] and [Calendar Quarter] are not related to each other.

Every MDX expression is evaluated in a context. In a simple case, the context includes the current cell which is defined by the current coordinates of all attributes in the cube. When an MDX expression, like ([Internet Sales Amount], [Date].[Calendar Year].[CY 2004]), overwrites the coordinates of some attributes to non-All members, it also overwrites the coordinates of all related and relating attributes. The following diagram illustrates how MDX overwrite semantics work when you set non-All slices to some attributes.

The common mistake I referred to at the beginning of the post is when people forget about the slices left over at the unrelated attributes. When dimension autoexists is applied and the new slices set by the MDX expression do not exist with the slices left over on the unrelated attributes, you end up with cells that don’t exist. Note that we don’t have this problem when there is only a simple linear attribute relationship defined on the dimension.

In case of a single linear relationship, all attributes in the dimension are always overwritten, either explicitly or implicitly, when any attribute is set to a non-All member. The problem arises only when there is a complex attribute relationship, one with tree-like structure.

Going to back to the Adventure Works example, to calculate the value of any cell, an initial cell coordinate is set to a quarter from the [Calendar] hierarchy. Moreover, all related and relating attributes are set implicitly as well. See a simplified version of attribute relationship below. Every attribute in the diagram, except for [Month Name] is set to a slice that corresponds to a calendar quarter.

After applying the MDX expression associated with calculated measure [m], some attribute slices change to new ones, shown as colored boxes below, other attribute slices retain the previous values, shown as white boxes below.

You get a valid new cell only when the new slices correspond to the first quarter of fiscal year 2004 exist with the untouched old slices derived from the original calendar quarter; otherwise you get a cell that doesn’t exist hence a NULL value for the MDX formula. In this case only the third quarter of calendar year 2003 produces valid cell coordinates. To force setting the current coordinate to the first quarter of fiscal year 2004 regardless of the current calendar quarter, you have to explicitly overwrite all attributes related to the [Calendar] hierarchy like below:

create member [Adventure Works].[Measures].[m] as

(

[Internet Sales Amount],

[Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2004],

[Date].[Calendar].[All]

)

Official Rules

The above example is based on overwriting the current coordinate to a non-All member. But what about the other cases? Well, the following table lists all combinations of how overwriting one attribute can affect its related or relating attributes.

Assume A → B.

Explicit Overwrite	Result
A.All to A.All	B unaffected
A.x to A.All	B to B.All
A to A.x	B to Exists(B.members, A.x)
B.All to B.All	A to A.All
B.x to B.All	A to A.All
B to B.x	A to A.All

An implicit overwrite is when an attribute is moved because of an overwrite on a relating or related attribute. When an attribute is impacted by both an explicit overwrite and an implicit overwrite, the explicit takes precedence. Implicit overwrites do not overwrite related or relating attributes.

Parent Child Dimension

According to the overwrite rules discussed so far, overwriting the key attribute of a dimension to a non-All member should overwrite all other attributes in the dimension implicitly. But this does not work for parent child dimensions.

If you run query

with member measures.x as

(

[Measures].[Amount],

[Account].[Account].[Work in Process]

)

select x on 0,

([Account].[Account].[Raw Materials], [Account].[Account Number].[1162]) on 1

from [Adventure Works]

		x
Raw Materials	1162	(null)

You get back null result even though the calculation overwrites the key attribute [Account]. You have to explicitly overwrite [Account Number] attribute to get back the [Amount] for [Work in Process].

with member measures.x as

(

[Measures].[Amount],

[Account].[Account].[Work in Process],

[Account].[Account Number].[All]

)

select x on 0,

([Account].[Account].[Raw Materials], [Account].[Account Number].[1162]) on 1

from [Adventure Works]

		x
Raw Materials	1162	$1,393,582.00

The official rule here is that:

Overwriting key attribute of parent child hierarchy does not overwrite other attributes except that of its parent.

Complex Relationships Are Very Common

Unless you define a single linear relationship for a dimension, you will end up with a complex relationship. Specifically, when there are more than two attributes in a dimension and you didn’t define any relationship, the default one is a tree-like relationship.

MDX Writeback with Custom Weight Expression

Users are likely to make the mistake we have discussed so far when the current cell coordinates contain many slices on numerous attributes. This is particularly true when it comes to MDX writeback with custom weight expression. When an MDX UPDATE CUBE statement is sent to Analysis Services, the desired result is allocated to leaf level nodes. So if you are writing a custom weight formula, the current context is always a leaf level cell with a slice on every regular attribute. If you are not careful overwriting all attributes in a dimension, your weight expression is likely to return NULL values and you won’t get the allocation you desired.

Conclusion

The complex rules of MDX overwrite semantics combined with a tree-like attribute relationship can easily lead to some very frustrating bugs in your MDX formula. You must make sure to overwrite slices on all attributes you don’t want to keep and preserve the slices on all attributes you do want to keep. Remember that the rules are different for parent child dimensions. If you have a simple tuple like MDX formula that returns unexpected NULLs, you should check whether you have violated the guidelines given in this blog post.

DAX Time Intelligence Functions

2011-01-17T16:33:00.000-08:00

Time intelligence functions are an advanced feature of DAX. They are typically composite functions built on top of other more basic DAX features and have more stringent requirements for the structure of the underlying database. But since time related calculations are fundamental to almost all BI projects, early adopters of DAX started using the time intelligence functions almost immediately after the first release of DAX. Due to the advanced nature of these functions, questions naturally arise about how they work and why they work that way even when people managed to get the desired results.

Marius Dumitru, architect of AS engine team, gave the following recipe to users of time intelligence functions.

1. Never use the datetime column from the fact table in time functions.

2. Always create a separate Time table with contiguous dates (i.e. without missing day gaps in the date values).

3. Create relationships between fact tables and the Time table.

4. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).

5. Make sure you have full years’ worth of data in the Time table. For instance, even though your transaction table may only have sales data up to May 2010, the Time table should have dates up to December 2010 (same goes for fiscal years).

6. The datetime column in the Time table should be at day granularity (without fractions of a day).

In this post, I’ll expose more details behind the implementation of time intelligence functions to shed light on the rationale behind Marius’ advice. This post is meant to supplement online documentation of SQL Server 2008 R2. I will not cover basic usage examples of time intelligence functions in typical BI applications. DAX is a young functional language that evolves rapidly. What is covered here applies to SQL Server 2008 R2.

I assume you are already familiar with advanced DAX concepts such as row context, filter context, Calculate function, Values function, measures, etc. I’ll start with some common features across all time intelligence functions before I delve into individual functions.

The <dates> argument

All time intelligence functions have a special <dates> argument. FirstNonBlank and LastNonBlank operate on non-date/time columns but the conversion rules descibed below still apply. With the exception of DatesBetween and DatesInPeriod, the <dates> argument can be one of three forms:

1. A reference to a date/time column.

2. A table expression that returns a single column of date/time values.

3. A boolean expression that defines a single-column table of date/time values.

Internally, all three forms are converted to a DAX table expression in the following fashion:

Format number	<dates> format	Internal table expression
1	T[C]	CalculateTable(Values(T[C]))
2	Single column table expression	As is
3	Well-formed scalar expression	Filter(All(T[C]), <scalar expression>)

What is a well-formed scalar expression? Vaguely speaking, it is a scalar expression that DAX engine can analyze and extract a fully qualified column reference to a date/time column. T[C] = Date(2011, 1, 17) is a well-formed expression as DAX engine can extract T[C] from it. T[C] * 2 is a well-formed expression for the same reason even though it is not a boolean expression DAX engine will cast it to boolean data type implicitly. Date(2011, 1, 17) is not a well-formed scalar expression as no column reference can be extracted from it.

Note that internally inserted CalculateTable in the first format automatically converts current row contexts to filter contexts. So

MaxX(Distinct(DimDate[CalendarYear]), OpeningBalanceYear([m], DimDate[Datekey]))

will give the maximum opening balance among all years, but

MaxX(Distinct(DimDate[CalendarYear]), OpeningBalanceYear([m], Values(DimDate[Datekey])))

will not as the <dates> argument is in the second format hence not correlated to the current calendar year on the row context.

A special rule regarding datetime filter inside Calculate/CalculateTable

If a Calculate filter has a unique column that is of data type date/time, all previous filters on all columns from the table which contains this date/time column are removed. This hacky feature implies that

Calculate(<expression>, <TI function>) = Calculate(<expression>, <TI function>, All(DateTable)).

This is the reason behind Marius’ recommendation #4. Let’s say you have some years on the pivot-table row and then you drag a measure which uses time-intelligence function DateAdd to show sales from the previous year. The author of the measure formula may not realize that DateAdd function only returns a single column of Datekey which overwrites existing filter on the same column. This special rule makes sure that the filter on the CalendarYear column, which comes from the pivot-table, is also removed so you get back the expected result. Without this special rule, Calculate(<expression>, <TI function>) would set days of the previous year on the Datekey column but leave the previous year as the filter on the CalendarYear column. The conflicting filters would have produced a blank result.

In PowerPivot v1, the only way to mark a column as unique is to create an incoming relationship to this column, hence Marius’ recommendation #3. You obviously also need a separate Date table in order to create a relationship.

In practice, people want to use integer keys to create relationship between Fact table and Date table. If you want to use time intelligence functions in that case, you must add All(DateTable) filter yourself to the measure expression.

In future versions of PowerPivot, users may be able to mark a date/time column as unique without creating an incoming relationship to the column. When that happens, recommendation #3 would no longer be needed.

Calendar

DAX engine creates an internal calendar data structure for each date/time column used in the time intelligence functions. The calendar contains a hierarchy of year-quarter-month-day. The minimum year and the maximum year in the calendar are determined by the actual dates in the date/time column. Internally, intermediate results are represented as arrays of days. So to represent January 2011, intermediate result would hold the 31 days in that month. Although a calendar contains all days in the years involved, it marks which days actually exist in the column. Calendar member functions return result days only if they are marked as exists. So if you have missing dates in the date/time column, they cannot be returned as a result of time intelligence functions.

When a calendar data structure is initially populated, it raises an error if two values from the date/time column correspond to the same day. So if you have a date/time column below the day granularity, it cannot be used in time intelligence functions.

Obviously calendar is an internal implementation detail which is subject to change in future releases of DAX. I mention it here to explain some of the limitations imposed on the current version of time intelligence functions. I’ll refer to calendar again when I discuss individual functions next.

Primitive time intelligence functions

As I mentioned at the beginning of the post, time intelligence functions are an advanced feature of DAX. They are built on top of other DAX features and functions, so there is nothing primitive about them. But many time-intelligence functions are composed from more basic time-intelligence functions which have their native implementations, I call the latter primitive ones.

FirstDate/LastDate/StartOfMonth/EndOfMonth/StartOfQuarter/EndOfQuarter/StartOfYear/EndOfYear return a table of a single column and a single row. They can be used anywhere a table expression or a scalar expression is needed due to implicit table to scalar cast.

As I described in the section on the <dates> argument, no matter which format the user uses, internally they are all converted to a table expression. From now on I will use <dates table> to denote the table expression equivalent to <dates>. I will use DimDate[Datekey] or simply [Datekey] to denote the date/time column extracted from <dates> expression.

FirstDate(<dates>)

LastDate(<dates>)

These two functions return a single column, single row table with values equivalent to

MinX(<dates table>, [Datekey]), or

MaxX(<dates table>, [Datekey]).

FirstNonBlank(<column>, <expression>)

LastNonBlank(<column>, <expression>)

These two functions are not pure time-intelligence functions as the first argument is not limited to <dates> as in all other time-intelligence functions. They are internally rewritten as

Top1(Filter(<column table>, Not(IsBlank(<expression>)), [column]), and

Bottom1(Filter(<column table>, Not(IsBlank(<expression>)), [column]) respectively.

Top1 and Bottom1 are internal functions similar to MinX and MaxX functions but support all DAX data types include boolean and string data types.
Note that <expression> is not automatically wrapped in Calculate, therefore,

FirstNonBlank(DimDate[Datekey], Sum(FactSales[SalesAmount]))

does not give you what you want, but

FirstNonBlank(DimDate[Datekey], Calculate(Sum(FactSales[SalesAmount])))

will produce the expected result.

StartOfMonth(<dates>)

StartOfQuarter(<dates>)

StartOfYear(<dates>, <year_end_date>)

Although not implemented this way, these functions first find FirstDate(<dates>), then jump to first day that exists in the same month/quarter/year.

EndOfMonth(<dates>)

EndOfQuarter(<dates>)

EndOfYear(<dates>, <year_end_date>)

Although not implemented this way, these functions first find LastDate(<dates>), then jump to the last day that exists in the same month/quarter/year.

DateAdd(<dates>, <number_of_intervals>, <interval>)

ParallelPeriod(<dates>, <number_of_intervals>, <interval>)

SamePeriodLastYear(<dates>)

SamePeriodLastYear(<dates>) is identical to DateAdd(<dates>, -1, Year).

Both DateAdd and ParallelPeriod invoke a function, Move, on the calendar object. The only difference is that ParallelPeriod requires the result days to fill an entire month/quarter/year, while DateAdd does not have this requirement.

Currently, DateAdd has a limitation that <dates table> must contain continuous days so that the result days can be continuous too. We have this limitation because when you move all 28 days in February one month forward, like below,

DateAdd(filter(All(DimDate[Datekey]), Year([Datekey]) = 2006 && Month([Datekey]) = 2), 1, Month)

you get back 31 days in March! As we mentioned in the section about Calendar, all intermediate results of time intelligence functions are represented as an array of days. So there is no difference between all days in February and February itself. But what if you have one day missing in the middle of February? Should we return one day missing in March or 27 days in March? This seems to be a tricky but less important question, so we left it undecided and raised an error instead.

The calendar object’s Move function is the most intelligent part of all time intelligence functions. It knows that moving both 3/30 and 3/31 one month forward end up on the same day 4/30. Internally it only moves by number of days or months, number of quarters or years is simply translated to corresponding number of months. Sometimes the Move function can be too smart for the user. When it moves a continuous range of days forward N months, it checks the start day and the end day of the range to see if you are moving whole months or just individual days. This works well when all days exist in the date/time column. But if you have missing days, the Move logic still tries to guess whether you are moving whole months based on days that exist, this may not be what you would expect, hence Marius’ recommendation #2.

DatesBetween(<dates>, <start_date>, <end_date>)

DatesInPeriod(<dates>, <start_date>, <number_of_intervals>, <interval>)

These two functions are different from the other time intelligence functions in that the <dates> argument can only be a fully-qualified column reference to a date/time column. Note that online document incorrectly states that the <dates> argument in DatesInPeriod can be any of the three formats.

Internally, both functions are rewritten as

Filter(All(DimDate[Datekey]), DatesRange([Datekey], Earlier(<start_date>), Earlier(<end_date>))) or

Filter(All(DimDate[Datekey]), DatesRange([Datekey], Earlier(<start_date>, Earlier(<number_of_intervals>), <interval>))

In the formulas above, Earlier is an internal extended version of the public Earlier function that evaluates an entire DAX expression by skipping the last row context. DatesRange is an internal boolean function that returns true if the value of the first argument falls within an interval. In case of DatesRange(<date_value>, <start_date>, <end_date>), the interval is defined as [start_date, end_date]. In case of DatesRange(<date_value>, <start_date>, <number_of_intervals>, <interval>), the interval is defined as [start_date, end_date) where end_date is calculated by calling the Move function on the calendar object, so it is a smart moveJ Note that since <number_of_intervals> can be a negative number, like when you move backwards in time, the interval specified by DatesInPeriod can be in the reverse order. DatesBetween does not allow reversed end points.

Also note that All(DimDate[Datekey]) implies that existing filter context does not apply to the date/time column, unlike when all other time intelligence functions use the name-pair syntax.

PreviousDay(<dates>)

PreviousMonth(<dates>)

PreviousQuarter(<dates>)

PreviousYear(<dates>, <year_end_date>)

Although not implemented this way, these function are equivalent to

FirstDate(DateAdd(<dates>, -1, Day)) in case of PreviousDay, or

DatesInPeriod(

<dates>,

StartOfMonth/StartOfQuarter/StartOfYear(

DateAdd(<dates>, -1, Month/Quarter/Year)

Month/Quarter/Year

) in case of PreviousMonth/PreviousQuarter/PreviousYear.

NextDay(<dates>)

NextMonth(<dates>)

NextQuarter(<dates>)

NextYear(<dates>, <year_end_date>)

Although not implemented this way, these functions are equivalent to

LastDate(DateAdd(<dates>, 1, Day)) in case of NextDay, or

DatesInPeriod(

<dates>,

EndOfMonth/EndOfQuarter/EndOfYear(

DateAdd(<dates>, 1, Month/Quarter/Year)

-1,

Month/Quarter/Year

) in case NextMonth/NextQuarter/NextYear.

Composite time intelligence functions

The remaining time intelligence functions listed below are always internally rewritten using other time intelligence function.

DatesMTD(<dates>)

DatesBetween(<dates>, StartOfMonth(LastDate(<dates>)), LastDate(<dates>))

DatesQTD(<dates>)

DatesBetween(<dates>, StartOfQuarter(LastDate(<dates>)), LastDate(<dates>))

DatesYTD(<dates>, <year_end_date>)

DatesBetween(<dates>, StartOfYear(LastDate(<dates>), <year_end_date>), LastDate(<dates>))

TotalMTD(<expression>, <dates>, <filter>)

Calculate(<expression>, DatesMTD(<dates>), <filter>)

TotalQTD(<expression>, <dates>, <filter>)

Calculate(<expression>, DatesQTD(<dates>), <filter>)

TotalYTD(<expression>, <dates>, <filter>, <year_end_date>)

Calculate(<expression>, DatesYTD(<dates>, <year_end_date>), <filter>)

OpeningBalanceMonth(<expression>, <dates>, <filter>)

Calculate(<expression>, PreviousDay(StartOfMonth(<dates>)), filter)

OpeningBalanceQuarter(<expression>, <dates>, <filter>)

Calculate(<expression>, PreviousDay(StartOfQuarter(<dates>)), filter)

OpeningBalanceYear(<expression>, <dates>, <filter>, <year_end_date>)

Calculate(<expression>, PreviousDay(StartOfYear(<dates>, <year_end_date>)), filter)

ClosingBalanceMonth(<expression>, <dates>, <filter>)

Calculate(<expression>, EndOfMonth(<dates>), <filter>)

ClosingBalanceQuarter(<expression>, <dates>, <filter>)

Calculate(<expression>, EndOfQuarter(<dates>), <filter>)

ClosingBalanceYear(<expression>, <dates>, <filter>, <year_end_date>)

Calculate(<expression>, EndOfYear(<dates>, <year_end_date>), <filter>)

Execution Plans and Plan Hints for MDX IIF Function and CASE Statement

2011-01-06T15:56:00.000-08:00

MDX IIF function, and similarly MDX CASE statement, is often a cause of performance issues during execution of MDX formulas. As a developer who worked on this feature, I'll explain the execution plans considered by the MDX formula engine (FE) in this post.

Introduction to some FE concepts

Before I delve into IIF function, I first need to introduce some basic concepts used by MDX FE. I admit that these concepts can be very complex and are worth several dedicated blog posts. The cursory description here can only give you some general ideas to help you understand execution plans for IIF. You can skip this section directly to the discussion about IIF plans and come back here for references when you need to.

Subspace

Every MDX expression is evaluated in a current context which includes a subspace. A subspace is a subset of cells within the cube space. The original subspace is defined by the MDX query, for example, evaluate a measure expression for all product categories and all years. For a given MDX expression, FE will produce a result, like a value for scalar expressions, for every cell in the current subspace.

Rectangular subspace vs. arbitrary-shaped subspace

FE represents most subspaces in a very efficient rectangular shape which is a crossjoin of all attributes with non-trivial slices. Many common operations are fast and efficient when performed on rectangular subspaces. For example, search for a cached execution plan; determine whether a cell belongs to a subspace; decide whether an MDX scoped calculation applies to a subspace. But sometimes FE has to create arbitrary-shaped subspaces which you can imagine as rectangular subspaces plus additional constraints such as an enumerated set of tuples. { food, drink } x { 2009, 2010 } is an example of rectangular subspace. { (food, 2009), (drink, 2010) } is an example of arbitrary-shaped subspace. In general, Arbitrary-shaped subspaces are bad for performance.

Cell-by-cell mode vs. block mode

FE can evaluate a given MDX formula in cell-by-cell mode, in which case FE first builds a context for each cell in the subspace and then calculates the expression in each single-cell context, or in block mode, in which case FE builds a context which contains all cells in the subspace and evaluates the expression in a single scoop. Microsoft introduced MDX block mode execution in SQL Server 2005 and extended it to much more functions in SQL Server 2008. Typically block mode execution is orders of magnitude faster than cell-by-cell execution.

Logical plan

An MDX expression is evaluated in multiple stages, at one stage, a logical plan tree is built. If you have any SQL background, don't confuse the logical plan for an MDX expression with the logical plan of relational engine. In particular, MDX FE may need to execute some subtrees in the middle of building a large logical plan tree.

Varying attributes

The varying attributes of an MDX expression in a given subspace are those attributes that have more than one member in the subspace and on which the MDX expression depends. Varying attributes of an expression is derived after FE has built a logical plan tree for the expression.

Sparse expression vs. dense expression

An MDX scalar expression is called sparse in a subspace if most of the time it produces a single value, typically NULL. For example, physical measures are sparse in typical query subspaces consisting of crossjoin of dimensional attributes. If an MDX expression is not sparse, it is called dense.

Default value

The common single value of a sparse MDX scalar expression is called the default value of that expression. The default value of an MDX scalar expression is determined after FE has built a logical plan for the expression.

Execution plans for IIF function

Now we are ready to explore the execution plans of IIF. Implementation of IIF underwent a major overhaul in SQL Server 2008. In SQL Server 2005, there is limited support of block mode for most MDX functions, therefore IIF branches are more often evaluated in cell-by-cell mode. When SQL Server 2005 is able to evaluate IIF in block mode, it evaluates both branches in the original subspace with some special flag to indicate that the subspace may be too large, see eager mode below.

In this post I focus on the algorithm adopted by SQL Server 2008 and above. As a first step, FE transforms a single IIF function call into nested IIF function calls by eliminating AND operators and OR operators in the predicate expression. Notice that one branch expression is duplicated as a result of this rewrite.

        IIF(a AND b, x, y) => IIF(a, IIF(b, x, y), y)
        IIF(a OR b, x, y) => IIF(a, x, IIF(b, x, y))

Next, FE builds a logical plan for the simplified predicate expression. Note that FE may execute some subtrees while building a logical plan tree, this is a big topic which I have to discuss in a seperate blog post. After FE has built a logical plan tree for the predicate expression, FE performs constant folding by checking whether the simplified predicate is constant in the current subspace, and if so, reducing IIF function to one of its branch expressions.

The next step is to build logical plans for the then branch expression and the else branch expression. FE can evaluate each branch expression in one of two modes: eager mode and strict mode. As mentioned before, the IIF expression is evaluated in a subspace, let's denote it as S0. FE can either evaluate the then branch expression in S0 or FE can build a new subspace, S1, by restricting S0 to those cells that the predicate is true. Similarly, FE can either evaluate the else branch expression in S0 or FE can build another new subspace, S2, by restricting S0 to those cells that the predicate is false. If FE evaluates a branch expression in the original subspace S0, we call it in eager mode. If FE evaluates a branch expression in the restricted subspace S1 or S2, we call it in strict mode.

Strict mode has the obvious benefit of reducing the number of cells an expression is evaluated, so why doesn't FE choose it all the time? As it turns out, constructing a new subspace by evaluating the predicate will produce an arbitrary-shaped subspace if the varying attributes of the predicate expression contain more than one attribute. In general, arbitrary-shaped subspace can lead to bad performance due to various reasons such as cache misses. Moreover, if a branch expression is simple enough, like when it is a constant value, it doesn't make sense to build a new subspace at all.

MDX gives user direct control over whether a branch is executed in eager mode or strict mode through branch hints.  For example, IIF(p, x HINT EAGER, y HINT STRICT) tells FE to build logical plan in eager mode for the then branch and in strict mode for the else branch.

When there is no direct hint from user, FE takes several steps to come up with a final logical plan for each branch expression. It first tries to build a logical plan for each branch in eager mode if it is cheap. It is always cheap to build a plan for a constant value expression. When branch expressions are more complex, FE looks at the varying attributes of the predicate logical plan. If the varying attributes contains more than one attribute, hence will produce an arbitrary-shaped subspace which makes an eager plan more appealing, FE first tries to build an eager logical plan tree for a branch expression if it can be cheaply done, in other words, if it can build a plan tree without executing any subtree. In the cheap mode, if at any point in the process of building a logical plan tree for a branch expression, FE finds out that it needs to execute a subtree before it can proceed, FE will abandon the process and roll back all the way to the root of the current tree. I have seen bad performance caused by this trial-and-error process when a deeply nested IIF tree recursively tried to build a cheap plan, aborted the effort, built a real plan, and then repeated this process at the next nesting level. FE may abandon a cheaply built plan when the predicate expression is sparse and a branch with a cheaply built eager plan is on the minority side. For example, if the predicate is mostly true, the else branch is on the minority side. If a branch is on the minority side and if it is dense or if its default value doesn't match the default value of the IIF expression, its cheaply built eager plan will be abandoned.

At this stage, if FE still doesn't have a logical plan for a branch expression, FE will build a logical plan for that branch in strict mode. It first produces a new subspace for the branch by actually executing the predicate expression, producing a materialized list of tuples of the varying attributes of the predicate that satisfy the condition for that branch, and then applying the list of tuples to the original subspace as a new constraint. FE then generates a logical plan of the branch expression in the new subspace.

As you can see, MDX FE employes a rule-based approach when building execution plans for IIF function. FE makes decisions based on sparsity and complexity of sub-expressons. Since sparisity of MDX expression is based on heuristics rather than statistics, FE may make the wrong decision which is why we need user hints.

IIF functions are often deeply nested, although this may not be obvious at first glance. As mentioned earlier in the post, AND and OR operators in IIF predicate are rewritten into nested IIF calls. Searched CASE statement is internally rewritten as nested IIF calls as well. Moreover, MDX script IF statement is also internally mapped to an IIF function call. All these internal tree rewrites make deeply nested IIF trees a common phenomenon in many cube.

Although MDX FE employes a complex algorithm to build a final plan for the IIF function, we can still learn some basic lessons that may help us understand performance issues associated with the function.

Strict mode helps when a branch expression is very expensive. It can be a lot of extra data has to be read from disk for those extra cells in eager mode. It can also be a very expensive MDX scoped calculation has to be included for one unwanted cell in eager mode. Try plan HINT STRICT if you think AS fetches too much data from disk as a result of evaluating a branch in eager mode.
All the trial-and-errors when building the eager plan can be a source of bad performance when the same IIF function is evaluated many times, typically since the parent function chooses cell-by-cell mode, or when deeply nested IIF calls recursively try eager mode and then fall back to strict mode. Adding plan hints in this case will force FE to make up its mind right away.
Order of predicates in nested IIF calls can be important. A wrong condition at the top level may produce an expensive arbitrary-shaped subspace which makes it expensive to evaluate a child subtree. Or a right condition at the top may save a lot of time by eliminating the need to evaluate a more expensive condition at lower level. Keep in mind that AND and OR operators are rewritten as nested IIF so the order in AND and OR operators is important for the same reason.
Sometimes cell-by-cell mode can be fast especially when a top level function is in cell-by-cell mode. I have seen cases where MDX FE spends a lot of time building plans for a branch expression but never execute the plans as the IIF condition was never met. When the IIF function itself is called many times since its parent is in cell-by-cell mode, all the time FE spent on building unused plans is wasted. Here you may try an undocumented HINT LAZY which applies to all MDX expressions but only takes effect when a server config flag LazyEnabled is set to 1. Using this hint will force FE to delay evaluating the MDX expression until execution time. No logical plan is built by traversing the expression tree. WARNING: You will not get support from Microsoft if you use this hint on your own. You must get approval from Microsoft Customer Service and Support if you want to use this hint in your production system.

Execution plans for CASE statement

There are two types of CASE statements: simple case statement and search case statement.

Search case statement is internally rewritten as nested IIF function calls in the following way:

CASE
WHEN p1 THEN e1
WHEN p2 THEN e2
...
WHEN pN THEN eN
ELSE eL
END

=>

IIF (p1, e1, IIF(p2, e2, ... IIF(pN, eN, eL)...))

After the rewrite, FE executes all IIF functions according to the algorithm outlined in the previous section.

SQL Server 2008 SP2 CTP1 introduced a hint for search case statement. The syntax of hint for CASE is:

CASE HINT <EAGER|STRICT>
WHEN ... THEN ...
...
ELSE ...
END

Internally, the provided hint is propagated to all branches of all IIF functions generated during the rewrite. Afterwards, FE will use the hint in the same way as hints provided directly to an explicit IIF function.

The simple case statement has an execution plan similar to that of IIF function. The main difference is that IIF function only has two branches while simple case statement has N branches. As of this writing, users cannot specify eager or strict hints to simple case statements.

In summary, since MDX IIF function and CASE statement are frequently a source of performance problems, using plan hints may help you solve your problem. Note that plan hints for IIF function are only available in SQL Server 2008 and later and plan hints for search case statement are introduced after SQL Server 2008 SP2 CTP1. I have to admit trying out plan hints is like shooting in the dark due to the lack of end user tools to display MDX execution plan trees.