SSAS Tabular in SQL Server 2016

During the MS Ignite conference and since then a lot of news regarding feature and functionality for SQL Server 2016 has been announced.

Common for most of the tools in the BI stack is that we have not seen any new features for quite som years except tabuler models for Analysis services. This seems to change a lot for the upcoming release and therefore i will try to cover all these change through a couple of posts.

Below i will try to go through the exciting news in SSAS Tabular for me as a Business Intelligence Consultant.

SSAS Tabular

When it comes to SSAS Tabular we can expect a lot of changes. Among these we can expect to be able to do a lot of advanced modelign directly in the designer. Specifically this means that Many-2-Many and Directional Cross Filtering will be enabled. Both of these features have been expect to reach the front-end for quite some time now.

A better Time Intelligence implementation is also mentioned as part of the next SQL Server release. Most of you probably already know the about PARALLELPERIOD, TOTALYTD and other function for this sort of operations.  During MS Ignite it was mentioned that this new feature will automatically create a separate date table with a range based on dates available in other tables and add the necessary relationships between tables.

Performance was also mentioned to be greatly improved. Not that many details have been leaked but looking at the improvements seen in PowerBI Designer we can expect quite a performance boost. This will especially apply to the communication between the Formula and Storage engine which should be expected to be decreased due to optimisations in the understand of DAX queries.

In general we should probably expect to see most, if not all, of the new features available in the PowerBI Designer popping op in SSAS Tabular for SQL Server 2016.

With this in mind and looking at the latest releases to the PowerBI Designer we can expect the following two things:

  1. A lot of new functions like AVERAGEX and CONCATENATEX. A lot more information about all these functions can be found in the documentation published by Microsoft.
  2. Variables was also introduced not hat long time ago in the designer and will enable you to do amazing things. To get more info on this please read this blog-post by Alberto Ferrari.

Parallel Partition Processing will be introduced with SQL Server 2016 and for people worker with bigger models this ia  pretty important improvement. Previously only a single partition for a table could be processed at a time.

A new native scripting language is also introduced for tabular models. Currently tabular models consists of a construct for Multidimensional which during deployment is reverse engineered to a tabular model. The new languages is intended to make it easy to understand, modify and deploy. A lot of people misunderstood this and is expecting a replace for DAX to query the models.

PowerBI will be enabled to do live request directly to on-prese Analysis Services (SSAS) models. This will enable companys which is not interested in moving to the cloud to use Power BI som front-end on the internet.

Currently there is no information available about when which features will be available for public testing or even released in a final version.

When i see new features showing or there is something more specific available i will try to keep this post up to date.

If you want more information about SQL Server 2016 you should either visit Microsofts product pages or have a look at the SQL Server blog.

Bent Pedersen
Member since November 20, 2016

I am a Senior Business Analytics Architect at Kapacity A/S, where I have been working with enterprise customers on solutions with SSAS Tabular, Datazen and SQL for the last three years. During the past year, he concentrated on working with devOps, Automation, testing and simplifying development processes for Microsoft BI.


    Your email address will not be published. Required fields are marked *