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


Similar to the SQL Server 2016 CTP 2.3 we have to look at what’s new with SQL Server Analysis Services (SSAS) release updates in specific to Tabular engine. As you are aware Tabular model has been introduced in SQL Server 2012 version but nothing much added within 2014 version, however this 2016 CTP drives Microsoft Power BI, Power BI Desktop, and even Power Pivot for Excel 2016. No doubt the efforts from Power BI team made General Availability of Power BI and this can benefit SSAS world in a longer with innovations that went into the Power BI service and Power BI Desktop.

 

This is ‘cloud first’ delivery features to customer’s on-premises, by default Power BI users will have the ability to get newest features from SSAS 2016 CTP as well.

By taking the huge list from SSAS product team about what’s new with SSAS 2016 CTP 2.3, have a look:

  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:

Date and Time Functions

Information Functions

Other

Filter Functions

Text Functions

Math and Trig functions

Statistical 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.

Excited enough!

Get the SQL Server 2016 CTP 2.3.

1 comment for “What’s new with Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3

Comments are closed.