Tokyo Power BI Study Group

Delivering Real-Time Power BI Dashboards

Peter Myers

17 February, 2018

Presenter introduction

Peter Myers▪ Independent BI Expert, Bitwise Solutions

▪ BBus, SQL Server MCSE, Data Platform MVP (since 2007)

▪ Experienced in designing, developing and maintaining Microsoft database and application solutions (since 1997)

▪ Focuses on consulting, education and mentoring

▪ Certified English teacher (CELTA)

▪ Based in Melbourne, Australia

peter.myers@bitwisesolutions.com.au

http://www.linkedin.com/in/peterjsmyers

Presentation outline

Power BI overview

Real-time for business analysts

Real-time for developers

Real-time for IT pros

Power BI overview

▪ Introducing Microsoft Power BI

▪ Power BI overview

Introducing Microsoft Power BIExperience your data. Any data, any way, anywhere.

Power BI is a cloud-based business analytics service*

Power BI

Power BI overview

Power BI REST APIPower BI Desktop

Prepare Explore ShareReport

SaaS solutionsE.g. Marketo, Salesforce, GitHub,

Google analytics

On-premises dataE.g. Analysis Services

Organizational appsCorporate data sources, or external

data services

Azure servicesE.g. Azure SQL, Stream Analytics

Excel filesWorkbook data and data models

Power BI Desktop filesRelated data from files, databases,

Azure, and other sources

Data refresh

Visualizations

Live dashboards

Content packs Sharing & collaborationNatural language query

Reports

Datasets01001

10101

Power BI dashboardsAdding dashboard tiles

Upload dataset to create “drillthrough” tile

Pin report visual

Pin live report page

Pin Q&A response

Quick Insights

Pin Excel chart or range

Pin SSRS visual

Add widget (text, video, image, web)

Add streaming tile

Pin from other dashboard

Power BI dashboardsAdding dashboard tiles

Upload dataset to create “drillthrough” tile

Pin report visual

Pin live report page

Pin Q&A response

Quick Insights

Pin Excel chart or range

Pin SSRS visual

Add widget (text, video, image, web)

Add streaming tile

Pin from other dashboard

Tiles

Report

Cloud Model

Live

Connection

SSRSQ&A and

Quick Insight

Excel

Charts and

Visuals

Ranges

Widget

Text

Video and

Images

Web

Streaming

Power BI datasetsReal-time updates

Power BI can deliver real-time dashboards

Dashboard tiles reflect up-to-date values when based on certain types of dataset

Three types:▪ Push datasets (store data)

▪ Streaming datasets (display latest data)

▪ PubNub streaming datasets (display PubNub data streams)

Power BI dashboardsAdding dashboard tiles

Upload dataset to create “drillthrough” tile

Pin report visual

Pin live report page

Pin Q&A response

Quick Insights

Pin Excel chart or range

Pin SSRS visual

Add widget (text, video, image, web)

Add streaming tile

Pin from other dashboard

Tiles

Report

Cloud Model

Live

Connection

SSRSQ&A and

Quick Insight

Excel

Charts and

Visuals

Ranges

Widget

Text

Video and

Images

Web

Streaming

Power BI data presentationDashboards vs. Reports

Dashboards▪ Intended for monitoring

▪ “Single pane of glass”

▪ Tiles can be sourced from many datasets

▪ No filtering

▪ Can embed (dashboards or tiles)

▪ Tiles automatically update when dataset is refreshed

▪ Can distribute with apps

▪ Can share to internal or external users

▪ Can navigate to source (report, question, etc.), or a custom URL

▪ Tiles display real-time data updates

Reports▪ Intended for interaction/exploration

▪ One or more pages

▪ Visuals sourced from one dataset

▪ Interactive filtering and sorting

▪ Can embed

▪ Requires manual refresh, or will update when interacting

▪ Can distribute with apps

▪ Can publish to web

Real-time for business analysts

▪ Streaming datasets

Streaming datasetsPinning real-time data

Streaming datasets are designed to display the latest data

A pure streaming dataset▪ Stores data temporarily (1 hour)

▪ Does not require authentication

Can be configured to store historic data▪ In which case, they behave like push datasets

Real-time dashboards with streaming datasets

01

Real-time for developers

▪ Extending Power BI

▪ Power BI REST API

Power BI REST API

Develop with the Power BI REST API to:▪ Create a Power BI dataset

▪ Push data directly into a Power BI dataset

▪ Clear rows from a Power BI dataset table

▪ Embed content into apps

▪ And more…

Power BI REST APIReal-time delivery

Datasets can be created with the defaultMode property set to:▪ Push

▪ Streaming

▪ PushStreaming

Dashboards will update in real-time as data is pushed with the REST API

Power BI REST APIObject model hierarchy

A Power BI tenant can have multiple datasets, which are each identified by a GUID

A dataset can:▪ Be owned by a user or a group

▪ Have multiple tables, which are referenced by name

A table has multiple rows

Datasets

Tables

Power BI

workspace

Rows

Power BI REST APIAuthentication

An application must be registered in the Azure Active Directory (AAD), and is required to establish an identity for the application ▪ Applications are identified by their Client ID

▪ Permissions are delegated to the Power BI service

▪ REST API calls are made on behalf of an authenticated user by passing a token in the “Authorization” header

