Warehouse of the Future Lakes Vs Marts Simon Whiteley|

Warehouse of the Future Lakes Vs Marts Simon Whiteley Adatis 06/04/2016

Why Cloud? Azure Data Warehouse Azure Data Lakes The New BI Architecture Q&A

Management Responsibility On Prem IaaS PaaS You Microsoft SaaS

Traditional Warehouse Architecture SSIS SSIS Stage SSIS Clean Warehous e

ETL

? Azur e On Prem AzCopy SSIS Feature Pack for Azure SSIS C# Task Azure Storage Explorer More!

Why Cloud? Azure Data Warehouse

Data Warehouse Massively Parallel Processing Clustered ColumnStores Distribution & Partitioning

PDW/APS Scaling Control ¼ Rack 8 Readers 8 Writers 8 Distributions

PDW/APS Scaling Control ¼ Rack 16 Readers 16 Writers 16 Distributions ¼ Rack

PDW/APS Scaling Control ¼ Rack 32 Readers ¼ Rack 32 Writers 32 Distributions ¼ Rack ¼ Rack

60 Distribution Nodes 600,000,000 Records

60 Distribution Nodes

Distribute by client – select * where clientId 321 Query Time Node 33 0 2 4 6 8 10 12 14 16

Query Time Node 58 Distribute by other – select * where clientId 321 Node 55 Node 52 Node 49 Node 46 Node 43 Node 40 Node 37 Node 34 Node 31 Node 28 Node 25 Node 22 Node 19 Node 16 Node 13 Node 10 Node 7 Node 4 Node 1 0 0.5 1 1.5 2 2.5 Query Time 3 3.5 4 4.5

100 DWUs Control Compute 8 Readers, 60 Writers 8 Readers

200 DWUs Control Compute 8 Readers Compute 8 Readers 16 Readers, 60 Writers

400 DWUs Control Compute 8 Readers Compute 8 Readers Compute 8 Readers Compute 8 Readers 32 Readers, 60 Writers

SSIS ADF BCP File, general Control Compute Compute Compute Compute

SSIS Control File, general Compute Compute Compute File, general File, general Compute

Control Compute File, general Compute Compute File, general Compute File, general PolyBas e File, general Source Files

Demo

Azure Data Warehouse External Table File, general Transform Table Warehouse Table ELT

Why Cloud? Azure Data Warehouse Azure Data Lakes

Data Lake Store Web HDFS Any File Type Any Size

Data Lake Analytics PAYG Scalable Processing New, Simple Querying Language

U-SQL C# meets SQL Natively Parallel VS Data Lake Tools

Demo

U-SQL File, general File, general File, general File, general File, general File, general File, general Data Lake Store File, general External Table TEL Warehouse Table Azure Data Warehouse

Why Cloud? Azure Data Warehouse Azure Data Lakes The New BI Architecture

Data Lake Store Scales as a system TEL ELT Scales for each job independently Data Warehouse

TELT

What’s Missing? Polybase over Data Lake Store Azure DW as U-SQL Destination Upload to Data Lake Store Component Azure DW Development Tools

Why Cloud? Azure Data Warehouse Azure Data Lakes The New BI Architecture Q&A

sqlsurrey.sqlpass.org @SQLSurrey

Thanks for Listening Simon Whiteley Principal BI Consultant @MrSiWhiteley http://blogs.adatis.co.uk