By: Koen Verbeeck | Updated: 2017-08-14 | Comments | Related: > Analysis Services Development
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.
Errors are pointed out with “squiggles” and you have the ability to save incomplete or incorrect measures.
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.
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):
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.
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!
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-08-14
