Connecting Power BI to On-Premise data with Power BI Gateways

by jlyttle Occasional Visitor on ‎11-01-2016 11:49 AM

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:

  • On-premises date gateway – This gateway is setup on a server and allows IT departments to deploy and manage central gateways. This gateway is the preferred choice for MCS engagements.  The rest of this article assumes the on-premise gateway is being used.  Details on the on-premise gateway can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/.  Unlike previous gateways the on-premise gateway can work with multiple services at the same time.  The on-premise gateway also functions with Microsoft Flow, Logic Apps, and Power Apps.  For those wishing for a more in-depth overview of the on-premise gateway there is an additional article at: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-indepth/.  A FAQ on the on-premises data gateway can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-faq/.
  • Personal – This gateway is designed to be setup for a single user. It is designed to work on a laptop or desktop computer.  Although a potential choice for proof of concepts or prototyping, it should not be used for production environments encountered by MCS.  The enterprise gateway should be used instead.  Details on the personal gateway can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway.
  • Analysis Services Connector (depreciated) – This gateway has been discontinued. It is important to be aware of it since there are still a lot of references to it.  It was the primary way to connect to on premise Analysis Services before the enterprise gateway was released.  The main take away is that this is old technology and nothing new should be done with it.  If this gateway is encountered it can be upgraded to an enterprise gateway.

Untitled.png

 

 

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:

  • Manual or scheduled refresh – With this type of connection data is imported from the on premise data source into the Power BI data model. This can be a manual refresh or the refresh can be setup to occur on a regular schedule.  This approach works with all of the supported data sources.  There is a significant limitation however:  Power BI data sources can only support 1 GB of content.  This means that large amounts of on premise data cannot use this approach.  Details of the 1 limit can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-manage-your-data-storage-in-power-bi....  The Power BI model will provide the best performance.
  • DirectQuery/Live Connections – Live Connections are used to connect to Analysis Services and DirectQuery is used to connect to Azure SQL Server, Azure SQL Data Warehouse, SQL Server and HDInsight. With DirectQuery/Live Connections Power BI generates a query that is passed to the on premise data source.  Power BI then displays the returned results.  There is no refresh to schedule since it is always a live connection back to the on premise server.  The big advantage is that the 1 GB limit no longer exists since the data is never stored in the Power BI data model. 

 

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:

  • Role based security – Security based on roles can be implemented. As long as the users account has been added to a role they will have the appropriate rights assigned.
  • Row level security – This is only available with an Analysis Services Live connection. Both multi-dimensional and tabular are supported.  Since the user’s credentials are passed to SSAS it is possible for SSAS to filter data based on the user’s id.  This allows not only filtering by table, but the filtering of individual rows within the table.  Note, SSAS multi-dimensional cell level security IS NOT supported at this time.  The more widely used dimension security is supported.  Details of security can be found at: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-indepth/

 

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.

Comments
by tringuyenminh92 New Contributor
‎11-04-2016 01:58 AM - edited ‎11-04-2016 01:59 AM

For 1GB limitation of importing mode, have you tried to public pbix file with fewer data for improting mode first, then schedule data gateway to refresh the rest?

 

by jlyttle Occasional Visitor
on ‎11-04-2016 05:11 AM

I have tried creating a smaller pbix file, uploading it and then having it do a data referesh over 1 GB.  I got an error with the refresh.  If you need to go over 1 GB then you should put the data in another environment and connect via LiveConnection or DirectQuery.  Don't try to cram more than 1 GB into the native model.