Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Often it is necessary to connect Power BI to a data source that is hosted in an on premise environment. Access to on premise data to Power BI is done through gateways. A gateway is a software component that resides on premise that can communicate with Power BI. There have been numerous (at least 3!) gateways and it can be confusing to determine when to use each one. At a high level the various gateways are:
Gateways help connect Power BI with on premise data. The person setting up the gateway and the users consuming dashboards that get on premises data will require a Power BI Pro license. A free 60 Pro Trial license is available. Updated license information can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-purchasing-power-bi-pro/. Also of note is that the Azure Service Bus is required to make the connection. Under the covers the on-premise gateway uses the Azure Service Bus to make the cloud to on premise connection. Further details about the Azure Service Bus and the on-premises gateway can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-indepth/
There are multiple ways that Power BI can use on premise data. Depending upon the data source there are 2 different types of connections that can be made. The complete list of data sources and the type of connection that can be used is at: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/. The 2 types of connections are:
Although DirectQuery and Live Connections behave ins a similar manner there are some differences. For a Live Connection end user credentials are passed to the on-premise data source, while for DirectQuery a single connection is used. Live Connections also support the option of UPN mapping for Analysis Services data sources. Live Connections also provides better performance than DirectQuery. Live Connections do not allow changes to the data model via Power BI Desktop, these changes must be made in Analysis Services. DIrectQuery allows some model changes to be made in Power BI Desktop. Further details can be found at:
There is a 2-minute connection limit for both DirectQuery and Live Connections. If a query does not return results within 2 minutes, then end users will see an error on visuals in report pages.
The on-premises data gateway provides some important security features when connecting to SSAS. When users connect to an on premise data source, their identity is passed to along. This allows the following:
When developing reports using an on premise data source and the on-premises gateway, it is necessary to develop the reports in the Power BI Desktop tool. The Power BI Desktop tool must be used from a machine that has access to the on premise data source. In other words, Power BI Desktop must be used on premise. When the workbook is published, Power BI will use a gateway for the connection. If more than one gateway is available, then the user will need to choose which gateway to use. The server name and database name must match between Power BI Desktop and the data source configured in the Power BI service (https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-indepth). The server hosting the on-premises gateway should be as close as possible to the data source it is sharing. If the data source server is a physical server then the server hosting the gateway should be on a physical server as well.
Due to the rapidly changing nature of Power BI, please check the Power BI documentation for any feature changes.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.