1

Advanced Analytics

in SQL 2016

Adastra

Pavel Stejskal, Consultant

Pavel.Stejskal@adastragrp.com

linkedin.com/in/pavelstejskal

20.4.2016

AdvancedAnalytics

2

Excel + Power BI add-insQuery, Pivot, View, Map

SharePointPower Pivot Gallery, Power View

ExcelData Mining

Power BI Desktop Power BI Portal

Azure ML

Power BI Mobile App

Analytics Platform System(APS)

Enterprise-class big data analytics platform for R

3

SQL Server R Services

In-database Advanced Analytics

4

Build intelligent applications with SQL Server R Services

R built-in to SQL Server

Mission critical OLTP

What is R ?

5

• A programming language for statistics, analytics, and data

science

• A data visualization framework

• Provided as Open Source

• Used by 2.5M+ data scientists, statisticians and analysts

• Taught in most university statistics programs

• New and recent graduates prefer it

• Active and thriving user groups across the world

• CRAN: 7000+ freely available algorithms, test data and

evaluation

• Many of these are applicable to big data if scaled

R basics

6

Easy syntax

Strong graphic capabilities

+

Large collection packages

in CRAN repository

R – main challenges for Enterprise environment

7

• Single threaded

• Memory limitations

• Data transfers between R and DB storage

• How to deploy model into production

2015 Acquisition of Revolution Analytics

Microsoft R Server (Revolution R Enterprise before)

8

Enterprise-class big data analytics platform for R

Microsoft R Server

Microsoft R Server is an enterprise-class big data analytics platform for R .

Supporting a variety of big data statistics, predictive modeling and machine learning capabilities, R Server supports the full range of analytics – exploration, analysis, visualization and modeling based on Open Source R.

By leveraging and extending open source R, R Server is fully compatible with R scripts, functions and CRAN packages, while extending R to analyze data at enterprise scale.

Microsoft R Server provides a unique opportunity to deliver our advanced analytics capabilities to customers who have already invested in storing their data on non Microsoft platforms like Hadoop, Teradata and Linux

SQL Server Enterprise Edition R Services – how it works

9

SQL Server 2016 Enterprise Edition

Integration Facilities:

• Component Integration• Launchers• Parameter Passing• Results Return• Console Output

Return• Parallel Data Exchange

(RTM)• Stored Procedures• Package Administration

SQL Server

Query

Processor Fast, Parallel, Storage Efficient Algorithms

Open Source R Interpreter

Advanced Analytics scenarios suitable for R

10

EXAMPLE SOLUTIONS

DEMO

Run Parallel Algorithms In-Database

11

SQL

In-Database Execution:

Remote Execution

Parallelized ComputeSQL

Server

Remote

Execution

Context

Explore and Model:

In Parallel In-Database

Parallelize Distributable R and CRAN

Operationlize:

Score In Parallel

Move

BIG

Work to

the Data

Large Data Sets in Chunks

Parallel

Algorithm

Iterate/ Sequence

How Does Remote Execution Work?

12

Algorithm

Master

Big

Data

Predictive

Algorithm

Analyze

Blocks In

Parallel

Load Block

At A Time

Distribute Work,

Compile Results

The Results:

• Even Faster Computation

• Larger Data Set Capacity

• Fewer Security Concerns

• No Data Movement, No Copies

“Pack and Ship” Requests

to Remote Environments

13

Demo

ADASTRA CZECH REPUBLICAdastra, s.r.o.

Karolinská 654/2, 186 00 Praha 8

Tel.: +420 271 733 303

infocz@adastragrp.com

www.adastra.cz

ADASTRA GROUP North America8500 Leslie St.

Markham, Ontario, L3T 7M8

Tel: +1 905 881 7946

info@adastragrp.com

Restrictions for public release and use:This document can comprise confidential information. As such it may not, without Adastra’s prior consent, be copied or transferred.

Important:All brands and names of products given in this documentation are or can be registered trademarks of their owners.© 2016 Adastra, all rights reserved.

14

Thank you!