Power BI Connected to On-Premises Data

Microsoft’s vision is that we would all use Office 365 for everything that we do, however there is one slight glitch in that plan, not all our data resides in the cloud or is accessible to the cloud easily. To facilitate this, Microsoft has released their Data Management Gateway tools that allow for On-Premises data to be consumed directly within Office 365, bridging the gap between the two.

The on-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services. You can use a single gateway with different services at the same time. If you are using Power BI, as well as PowerApps, a single gateway and be used for both. It is dependent on the account you sign in with.

The current supported list of data services that can be consumed are:

These services can be connected to Office 365 through the Data Management Gateway.

During the installation process you can select a personal gateway, which is for Power BI only or use the Data Gateway which can then be used by multiple services within Office 365.

Once selected you can continue with the base installation by following the core steps. Once done you need to then sign to register your gateway with your tenant.

Clicking the Sign in link will take you to the Office 365 tenant login where you are then able to register your gateway. Setting a recovery key is needed in case you need to ever recover and reinitialize the gateway.

Upon completion you should see the confirmation screen letting you know that the gateway is now ready to use. 

Even though there is a new icon that appears on the server, this does not take you to any application that can be used for configuring data sources. This application link simply takes you back to the configuration screen you used previously.

To configure the gateway, you need to login into the Office 365 Tenant, specifically Power BI, and perform the configuration there. To access the Management Gateway configuration, use the gear icon and select “Manage Gateways”.

Once the page loads it will let you know the current status of the gateway connection. If the status is okay, you can then add data sources. For this I have added the SQL 2016 Server Adventure Works Database that the Gateway is installed on.

To connect to an On-Premise Data Source you can select the type or platform, and then complete the details as needed.

This configuration once saved then becomes available within Power BI and other services allowing a direct connection to the data On-Premises. Once this is created, you can then use the Power BI Desktop to create the datasets, reports and dashboards as needed. Right now the Personal Version of the Gateway is the one that works with Power BI, the Enterprise Gateway can be used with multiple services. As an example if we access PowerApps, the gateway is available.

Now that this is available, we can connect a dataset configured to use the Gateway, making sure that the Server and Connection details match the same as the Gateway.

Once it is connected Office 365 will connect back to the On-Premises Gateway and connect to the data, allowing the tables within the SQL Database to be iterated from within the PowerApps Desktop application.

Selecting a table, now loads the data directly from the On-Premises SQL database through the Data Gateway directly into the Application.

As you can see this brings great power allowing you to consume On-Premises Data into Cloud Services and Mobile Services.

To learn more about consuming On-Premises Data through the Gateway you can follow the steps read details using the links below.

On-Premises Data Gateway

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/#install-the-on-premises-data-gateway