POWER BI – Connecting to an On-Premise SQL Server Database

Posted By Posted by: Peter Williams on July 5, 2016

NOTES:

  1. A POWER BI PRO licence is required to connect to On-Premise Data. For details, please refer to https://powerbi.microsoft.com/en-us/documentation/powerbi-power-bi-pro-content-what-is-it/
  2. For security related questions, refer to the Power BI whitepaper http://download.microsoft.com/download/4/8/C/48CFCF8A-2025-4B97-B249-7B505E26E7ED/Power%20BI%20Security%20Whitepaper.docx

 

Installing Data Management Gateway on computer on the same network as the SQL Server

I have logged into the server I wish to install the Data Management Gateway on. This server is not the SQL Server that I wish to make a connection to, as will be used for the Power BI reports in this blog. However, it is on the same local network.

Sign into https://app.powerbi.com and click on the download link and then the Power BI Gateway menu option.

Img-1

 

Click on the Download button.

Img-2Execute the installation file.

 

After a short while the installation finished. Click on the Sign in to Power Bi button.

 

Click on Next when prompted to either Configure a new gateway or Restore existing gateway.

Img-5

 

 

I had already configured a gateway on a different server. This will be an additional gateway.

Define the name of this gateway, supply a Recovery key and click on Configure. Obviously, keep this key stored in a safe place, as it is needed if the gateway ever needs to be restored.

Additional Note: Two or more gateways can be configured with connections to the same SQL data source. Such a setup provides a redundancy mechanism. If Power BI cannot connect to the first gateway, it will attempt to use the other gateway servers.

 

The setup was successful. As suggested, let’s now Add a data Source. Click on Close & Add data sources.

Img-7

 

Establishing a Data Source Connection to On-Premise SQL Server

Choose the SQL Server Data Source type, and then provide the rest of the necessary details and then click on the Add button.

Img-8

 

It has successfully made a connection to my specified SQL data source.
As per the suggestion, I’m going to share this connection with my colleagues.

Img-9

 

Type in the email addresses of colleagues that have a Power BI subscription in your organization.

Img-10

Install Power BI Desktop on a computer on the same network as the SQL Server

Let’s download Power BI Desktop, which is the tool that can be used to create Power BI reports. In my case, I’m downloading it onto the server, as my PC isn’t on the same local network as the SQL server.

From within the Power BI site, click on the Power BI Desktop menu option from the download section.

Img-11

 

Execute the installation package.

Img-12aImg-12

 

Creating a Report using Power BI Desktop and an On-Premise SQL data source

Open Power BI Desktop application.
Click on Get Data.

Img-13


Select SQL Server Database and click Connect.

Img-14

Enter the SQL Server name and Database Name. This must be exactly the same as specified when creating the Gateway Data source earlier.

NOTE: the “Advanced options” section allows for a SQL query to be defined instead of selecting a Database. Choosing this option will disable “Direct Query (aka Live Connection)” option later in this wizard.

Click OK.

Img-15

 

Enter access credentials and click on Connect.
Again, the credentials entered must exactly match those supplied when configuring the Gateway Data source connection earlier.

Img-16

 

Click OK.

Img-17

 

I have chosen both the MSP_EPMProject_Userview and MSP_EPMTask_UserView views.

Click Load.

Img-18

 

Choose Import and select OK.
Note: Choosing the DirectQuery option means that data is refreshed on runtime of the Power BI report. Choosing the Import option will load the data into the Power BI “Cloud”, whereby a data refresh can be scheduled. In either scenario, data is uploaded to the Power BI cloud at some point. Data associated to DirectQuery source will be purged from “the cloud” after an hour on inactive use.

Img-19

 

Notice both “views” of data are being uploaded.
Note: all of the above steps in this section “Creating a Report using Power BI Desktop and an On-Premise SQL data source” can be repeated to add new tables to the data source for this report.

Img-20After the data is uploaded, click on the Manage Relationships toolbar button.

Power BI Desktop has auto detected the relationship between these two views.
Click Close.

Img-21

I’m adding a simple Pie Chart to my Report. In the visualizations section, click on the Pie Chart.

Img-22

Img-23

 

I’m going to add the ProjectName field to the Values section.

Img-24

 

Img-25

 

I have now added the column “Strategy Owner” to the Details section of the visualization.

Img-26

Img-27

 

I’m going to now publish this report to Power BI. Click on the Publish toolbar button.

Img-28

 

Img-29Img-30Img-31

Success.

Note: if it hadn’t found an existing Gateway data source connection, the publishing of the report would have failed.

Let’s view the report in the web browser.
Click on the “Open … in Power BI” link.

Img-32

Img-33

 

Click on the “hamburger” menu.

Img-34

 

Let’s setup a scheduled refresh of this dataset.
Click on the ellipse beside the dataset and click on the Schedule Refresh.

Img-35

 

Confirm the use of the enterprise gateway and then click Apply.

Img-36

 

Expand the Schedule Refresh section.
Enable a daily refresh by changing the “Keep your data up to date” field to Yes.
Additional refreshes can be created by clicking on the “Add another time” link.

Img-37

 

Let’s review the details of the Gateway within the Power BI website.
Click on the cog wheel and select Manage gateways from the menu.

Img-38

I can remove and edit the details of the Gateway as needed in this section.

Img-39

 

 


Blog Posted In Blog Posted In: Blog, How to
Blog Posted In 

Leave a Reply

Your email address will not be published.