When Microsoft first included OLAP Services (the precursor of today’s SQL Server Analysis Services) in SQL Server 7, the company totally changed the enterprise database landscape. The introduction of OLAP Services marked the first time that OLAP (online analytical processing)/business intelligence analysis capabilities were bundled with an enterprise relational database, and it was one of the driving forces that transformed BI from a niche IT role to mainstream database technology.
Today, most organizations are deploying some type of BI application and reporting capability as a part of their database infrastructure. When it comes to SQL Server, OLAP Services has evolved into SQL Server Analysis Services (SSAS), and the product has continued to mature and expand in terms of its BI capabilities. Some of the most important new features in SQL Server 2016 Analysis Services include:
- New SQL Server Data Tools (SSDT) –SSDT is the primary platform for developing SSAS, SSIS and SSRS database applications. The new SSDT is built on the Visual Studio 2015 shell, and it is a separate installation from the SQL Server instance. You can download it from Download SQL Server Data Tools (SSDT).
- Improved modeling performance for tabular 1200 models -- Metadata operations in SSDT are much faster than tabular 1100 or 1103 models.
- Bi-directional cross filtering -- Bi-directional cross filters in tabular models eliminate the need for hand-coded DAX workarounds to propagate filter context across table relationships.
- Parallel processing for multiple table partitions in tabular models – SSAS in SQL Server 2016 now includes automatic parallel processing functionality for tables with two or more partitions.
- Add computer accounts as SSAS administrators using SQL Server Management Studio (SSMS) – With SQL Server 2016, you can now use SSMS to configure computer accounts to be members of the SSAS administrators group.
- DBCC for SSAS -- Database Consistency Checker (DBCC) runs automatically to detect potential SSAS data corruption issues. It can also be run on demand to check for problems in your data or models. DBCC runs different checks for tabular and multidimensional models.
- Extended Events updates – With SQL Server 2016, you can now use SSMS to configure and manage SSAS Extended Events.
- Analysis Services Management Objects enhancements – Analysis Services Management Objects (AMO) in SQL Server 2016 has been re-factored to include the Microsoft.AnalysisServices.Core.dll assembly that separates out common classes like Server, Database and Role.
- PowerShell enhancements for tabular models – SQL Server SSAS 2016 includes several new PowerShell enhancements for tabular models. The PowerShell Invoke-ASCmd cmdlet now accepts Tabular Model Scripting Language (TMSL) commands.
- DAX enhancements – SSAS 2016 includes support for variables for PowerPivot Data Analysis Expressions (DAX). There are also more than 50 new functions to support faster calculations and enhanced visualizations in Power BI.
For more detailed information about the new features in SQL Server 2016 SSAS, check out What's New in Analysis Services. For a hands-on, step-by-step guided tour through the new SSAS features, see the What's New in SQL Server 2016 Analysis Services Virtual Lab.
HPE and Microsoft are the underwriters of this article.