Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 21 of 212

11 C H A P T E R 1 | Faster queries Analysis Services enhancements Analysis Services provides two different modes to support fast reporting and analytics: multidimensional and tabular. Multidimensional mode has long been a component in SQL Server, whereas tabular mode was introduced in SQL Server 2012. Before the introduction of columnar storage in the SQL Server database engine, the use of either of these Analysis Services modes was the best way to deliver superior query performance. Now you 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 you 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. In SQL Server 2016, both multidimensional mode and tabular mode benefit from enhancements that improve performance. Understanding multidimensional performance improvements The Analysis Services multidimensional engine in SQL Server 2016 includes an optimization for faster query performance. There is no user interface that makes this change noticeable. To quantify the benefits for your own workload, you should perform baseline testing of query performance in your current version of Analysis Services and compare the baseline results to testing in SQL Server 2016 Analysis Services. First, the performance of queries that include an unnatural hierarchy is faster than it was in previous versions, although natural hierarchies remain faster yet. Nothing has changed with regard to the storage structure of an unnatural hierarchy. Instead, the engine is now able to execute the query in a more optimal fashion when it encounters a drill-down query such as the one shown in Example 1-9, which Excel generates when you explore a hierarchy. This optimization requires no configuration or redevelopment of models to improve query performance. You need only deploy your multidimensional model to a SQL Server 2016 Analysis Services multidimensional instance. Example 1-9: Drilling down one level of a hierarchy SELECT NON EMPTY Hierarchize(DrilldownMember({{DrilldownLevel({[Product].[Color-Size].[All]},,, INCLUDE_CALC_MEMBERS)}}, {[Product].[Color-Size].[Color].&[White]},,,INCLUDE_CALC_MEMBERS)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Adventure Works] WHERE ([Measures].[Reseller Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS 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 specific month, such as January 2016, always has a single relationship to a quarter (Q1 2016) in the dimension and a single relationship to a year (2016). 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, you must define attribute relationships for the natural hierarchy to materialize the data.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016