What’s New in Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3
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:
- 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
- Filter Functions
- Text Functions
- Math and Trig functions
- ACOS Function
- ACOSH Function
- ASIN Function
- ASINH Function
- ATAN Function
- ATANH Function
- COMBIN Function
- COMBINA Function
- COS Function
- COSH Function
- DEGREES Function
- EVEN Function
- EXP Function
- GCD Function
- ISO.CEILING Function
- LCM Function
- MROUND Function
- ODD Function
- PI Function
- PRODUCT Function
- PRODUCTX Function
- QUOTIENT Function
- RADIANS Function
- SIN Function (DAX)
- SINH Function (DAX)
- SQRTPI Function (DAX)
- TAN Function (DAX)
- TANH Function (DAX)
- Statistical Functions
- BETA.DIST Function (DAX)
- BETA.INV Function (DAX)
- CHISQ.INV Function (DAX)
- CHISQ.INV.RT Function (DAX)
- CONFIDENCE.NORM Function (DAX)
- CONFIDENCE.T Function (DAX)
- EXPON.DIST Function (DAX)
- GEOMEAN Function
- GEOMEANX Function
- MEDIAN Function
- MEDIANX Function
- PERCENTILE.EXC Function
- PERCENTILE.INC Function
- PERCENTILEX.EXC Function
- PERCENTILEX.INC Function
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!