In a hybrid architecture like the one in Power BI 2.0, it is critical for the Cloud service to get access to on-premises data sources and moreover, to be able to refresh the data. So in this post, I will focus on this part of the architecture:
In Power BI 2.0, the gateways give you the capability to refresh data from on-premises sources. In fact, you have two gateways.
- Power BI Personal Gateway
- Power BI Analysis Services Connector
Power BI Personal Gateway
The Power BI Personal Gateway is essentially a Windows executable that you can run as a standard Windows application or as a Windows Service.
You can either run it:
- without admin permissions as a standard application (but in this case you must be logged in to your computer at the scheduled refresh time), or
- with admin permissions as a Windows service (in this case you need only to have the computer and the Gateway service running at the scheduled refresh time)
Once launched, Power BI Personal Gateway will show up in the system tray:
It will act as a bridge, doing scheduled refreshes of your on-premises files to Power BI Service in the cloud.
It is NOT needed for any cloud-based services.
It is NOT needed when you put your Excel or .PBIX files in OneDrive or OneDrive for business where automatic refreshes can occur.
It IS needed when your data source is on-premises. It supports:
- Databases sources: SQL Server, Oracle, Teradata, IBM DB2, Sybase, MYSQL
- SharePoint lists
- Files (Excel, .csv, .xml, .txt, Access) and folders
- On-premises SQL Server Analysis Services Tabular models (as uploaded data; not live connections)
Once installed, you are able to query the above supported data sources via:
- Query in a .pbix file and upload that file to the PBI service
- Power Query in an Excel file and upload that file to the PBI service
Clicking on the Dataset in the browser interface gives you something like that:
Please note here that you can add up to eight refresh times per day.
Behind the scenes, if we go to the services.msc we can see that PBI Personal Gateway is relying on the “Data Management Gateway”:
A configuration tool is even available, which provides access to the following information:
Power BI Analysis Services Connector
PBI Analysis Services Connector has a very distinct functional goal. Microsoft designed it to give access to SQL Server Analysis tabular models stored in on-premises instances from the Power BI Service.
However, it’s technically very similar but you cannot install both on the same computer. PBI AS Connector relies on a “Data Management Gateway Host Service,” which is obviously a flavor of the other. Note that PBI AS Connector can only be run as a service, and is designed to run on a server rather than on a PC.
There are some interesting prerequisites for the AS Connector to run smoothly:
- The Analysis Services server is domain joined.
- The Analysis Services connector and Analysis Services server should be installed on computers in the same domain.
- If you use an .onmicrosoft.com email address, you’ll need to sync your Active Directory to Azure Active Directory using Azure Active Directory Sync (DirSync).
The complete installation process for the connector is detailed here: Configure a Power BI Analysis Services Connector.
You can see views when selecting (in Power BI Service): Get data > Database & More > SQL Server Analysis Services > Connect
You can find Patrick’s other articles in this series on [ #Office365] Power BI 2.0:
- The big picture
- Architectural aspects
- More on data sources
- More on Power BI mobile apps
- The gateways (this original article)