Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What's New in SQL Server Analysis Services 2016 – Part 1


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

With the release of SQL Server 2016, many new features and improvements were introduced for all its products. SQL Server Analysis Services (SSAS) is no exception, but it should be noted most changes are for SSAS Tabular, the in-memory columnar database. Over the past months, a couple of tips have been written about the changes in SSAS. This tip gives a comprehensive overview of all the new features and links back to the original tips for more details if applicable. Azure Analysis Services is not included in this overview.

Solution

DAX Improvements

The DAX formula and query language received a long-needed update. Variables were introduced, as well as over 50 new functions. Writing DAX became easier, with improved Intellisense, code highlighting, code formatting and the use of comments.

DAX writing - intellisense 2

Errors are pointed out with “squiggles” and you have the ability to save incomplete or incorrect measures.

DAX writing - errors

You can find an overview of all changes in the tip Enhancements for the DAX language in Analysis Services 2016.

There also have been some improvements in the use of DAX by client tools, such as Power BI. This is done by reducing the number of queries between the client tool and the server, by creating one single “super DAX” query instead of multiple queries. Furthermore, measure execution has been streamlined to optimize performance. One example not mentioned in the tip above is a countrows optimization, by using table heuristics.

Analysis Services Multidimensional Improvements

Unfortunately, this list is quite short:

  • Distinct count ROLAP optimization for data sources like DB2 and Oracle (certainly a very limited use-case)
  • Drill-through multi-selection support with Excel 2016
  • Excel query optimizations

The New Tabular 1200 Compatibility Level

A new compatibility level has been introduced for Tabular: 1200. You can choose the level when creating a new Tabular project.

SSAS compatibility level 1200

It’s possible to upgrade an existing model (1100 or 1103) to the new 1200 level.

What’s remarkable about this compatibility level is that SSAS Tabular has moved away from XMLA and uses JSON instead. One immediate advantage is that modeling performance has much improved compared with the previous levels. The latest versions of SQL Server Management Studio (SSMS) can work with the new 1200 level. SSMS supports scripting the Tabular model for the commands Create, Alter, Delete, Backup, Restore, Attach and Detach. The output is Tabular Model Scripting Language (TMSL) which is in JSON. Here you can see an example for tacking a backup of a model (note that SSSM still calls this an XMLA query, even though it’s JSON):

TMSL output for backup command

Of course, changing to JSON has an impact at various places:

  • All PowerShell cmdlets have been upgraded to work with the 1200 level. The Invoke-ASCmd now also accepts TMSL scripts.
  • The Analysis Services Execute DDL Task in Integration Services has been updated to work with the TMSL language
  • The Analysis Services Management Objects (AMO) has been adjusted to include a Tabular namespace for managing Tabular models or to create them programmatically. There have also been some changes in the assemblies: Microsoft.AnalysisServices.Core.dll has been added to treat common classes like Server, Database and Role. These classes are the same, whether you are working with Multidimensional or with Tabular. You can find more information about the API here and about AMO here.
  • When you open a model with View Code in Visual Studio, the model will be rendered in JSON. Depending on your version of Visual Studio, this might be in the built-in JSON editor or in plain text.

Translations

The new release of SSAS Tabular now has the ability to let you create translations for your metadata. This is done by editing a JSON file which stores all the objects metadata (model, tables, columns, measures etc.). In the tip Creating Translations for Analysis Services Tabular 2016 I describe how you can use the tool Tabular Translator to easily create translations for your Tabular model.

create translations in editor

Keep in mind there’s no option available to translate the actual data, like in SSAS Multidimensional. You can find more information about translations in the tip Multi-language support for SSAS Tabular Models.

DirectQuery

There are several significant enhancements to the DirectQuery engine for the 1200 models:

  • New data sources. Oracle, Teradata and Microsoft Analytics Platform (the former Parallel Data Warehouse) are now supported.
  • Generation of simpler queries which leads to better performance
  • Row Level Security (RLS) is supported
  • Calculated columns are now supported as well
  • Extra control over the sample datasets used for model design and testing
  • MDX support. You can for example use Excel PivotTables to connect to a Tabular model that uses DirectQuery. This is available for all compatibility levels.

DirectQuery also benefits from all the DAX enhancements listed in the first section. Less DAX queries (and better DAX queries) drastically improve DirectQuery performance.

Next Steps
  • If you want to try out all of those new features, you can download SQL Server 2016 Developer edition for free.
  • The official overview of new features can be found here.
  • You can find more Analysis Services tips in this overview.
  • For more SQL Server 2016 tips, you can use this overview.
  • Stay tuned for part 2 of this tip!


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools