Real Time Power BI DashboardsGuy BarretteMVP Azure, Freelance Dev/Architecthttp://guy.cloud

Who are you?

Developer Analyst

The Goal

Build a Power BI dashboard that displays data in real

time

Data Refresh in Power BI

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/

*Cloud data is refreshed automatically about every fifteen minutes

Cloud

Desktop Web

Real-Time Dashboards

Stream Analytics

Stream Analytics

Stream Analytics

Azure Stream Analytics• Highly scalable service for analyzing data in motion• Supports SQL-like query language for data analysis• Scales using Streaming Units (1 SU ~= 1 MB/sec)

Event Hubs

Blob StoragePower BIAzure SQL Database

Event Hubs

Other Output SinksStream Analytics

IoT Hubs

Stream Analytics at Work

Stream Analytics Query Language• SQL-like language for querying live data streams• Subset of T-SQL• Supports bigint, float, nvarchar(max), datetime, record, and

array• Supports SELECT, FROM, WHERE, GROUP BY, and other

common Data Manipulation Language (DML) statements• Supports COUNT, AVG, DATEDIFF, and other common functions

• Adds extensions such as TIMESTAMP BY and System.Timestamp• Supports temporal grouping of events via "windowing"

Querying a Data Stream• List all Connecticut

cars that enter a toll booth, and include the entry time, toll booth ID, and license-plate number

SELECT EntryTime, TollId, LicensePlateFROM EntryDataWHERE State = 'CT'

Designating a Field as the Event Time

• Designate the EntryTime field as the event time for calculations that involve event time

SELECT System.Timestamp AS [Entry Time], TollId, LicensePlateFROM EntryData TIMESTAMP BY EntryTimeWHERE State = 'CT'

Windowing• Count or aggregate events over a specified time period

TumblingWindow

HoppingWindow SlidingWindow

Using TumblingWindow

• How many New York cars enter a toll booth every 5 minutes?

SELECT DateAdd(minute, -5, System.TimeStamp) AS [Start Time], System.TimeStamp AS [End Time], COUNT(*)FROM EntryData TIMESTAMP BY EntryTimeWHERE State = 'NY'GROUP BY TumblingWindow(minute, 5)

Using HoppingWindow

• What is the average wait time at all toll booths for the last 5 minutes, updated every 1 minute?

SELECT DateAdd(minute, -5, System.TimeStamp) AS [Start Time], System.TimeStamp AS [End Time], AVG(DATEDIFF(minute, EN.EntryTime, EX.ExitTime)) AS [Average Wait Time]FROM EntryData EN TIMESTAMP BY EntryTimeJOIN ExitData EX TIMESTAMP BY ExitTime ON EN.TollId = EX.TollId AND EN.LicensePlate = EX.LicensePlate AND DATEDIFF(minute, EN, EX) BETWEEN 0 AND 60GROUP BY HoppingWindow(minute, 5, 1)

Using SlidingWindow

• In which 5-minute windows does at least one Connecticut car enter a toll booth?

SELECT DateAdd(minute, -5, System.TimeStamp) AS [Start Time], System.TimeStamp AS [End Time], TollId, COUNT(*) FROM EntryData TIMESTAMP BY EntryTimeWHERE State = 'CT'GROUP BY TollId, SlidingWindow(minute, 5)HAVING COUNT(*) > 0

DEMO

guyb.ca/20170315

Power BI REST API

REpresentationalStateTransfer

Power BI REST API - Workflow

1-Create a streaming dataset

2-Define the structure

• Text• Number• DateTime

3-Save the data?

4-Give this to the developer

JSON sample

URL and Key

5-Add tiles to your dashboard

Direct support for PubNub

Microsoft Flow support

<no></code>

DEMO

Witch one to choose?

Stream Analytics

Witch one to choose?

Stream Analytics

Stream Analytics• Large volumes of data• Highly scalable

• Each streaming unit corresponds to 1MB/second of throughput• Up to 48 units (contact support for more)

• Max rows operations in Power BI• Free plan: 10,000 rows per hour• Paid plan: 1,000,000 rows per hour

• Complex scenarios• Ability to transform the data before sending it • Sources: Event Hub, IoT Hub and Blob Storage• Data sent to Power BI is stored only if Historical Data is selected

Power BI• Smaller volumes of data• Simpler scenarios• Max rows operations in Power BI• Free plan: 10,000 rows per hour• Paid plan: 1,000,000 rows per hour

• Easy to write clients in any language• REST API• Data sent to Power BI is stored only if Historical Data is

selected

Power BI REST API limitations• To POST Rows

• 75 max columns• 75 max tables• 10,000 max rows per single POST rows request• 1,000,000 rows added per hour per dataset• 5 max pending POST rows requests per dataset• 120 POST rows requests per minute per dataset• If table has 250,000 or more rows, 120 POST rows requests per hour per dataset• 200,000 max rows stored per table in FIFO dataset• 5,000,000 max rows stored per table in ‘none retention policy’ dataset• 4,000 characters per value for string column in POST rows operation

• POST Rows operation per Power BI plan• Dataset created by user with free service plan: 10,000 rows added per hour per dataset• Dataset created by user with paid service plan: 1,000,000 rows added per hour per dataset

• If a user exceeds this limit, we will fail subsequent API calls with the following details:• HTTP Status Code: 429 Too Many Requests

Questions