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

  • SSISDB
    database in AlwaysOn Availability Group. This is already possible with some
    work arounds.

• 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 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:  http://aka.ms/SqlBiUserVoice