Power BI REST APIAuthentication flow: Client application

Power BI REST APIAuthentication flow: Web application

Power BI REST APICommon operations

Operation Method

List all datasets GET Returns a JSON list of all dataset objects that includes a name and dataset ID

Create a dataset POST Creates a new dataset with the schema definition that is in the JSON body,

and returns the dataset ID and the properties of the objects created

List all tables GET Returns a JSON list of tables for the specified dataset

Update an existing table schema PUT

Add rows to a table in a dataset POST

Clear the rows in a table DELETE

List all groups GET Returns a JSON list of all the groups of which the user is a member

List all dashboards GET (In preview)

List all tiles GET (In preview) Returns a JSON list of tiles for the specified dashboard.

Use this method to retrieve the embed URL.

List all reports GET (In preview) Use this method to retrieve the embed URL

Power BI REST APIExample: List all datasets

Request

GET https://api.powerbi.com/v1.0/myorg/datasets HTTP/1.1Authorization: Bearer {AAD Token}Content-Type: application/json; charset=utf-8

Response

{"datasets": [{"id": "2C0CCF12-A369-4985-A643-0995C249D5B9","name": "Music"

},{"id": "C446840B-94AD-4B23-8896-C276E69B8FAA","name": "SalesMarketing"

}]

}

Power BI REST APIExample: Add a row to a table in a dataset

Request

POST https://api.powerbi.com/v1.0/myorg/datasets/2C0CCF12-A369-4985-A643-0995C249D5B9/Tables/Product/Rows HTTP/1.1

Authorization: Bearer {AAD Token}Content-Type: application/json; charset=utf-8

{"rows": [

{"ProductID":1,"Name":"Adjustable Race","Category":"Components","IsCompete":true,"ManufacturedOn":"07/30/2014"

}]

}

Real-time dashboards with the Power BI REST API

02

Power BI REST APILimitations

Maximum columns: 75Maximum tables: 75Maximum rows per table: 5M▪ None retention policy: Rows will be collected to the limit▪ basicFIFO: retention policy: Will keep up to 200K rows, and replace older rows with new rows

Maximum rows per single push: 10KMaximum rows per hour per dataset: 1M▪ Except if the table has more than 250K row, in which case it is 120

Maximum row requests per minute per dataset: 120Maximum characters per string column : 4KMaximum pending requests: 5

Real-time for IT pros

▪ Integration with Azure services

Integration with Azure services

Azure and Power BI have the built-in connectivity and integration to bring Business Intelligence efforts to life

Integration can be achieved without the need to develop complex solutions:▪ Direct connect:

▪ Azure SQL Database

▪ Azure SQL Data Warehouse

▪ Spark on Azure HDInsight

▪ Power BI Desktop

▪ Real-time dashboards with Azure Stream Analytics

HDInsight

Storage

Event Hubs

Machine Learning

SQL Server

Stream Analytics

Power BI

Integration with Azure servicesReal-time dashboards with Azure Stream Analytics

Use Azure Stream Analytics to push live, streaming data to Power BI▪ Enables real-time dashboards at scale, over data from devices and applications

▪ Can stream millions of events per second

▪ Can perform aggregation over time windows

▪ Can integrate Azure Machine Learning predictions

Integration with Azure servicesReal-time dashboards with Azure Stream Analytics

Azure Portal

Azure Ops Team

ML Studio

Data Professional

HDInsight

Azure Storage

Desktop Data

Azure Portal & ML API service

Azure Ops Team

ML API service Application Developer

Mobile AppsWeb Apps Streaming

On-Prem Data

Business users easily access results from anywhere, on any

device Power BI

Real-time dashboards with Azure Stream Analytics

03

ResourcesWeb

Power BI site▪ http://powerbi.microsoft.com

Power BI documentation▪ http://support.powerbi.com/

Power BI community▪ http://community.powerbi.com/

Power BI blog▪ http://blogs.msdn.com/b/powerbi/

ResourcesWeb

Power BI Developer Center▪ https://powerbi.microsoft.com/developers

Power BI REST API Console▪ http://docs.powerbi.apiary.io

▪ Test each of the REST API operations using the console interface

Power BI REST API Limitations▪ https://msdn.microsoft.com/library/dn950053.aspx

Azure and Power BI knowledge base▪ https://support.powerbi.com/knowledgebase/articles/568614-azure-and-power-bi

ResourcesWeb

Power BI Blog: Real-time in no time with Power BI▪ https://powerbi.microsoft.com/en-us/blog/real-time-in-no-time-with-power-bi

Power BI: Real-time streaming in Power BI▪ https://powerbi.microsoft.com/en-us/documentation/powerbi-service-real-time-

streaming/

ResourcesTraining

Peter Myers provides training in SQL Server BI and Power BIAll training courses have been specifically designed to enable students to quickly commence developing and maintaining state-of-the-art integrated Business Intelligence (BI) solutions developed by using Microsoft products

Training courses can be delivered remotely, or in-person

SQL Server EIM (2 days)

SQL ServerSSAS (3 days)

SQL ServerSSRS (3 days)

Power BI and DAX (1-3 days)

ResourcesToday’s presentation

You can download this presentation from:bit.ly/petermyers_ug_20180217

Questions?

Thank you