Mauritius Finance

Oct 26 2017

SQL Server 2016 real-time operational analytics #sql #server #2016,real-time #operational #analytics,updatable #nonclustered #columnstore #index,ncci


#

SQL Server 2016 introduces a very cool new feature called real-time operational analytics, which is the ability to run both analytics (OLAP) and OLTP workloads on the same database tables at the same time. This allows you to eliminate the need for ETL and a data warehouse in some cases (using one system for OLAP and OLTP instead of creating two separate systems). This will help to reduce complexity, cost, and data latency.

Real-time operational analytics targets the scenario of a single data source such as an enterprise resource planning (ERP) application on which you can run both the operational and the analytics workload. This does not replace the need for a separate data warehouse when you need to integrate data from multiple sources before running the analytics workload or when you require extreme analytics performance using pre-aggregated data such as cubes.

Real-time operational analytics uses an updatable nonclustered columnstore index (NCCI). The columnstore index maintains a copy of the data, so the OLTP and OLAP workloads run against separate copies of the data. This minimizes the performance impact of both workloads running at the same time. SQL Server automatically maintains index changes so that OLTP changes are always up-to-date for analytics. This makes it possible and practical to run analytics in real-time on up-to-date data. This works for both disk-based and memory-optimized tables.

To accomplish this, all you need to do is to create an NCCI on one or more tables that are needed for analytics. SQL Server query optimizer automatically chooses NCCI for analytics queries while your OLTP workload continues to run using the same btree indexes as before.

The analytics query performance with real-time operational analytics will not be as fast as you can get with a dedicated data warehouse but the key benefit is the ability to do analytics in real-time. Some businesses may choose to do real-time operational analytics while still maintaining a dedicated data warehouse for extreme analytics as well as incorporating data from other sources.

Share:

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.

2 Responses to SQL Server 2016 real-time operational analytics

Andrew Peterson says:

Thanks James. In theory, it s great, and I hope it is a great success. The one question/concern I d want to know is how OLTP concurrency is affected if we have a very heavy analytic load. Will OLAP queries launch locks which effectively block OLTP. And if not, will our OLAP query results be changing under our feet as the OLTP insert/updates the core data tables in mid query?

About James Serra

I am a big data and data warehousing solution architect at Microsoft. Previously I was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. I am a prior SQL Server MVP with over 25 years of IT experience.

Follow Me!

Subscribe by email

Recent Posts

Recent Comments


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *