SharePoint Jon

On Premise Databases with Office 365 and Power BI


This post is meant to be a semi-technical primer into connection On-Premise data to Office 365 and Power BI…

Behold, the On-Premise Data Gateway.  The On-Premise Data Gateway is a free tool developed by Microsoft that allows the connection of multiple on-premise data sources to connect to an Office 365 environment.  This tool allows developers to build applications within Office 365 that reference on-premise data sources.

A word of caution, in this example, the On-Premise Data Gateway will be running on my local machine, in production, the On-Premise Data Gateway should be executed from a machine that is always on and connected to the internet, perhaps a Window Server.

The On-Premise Data Gateway can be downloaded from the following link.


The installation process is essentially a series of “Next”

Step 1

Step 2

Step 3

Step 4

Step 5 – In Step 5 we start the process of entering our Office 365 login credentials.

Step 6

Step 7 – Here we name our gateway and create a “Recovery Key”

Step 8 – Magic!  Our gateway is created and connected to Office 365 and Power BI

In this case, I am using the “AdventureWorks Internet Sales” tabular data model, though other data sources exist.  I am leverage SQL Server 2017 Developer Edition running locally on my laptop where my On-Premise Data Gateway is also running.

First, you must log into your Office 365 instance and have the appropriate access to Power BI.  You can use the same credentials that you are using to create your On-Premise Data Gateway to access Office 365 and Power BI and to create your Power BI dashboard, though this is not required.

We will then need to select the gear at the top of the Office 365 / Power BI and select “Manage Gateways

Here we will select “Add data source to use the gateway”  On this screen we enter the data source of our Analysis Services Tabular model and provide on-premise credentials that can access Analysis Services.

Now we will go back to our workspace home page of Power BI and select the “Get” button under “Databases” under “Import or Connect to Data”

Then select “SQL Server Analysis Services

Then select “Connect

Now we will see a warning and it appears we are unable to connect.

However, we need to select the drop down arrow at the top of the screen launch the download of “Power BI Desktop”

Power BI Desktop installs by selecting “Next” multiple times and accepting the license agreement.

Once installed, you will receive a prompt to create a Power BI account or to sign in with an existing account.

Once inside of Power BI Desktop, we can then select “Get Data” and choose “Analysis Services

On the next screen we enter our Analysis Services Tabular instance and select connect live.  

We then navigate to our Tabular Data Model.

Now we can build a simple dashboard.  I am going to select “Clusterd column chart” with “Sales Amount” as my Axis and “Order Quantity” as my Value.

Then we can select Publish, save our dashboard, and publish the dashboard our workspace within Power BI.

Once this is completed, we will now see a successful message.

Note, that the success screen states that the …report has been configured to use an enterprise gateway.  Enterprise Gateway was the old name for “On-Premise Data Gateway