Using a Gateway to Leverage On-Premises Data in Power BI - PowerPoint Presentation

Slide1

Using a Gateway to Leverage On-Premises Data in Power BI

Adam SaxtonSr. Content Developer@GuyInACube

Adam Saxton

Who am I?

Sr. Content Developer working on Business Intelligence products

10 years supporting SQL Connectivity and BI Products

Avid blogger and YouTuber

I write stuff and make videos

Starting with

Power BI Desktop

SQL Server

Analysis Services

Live Power BI reports & dashboards

Live query

Cloud

On-premises

Data

Source

Data

Source

Direct query

Scheduled refresh

Getting data into PBI Desktop

Three options:

Import

DirectQuery

Live connections (SSAS only)

Data pulled into PBI Desktop

Data is streamed from source

Supported import sources

Supported cloud sources:

SaaS sources

Azure – SQL, DW, Blob, Table, HDInsight, Marketplace

SharePoint, web sources,

Odata

OneDrive

Supported on-

prem

sources:

SQL Server, Teradata, Oracle, DB2, MySQL, PostgreSQL, SAP BW, SAP HANA

Sybase, Access, Custom SQL, Custom ODBC Drivers

SQL Server Analysis Services

Files/folder, SharePoint on-premises

More added regularly!

What this looks like…

File location and freshness

File locations

Files from your computer

Files in the cloud

What location means…

Local Files:

Only copy the model to the cloud

Local files is never updated or pulled from

Files in the cloud:

Model is copied to Azure BI backend

Poll original file for updates

Refresh will update model and original file

About hourly

Refresh & Gateways

How do you want to refresh data?

Import (cached mode)

DirectQuery

or Live connection

Refresh frequency

Scheduled - hourly or daily

Real-time

Performance

No noticeable delay since data is already cached

Depends on how

fast the data source is as queries are executed in real-time

Data storage in Power BI

Since it is cached mode, data is stored in the cloud

No data is stored in Power BI. Data is always on-premises*

Data size

Current limit of 250MB (compressed) per

modelThe on-premises database is the limit; no Power BI limitationRow level security No support for on-prem row level security. Supports Cloud RLS.Re-use on-prem role based row level security for Analysis Services. Non-SSAS supports Cloud RLS.*Some data is cached for optimizing first-time load performance

Power BI Gateway - Personal

Self-service refresh – no IT dependency

Easy & simple to use: Lightweight one-click installer running on your computer

One Personal Gateway per user

Runs either as a service or an app

On-premises Data Gateway

Centralized way to refresh on-premises content in Power BI

Access control to data sources

Monitor and track usage

Live, interactive query with on-premises data sources

Data gateway and Analysis Services

Gateway Service Account needs to be Admin on AS Instance

Org Account sent via Effective User Name connection property

Email address

Email address has to match UPN for account in local domain

May need to use Azure AD DirSync or can use alternate UPN Suffix

Gateway service

How this works…

Cloud

DB

On-premises

Install & configure gateway

Create a data source or schedule refresh

Interact with report, schedule refresh or refresh now

Gateway Service pushes to Service Bus

Gateway polls bus looking for requests

Gateway takes request and decrypts creds

Gateway connects and runs query

Gateway sends results back up stack

Power BI

Scheduler

Service

creds

Troubleshooting

Firewall

Outbound connection from gateway to Azure Service Bus

Outbound Ports:

443

5671, 5672

9350-9354

Tools of the trade

Make sure on latest version of the gateway

Refresh history

Event Logs

Trace logs for gateway

Setup logs

Proxy configuration

Fiddler

Troubleshooting articles

asaxton@microsoft.com