Microsoft Power BI, Flow, and PowerApps: Connecting to data using the on-premises data gateway

2. Microsoft Power BI, Flow, and PowerApps: Connecting to data using the on-premises data gateway Robert Bruckner Partner Software Architect Data Integration Team, Microsoft Miguel Llopis Principal Program Manager Data Integration Team, Microsoft Arthi Ramasubramanian Iyer Senior Program Manager Data Integration Team, Microsoft BRK3077

3. Session objectives and takeaways At the end of this session, you should be better able to… Understand what is the On-premises Data Gateway Use the gateway in Power BI Apply Best practices using the gateway Know what’s on the roadmap and what’s “coming soon”

4. On-premises Data Gateway One gateway for multiple cloud services and experiences Cloud services Azure Analysis Services PowerApps Power Query Online Azure Logic Apps Power BI Microsoft Flow Gateway Management Cloud ServiceData source connection credentials are encrypted Azure Service Bus On-premises Data Gateway Data source connection credentials can only be decrypted by the gateway On-premises data sources SQL ServerAnalysis Services Other data sources SQL Server Files, SharePoint

5. What is an On-premises Data Gateway? Secured, and optimized communication channel to use with your cloud services (Power BI, PowerApps, Microsoft Flow, Power Query Online, Azure Analysis Services, Azure Logic Apps) The gateway is nota general purpose/bi-directional VPN The gateway installs on any domain-joined machine Outbound traffic only You don’t have to install it on a DMZ, or open your firewall for inbound traffic The gateway enables data/API connectivity It’s orthogonal to how you consume/integrate Dashboards, Reports, Flows or Apps on top of these gateway connections (e.g. browser app, mobile app, API calls)

6. What is an On-premises Data Gateway? • The Gateway uses an Azure Service Bus Relay to communicate between on-premises data sources and cloud services. • Azure Service Bus infrastructure is automatically provisioned, managed, and owned by the Gateway Cloud Service. • Enables a self-service, low-friction installation, configuration, and runtime of gateways. • Unless the gateway is up and running, connections & requests won’t pass through the gateway • No queuing or caching of query requests or data on the on-premises gateway or the communication pipeline to the cloud.

7. On-premises Data Gateway management Centralized way to refresh on-premises content Access control to data sources Users and admin management and controls

8. On-premises data gateway (personal mode) v/s On-premises data gateway

9. Demo Gateway Setup & Configuration

10. Architecture: Power BI Data Refresh on-premises Gateway is installed & configured. During configuration, a dedicated Service Bus channel is autom. configured. Credentials entered for the data source in the cloud services are encrypted, tested, then stored encrypted. Only the associated gateway (cluster) can decrypt the credentials. The Power BI Personal Gateway keeps credentials on-prem only. One of the supported cloud services kicks off a refresh or a live query. Data Movement Service analyzes the query and pushes to appropriate Service Bus communication channel. Gateway polls its Service Bus channel for pending requests. It takes the pending request. Gateway fetches the query, decrypts the credentials, sends query to the data source for execution. Upon query execution, the gateway securely streams the data compressed to the cloud service. cred 2 Cloud Service Scheduler service 3 Data Movement Service 4 Azure Service Bus channel Cloud On-premises 5 7 Gateway 1 cred 2 6 DB

11. Connectivity in Power BI

12. Where is your data? How do you connect? How do you refresh? Data refresh in Power BI • Cloud • On-premises • Import data • Direct Query • Personal mode • Enterprise mode

13. Supported on-premises data sources https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/ • Relational Sources: SQL Server, Teradata, Oracle, IBM DB2, MySQL, PostgreSQL, Access, Sybase, IBM Netezza, Informix, PostgreSQL, Impala, Custom ODBC and OLE DB Drivers • Cube Sources: SQL Server Analysis Services (tabular and multi-dimensional), SAP HANA, SAP BW, Snowflake • Files/folder, SharePoint on-premises, network/local files • Other Sources: Web, OData, Active Directory, Apache Spark • Custom ConnectorsNew

14. How do you want to refresh data?

15. Single Sign-on & Row Level Security

16. Data Row Level Security (RLS) in Power BI Define RLS at a dataset level in Power BI • https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/ Leverage RLS defined in the underlying data source • Live Connect with SSAS on-premises, and Azure AS • DirectQuery with Single Sign-on

