Share via

What’s New in Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3

  • Article

This is an exciting time for SQL Server Analysis Services as we start to release updates to our Analysis Services Tabular engine as part of SQL Server 2016 CTP 2.3 preview This blog post introduces some of the enhancements coming to SQL Server Analysis Services Tabular and many more will be coming in the next CTPs.

Analysis Services Tabular drives Microsoft Power BI, Power BI Desktop, and even Power Pivot for Excel 2016. It has received many enhancements since SQL Server 2014. The Power BI team worked diligently on Power BI and now with the General Availability of Power BI behind us it is time to take a look at how you, the user of Analysis Services, can benefit from the innovations that went into the Power BI service and Power BI Desktop.

This is the first time that the Analysis Services team delivers “cloud first” features to customer’s on-premises. Features that are used by Power BI users every day now ship as part of Analysis Services in SQL Server 2016.

Let’s take a look at what’s available in SQL Server 2016 CTP 2.3:

  1. New DAX Functions. New DAX functions, already available in Power BI Desktop and Excel 2016 Preview, are now available in Analysis Services Tabular as well, including real gems like Percentile, Median, DateDiff and Product making DAX even more powerful. There are over 50 new DAX functions:

See the full  of all DAX functions here.

2.  “Super DAX”. This is the codename for a project that brings performance enhancements to DAX in two areas:
o More performant queries from client tools that use DAX
o Optimization of measure execution 

“Super DAX” helps reduce the chattiness between DAX clients and Analysis Services. The vast majority of Power BI visuals (both for the service and desktop) have been rewritten to issue a single “Super DAX” query, which in turn requires only a single storage engine query (VertiPaq or DirectQuery), at least for simple measures. Previously, depending on chart type and fields you might get anywhere between 3 to hundreds of storage engine queries.

In addition to query optimizations, measure execution has been streamlined to boost performance of any client tool, such as Excel, Datazen, or SSRS. Let’s take a look at a few examples:

    • Variables have been introduced to DAX: In a query or measure, evaluate an expression once and use the results many times, thus reducing the times the expression is executed.
    • Strict evaluation of IF/SWITCH: A branch whose condition is false will no longer result in storage engine queries. Previously, branches were eagerly evaluated but results discarded later on.
    • Non empty calculation optimizations: Just a single scan is needed for non empty results, instead of multiple scans in previous version of SSAS.
    • Measure Fusion: Multiple measures from the same table are combined into a single storage engine query.
    • Grouping sets: When a query asks for measures at multiple granularities (Total/Year/Month), a single query is sent at the lowest level and the rest of the granularities are derived from that level, thus reducing the times the expression is executed.
    • Redundant join elimination: A single query to the storage engine returns both the dimension columns and the measure values.
    • Multiple result sets for DAX: Multiple results row sets from a single DAX query, as leveraged by Power BI to share intermediate results across multiple result sets.
    • Join orders: Improved ways to arrange join orders so that the joins start from the most restrictive intermediate table that correlates with most other intermediate tables.
    • Countrows optimization: Use table heuristics to return results.
    • Storage engine cache improvements: Storage engine now caches per database instead of per sever.

3. Improved DirectQuery source query generation. DAX query enhancements listed above help with  DirectQuery optimization, as reduced chattiness helps to improve performance.  This is especially important in DirectQuery mode because less queries are sent to the SQL Server data source. Further optimizations help generate simpler SQL queries. SQL Server query performance benefits from simple queries.

 

You can leverage all these features right now by just restoring your existing SSAS tabular models to a SQL Server 2016 CTP2.3 instance or create a new model using the existing SSDT-BI tools.

I hope you enjoyed this quick glance at some of the enhancements coming to Analysis Services Tabular. In my next blog post, I’m going to show you based on a concrete example what the combination of Super DAX and improved DirectQuery could mean for your existing and new Analysis Services Tabular data models. Stay tuned!

Comments

  • Anonymous
    September 02, 2015
    And you forgot to mention that DAX Formatter already supports all of the new DAX functions, including the variables!

  • Anonymous
    September 02, 2015
    When you write: "… Analysis Services Tabular drives Microsoft Power BI, Power BI Desktop, and even Power Pivot for Excel 2016 …" does this mean that the relevant  "super DAX" improvements are applied to Excel 2016 when you're connecting to the Excel data model (power pivot 2016) as opposed to a separate tabular analysis server instance?

  • Anonymous
    September 03, 2015
    Hi Oxenskiold, Actually it means both :) the same Tabular engine is being used for Excel and SQL Server Analysis Services. Thanks, Kasper

  • Anonymous
    September 04, 2015
    Is there an easy way to test these? I haven't been able to get a project to deploy to my dev server from my SSDT-BI 2014 system.

  • Anonymous
    September 04, 2015
    Hi Jason, You should be able to install a SQL 16 CTP 2.3 SSAS instance on your machine and deploy to is from the existing SSDT. Are you getting an error? Thanks, Kasper

  • Anonymous
    September 10, 2015
    Great stuff Kasper! Could you correct the following issues with the function list you provide?

  1. Some links to function descriptions are broken, or don't exist.
  2. Some functions are grouped incorrectly - In the Statistics group, none of the functions after PERCENTILEX.INC have anything to do with statistics.
  3. Could you please add the KEYWORDMATCH function to the list.

  • Anonymous
    September 10, 2015
    Hi Colin, Thanks for letting me know, it looks like the URL changed on me, I updated them. KEYWORDMATCH is not supposed to be out there and will get removed soon. Thanks, Kasper

  • Anonymous
    September 11, 2015
    Thanks for all the updates Kasper! Question - will the "Non empty calculation optimizations" apply to both a DAX query as well as an MDX query sent to a Tabular model?  I have noticed that non empty MDX queries to Tabular tend to be much 'chattier' than equivalent ones in DAX (pre-2016).

  • Anonymous
    September 14, 2015
    This article says "This is the first time that the Analysis Services team delivers “cloud first” features to customer’s on-premises.". I want to know where is reference of configuring on-premise. are they calling Power BI desktop apps as on-premise? I am more interested to know how to configure on-premise.

  • Anonymous
    September 14, 2015
    Hi Javier, Can you give me more details on the Non-Empty scenario you are describing? Thanks, Kasper

  • Anonymous
    September 14, 2015
    Hi Jay, What I meant is that customers who use the on premise SQL Server Analysis services will be able to leverage the changes made for Power BI. Hope that helps, Kasper

  • Anonymous
    October 28, 2015
    The comment has been removed