Analysis Services in SQL Server 2016

SQL Server 2016 is fully loaded with loaded with several new amazing features, including more speed, more security and more value. 

Analysis Services is an online analytical data engine and SQL Server 2016 makes several enhancements to it including improvements in enterprise decision support and business analytics, modeling platform, BI tools, SharePoint integration, and hybrid BI.

We know that Analysis Services provides two different ways to support fast reporting and analytics and these are multidimensional and tabular mode. Multidimensional mode has long been a component in SQL Server, whereas tabular mode was introduced in SQL Server 2012. These both modes were the best way to deliver superior query performance before introducing of columnar storage in the SQL Server database engine.

After Analysis Services enhancements in SQL Server 2016, we can choose whether to use in-memory OLTP or columnstore indexes in the database engine (one of the Analysis Services engines) or even a hybrid-mode in which we use ROLAP mode with a multidimensional model or DirectQuery mode for a tabular model to directly query the database engine instead of data stored by Analysis Services.

Microsoft development team stated that the performance of queries that include an unnatural hierarchy is faster than it was in previous versions, although natural hierarchies remain faster yet.

Natural versus unnatural hierarchies in a multidimensional database A natural hierarchy describes the one-to-many relationship moving between levels from the top down, such as Year, Quarter, and Month, in which one year can have four quarters and one quarter can have twelve months. A natural hierarchy is always a better structure to design into a dimension because of the materialization of the data in the Analysis Services database. However, we must define attribute relationships for the natural hierarchy to materialize the data.

The most interesting fact is that this optimization requires no configuration or redevelopment of models to improve query performance except only to deploy our multidimensional model to a SQL Server 2016 Analysis Services multidimensional instance.

Furthermore, a change in memory allocation request handling is available in SQL Server 2016. In previous versions, we had two options for memory allocations such as Windows Low-Fragmentation Heap (LFH) or a custom heap allocator for Analysis Services where LFH is set default to LFH to improve performance for multi-user workloads. Ordinarily, LFH works well with small memory blocks, but some situations can compromise the efficiency of LFH for Analysis Services by fragmenting memory, which in turn reduces query performance. In SQL Server 2016 Analysis Services, the default option is a new hybrid allocator that strikes a balance by using LFH for small allocations and the custom heap allocator for large allocations.

Remember: Tabular mode uses the same memory settings as multidimensional mode, but it has its own msmdsrv.ini file in which these settings are defined. The tabular mode tends to require larger allocations than multidimensional mode, so the new hybrid allocator should rely more heavily on the custom heap allocator.

Tabular-model performance has always been good because of its use of the same columnar technology as columnstore indexes and in-memory OLTP. However, it’s even better in SQL Server 2016 because of DAX optimizations, storage-engine caching improvements, and changes to the way that T-SQL is generated for DirectQuery mode.

In SQL Server 2016, Analysis services tabular models simplify solving complex business problems using over 50 new DAX functions and new relationship types can help solve many to many issues because DAX optimization is a reduction in the number of queries sent to the storage engine from Power BI and Power BI Desktop. A single DAX query can return multiple result sets as intermediate results that Power BI can use across multiple requests.

In SQL Server 2016, Microsoft development teams focused on tabular models in Analysis Services to enhancements and they have added around 1200 new compatibility level functions in tabular model databases. Analysis Services 2016 is coming with a number of enhancements to multidimensional models also.
This efficiency is a result of changes in the following areas that affect measure execution:

1. Variables -  As we describe in more detail in Chapter 6, “More analytics,” the use of variables in a DAX expression for measures allows you to reuse logic within the same expression, which can reduce overall execution time.
2. Conditional expressions - Rather than evaluating each branch of an IF or SWITCH conditional expression, a branch with a false condition no longer generates a storage-engine query.
3. Nonempty calculations - The number of scans necessary to retrieve a nonempty result set is reduced from many to one.
4. Multiple measures in same table - A single query now includes all measures from the same table.
5. Measure grouping - When a query requests a measure at different levels of granularity, such as Month, Quarter, Year, and Total, the query requests only the lowest-level measure value and then derives the higher-level values.
6. Joins - A storage-engine query now returns both dimension columns and measure values in the same query to eliminate redundant joins, and the order of joins now starts from the most restrictive intermediate table that correlates with the greatest number of other intermediate tables.
7. Multiple result sets - A storage-engine query now returns both dimension columns and measure values in the same query.
8. Countrows -This function is optimized to now use table heuristics.
9. Storage-engine caching - The storage engine uses one cache per database rather than one cache per server as it did in prior versions.

DirectQuery is the feature in tabular models that retrieves data from the data source instead of data that has been imported into memory. Now that MDX queries are supported by DirectQuery, we can use Excel to connect to a DirectQuery-enabled tabular model, the translation to T-SQL is also improved in SQL Server 2016 by just restoring our tabular database to a SQL Server 2016 Analysis Services tabular instance.

Conclusion

Both multidimensional mode and tabular mode benefit from enhancements that improve performance in SQL Server 2016. The Analysis Services multidimensional engine includes an optimization for faster query performance. Memory allocation request handling is available in SQL Server 2016 where the default option is a new hybrid allocator that strikes a balance by using LFH for small allocations and the custom heap allocator for large allocations. The existing multidimensional model requires no configuration or redevelopment of models to improve query performance except only to deploy them to a SQL Server 2016 Analysis Services multidimensional instance.

Referenced Book: Introducing Microsoft SQL Server 2016