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

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

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;

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.

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

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.

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)

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.

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

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.

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.

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

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

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.

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.

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.

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;

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:

USE [AdventureWorksDW2014]
GO

INSERT INTO [dbo].[FactInternetSales]
           ([ProductKey]
           ,[OrderDateKey]
           ,[DueDateKey]
           ,[ShipDateKey]
           ,[CustomerKey]
           ,[PromotionKey]
           ,[CurrencyKey]
           ,[SalesTerritoryKey]
           ,[SalesOrderNumber]
           ,[SalesOrderLineNumber]
           ,[RevisionNumber]
           ,[OrderQuantity]
           ,[UnitPrice]
           ,[ExtendedAmount]
           ,[UnitPriceDiscountPct]
           ,[DiscountAmount]
           ,[ProductStandardCost]
           ,[TotalProductCost]
           ,[SalesAmount]
           ,[TaxAmt]
           ,[Freight]
           ,[CarrierTrackingNumber]
           ,[CustomerPONumber]
           ,[OrderDate]
           ,[DueDate]
           ,[ShipDate])
     VALUES
           (314,    20140208,    20110220,    20110215,    16657,    1,    6,    9,    'SO4397133',    1,    1,    1,    3578.2700,    3578.2700,    0,    0,    2171.2942,    2171.2942,    3578000.2700,    286.2616,    89.4568,    NULL,    NULL,    '2011-02-08 00:00:00.000',    '2011-02-20 00:00:00.000',    '2011-02-15 00:00:00.000')
GO

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),

And you will see the change there.

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

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.