SQL Server 2016: a major upgrade for BI

At the time of writing SQL Server 2016 is in preview (CTP 3.1).

We will discuss some of the most interesting changes to this new release from a BI perspective. For a full list of changes, I refer to the official SQL Server 2016 website: http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

Integration Services

SQL Server 2016 introduces quite some interesting improvements to Integration Services:

  • The SSIS catalog can now, just as a normal user database, be added to an AlwaysOn Availability group for high availability.
  • Incremental deployment allows you to deploy only new and changed packages instead of the full project.
  • New connectors: OData 4.0, Hadoop, JSON Scripts.
  • New components, for example the balanced data distributor to take advantage of concurrent processing capability of modern CPUs.
  • The buffer size will be calculated automatically increasing the data throughput of your data flows.
  • Its easier to retrieve the name of a column that caused an error in the data flow.
  • Reusable control flow templates.
  • Custom logging levels in the SSIS Catalog for more control over the logging process.

Reporting Services

It has been a while since the last remarkable changes in Reporting Services, but the changes announced for SSRS 2016 are definitely impressive:

  • Export to PowerPoint.
  • New charts like tree maps and sunburst charts.
  • Possibility to customize the parameter pane.
  • Support for modern browsers using an HTML-5 rendering engine.
  • An updated look n feel for the Report Builder, along with support for High DPI rendering.
  • Pin visual from SSRS reports to Power BI dashboards.

 

Figure 1 - New chart types

 

SQL Server 2016: a major upgrade for BI
click to enlargeSQL Server 2016: a major upgrade for BI

 

 

Figure 2 - Customizable parameter pane

 

SQL Server 2016: a major upgrade for BI
click to enlargeSQL Server 2016: a major upgrade for BI

 

Its also announced by Microsoft that SSRS and Datazen will integrate into 1 product allowing you to build modern-looking, interactive and mobile reports with SSRS. The Microsoft reporting solution will focus on 4 report types:

  • Paginated reports as we currently know the existing SSRS reports.
  • Interactive reports built with Power BI.
  • Analytical reports and charts with Excel.
  • Mobile reports based on Datazen technology.

 

Figure 3 - Datazen

 

Where Reporting Services will be the on-premises version, Power BI will be the cloud version of Microsofts reporting solution delivering all 4 report types through 1 interface. To harmonize the mobile BI experience there will be 1 mobile app for all report types.

 

Figure 4 - Reporting platform

 

Figure 5- Mobile

 

Analysis Services

SSAS 2016 will add support for Netezza as a new data source.

 

Next to that some performance improvements will be made for multidimensional cubes (Improved performance for unnatural hierarchies, Faster ROLAP distinct counts) and for tabular models (parallel partition processing, an optimized query engine ).

 

There will also be quite some new DAX functions, such as DATEDIFF, PERCENTILE, MEDIAN, etc.

 

However, the most interesting change to tabular models is the newly added support for "bi-directional cross-filtering, eliminating the need for hand-crafted DAX workarounds for propagating filter context across table relationships like for example many-to-many relationships.

 

Master Data Services

A lot of changes were done in MDS to optimize the performance, especially for larger models:

  • Working from the Excel Add-in will be a lot faster (up to 15 times faster), allowing you to create larger models while decreasing data load times.
  • Reducing disk I/O by applying SQL Row Level Compression.
  • Reducing network I/O by enabling the Dynamic Content Compression IIS feature.

Security has been improved:

  • A new "super user that has the same permissions as the server admin.
  • More granular permissions by introducing the following new permissions: Read, Create, Update and Delete.

Attribute names can now be longer than 50 characters. And it will also be possible to rename or hide these annoying default "code and "name attributes.

It will also be possible to sync entities between different models and keep track of the change history of the members of an entity. This allows you to create type 2 views on top of your entities.

R

Very interesting for data scientists is the integration of the SQL Server Database engine with the R analytical engine supporting Rs predictive analytic capabilities via T-SQL queries. And it will even be possible to show your R-graphics in SSRS-reports.

Polybase

Polybase doesnt require a Parallel Data Warehouse anymore. With Polybase - optimized for data warehousing workloads and intended for analytical query scenarios - you can write T-SQL queries to combine relational data with unstructured data from Hadoop or Azure Blob Storage.

 

Figure 6- R

 

SQL Server 2016: a major upgrade for BI
click to enlargeSQL Server 2016: a major upgrade for BI

 

Figure 7- Polybase

 

SQL Server 2016: a major upgrade for BI
click to enlargeSQL Server 2016: a major upgrade for BI

 

Database

A great new feature introduced in SQL Server 2016 to track history is temporal database. It allows you to define temporal tables. Temporal tables keep track of history by holding a version of the record for any point in time. You can compare this to whats in BI called a slowly changing dimension (SCD) type 2.

And many more

Many more interesting changes are announced. If youre into database performance tuning its definitely worth to have a look at Live Query Statistics, showing query plans live during the execution. Or the Query Store, automatically capturing a history of queries, plans, and runtime statistics.

Do you want to easily protect your data? Then you should definitely have a look at Always Encrypted, making encryption completely transparent to your application.

Also In-Memory OLTP has been further improved: now nonclustered columnstore indexes are also updateable.

If you need extra disk space for your databases, Stretch Database could be a good alternative instead of buying extra disks, combining on-premises storage with storage in Azure, completely transparent from your application.

Conclusion

SQL Server 2016 is definitely a very interesting release. Reasons enough to consider an upgrade:

SQL Server 2016

  • lets you combine the advantages of Azure with your on-premises solutions.
  • allows you to take your data with you on your mobile device
  • offers rich new visualizations and interactive reports
  • gives you more insight into your data, structured and unstructured.
  • makes you queries, cubes and reports faster
  • protects your data better than ever
  • makes the life of developers, administrators and not to forget the users (!) a lot easier.
  •