Black Friday 2014

DAX Patterns

hierarchy

Parent-Child Hierarchies

DAX does not directly support parent-child hierarchies. To obtain a browsable hierarchy in the data model, you have to naturalize a parent-child hi…

» See more patterns



Most popular whitepapers




Black Friday 2014



 
Latest articles
Search    Tags


All the secrets of SUMMARIZE

Summarize 1

SUMMARIZE is a function that looks quite simple, but its functionality hides some secrets that might surprise even seasoned DAX coders. In this article, we analyze the behavior of SUMMARIZE, in order to completely describe its semantic. The final advice might surprise you: we will suggest to avoid the use of SUMMARIZE in your code, unless you know exactly which of the functionalities of SUMMARIZE are safe to use.


AutoExist and Normalization

AutoExist_F01

The autoexist feature used by MDX queries sent to an Analysis Services or Power Pivot data models can have different behaviors depending on the normalization level. This article describes these differences and provides best practices to avoid unwanted results.


From SQL to DAX: Grouping Data

The GROUP BY condition of a SQL statement is natively implemented by SUMMARIZE in DAX. This article shows how to use SUMMARIZE and an alternative syntax to group data.


From SQL to DAX: Filtering Data

The WHERE condition of a SQL statement has two alternatives in DAX: FILTER and CALCULATETABLE. In this article we explore the differences between them, providing a few best practices in their use.


DIVIDE Performance

DIVIDE Performance 01

The DIVIDE function in DAX is usually faster to avoid division-by-zero errors than the simple division operator. However, there are exceptions to this rule, described in this article through a simple performance analysis.


Rolling 12 Months Average in DAX

Rolling Average 12 Months 01

Computing the rolling 12-month average in DAX looks like a simple task, but it hides some complexity. This article explains how to write the best formula avoiding common pitfalls using time intelligence functions.


Check Empty Table Condition with DAX

In DAX there are different ways to test whether a table is empty. This test can be used in complex DAX expressions and this short article briefly discuss what are the suggested approaches from a performance perspective.


Last Process Date in SSAS Tabular

How do you get the last process date for an SSAS Tabular Database? This article shows the techniques you can use and explains how to interpret the information available.


Natural Hierarchies in Power Pivot and Tabular

UnnaturalHierarchySample

Creating a hierarchy in a Power Pivot or Tabular data model is very easy, but you might experience performance issues if the hierarchy is not “natural”. This article explains how to create hierarchies avoiding these problems.


Semi-Additive Measures in DAX

FIG_02_08

Values such as inventory and balance account, usually calculated from a snapshot table, require the use of semi-additive measures. In Multidimensional you have specific aggregation types, like LastChild and LastNonEmpty. In PowerPivot and Tabular you use DAX, which is flexible enough to implement any calculation, as described in this article.


USERELATIONSHIP in Calculated Columns

MultipleRelationships

In a Power Pivot or Tabular model that has inactive relationships, you can use the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. Its usage is simple in a measure, but you might consider alternative syntax in a calculated columns, as it is explained in this article.


Handling Wrong or Missing Dates in Tabular

Missing Year

In the traditional star schema design of a Data Mart, you replace a missing, unknown or wrong date in the fact table with a dummy value in the Date dimension table. In Tabular, handling a Date table requires an existing date and you cannot use a NULL date in a Date table. This article describes how to apply the NULL replacement in the fact table using views, without altering the relational structure of the Data Mart.


Yearly Customer Historical Sales in DAX

Yearly Historical Sales by Occupation

With DAX you can calculate the sales of the first, second and third year of a new customer without any ETL. In this article you see how to implement this calculation with good performance.


Organizing Measures and Attributes in Folders with Tabular

DisplayFolderMenu

When you create a Tabular model in Microsoft SQL Server Analysis Services (SSAS), you have to define all of the measures that the user will be able to use. All the measures defined in the measure grid of a table are displayed within a measure group having the same name of the table itself.


Week-Based Time Intelligence in DAX

pivot-ytd

The DAX language provides several Time Intelligence functions that simplify writing calculations such as year-to-date (YTD), year-over-year (YOY) and so on. However, if you have special calendar structure such as 4-4-5 weeks’ calendar, you need to write your custom time intelligence calculation. In this article, you will learn how to write the required DAX formulas.


Rules for DAX Code Formatting

With the modern editors that have automatic code formatting features, each one has its own code formatting style. Usually there are a few “common rules” for code formatting depending on the language, but there was nothing for DAX


Usage of dates argument in a row context

NumDays and NumLastDay

Several Time Intelligence functions in DAX use a <dates> argument, which has a special behavior because it can be expressed by using both a column reference syntax, a table expression or a Boolean expression.


Parallelize ProcessAdd with AMO

In this article we see a function to enable parallelism in commands sent to Analysis Services by using the AMO library even when you need to use the change the QueryBinding. This function is useful because the current AMO library (up to SQL Server 2012) cannot correctly generate the required XMLA code if multiple commands using QueryBinding are executed within the same batch.


Best Practices Using SUMMARIZE and ADDCOLUMNS

FIG01

Everyone using DAX is probably used to SQL query language. Because of the similarities between the Tabular data modeling and the relational data modeling, there is the expectation that you can do the same operations that SQL allows. However, in its current implementation DAX does not permit all the operations that you can do in SQL. A few of the limitations are caused by the lack of equivalent syntax, others depend on a counterintuitive behavior of the xVelocity in-memory engine when extension columns are involved in a query.


