Using a Gateway to Leverage OnPremises data in

Using a Gateway to Leverage On-Premises data in Power BI /guyinacube Adam Saxton

Adam Saxton /guyinacube @guyinacube I write stuff and create videos… /guyinacube

Starting with Power BI Desktop

File location and freshness

Refresh & Gateways

Personal mode On-premises data gateway (Enterprise mode - default) Target Persona Business analyst sets up and uses the gateway for her data sources • BI Admins set up the gateway for their department/ company • Multiple users use the gateway setup by the admins Features Import with scheduled refresh Direct query and scheduled refresh No additional data source configuration to set up a refresh Gateway admin needs to configure each data source explicitly Data source connections managed per user Central data source management and access control Power BI, Power. Apps, Microsoft Flow, Azure Logic Apps and Azure Analysis Services supported

Import (cached mode) Direct. Query or Live connection Refresh frequency Scheduled – hourly (8 x a day) Premium allows for 48 x a day 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 onpremises* Data size Current limit of 1 GB (compressed) per model The on-premises database is the limit; no Power BI limitation Security Can create row-level security on the Power BI dataset (Import and Direct. Query) Re-use on-prem role based row level security for Analysis Services. Non-SSAS supports Cloud RLS. Data sources All on-prem data sources supported can be used in import mode Select data sources *Some data is cached for optimizing first-time load performance

Centralized way to refresh on-premises content in Power BI and other services Access control to data sources Monitor and track usage Live, interactive query with on-premises data sources

DB

Power BI Cloud On-premises data sources SQL Server Analysis Services SAP Oracle

Power BI Cloud On-premises data sources SQL Server Analysis Services SAP Oracle

Troubleshooting

asaxton@microsoft. com