THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Data & Analytics consultant and Microsoft Data Platform MVP from the Netherlands

BI on your terms with SQL Server 2016

The last few years Microsoft's strategy was all about cloud first (or cloud only?), releasing new BI products and updates to existing products to the cloud in high pace without almost any investments in on-premises BI. In 2015 Microsoft seems to change its course, they now aim more on the enabling of hybrid scenarios, investing a lot in both cloud (Power BI/Azure) and on-premises with SQL Server 2016.
Microsoft’s message regarding BI for 2015/2016 is:  “BI on your terms”.

BI on your terms means leveraging up-to-date possibilities for one or a combination (hybrid) of the following architectures:

  • Cloud with Azure and Power BI

  • On-Premises with SQL Server 2016

  • Server driven or Self-Service

To be able to offer quality hybrid architectures Microsoft invests a lot in the on-premises BI suite with SQL Server 2016 and they have announced to keep investing in it the coming years. So not only cloud first like we have seen in previous years, but more on hybrid possibilities, and if you desire on-premises only.

For the first time in many years an exciting version of SQL Server is coming in terms of BI. The main topics are:

  • Hybrid BI (Cloud/On-Premises)

  • Modern Reports

  • Enhanced Analysis

  • Mobile BI

Below is an overview of the new BI related features per SQL Server 2016 service or product. As the length of this list shows, SQL Server 2016 will be a massive BI version!!

Analysis Services Tabular

  • Enhanced modeling capabilities in the semantic layer

    • Many-to-many relationships

    • BI Directional cross filtering. This means you can not only filter on the 1 side of a 1 to many relationship in your tabular model, but also on the many side. For example, two connected tables, Sales à Product:

      • Product: product, product category

      • Sales: sales date, connection to product table

        Now select products sold filtering on sales date(many side) while also filtering on product category (1 side). This is not possible in today’s version of SSAS tabular.

  • Time intelligence

    • Date/time columns are automatically converted to rich date/time tables starting from the column’s MIN date till the MAX date found

  • New DAX functions

    • A lot of new functions that at the moment require quite complex formulas like present time, date difference, percentile, product, geomean, median, etc.

  • Performance improvements

    • For end users

      • Query engine optimized

    • For developers

      • Metadata operations; modeling related operations are much faster

    • For data processing

      • Parallel partition processing

  • Expose on-premises tabular models in the cloud (hybrid) à Power BI feature, possible already today with SQL Server 2012.

Analysis Services Dimensional

  • Netezza as a Data Source (Netezza Data Warehouse | IBM - NDM Technologies)

  • Performance improvements

    • Unnatural hierarchies

    • Distinct counts

    • Other performance improvements in areas where multidimensional is not performant at the moment

  • DBCC (DataBase Check Consistency) support. Checks the logical and physical integrity of objects in the specified database.

  • Expose on-premises multidimensional cubes in the cloud with Power BI (hybrid)

SQL Server Database Engine

  • Integration of R analytical engine, predictive analytic capabilities via T-SQL queries

  • PolyBase available without the need of PDW, makes it possible to query both structured relational SQL, and unstructured Hadoop data through T-SQL statements

  • Data encryption for stored data and data in motion

  • Row-level security

  • Updates to the in-memory OLTP engine, for example updateable in-memory nonclustered columnstore indexes

  • Parsing and storing native JSON data

  • XEvents-based monitoring in Management Studio

Reporting Services

  • New look and feel and possibility to apply themes and branding using CSS

  • New visualizations, chart types like tree maps and sun bursts

  • Improved flexible parameter panel with support for:

    • Autocomplete

    • Search

    • Hierarchical tree display

  • Runs in all modern browsers on both desktops as tablets (any device)

  • Integration of R analytical engine

  • Power Query as a data source

  • Pin on-premises SSRS reports to Power BI Dashboards (hybrid)

Integration Services

  • High Availability support

  • Power Query integration

  • Azure Data Factory integration (hybrid)

    • Execute on-premises SSIS packages from Azure Data Factory

    • Azure Data Factory data flow task

    • Azure storage connector

    • Azure commandlets

  • OData 4.0 support

  • Hadoop File System (HDFS) support

  • JSON support

  • New Oracle/Teradata connector (4.0)

  • Incremental deployment options

  • Custom logging levels

  • SSIS package templates to reuse ETL code

Mobile BI

  • In the cloud with Power BI

    • Power BI App for Windows Phone (coming soon) and iOS

  • On-premises with Datazen Server

    • Now available for free for SQL Enterprise Edition customers (2008 or later)

    • All major platforms: Windows Phone, Android, iOS

    • Beautiful interface and data visualizations

    • Optimizable for Phone, Tablet and Laptop

SharePoint vNext integration

  • Edit Mode of PowerPivot Excel workbooks in browser

  • Support for Excel vNext (Office 2016) DAX functions

Master Data Services

  • Improved performance for large models

  • Row-level compression per entity

  • Improved user interface

  • Configurable retention settings

  • Enhanced security possibilities for read, write, delete and create operations and support for multiple system administrators with specific permissions

  • Excel Add-in is 15 times faster and is updated to support bulk entity based staging operation

Visual Studio

  • Database and BI project types merged into one Visual Studio

  • New scripting language for tabular models. Currently tabular models are wrapped into multidimensional constructs and when you deploy it will be reverse engineered to the tabular model. The new native language for tabular will be easy to understand, modify and deploy.

  • SSIS designer supports previous versions of SQL Server

Of course there is still also a lot of exiting news coming from the cloud side of Microsoft BI, for example the Azure Data Lake is announced, following the principles of my blogpost about the relational data lake. You can expect a post about the Azure Data Lake on this blog soon!

P.S. Don’t forget to suggest and vote for feature requests for SQL Server yourself at:


Published Friday, May 22, 2015 4:07 PM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Marimuthu said:

MSDB database in Always On Availability Group: This makes your SQL Agent Jobs that execute your SSIS packages High Available (already possible in SQL 2014)

 -- Could you please throw some more light on MSDB on AlwaysOn AG. i.e.,how to configure msdb on AlwaysON AG .. Please give the step by step details ...

Thanks in advance

May 24, 2015 5:34 AM

Nacho Alonso Portillo said:

Hi Marimuthu,

Actually, you cannot replicate any of the system databases, including MSDB. Any attempt to do so fails with error 35249 "An attempt to add or join a system database, '%.*ls', to an availability group failed. Specify only user databases for this operation." And the rule evaluated triggers the error and prevents the action on any database whose database_id is 1 (master), 2 (tempdb), 3 (model) or 4 (msdb.)



May 25, 2015 7:40 AM

jorg said:

Hi Nacho,

I recently attended a session from a SQL Server MVP about SQL 2014 and he told me adding the MSDB to an AlwaysOn Group should be possible from SQL 2014. I had some contact with him about this matter and he told me this was probably in a CTP version of SQL 2014 because it is indeed not possible anymore in SQL 2014 today.

So you are right Nacho, thanks for letting us know, I have edited the blog post.



June 3, 2015 7:42 AM

Leave a Comment


About jorg

Jorg Klein, Microsoft Data & Analytics consultant from the Netherlands.
Privacy Statement