After a few years of silence, wondering if cloud first meant cloud only, SQL Server BI on-premises customers finally got a peek at the investments being made in SQL Server 2016. In a series of sessions that can be watched on Channel 9 from //build and Ignite, Microsoft executives and product managers emphasized a significant commitment to the on-premises SQL Server offerings including many enhancements to the relational database, new in-database R analytics, Master Data Services, Integration Services, Analysis Services and Reporting Services. That refreshing and warmly welcomed message eased minds of SQL Server BI professionals around the world.
Back on April 14, 2015, Microsoft announced a surprise gift to the same on-premises SQL Server BI audience. Customers with SQL Server Enterprise Edition 2008 or later and Software Assurance are now freely entitled to Datazen Server for mobile BI at no additional cost. The complimentary native iOS, Android, and Windows mobile BI client apps for both tablets and smart phones are already available in device app stores. Datazen also supports simple guest access for public web delivery of gorgeous dashboards opening up a whole world of exciting possibilities for Microsoft BI pros.
Times have Changed
In case you missed the memo last summer, times have changed in the Microsoft BI world! We are literally seeing life breathed back into both cloud and on-premises Microsoft BI in 2015. I haven’t seen this much buzz and interest since the SQL Server 2012 release. If you’d like to watch the Microsoft BI Overview session or download the latest presentation, it is posted to Channel 9. The following is a brief summary of SQL Server 2016 on-premises specific BI announcements. I will cover May Power BI news in another article since there is so much being released on a weekly basis these days.
SQL Server 2016 BI Related Announcements
- Updateable nonclustered columnstore index support for deploying an operational analytical model with columnar index in-memory or on-disk row store.
- Parsing and storing of native JSON as relational data and exporting relational data to JSON.
- Revolution Analytics R is being cooked into the relational database to empower predictive analytic capabilities via simple T-SQL queries for smart reporting. R models can also optionally be deployed as a web service to the Azure Marketplace.
- PolyBase is now available without requiring Analytics Platform System (formerly PDW appliance) for distributed querying of both relational SQL Server data and Hadoop data through a single T-SQL statement.
- Improved security with encryption for both data at rest and data in motion. Row level security for fine-grained access control to table rows based on users rights and dynamic data masking for real-time data obfuscation.
- Neglected classic Reporting Services (SSRS) is finally getting love. In this release, we will see more chart types and support for mobile on all major modern browsers, including the latest versions of Internet Explorer, Chrome, Firefox, and Safari. Reporting Services report styles have also been given a facelift with improved report parameters that also support autocomplete, search and hierarchical display as a tree, enhanced new report templates and themes that are similar to those in Power BI. You can also customize report themes and branding using popular CSS. Last but not least, Reporting Services can finally be published to PowerBI.com meaning on-premises data can be easily viewed on-prem or in the cloud.
- Analysis Services Multidimensional Mode (SSAS) is also getting a bit of investment. That alone is fantastic news, right? In 2016, we will see Netezza and Power Query being supported as data sources, performance improvements and DBCC support.
- Analysis Services Tabular Mode (SSAS) is getting a massive upgrade to be more enterprise ready. Improvements include adding 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, PRECENTILE, PRODUCT, XIRR, XNPV, etc.) and improved in-memory performance (query and metadata operations).
- SQL Server Development Tools (SSDT) and SQL Server BI Tools (BIDS) will be unified in Visual Studio. The setup experience has been streamlined along with the process for importing from the designer and from Office vNext. There is also support now for the Analysis Services Tabular scripting language and extended event-based monitoring in SQL Server Management Studio (SSMS).
- Master Data Services (MDS) has improved the web application for increased performance when working with large models, added optional row-level compression per entity, improved the manageability user interface, and introduced new configurable retention settings. In 2016 there is deeper granular security permissions around read, write, delete, and create actions. There is also support for multiple system administrators and an explicit model administrator permission property. The Master Data Services Add-in for Excel is now 15X faster than the current version and was updated to support bulk entity-based staging operations.
- Integration Services (SSIS) designer supports previous versions, Power Query and high availabilty with AlwaysOn. Azure Data Factory (ADF) can now orchestrate on-premises Integration Services package execution. There is a new Azure Data Factory data flow task, Azure Storage Connector, Azure commandlets, and connectors for OData Version 4, Hadoop File System (HDFS), JSON, and Oracle/Teradata connector V4 by Attunity. To wrap up, 2016 brings more usability upgrades, incremental deployment options, custom logging levels, and package templates for ETL code reuse.
For More SQL Server 2016 Information
Believe it or not, I barely scratched the surface of what is coming in SQL Server 2016. Microsoft is claiming this is one of the largest releases ever for SQL Server. SQL family (#SQLFamily) around the world have already signed up for this summer’s early reveal. If you’d like to be notified of preview availability, you can sign up at the official SQL Server 2016 web page.