Enable business insights for everyone with SQL Server 2016: Part 2

March 17, 2016

Product
SQL Server 2016

This blog post was authored by Kasper de Jonge, Senior Program Manager, SQL Server Analysis Services.

This is the second installment of a two-part series. If you missed it, please have a look at part one and learn how SQL Server Analysis Services (SSAS) provides fast access to data to allow analysis at the speed of thought. Read on to learn about the specific improvements made to SSAS for SQL Server 2016.

SQL Server 2016 Analysis Services improvements

SQL Server 2016 offers a number of significant enhancements compared to previous versions, such as:

The improved Tabular model allows BI developers to solve increasingly complex business problems in an agile and straightforward manner with out-of-the-box support for modeling and calculation scenarios such as percentile and many-to-many patterns.
Improvements to the DirectQuery storage mode allow BI developers to utilize data sets from different data sources directly without the need to move the data.
Improvements to the high-performing in-memory (VertiPaq) storage mode can enable analytics on large data sets at the speed of thought.

Many organizations today have built their infrastructure around the rich Semantic model and highly scalable Multidimensional models. In this release of SQL Server, we have improved or added some highly requested performance and manageability features, such as:

Distinct count ROLAP optimization for data sources like DB2 and Oracle allow for faster ROLAP performance.
Drill-through, multi-selection support with Excel 2016 allows for better drill-through support when using slicers and other filters on the PivotTable on double-click (default drillthrough), but not for custom actions (right-click).
Check for corruption issues on database or individual objects with DBCC support for Analysis Services (supported for both Tabular and Multidimensional models).
Diagnose problems with xEvents, a lightweight tracing and performance monitoring system in SQL Server Management Studio (supported for both Tabular and Multidimensional models).
Visualize and explore existing models with Power BI and Power BI Desktop directly or through the enterprise gateway.

Tabular Semantic Model improvements

Organizations who want to use the lighter, in-memory and more agile way of building Semantic models can use the Tabular model introduced in SQL Server 2012. In SQL Server 2016, Tabular models received a number of updates; some of the enhancements to the Tabular Semantic Model in SQL Server 2016 are highlighted below:

Bi-directional cross filtering allows modelers to determine how they want filters to flow between two tables. In SQL Server 2014 and previous versions, Tabular models did not support scenarios such as many-to-many patterns without having to write complicated DAX expressions. However, now with support for bi-directional cross filtering, this and many other scenarios are available by simply changing the type of relationship.
An improved diagram view layout helps navigate complex models with ease. The design has been optimized to help BI professionals understand relationships in models easily and be able to immediately see the filter direction of a relationship and the cardinality of data.
More than 50 additional DAX functions and added support for variables in DAX help solve complex business problems faster.
A translated Semantic model enables business users in multinational organizations to access the BI Semantic Model in their preferred language(s).

DirectQuery improvements

Some companies may prefer to access data sources directly, because their data may be too large to be moved or they need real-time access. Microsoft in SQL Server 2016 provides the ability for BI developers to connect the Tabular BI Semantic Model to its underlying data directly with DirectQuery.

Here is an overview of the improvements to DirectQuery in SQL Server 2016:

Support for more enterprise data sources like Microsoft APS, Oracle and Teradata in addition to the data sources SQL Server already supports.
Significantly improved performance compared with SQL Server 2014.
MDX support for DirectQuery models, allowing business users to use their favorite tools, such as Microsoft Excel and other MDX-based tools. Connectivity from Power View and Power BI continues to be offered.
Support for row level security and calculated columns allows BI professionals to benefit from the rich Semantic model even when connecting to the data source directly.

Analysis Services improvements

With SQL Server 2016, Analysis Services is designed to be faster out of the box. By upgrading your existing server, you can benefit from these enhancements:

Improvements to existing DAX functions and query engine help increase performance for client tools like Excel and Power View when using Tabular models.
Additional DAX functions can be used to further optimize, measure and query performance. Power BI can detect that these functions are available in SQL Server 2016 Analysis Services and then use them to query the data. This can result in significant performance enhancements when compared with Analysis Services in SQL Server 2014.
Extensively reduced data load time through parallel partition processing.

BI developer productivity improvements

The following enhancements have been made to improve BI professionals’ and developer productivity in SQL Server 2016:

SQL Server Data Tools (SSDT) for Analysis Services is now available as part of SSDT Visual Studio 2015 Preview and can be downloaded here. This provides a single simplified installation experience for all of your SQL Server data tools that are now available in Visual Studio 2015.
Updates to the formula bar help write formulas with more ease by differentiating functions, fields and measures using syntax coloring. They provide intelligent function and field suggestions and tell if parts of DAX expression are wrong using error “squiggles.” The updates further enable the use of multiple lines (Alt + Enter) and indentation (Tab) while the formula bar also lets BI professionals write comments as part of the measures.
The new JSON-based Tabular Model Scripting Language (TMSL) allows for simplified scripting and development for Tabular models. It uses Tabular concepts instead of Multidimensional concepts, as was the case in SQL Server 2014. The changes to the metadata only impact a single object, resulting in faster metadata operations and enabling simple code merges in SSDT.
The new Tabular Object Model is part of Analysis Management Objects (AMO), the complete library of programmatically accessed objects that enables an application to manage a running instance of Microsoft SQL Server Analysis Services. With the Tabular Object Model, BI professionals can now use concepts familiar to the Tabular developer instead of using Multidimensional concepts. This allows for simpler and more readable code when developing against a Tabular model.

Getting started

This is an exciting time for SQL Server Analysis Services. SQL Server 2016 will provide many improvements for a wide range of use cases, from DirectQuery to modeling enhancements and developer productivity. Many of the performance enhancements can be enjoyed by just upgrading the server to SQL Server 2016, with no other changes required.

For more information and details on the changes made for SQL Server Analysis Services in SQL Server 2016, please visit the Analysis Services and PowerPivot Team Blog and part one of this blog post. Looking for a deep-dive into the updates to the Tabular semantic model and Analysis Services DirectQuery? Be sure to reference our video sessions from Data Driven 2016 to gain an overview and learn how to implement these updates.

See the other posts in the SQL Server 2016 blogging series.