Step by Step Walk through: On-Premises Live SQL Server Connection with Power BI Enterprise Gateway

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-06-30_23h46_29

I have mentioned previously that one of the ways to step beyond Power BI 10GB limitation is to use Power BI with Live Connection. In this post I will explain in a full step by step walk through how to use SQL Server as a DirectQuery or Live source for Power BI, and how to set up Enterprise Gateway to work with that. If you like to learn more about Power BI, read Power BI online book; Power BI from Rookie to Rock Star. There are many resources explaining Power BI live connection, or how to use Enterprise Gateway. In this post I’ll explain all in one; set up Enterprise Gateway and create a live Power BI report from On-premises SQL Server.

Prerequisite

For this example I will be using AdventureWorksDW2014 sample database. You can download this sample database from URL here. and restore the database backup. here is the link to backup file.

Install and Configure Enterprise Gateway

Purpose of gateways in Power BI is to create connection from Power BI cloud service to on-premises data sources. There are two types of gateways; Enterprise, and Personal. Naming of these gateways are a bit misleading. Personal doesn’t mean you have to install gateway on your laptop only, and Enterprise doesn’t mean only for organizations. There are more differences which I will talk in different post. In general Enterprise is built for more live connections specially to Analysis Services, and Personal is more for importing data from many data sources. Let’s leave details of that for now. For this example we will be using Enterprise Gateway.

You can download latest version of Enterprise Gateway from PowerBI.Microsoft.Com website under downloads

2016-06-30_22h33_56

Then from gateways download page, choose Enterprise gateway, and download it.

2016-06-30_22h35_14

Installation of Enterprise Gateway is just following a wizard, so continue that till end of the wizard where it asks for Power BI login. Sign in to your Power BI account;

2016-06-30_22h38_39

After sign in, configure the Power BI Gateway with a name, and key, and then you should be good to go. If you see green checkbox and Connected means your configuration was successful.

2016-06-30_22h41_44

As you can see after successful configuration, you can close the gateway (don’t worry it is running behind the scene as a service), and add a data source to this gateway in Power BI Service.

Or alternatively you can directly go to Power BI website, after login go to setting menu option and choose Manage Gateways

2016-06-30_22h43_50

In Gateway management window, you can see all gateways you have set up. My gateway here is named BIRADACAD, and I can see that it is successfully connected.

2016-06-30_22h45_14

Create Data Source

Now Let’s create Data Sources. You might think that one gateway is enough for connecting to all data sources in a domain. That is absolutely right, however you still need to add a data source under that gateway per each source. each source can be a SQL Server database, Analysis Services database and etc. For this example we are building a data source for SQL Server on premises. Before going through this step; I have installed AdventureWorksDW2014 on my SQL Server, and want to connect to it. If you don’t have this database, follow instruction in prerequisite section.

For creating a data source, click on Add Data Source in manage gateways window (you have to select the right gateway first)

2016-06-30_22h52_21

Then enter details for the data source. I named this data source as AdventureWorksDW2014, I enter my server name, and database name. then I use Windows authentication with my domain user <domain>\username and the password. You should see a successful message after clicking on Apply.

2016-06-30_22h54_29

Now that we have data source ready, let’s build a very simple Power BI report and publish it to the service.

Building Sample Power BI Report

Open Power BI Desktop, and Get Data from SQL Server Database

2016-06-30_22h59_49

Enter server name, and database name, and choose the connection type as DirectQuery. DirectQuery connection is a live connection to the source. this type of connection is not available for all data sources. Fortunately it is available for SQL Server. Live Connection won’t import data into Power BI. It will only bring metadata, and the report every time for refreshing the content will query the data source directly.

2016-06-30_23h00_59

You will be then asked for credentials to connect to the database if it is for the first time. use your windows authentication there, and then you will be redirected to Navigator window. For this example let’s only select DimDate, and FactInternetSales. Click on Load.

2016-06-30_23h13_56

As you can see Live connection will be created instead of importing data

2016-06-30_23h15_50

You can also see in the down right hand side of the Power BI that it mentioned the connection is DirectQuery: Enabled

2016-06-30_23h18_15

Now go to Relationship tab. Here you can see DimDate and FactInternetSales are connected to each other with three relations. Because there are three date fields in FactInternetSales; DueDate, OrderDate, and ShipDate. For this example remove relationship for DueDate and ShipDate, and only keep the relationship for OrderDate, and change it to be Active Relationship.

2016-06-30_23h39_40

Now build a very simple report with a column chart, that has Sales Amount (from FactInternetSales) as the Value, and CalendarYear (from DimDate) as Axis.

2016-06-30_23h43_18

This is all we want to build for this example. One chart is enough to show the functionality. Save the report as something, for example; SQL DB Live Example, and Publish it. When you publish the report you will see a message saying that publishing succeeded, The Published report has been configured to use an enterprise gateway. This is good message showing that report’s data source, and enterprise gateway data source are able to find each other :) Nothing more is required then.

2016-06-30_23h46_29

Test the Live Report

To check the Enterprise gateway connection to on-premises SQL Server, the last step is to browse the report from Power BI website. You should be able to see the column chart. If you get any message regarding configuration issues, check enterprise gateway, and data source configurations to be set up correctly. Here is the report refreshed successfully;

2016-06-30_23h49_04

Live Refresh

This report is live which means you don’t need to schedule a refresh for the data set. report directly connected to the data source, and with every change in data source there will be a change in the report. Let’s do a small change by adding a record in fact table which increase sales amount of year 2014 about $3M. here is the script for the change:

Right after adding the record, without any delay, go and refresh the report (Not even the data set, just refresh the report in Power BI website),

2016-06-30_23h58_23

And you will see the change there.

2016-06-30_23h59_16

As you can see the column height for year 2014 changed now. Fantastic live report, isn’t it?

Advantages of Live Connection

As I’ve mentioned previously, one of the biggest advantages of live connection is that you step beyond limitation of Power BI file size which is 10GB. You can have databases with Terabytes of data, and create live connection for it. That said, you have to take care of performance of your database, because live queries are expensive (performance-wise I mean). As an example have a look here at two queries on same size of data, but different response time (one of them only performance tuned a bit with clustered column-store index).

DirectQuery or Live SQL Server On-Premises Limitations

With live connection you won’t have the Modeling (or Data) tab in Power BI, you can’t create DAX calculated columns or measures, You cannot do formatting for columns. However you have Power Query, and all transformations in Power Query will be translated into T-SQL when queries sent to database.

You cannot have multiple data sources in your Power BI report. Live connection only works with one database.

 

Save

Save

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">