analyze and visualize non-relational data with documentdb + power bi

Analyze and visualize non-relational data with Azure DocumentDB + HDInsight + Power BIHan WongProgram ManagerAzure DocumentDB

Session Objectives And Takeaways

Internetof Things

ActionableData Insights Scalable Solutions

Internetof ThingsActionableData Insights Scalable Solutions

NoSQL - document databases

Microsoft Confidential

Modern Applications…Are developed iteratively with continuously evolving data models

Produce and consume data at unprecedented rates

Require instantaneous response times to match user expectations

Experience unpredictable and explosive growth

Microsoft Confidential

Developers want…

• Part of NoSQL family• Built for simplicity, scale and performance• Non-relational, no enforced schema• Fully managed

Document Database Services

Not these documents

{“id":“itemdata2344",“data":“TWFuIGlzIGRpc3Rpbmd1aXNoZWQsIG5vdCBvbmx5IGJ5IGhpbmd1aXNoZWQsIG5vdCBvbmcyByZWFzb24sIGJ1dCBieSB0aGlzHNpbmd1bGFyIHBhc3Npb24gZ0aGlzHNpbmd1bGFyIHnJvbSBvdGhlciBhbmltYWxzLCB3aGljaCBpcyBhIGx1c3Qgb2YgnJvbSBvdGhlciBhbmltdGhlIG1pbmQsIHRoYXQgYnkgYSBwZXJzZXZlcmFuY2Ugb2YgZGVsaW0dGhlIG1pbmQsIHRdodCBpbiB0aGUgY29udGludWVkIGFuZCBpbmRlZmF0aWdhYmxlIGdldodCBpbiB0aGUgY2bmVyYXRpb24gb2Yga25vd2xlZGdlLCBleGNlZWRzIHRoZSBzaG9ytmVyYXRpb24gb2Yga2cyByZWFzb24sIGJ1dCBieSB0aGlzHNpbmd1bGFyIHBhc3Npb24gZ0aGlzHNpbmd1bGFyIHnJvbSBvdGhlciBhbmltYWxzLCB3aGljaCBpcyBhIGx1c3Qgb2YgnJvbSBvdGhlciBhbmltdGhlIG1pbmQsIHRoYXQgYnkgYSBwZXJzZXZlcmFuY2Ugb2YgZGVsaW0dGhlIG1pbmQsIHR dodCBpbiB0aGUgY29udGludWVkIGFuZCBpbmRlZmF0aWdhYmxlIGdldodCBpbiB0aGUgY2 bmVyYXRpb24gb2Yga25vd2xlZGdlLCBleGNlZWRzIHRoZSBzaG9ytmVyYXRpb24gb2Yga2cyByZWFzb24sIGJ1dCBieSB0aGlzHNpbmd1bGFyIHBhc3Npb24gZ0aGlzHNpbmd1bGFyIHnJvbSBvdGhlciBhbmltYWxzLCB3aGljaCBpcyBhIGx1c3Qgb2YgnJvbSBvdGhlciBhbmltdGhlIG1pbmQsIHRoYXQgYnkgYSBwZXJzZXZlcmFuY2Ugb2YgZGVsaW0dGhlIG1pbmQsIHR dodCBpbiB0aGUgY29udGludWVkIGFuZCBpbmRlZmF0aWdhYmxlIGdldodCBpbiB0aGUgY2 bmVyYXRpb24gb2Yga25vd2xlZGdlLCBleGNlZWRzIHRoZSBzaG9ytmVyYXRpb24gb2Yga2cyByZWFzb24sIGJ1dCBieSB0aGlzHNpbmd1bGFyIHBhc3Npb24gZ0aGlzHNpbmd1bGFyIHnJvbSBvdGhlciBhbmltYWxzLCB3aGljaCBpcyBhIGx1c3Qgb2YgnJvbSBvdGhlciBhbmltdGhlIG1pbmQsIHRoYXQgYnkgYSBwZXJzZXZlcmFuY2Ugb2YgZGVsaW0dGhlIG1pbmQsIHR dodCBpbiB0aGUgY29udGludWVkIGFuZCBpbmRlZmF0aWdhYmxlIGdldodCBpbiB0aGUgY2 bmVyYXRpb24gb2Yga25vd2xlZGdlLCBleGNlZWRzIHRoZSBzaG9ytmVyYXRpb24gb2Yga2cyByZWFzb24sIGJ1dCBieSB0aGlzHNpbmd1bGFyIHBhc3Npb24gZ0aGlzHNpbmd1bGFyIHnJvbSBvdGhlciBhbmltYWxzLCB3aGljaCBpcyBhIGx1c3Qgb2YgnJvbSBvdGhlciBhbmltdGhlIG1pbmQsIHRoYXQgYnkgYSBwZXJzZXZlcmFuY2Ugb2YgZGVsaW0dGhlIG1pbmQsIHR

Not ideal for these documents

{"name": "SmugMug","permalink": "smugmug","homepage_url": "http://www.smugmug.com","blog_url": "http://blogs.smugmug.com/","category_code": "photo_video","products": [{"name": "SmugMug","permalink": "smugmug"

}],"offices": [{"description": "","address1": "67 E. Evelyn Ave","address2": "","zip_code": "94041","city": "Mountain View","state_code": "CA","country_code": "USA","latitude": 37.390056,"longitude": -122.067692

}]

}

Perfect for these Documentsschema-agnostic JSON store

forhierarchical and de-normalized data at scale

• Rich querying over flexible schemas• Automatic indexing of every document property• Easy-to-use SQL grammar• Integrated with other Azure data services

Azure DocumentDB

Catalog DataPreferences & State Data Gaming Events & Logging

User Data & Content Data Exchange

Common Use Cases

Next GamesHalo5

The basics

The Basics• DocumentDB Resources


!=

Performance levels

Performance Levels

Performance Levels

S1 S2 S3

Performance Levels

S2 S3S3

http://aka.ms/docdbperf

Scaling through Collections

Demo - Portal & Tools

The Scenario

Running a Database as a Service

CPU Utilization Storage Utilization

Sample JSON data{

"TIMESTAMP": "12/10/2015 00:00:00","TimeStampEpoch": 1449705600,"Server": "ProdApp002","Counter" : "percent processor

time","UnitMeasure" : "percent","CounterValue" : 4.436914

}

CPU Utilization Storage Utilization{

"TIMESTAMP": "12/10/2015 00:10:00","TimeStampEpoch": 1449706200"Server": "ProdApp002","Counter" : "percent free space","UnitMeasure" : "percent","Location" : "d","Type" : "data","CounterValue" : 96.717065

}

Goal – Pinpoint DocumentDB Hotspots


Solution – Power BI Integration { }

SELECT * FROManalytics

34.56%

"TIMESTAMP": "2015-12-10T00:00:00Z""Server": "ProdApp002""Counter" : "percent processor time""UnitMeasure" : "percent""CounterValue" : 4.436914"TIMESTAMP": "2015-12-10T00:10:00Z""Server": "ProdApp002""Counter" : "percent free space""UnitMeasure" : "percent""CounterValue" : 96.717065

12.09%

Goal – Generate 50th, 90th, and 99th percentiles


Solution – HDInsight Integration { }

SELECT * FROMlogs201512 WHERE logs201512.Counter = “percent processor time”

SELECT * FROMlogs201512 WHERE logs201512.Counter = “percent free space”

percentile(CounterValue, 0.50)percentile(CounterValue, 0.90)percentile(CounterValue, 0.99)

Solution – Full Solution { }

SELECT * FROManalytics

percentile(CounterValue, 0.50)percentile(CounterValue, 0.90)percentile(CounterValue, 0.99)

SELECT * FROMlogs201512_summary

SELECT * FROMlogs201512

34.56%

"TIMESTAMP": "2015-12-10T00:00:00Z""Server": "ProdApp002""Counter" : "percent processor time""UnitMeasure" : "percent""CounterValue" : 4.436914"TIMESTAMP": "2015-12-10T00:10:00Z""Server": "ProdApp002""Counter" : "percent free space""UnitMeasure" : "percent""CounterValue" : 96.717065

12.09% P5028%

P9036%

P9954%

OverallGreat fit for Logging and Event based scenarios

Adhoc, real-time queries Integration with other Azure Data services: HDInsight, Stream Analytics, Data Factory, and Power BI

Great fit for dynamic, evolving applications On the fly schema changes Automatic indexing Horizontal Scaling

Get Started Todayselect * from playground p where p.name = “DocumentDB”

http://aka.ms/docdbplayground http://aka.ms/docdbstarter http://aka.ms/docdbimport

analyze and visualize non-relational data with documentdb + power bi

Technology