Select Page

Top SQL Server 2016 Features: Analytics

Rajnikant Tandel | | June 4, 2019

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.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Shortcut to Fixing a Corrupted Oracle Inventory

Oracle Central Inventory files are essential to installing and patching software. The process for fixing a corrupted file can be found here.

Chad Cleveland | February 23, 2016

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.