Top SQL Server 2016 Features: Analytics

The release of SQL Server 2016 offered a host of new features for organizations. Some of the new capabilities and enhancements included Stretch Databases, Always Encrypted, a Query Data Store, Dynamic Data Masking, and more. The release also included several new analytical capabilities, including support for real-time operational analytics and integration of the R language.

 
The adoption of big data analysis capabilities is soaring in the enterprise, according to Forbes. In 2015, just 17% of enterprises had advanced analytics solutions in place. Today, that percentage is 59% and growing quickly. SQL Server 2016 could be the right pathway to enhance your capabilities to perform sophisticated big data analyses through support to run Online Analytical Processing (OLAP) and Online Transaction Processing (OLTP).

What is Real-Time Operational Analytics in SQL Server 2016?

The Operational Analytics feature in SQL Server 2016 offers organizations the capability to run OLAP and OLTP processes simultaneously, on the same database tables. Previously, organizations who had a use case for simultaneous OLAP and OLTP needed to create an external data warehouse and use ETL (extract, transfer, and load) processes in two separate systems. By combining the capabilities, SQL Server 2016 allows organizations to reduce cost, latency, and the complexity of operational analytics processes.

To implement the Operational Analytics capabilities, Microsoft suggests the following process:

• Identify the tables and columns for running analytics, either disk-based or memory-optimized or both.
• Create a columnstore index on the identified tables.
• Set up the Analytics framework to source data directly from the operational store. The query optimizer will automatically choose the columnstore index.

 
After performing the above steps, your database is ready for real-time operational analytics without making any changes to your application. Analytics queries will run against the columnstore index and OLTP operations will keep running against your OLTP b-tree indexes.

Not every operational analytics workload and use case is the right fit for this SQL Server 2016 feature. According to Microsoft, a Data Warehouse is still the right choice if your organization is aggregating data from multiple sources for OLAP or OLTP.

SQL Server 2016: an end to ETL?

ETL and data warehousing challenges are a barrier to big data adoption and maturity for many enterprises. ETL is an extremely time-consuming process, which limits organizations ability to refresh information within a warehouse to once per every 24 hours. SQL Server 2016 offers new support for real-time operational analytics by offering a solution that doesn’t require ETL processes. Similarly, the integration of the R solutions as an add-on can enable organizations to leverage sophisticated data analysis while keeping analytics close to the dataset.

R Services

R Services is available as an add-on to a SQL Server 2016 database to execute R code and functions on SQL Server. In January 2015, Microsoft purchased Revolution Analytics, the company which developed the open source statistical analysis language R. Prior to the integration of R as an add-on feature, big data analysts were required to export data to perform analysis using R.

Microsoft SQL Server 2016 R functions and algorithms are suitable for numerous applications in the enterprise, including:

• Predictive Analytics
• Statistical Modeling
• Data Visualizations
• Machine Learning Algorithms

 
By integrating R capabilities with the SQL Server 2016 database engine, organizations gain the benefit of being able to keep statistical analysis processes close to the data, which eliminates the costs, latency, and security issues associated with moving data for analytics.