Fifth article from a series on [ #Office365 ] Power BI 2.0 :
In an hybrid architecture like the one of Power BI, 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 we will focus on this part of the architecture:
In Power BI world, data refresh from on-premises sources capability is given by the gateways. In fact you have two gateways.
Power BI Personal Gateway
Power BI Analysis Services Connector
The Power BI Personal Gateway is essentially a Windows executable that can be 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 looged 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)
It will act as a bridge, doing scheduled refresh 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
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 time per day.
Beyond the scene, if we go to the services.msc we can see that PBI Personal Gateway is relying on the “Data Management Gateway”:
2. Power BI Analysis Services Connector
PBI Analysis Services connector has a very distinct functional goal. It is designed 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 at the point that you can’t install both on the same computer. PBI AS Connector is also relying 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 prerequisite for the AS Connector to run smoothly:
- The Analysis Services server is domain joined.
- The Analysis Services connector & Analysis Services server are installed on computers in the same domain.
- If you use a .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. Il will bring you to such
And bring you to such views when selecting (in Power BI Service): Get data > Database & More > SQL Server Analysis Services > Connect
and as always personal testing !