BI on your terms with SQL Server 2016

Posted by

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
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

  • Enhanced

  • Mobile

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

Analysis Services Tabular

  • Enhanced
    modeling capabilities in the semantic layer

    • Many-to-many

    • 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

    • 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

    • For
      end users

      • Query engine optimized

    • For

      • 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

    • Unnatural

    • Distinct

    • 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

  • 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

  • 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

  • OData
    4.0 support

  • Hadoop
    File System (HDFS) support

  • JSON

  • 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

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 myblogpost 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:



  1. 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


  2. 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.)


  3. 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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s