Enhancements for the DAX language in Analysis Services 2016

存储架构 2017-07-20

By: Koen Verbeeck | | Related Tips: > Analysis Services Development

Problem

With the release of SQL Server 2016, several improvements were shipped for Analysis Services Tabular. A couple of key enhancements were made to the DAX language; the scripting and querying language for Tabular models. In this tip, we’ll give an overview of these new features. References will be given if previous tips have already been written about the subject.

Solution

All the mentioned improvements in this tip apply to Analysis Services Tabular 2016 or later, to Azure Analysis Services and to Power BI Desktop as well.

Improved DAX formula writing

When creating a Tabular model, you can write DAX formulas to create calculated columns or measures. Efforts have been made to improve the usability experience of writing DAX. This has been done by adding highlighting and the ability to add comments.

You can also format your code by adding new lines or by indenting it.

There is also Intellisense included:

This also works for referencing tables or columns:

To help you out even more, there are error “squiggles” to point out mistakes in your DAX formula:

You can find the actual error message in the Error List window. Another nice addition is the ability to save incomplete (or incorrect) measures. This might be useful when you’re in the middle of a formula and for some reason you need to stop writing and work on something else (for example creating another measure or column first so you can use it in your formula). The result will be marked as #Error for a measure:

The same is true for calculated columns:

DAX variables

A new feature is the ability to use variables inside a DAX formula. Using variables might make your DAX code easier to write, easier to read and performance might improve as well. Variables can store the result of any DAX expression. Let’s illustrate with an example. I’m using the same Tabular model I created in the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3 . We’re going to create a measure displaying the percentage of the grand total of the sales amount. In previous versions of SSAS, we had to use the following formula:

With variables, we can split out the formula and calculate the grand total in advance (which might improve performance since you need to calculate it only once):

Although the entire formula is now longer, it is easier to read. Keep in mind you can also store tables in variables, besides scalar values.

If you want more information and examples of DAX variables:

New DAX functions

Lots of new functions have been added to the DAX language. They are too many (over 50!) to mention them all here, but you can find a full list at New DAX Functions .

Noteworthy new functions are CALENDAR , MEDIAN and CONCATENATEX, but there are many more especially in the statistical and mathematical area.

With the CALENDAR function, you can easily create a continuous range of dates. This can be the starting point of a date dimension. An example is given in the tip Scenarios for Using Calculated Tables in SSAS Tabular 2016 or Power BI Desktop – Part 2 .

In previous versions it was quite difficult to calculate a median value. You had to sort the data set according to a measure and find the middle value. This led to complex and long formulas using a combination of CALCULATE, FILTER and EARLIER. You can find the formula for this statistical pattern at the DAX Patterns website. With the introduction of MEDIAN, this becomes a lot easier. You just need the following formula:

The result:

Remark: it can take some time to find a good example using the Wide World Importers database. The data is randomly generated but apparently distributed around the median 10 for a lot of dimension attributes.

With CONCATENATEX, you can concatenate different columns together over the various rows. For example, we can create a formula this displays all sales territories with the country:

The SUMMARIZE function is used to find the distinct set of the Sales Territories and the countries.

Some other new functions are covered in the tip New DAX functions in SQL Server 2016 .

Other enhancements

There are also some improvements behind the scenes:

  • The number of scans needed for non-empty calculations.
  • Multiple measures from the same table will be combined into a single storage engine query. This is called measure fusion.
  • When a measure is requested at multiple granularities, the query is sent at the lowest level and the other granularities are aggregated from this level. For example, if you have a query on the year, month and day granularity, only the day level data is fetched.
  • Elimination of redundant joins.
  • Strict evaluation of IF and SWITCH. Branches that result in False are no longer evaluated which means no storage engine queries are sent.

Next Steps

Last Update:
2017-07-20

MSSQLTips

责编内容by:MSSQLTips (源链)。感谢您的支持!

您可能感兴趣的

ALERT: Database Corruption ORA-600 ORA-7445 errors... APPLIES TO:Oracle Database – Enterprise Edition – Version 11.2.0.3 to 12.2.0.1 IBM AIX...
66、django之模型层(model)–多表相关操作(图书管理小练习)... 前面几篇随笔的数据库增删改查操作都是在单表的操作上的,然而现实中不可能都是单表操作,更多的是多表操作,一对一,一对多,多对多的表结构才是我们经常需要处理的,本篇将带我们了解多表操作的一些相关操作。也会...
Remove the object from the database relationship &... I have a one to many relationship between a Product entity and a Cart Entity. The user needs to ha...
【毕设进行时-工业大数据,数据挖掘】Java 数据库写入百万条记录!时间减半... 正文之前 众所周知,昨天跑了一百万条数据,结果活生生的耗了我 这么多时间!!沃日啊!!想死了好么!!本来四点多准备跑完就去健身,结果跑完都五点半了,所以只能先去吃饭,然后晚...
Python实现时间比较 最近用Python来做时间的比较,整理一下。 场景:数据库保存的是datetime.datetime.now()的str类型的数据,需要把数据库保存的时间跟现在的时间进行比较。用到的库...