The 2014 release of SQL Server was mainly focused on the new in-memory OLTP engine and brought little new to the BI stack. However, with SQL Server 2016 Microsoft has heavily invested again in Analysis Services.

There are not many notable changes for SSAS Multidimensional, but the Tabular model has been improved a lot. The main improvement is the new 1200 compatibility level. Behind the scenes, the Tabular model is completely rewritten to use JSON instead of XML. This has some important advantages, such as better scripting options in SQL Server Management Studio (SSMS) but also using PowerShell but also better development support inside Visual Studio, such as the Tabular Model Explorer for example.

Other great improvements for Tabular 2016:

  • The support of display folders
  • The introduction of translations (although not as flexible as in Multidimensional)
  • Bi-directional cross-filtering. This means relationships can be filtered in both directions. This allows for easier many-to-many relationship support in Tabular, which was much more difficult in previous versions.
  • Calculated tables: tables can now be generated by a script
  • Parallel processing for partitions
  • Over 50 new DAX functions!
  • The introduction of variables into the DAX language
  • Better support for writing DAX formulas
  • Massive performance improvements (especially for DirectQuery)

With SQL Server 2016, the Tabular model has become much more mature. Its also important to note that most changes are also incorporated into Power Pivot in Excel 2016 (in whats now known as the Excel Data Model) and in Power BI.

Contact us for more information on SQL Server 2016 Analysis Services!