Microsoft SQL Server 2016

The SQL Server Data Platform has been a major player in the Business Intelligence and Data Warehousing market for years and is often selected due to its cost price, performance and extensive feature list.

Microsoft SQL Server 2016

In June 2016, the latest release of SQL Server was made available and it seems one of their biggest release yet with lots of new features and strong improvements. A couple of the new key features and improvements of the SQL Server data platform for business analytics:
  • Reporting Services finally got an upgrade and is ready for the modern requirements of BI. The report portal has been completely rewritten and has a fresh user interface, which you can customize using your style guide. Reports are now rendered in HTML5, which means they can be viewed on any modern browser on any device. Microsoft has also acquired Datazen, a company focused on delivering mobile reports. This technology is now incorporated in Reporting Services, which means you can create stunning reports and dashboards intended for mobile browsing. These reports can also be viewed in the native Power BI app.
Figure 1 - Example report within Reporting Services 2016
Microsoft SQL Server 2016
click to enlargeMicrosoft SQL Server 2016
  • Integration Services has been improved for manageability and a better development experience. For example, backwards compatibility in SQL Server Data Tools (SSDT) has been introduced, which means you now only need one single version of Visual Studio to develop SSIS packages for SQL Server 2012, 2014 and 2016. There is also improved connectivity to the Azure cloud and to several big data solutions. Furthermore, the Polybase technology allows you to query big data sources using SQL from inside the database engine, providing you with an effective abstraction layer between your own application data and data in a big data solution.

  • Support for big data and advanced analytics. Microsoft acquired the company Revolution Analytics, who specializes in scalable R solutions. R is an open-source statistical language with great capabilities for advanced analytics and data science. The technology has now been included directly in the SQL Server database engine. This means you can now run your R scripts directly in SQL Server, or you can connect with your own data science platform to run R code on SQL Server, benefiting from the massive computing power, scale and parallelism. The R language is also integrated into Power BI Desktop.

Figure 2 - SQL Server R Services embedded within SQL Server 2016
Microsoft SQL Server 2016
click to enlargeMicrosoft SQL Server 2016
  • Improved columnstore capabilities. In SQL Server 2014, you already had the possibility to create an updateable clustered columnstore index on a table. Now you can also create updateable columnstore indexes on a traditional rowstore table. Or you can put a regular rowstore index on top of a table with a clustered columnstore index. You now have the flexibility to index your biggest table so they can support any type of query. Aside from the performance improvements, you can also get a greatly reduced data footprint, sometimes even up to 4 times less storage for the same table.

  • Improved and enhanced Master Data Services. The Master Data Service interface has been updated, which makes it more pleasant and efficient to work with MDS. A lot of new features have been introduced, such as the ability to create a workflow to allow an admin to approve any change to an entity (member revision), entity sync, better security management, improved logging and performance and so on.

  • Analysis Services Tabular has been expended and is now much more mature. Dozens of new DAX functions have been added, such as the median function for example and the ability to have bi-directional cross-filtering in the model means you can now support many-to-many relationships. Performance for DirectQuery has been greatly improved. Analysis Services Multidimensional now has the capability to support Netezza as a data source.

element61 offers a vast variety of service based on Microsoft SQL Server 2016, from training & coaching to end-to-end implementations. For more information on this matter, feel free to contact us.