SQL Server Business Intelligence Development

Over the past few years we did not hear anything from Microsoft about SQL Server on-premises business intelligence enhancements. The extended cricket-chirping silence left us speculating about the future of Reporting Services, Analysis Services and other key technologies that are being used today to power critical decision making solutions. Good news, the waiting and wondering is over. Microsoft recently announced significant SQL Server on-premises upgrades in numerous presentations at both //build and Ignite conferences. Here are a few of my top take-away points from a BI professional perspective.

Key Planned Investments

Starting with the development experience, SQL Server Development Tools (SSDT) and SQL Server BI Tools (BIDS) will finally be truly unified in Visual Studio. The setup experience has been improved along with the process for importing from the designer and from Office 2016. There is also support for the Analysis Services Tabular scripting language and extended event-based monitoring in SQL Server Management Studio (SSMS).

Looking at the data mart or data warehouse side of things, SQL Server 2016 will include updateable nonclustered columnstore index support with columnar index in-memory or on-disk row store for deploying an operational analytics. Another incredible feature that was previously only available in Microsoft Analytics Platform System is Polybase. Polybase will now be out-of-the-box in SQL Server 2016. PolyBase is a distributed query capability that allows users to query non-relational data in Hadoop, blobs, files, and existing relational data in SQL Server without necessarily moving data. There are options for users to import Hadoop data for persistent storage in SQL Server or export aged relational data into Hadoop. PolyBase also provides the ability to access and query data that is either on-premises or on the cloud for hybrid solutions on your data, wherever it may be located. This topic alone deserves a dedicated future article!

Integration Services (SSIS) fans will be happy to know that designer now supports previous versions. Microsoft finally woke up and realized in the real-world, there are many versions being managed and installed. Rarely can a large enterprise upgrade all ETL to the latest and greatest version. Other SSIS enhancements include using Power Query as a data source for self-service ETL to enterprise ETL upgrade scenarios. High availability is also compatible with SQL Server Server AlwaysOn. There is a new Azure Data Factory data flow task and new connectors for Azure Storage, Azure commandlets, OData Version 4, Hadoop File System (HDFS), JSON, and Oracle/Teradata connector V4 by Attunity. In 2016, SSIS also gets more usability improvements, incremental deployment options, custom logging levels, and package templates for ETL code reuse.

Most large data warehouse deployments also use master data management for customer, product and other master entities that have attributes that feed and get updated from numerous operational applications. If you use SQL Server Master Data Services (MDS), you will be pleased to know that it also has been getting quite a bit of love in the 2016 builds. You should notice improved performance when working with large models, added optional row-level compression per entity, a better administrative interface, and new configurable retention settings. There are also new features for granular read, write, delete, and create security permissions. For your data stewards, the Master Data Services Add-in for Excel is now 15X faster and in 2016 will support bulk entity-based staging operations.

We are starting to see the Revolution Analytics R acquisition being baked into Microsoft’s solutions. A warmly welcome and long overdue enhancement to SQL Server 2016 is in-database R predictive analytic functions. These advanced analytical function call capabilities can be made via T-SQL queries. In the past you may have used DMX queries with SQL Server Analysis Services but those functions were fairly limited to a few algorithms, added another layer of complexity and overhead adding an Analysis Service instance and also have not been invested in for a few years. The new R in-database analytics is far better! It opens up a whole new world of unlimited analytical algorithms and you can even import models made in Azure ML.

Popular classic Reporting Services (SSRS) is also, finally seeing real investment. In 2016, we will see a number of enhancements to that enable you to create mobile-friendly reports with support for modern browsers on multiple platforms. There will be native connectors for the latest versions of Microsoft data sources such as SQL Server and Analysis Services; third-party data sources such as Oracle Database, Oracle Essbase, SAP BW, and Teradata; and ODBC and OLEDB connectors for many more data sources. New report themes and styles will make it easier to create modern-looking reports, while new chart types enable you to visualize your data in new ways. The 2016 release will also deliver greater control over parameter prompts and dynamic, parameterized report design options.

SQL Server 2016 makes several enhancements to Analysis Services including improvements in enterprise readiness, modeling platform, BI tools, SharePoint integration, and hybrid BI. SQL Server 2016 Analysis Services also provides functional parity with SharePoint vNext and Excel vNext. In the area of enterprise readiness, enhancements include improved performance with unnatural hierarchies, relational OLAP distinct count, drill-through queries, processing and query process separation and semi-additive measures. For Analysis Services Multidimensional Mode, Database Console Commands will support detecting issues with multidimensional OLAP indexes. Additionally, Netezza is will be available as a data source.

For early adopters of Analysis Services Tabular Mode (SSAS), you will be relieved to know that it is getting an upgrade to become more enterprise ready in 2016. There are query engine optimizations that enhance performance for Direct Query, parallel partition processing, advanced modeling with bi-directional (many-to-many) cross filtering (already seen in Power BI Designer today), new DAX functions (DATEDIFF, GEOMEAN, PERCENTILE, PRODUCT, XIRR, XNPV) and other performance improvements.

Want Even More Details

Sound too good to be true? I know. These features did not make the current public CTP. If you don’t believe me, check out the SQL Server 2016 Evolution sessions on Channel 9 and also search for sessions on your favorite BI topic. There is a lot coming at us so quickly these days, it seems impossible to stay up to date. If you’d like to be notified of future CTP preview availability, you can sign up at the official SQL Server 2016 web page.