What email address or phone number would you like to use to sign in to Docs.com?
If you already have an account that you use with Office or other Microsoft services, enter it here.
Or sign in with:
Signing in allows you to download and like content, and it provides the authors analytical data about your interactions with their content.
Embed code for: Montreal PowerBI User Group - Realtime Power BI Dashboards
Select a size
My presentation at the Montreal Power BI & Excel user group on March 15, 2017 about creating Power BI dashboards with real time data. https://www.meetup.com/msdevmtl/events/236569791/
Real Time Power BI Dashboards
MVP Azure, Freelance Dev/Architect
Who are you?
Build a Power BI dashboard that displays data in real time
Data Refresh in Power BI
*Cloud data is refreshed automatically about every fifteen minutes
Azure Stream Analytics
Azure SQL Database
Other Output Sinks
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)
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, LicensePlate
WHERE 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],
FROM EntryData TIMESTAMP BY EntryTime
Count or aggregate events over a specified time period
How many red cars go through my toll booths every 5 minutes?
Tell me once a minute how many red cars go through my toll booths every 5 minutes.
During which 5-minute time periods do 10 or more red cars go through my toll booths?
SELECT DateAdd(minute, -5, System.TimeStamp)
AS [Start Time], System.TimeStamp AS [End Time],
WHERE State = 'NY'
GROUP BY TumblingWindow(minute, 5)
How many New York cars enter a toll booth every 5 minutes?
What is the average wait time at all toll booths for the last 5 minutes, updated every 1 minute?
AVG(DATEDIFF(minute, EN.EntryTime, EX.ExitTime))
AS [Average Wait Time]
FROM EntryData EN TIMESTAMP BY EntryTime
JOIN ExitData EX TIMESTAMP BY ExitTime
ON EN.TollId = EX.TollId
AND EN.LicensePlate = EX.LicensePlate
AND DATEDIFF(minute, EN, EX) BETWEEN 0 AND 60
GROUP BY HoppingWindow(minute, 5, 1)
In which 5-minute windows does at least one Connecticut car enter a toll booth?
GROUP BY TollId, SlidingWindow(minute, 5)
HAVING COUNT(*) > 0
Power BI REST API
Power BI REST API - Workflow
1-Create a streaming dataset
2-Define the structure
3-Save the data?
4-Give this to the developer
URL and Key
5-Add tiles to your dashboard
Direct support for PubNub
Microsoft Flow support
Witch one to choose?
Large volumes of data
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
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
Smaller volumes of data
Easy to write clients in any language
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
A good way to characterize Stream Analytics is to say that a database lets you determine how many red cars were sold last year. Stream Analytics lets you determine how many red cars are in the parking lot at any given time. A great example of why it's useful to perform temporal queries on streaming data can be found at http://bit.ly/1SE5g9X ("Connected Cows").
Here's a typical scenario showing how Stream Analytics is used. On the left are the event producers, such as mobile phones an IoT sensors. They fire events to an Azure event hub (using a cloud gateway as an intermediary if the devices themselves aren't IP-capable), which aggregates the events and delivers them to a Stream Analytics job. Stream Analytics queries the data stream and sends the results to one or more outputs, which might include blob storage, a SQL Azure database, another event hub, or (not shown here) Microsoft Power BI.
The genius of Azure Stream Analytics is that it allows you to "use what you know" by using SQL to query dynamic data streams. It's not straight SQL, but an enhanced version of T-SQL. The enhancements are necessary to deal with dynamic data. For example, the windowing enhancements enable you to ask questions such as "how many red cars enter the parking lot every 5 minutes?" TIMESTAMP BY lets you designate a field in the data stream as the one that holds the event time -- the time at which the event actually occurred (as opposed to the time the event reached an event hub, which is the default).
This is a simple query that would work against a SQL database. It also works in Azure Stream Analytics. The context is a scenario in which you manage a collection of toll booths, and an event is fired each time a car enters or leaves a tool booth (two separate data streams in this scenario). Each event includes information such as the event time, the make and model of the car, and the car's license plate info (state and license-plate number).
TIMESTAMP BY is an important extension to the Stream Analytics Query Language because it lets you designate one field in the data stream as the one that specifies the event time -- the time at which the event actually occurred. Without this, queries that involve event times use the time at which the event arrived at the event hub, not the time at which the event occurred. Virtually all IoT data streams include an event-time field.
One of the key features of the Stream Analytics Query Language is its ability to group results using windows of time whose length you specify. Syntactically, you exercise windowing by using SQL's GROUP BY clause with TumblingWindow, HoppingWindow, or SlidingWindow. TumblingWindow allows you to ask questions such as "How many red cars go through my toll booths every 5 minutes?" HoppingWindow lets you ask the same question but generate output at intervals that are independent of the window size -- for example, "Tell me once a minute how many red cars go through my toll booths every 5 minutes." SlidingWindow lets you ask "During which 5-minute time periods do 10 or more red cars go through my toll booths?"
This query answers the question "How many cars with NY license plates enter my tool booths every 5 minutes?" and generates an output every 5 minutes.
This query answers the question "What is the average wait time at all toll booths for the last 5 minutes?" and generates an output every 1 minute.
This query answers the question "Which toll booths have served at least one car with CT plates in any 5-minute period?"
t also works in Azure Stream Analytics. The context is a scenario in which you manage a collection of toll booths, and an event is fired each time a car enters or leaves a tool booth (two separate data streams in this scenario). Each event includes information such as the event time, the make and model of the car, and the car's license plate info (state and license-plate number).
One of the key features of the Stream Analytics Query Language is its ability to group results using windows of time whose length you specify. Syntactically, you exercise windowing by using SQL's GROUP BY clause with TumblingWindow, HoppingWindow, or SlidingWindow. TumblingWindow allows you to ask questions