17. Single Sign-On / RLS from Power BI via Gateway Power BI Service: For each query by a Power BI AAD user to on-premises SSAS servers, or supported SSO DirectQuery sources, it passes along UPN with query: e.g. “firstName.lastName@contoso.com” Power BI Cloud On-premises On-premises Data Gateway: Map UPN to a local user (if needed) Execute query on behalf of the original user. Oracle SAP HANA SQL Server SQL Server SAP BW Tera-data Impala On-premises data sources

18. Sharing Dashboards/Reports with seamless SSO and RLS in the underlying data source • Kerberos SSO with Gateway • Documentation: https://docs.microsoft.com/en-us/power-bi/service-gateway-kerberos-for-sso-pbi-to-on-premises-data • Live demo: https://www.youtube.com/watch?v=HaSD6uRljDw&feature=youtu.be&t=37m20s • SSO with AAD to Azure SQL • in Power BI Desktop • & Power BI Cloud Service

19. Demo Power BI SSO with AAD to Azure SQL

20. High Availability & Load-Balancing

21. Configuring Gateway Clusters for High Availability Power BI Cloud On-premises Gateway Cluster SQL DB/DW

22. High Availability & Load Balancing • Automatic High Availability • A gateway can join a cluster of gateways: need to be an admin on original gateway, and know recovery key. • A group of gateways will act as a single unit (e.g. data source definitions) • Load Balancing: • A gateway cluster automatically distributes traffic across all gateways in that cluster

23. PowerShell Commands • Gateway PowerShell commands are now available in the PowerShell Gallery: https://aka.ms/GatewayPowerShell • Using those commands, you can: • Retrieve the list of gateway clusters, gateway instances and their online status • Modify the enable/disable status for gateways • Delete a gateway • … and more • More administration commands and APIs are coming in the future

24. Extensibility & Custom Connectors Support

25. Custom Data Connectors Support for Power BI • Last year we released the Power Query SDK for developing custom connectors that can be used in Power BI Desktop • Earlier this year, we released support in Enterprise & Personal Gateway modes to use Custom Connectors, including Import & DirectQuery modes. • Learn More about Custom Connectors: http://aka.ms/DataConnectors

26. Best Practices for Configuring and Managing Gateways

27. Where to install the gateway • Recommendation: • Start with an 8-core machine • Keep an eye on your performance countershttps://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/#performance • Depending on your usage, you can decide to scale up or down • Gateway doesn’t have to run on the same machine as the data source • Hardware needs depend on the usage scenarios for the gateway: • Frequency and data volume of data model refreshes in Power BI • DirectQuery: number of concurrent users, types of visualizations for interactive querying, whether RLS is used, number of pinned dashboard tiles over DirectQuery/LiveConnect keep updating in the background.

28. Gateway Network Communication

29. Gateway logs • Logs, logs, logs! • We’ve made it easier to retrieve all the logs.

30. Disaster recovery and gateway restore When setting up a gateway, you are asked for a “Recovery Key” Save it somewhere safe! Gateway admin can use the recovery key to restore a gateway to a different machine, or join another gateway for high availability clustering. Once a gateway is restored, all data sources and (encrypted) credentials will be restored automatically to the new gateway. Restored gateway has the same name, and takes place of the original gateway across cloud services. No need for mapping!

31. Roadmap for rest of CY18

32. On-Premises Data Gateway Roadmap Custom connectors support in the Enterprise gateway Certified custom connectors support – later 2018 Single Sign-On Support for additional sources: SAP BW (Kerberos) SAP HANA (SAML) New Data Sources Support: Vertica (Import & DirectQuery)

33. In review: session objectives and takeaways What is the on-premises data gateway How to set up and use the gateway Learned more about gateway capabilities like Connectivity and data refresh Single Sign-on & Row Level Security High Availability Load Balancing What’s coming for the gateway

34. Session resources On-premises data gateway in depth High availability clusters Troubleshooting gateway issues

35. Q&A If you have questions please proceed to the Q&A MICROPHONE located in your session room

36. Please evaluate this sessionYour feedback is important to us! This slide is required. Do NOT delete or alter the slide. The slide will be replaced onsite through Silver Fox Productions with an updated QR code. From your PC or Tablet visit MyIgniteat http://myignite.microsoft.com From your phone download and use the Ignite Mobile Appby scanning the QR code above or visiting https://aka.ms/ignite.mobileapp