Order of Evaluation in CALCULATE Parameters

DAX is the new language used by PowerPivot and Analysis Services in Tabular mode and it resembles the syntax of Excel formula and it can be considered a functional language. You do not have iterative statements, but you can run iterative functions like, for example, SUMX and FILTER. The most important functions in DAX are CALCULATE and CALCULATETABLE, and these functions are also the most complex to master, because of the many operations they do.


Clever Hierarchy Handling in DAX

image

Hierarchy handling in DAX is not very easy, due to the fact that hierarchies, unlike it was in MDX, are not first-class citizens in the DAX world. While hierarchies can be easily defined in the data model, there are no DAX functions that let you access, for example, the parent of the CurrentMember. Well, to tell the truth, there is no concept of CurrentMember in DAX either.


Incremental Processing in Tabular Using Process Add

In Analysis Services 2012 you can process a table in a Tabular model by several ways: you can process the whole table, you can split the table in several partitions and process a single partition, you can merge partitions and you can incrementally process a single partition by using ProcessAdd, which is the topic of this article.


Memory Settings in Tabular Instances of Analysis Services

Memory settings in Analysis Services might be an important tuning of a correct setup, both for production and developer machines. Analysis Services 2012 can be installed in different ways and the new Tabular instance has new memory settings that are important to know.


Optimizing High Cardinality Columns in VertiPaq

VertiPaq is the internal column-based database engine used by PowerPivot and BISM Tabular models. High cardinality columns might be the more expensive parts of a table. If you cannot remove a high cardinality column from a table, by using the techniques described in this article you might optimize its storage saving up to more than 90% of original space.


Budget and Other Data at Different Granularities in PowerPivot

Product table

PowerPivot and BISM Tabular handle relationships between tables by using a single column that requires to be a unique column in the lookup table. In this article you will see how to handle relationships between tables that have different granularities, which is a typical scenario whenever you have a monthly budget table and a daily level sales table.


Execute DAX queries through OLE DB and ADOMD.NET

If you want to execute a DAX query from your code to a Tabular model, you might think that an OLE DB connection should be the right way to get your data. It works, but if you your code is written in .NET a better solution is using the ADOMD.NET library. In this article you will see why ADOMD.NET is better in this scenario.


Understanding Circular Dependencies in Tabular and PowerPivot

Circular Dependency Error

When you design a data model for Tabular you should pay attention to a though topic, which is that of circular dependencies in formulas. It is very important to learn how to handle circular dependencies now because in SQL 2012 (and in PowerPivot 2012) there will be a stronger validation of circular dependencies. Some of the checks described in this article have been introduced with the release RC0 of SQL Server 2012 and were not present before.


From SQL to DAX: String Comparison

In DAX string comparison requires you more attention than in SQL, for several reasons: DAX doesn’t offer the same set of features you have in SQL, a few text comparison functions in DAX are only case-sensitive and others only case-insensitive, and performance might have a relevant impact in your query according to the comparison technique you use.


From SQL to DAX: Projection

Using DAX as a query language you will lack a fundamental feature like projection. In fact, DAX allows you add columns to an existing table, but you cannot simply remove some column from a table. In order to do that, you have to use SUMMARIZE, or you can use ADDCOLUMNS starting from a column that you want to include in the query output and that has to be unique for each row of the result.


Ratio Over Subtotals with Normalized Tables in DAX

Calculating a ratio in DAX is relatively simple in case the underlying data model is a star schema, but you have to consider additional complexities whenever you have a more normalized model, even just a snowflake schema. In this article, you will see how to perform the right computation.


Sorting Dates Columns in PowerPivot v2 and BISM Tabular

Sort by Column Dialog Box

Sorting a column by using another column is one of the nice features of the new version of PowerPivot introduced with SQL Server 2012 (we call it PowerPivot v2, but it really v11 because it is aligned with SQL Server). For example, this allows you to sort the Month name in the right order (January, February, …) instead than in alphabetical order. Usually, you should have a month number column that allows you to correctly sort the month name. However, sometime it is necessary to build a calculated column in order to provide the right sort, as you will see in this article.

-

Videos available from SQLBits

We presented a few session at SQLBits and the video of these sessions are available.

This is the list of sessions available from SQLBits 8 and SQLBits 9 – just click the link on the title and jump to the session page with video, slide deck and demo!


Converting MDX to DAX – First Steps

Relationship between Internet Sales and products tables

A BISM Tabular model can be queried by using both MDX and DAX. These two different languages rely on different modeling concepts, because MDX has a semantic based on dimensions, attributes, hierarchies and measures, whereas DAX only knows table and columns. In order to understand these differences and how to find a corresponding behavior in DAX and assuming you already have MDX knowledge, I’d like to examine how you can convert an MDX query in DAX.


Creating a copy of a BISM Tabular project

Using Visual Studio to author SSAS Multidimensional projects, I was used to copy the project, deploy it with another name and then proceed with updates, until I was satisfied with the final result. Then, working with the two projects side-by-side I could check performance and numbers.


Separate date and time in PowerPivot (and BISM tabular)

In PowerPivot you can import data from several sources and it is often the case that you import a DateTime column from a database. From an analytical point of view, you usually make analysis over dates (day/month/year/quarter/week) and over time (hour/minute) but to do that it is often useful to have two independent set of attributes: one for the date (the Calendar table) and one for time (for those businesses where this element is relevant).


DateTool dimension: an alternative Time Intelligence implementation

Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).