Modern Data Warehouse:Microsoft APS

Alain Dormehl June 2015

Why the APS ?

2

THE EXPECTATION :• Continuous innovation• Larger more complex

deliveries• The Google Phenomenon • Unrealistic Clients • Continuous Product

Development• No slacking on QA• SQL is easy !? RIGHT ?• Quick No Compromise Work• Inefficient Development

Cycles

THE REALITY :• We didn’t know better• Thought this is how everyone

struggles • Thought we were making the

best lemonade with the lemons we had

• Our databases are bigger than anyone else's !?

• Cost of new staff and infrastructure eating revenue

• High Staff turn over in delivery teams

THE PROBLEM :• Massive Data Sets• Long Process/Analysis Times • Server/Resource contention• Cost of continuous expansion • Staff supplementing• Work Life Balance• Diminished quality assurance

checks • Limited New Product

Development• Crazy Deadlines

THE BOTTOM LINE:Business/Clients often do not understand the impact of bad decision making down stream especially in the Analytics and BI Sphere.

INFRASTRUCTURE

DATA MANAGEMENT AND PROCESSING

DATA ENRICHMENT AND FEDERATED QUERY

BI AND ANALYTICS

Self-service CollaborationCorporate PredictiveMobile

Extract, transform, loadSingle query model Data quality Master data management

Non-relationalRelational Analytical Streaming Internal and external

Data sources

OLTP ERP CRM LOB

Non-relational data

Devices Web Sensors Social

The Modern Data Warehouse

Provides a single T-SQL query model for PDW and Hadoop with rich features of T-SQL, including joins without ETL

Uses the power of MPP to enhance query execution performance

Supports Windows Azure HDInsight to enable new hybrid cloud scenarios

Provides the ability to query non-Microsoft Hadoop distributions, such as Hortonworks and Cloudera

SQL ServerParallel DataWarehouseMicrosoft Azure

HDInsight

PolyBase

Microsoft HDInsight

Hortonworks for Windows and Linux

Cloudera

Bringing Hadoop point solutions and the data warehouse together for users and IT

Result set

Select…

The Unsung Hero of APS

How would your clients react ?

Data Query Performance Complex Analytics

Data QueryPerformanceComplex Analytics 2

71x faster 60x faster 55x faster

Concurrent Execution Performance

25min vs 21.8 hours 27min vs 27.3 hours 37min vs 33.6 hours