Power BI and Power Query can connect to files such as Excel, CSV, text files and on-premises databases such as SQL Server, Oracle, MySQL. Power BI can connect to many data sources on cloud such as Azure SQL Database, Azure SQL Data Warehouse and etc. In this part you will learn how to connect from Power BI Desktop to Azure SQL Database. There is also a way of connecting to Azure SQL Database with a direct connection from Power BI website which will be explored in this section as well. You will also learn how you can schedule your report to refresh data loaded from Azure SQL DB. So In general you should expect to learn everything related to Power BI relation to Azure SQL Database in this section.

In this section you will learn;

• How to connect from Power BI Desktop to Azure SQL Database
• Schedule Power BI for refreshing data from Azure SQL Database
• Direct Connection to Azure SQL Database from Power BI Website

Preparation

For this section you need an Azure SQL Database. I use AdveutureWorksLT example, this is the sample database in Azure SQL Database templates that you can easily install and configure. If you have this database set up on Azure then you can skip this step. For creating an Azure SQL Database for AdventureWorksLT database follow below steps;

Do you need an Azure subscription to run this experiment?

Yes, but don’t worry if you don’t have it. You can have an Azure subscription free for 25 days with 200$ free credit for you to use, just use the trial version. You can start trial version by following this URL: https://azure.microsoft.com/en-us/pricing/free-trial/

After setting up your Azure Account, go to Azure Portal.  I have to mention here that there are two versions of management portal for Azure. the new Azure portal which is tablet friendly, with newer and better look and feel, and the old management portal. screenshots and steps described in this example all has been done in the new Azure Portal. You can go to Azure portal by using this URL: https://portal.azure.com

You can manage your Azure services in the management portal by creating new services, editing existing services. Talking about Azure services is out of scope for this example and you need to read books on that topic. However for this example let’s smoothly continue steps to create an Azure SQL Database. Click on New on the top left side and then  under Data + Storage choose SQL Database

In the SQL Database Create pane, name the database as AdventureWorks LT. You have to choose the server also. Server is like a SQL Server instance that this database will be hosted on that. You can choose from an existing server or you can create a new Server.

As you can see in screenshot above, after going to Configure required Settings for the Server, you can choose to create new server (numbered 3), which will redirect you to a new pane for setting up the server (numbered 4). or you can choose an existing server (numbered 5).

After setting up the server, you have to select source for the database. for this example choose Sample. after choosing sample you will see the Select Sample option below appears.

Choose the sample as AdventureWorksLT [V12], and then type in the sever admin login and password (you have defined that when you set up the Server)

You have to also choose a pricing tier, and resource group. for pricing tier just use one of the tiers (you can better choose yourself), and then for the resource group you can choose an existing one of create a new. A resource group is a grouping for Azure services, you can have a resource group and add all related azure services under that. for example you can have a resource group for Power BI Online Book and create all examples of this book under that. Please note that the resource group name should not have spaces in the name, but it can have dashes.

After all the configuration click on Create so the SQL DB creates. the tick on check box for pin to Startboard will bring the SQL DB on the first welcome page (start board) of the Azure Portal. It may take a bit time for the database to be created. After completion of creating database process, you will be redirected to database page in azure portal (if you didn’t, then click on the AdventureWorks LT database on start board to go to its pane). screenshot below is showing the database created

Now you are all set, example database is ready to be used in Power BI.

Get Data From Azure SQL Database

You can connect to Azure SQL Database from Power BI Desktop or Power Query for Excel. Both methods works same. Let’s go through the connection from Power BI Desktop. Before starting steps I have to mention that Power BI Desktop connection to Azure SQL Database is an off-line connection. Off-line connection here means the data from Azure SQL Database will be loaded into the Power BI model and then reports will use the data in the model, this disconnected way of connection is what I call off-line. The off-line connection to Azure SQL DB can be scheduled in the Power BI website to be refreshed to populated updated data from the database. In this section we will create the connection from Power BI Desktop to Azure SQL DB, and in the next section following you will learn how to schedule the data refresh.

Open the Power BI Desktop and Get Data from Azure SQL Database

In Power Query for Excel you can also follow the path mentioned in screenshot below

You need to enter the server name in SQL Server Database dialog box. Remember that you’ve set up the server when you created Azure SQL DB. if you don’t know what is the server for your database, simply find it through Azure portal under the Azure SQL DB pane;

Type in the server name in SQL Server Database dialog box in Power BI Desktop. and type in the database as AdventureWorks LT. leave the SQL statement as is. then press OK

It is very likely that you get to the window that says Unable to Connect. This window is saying that Power BI Desktop cannot connect to Azure SQL DB and the reason is that the Azure SQL Server didn’t allowed your IP address to pass through its firewall. I have to mention that Azure SQL Server by default doesn’t allow external IP addresses to connect to it. if you want to connect to any databases on Azure SQL Server you have to allow the IP of that machine to pass through. This is not your internal network IP, this is the IP that your internet connection has. You can find the IP easily. It is mentioned in Unable to Connect error message below!

We encountered an error while trying to connect to “…..”. Details: Microsoft SQL: Cannot open server’…’ requested by the login. Client with IP address’210.246.15.145‘ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.”

So Let’s add the IP in firewall pass list. Go to Azure Portal again. if you closed it, open it again, go to Browse All, then Choose SQL Databases, then choose AdventureWorksLT database in the list, and then click on the server name of it to open the Azure SQL Server administration pane

Click on Show firewall settings. In the Firewall Settings pane, enter the new IP as a rule, and then save it.

Now you can try again to connect from Power BI Desktop (if you get that error again, just wait for few minutes and try again. Sometimes it takes few minutes for changes to take effect). After a successful connection you should be able to see Navigator dialog box with the structure of AdventureWorksLT database

As you can see in the navigator all views, tables and functions will be listed. You can choose multiple objects and then continue editing them in the Edit Queries or Power Query Editor window. For this example I’ve chosen these tables: Customer, Product, ProductCategory, ProductModel, SalesOrderDetail, and SalesOrderHeader.

You can then choose these tables with their fields to be used for building a report without any modification in Power Query or Data tab. You can even see that Power BI and Power Query understand the relationship in Azure SQL Database and load the same relationship in the Power BI model.

This section is still one of the early chapters of Power BI online book, and I don’t want to discuss visualization and modelling. However for this example I’ve build a simple chart, the chart is a clustered column chart with Color (from Product table) as Axis, and SalesPerson (from Customer) as Legend, and OrderQty (from SalesOderDetail) as Value.

The chart is simple, but still revealing something interesting. Jae0 did the most sales, however color wise each sales person did the best in specific color. shu0 was best at Yellow. lina3 at Silver, and Jae0 at Black. Now you can publish your report into Power BI website with the Publish menu option. after publishing the report you would be able to see that under your Power BI account in the website.

Schedule Data Refresh

In Power BI website you can set up scheduled data refresh for data sets, but not all data sets supports this feature. Fortunately Azure SQL Database supports it. To find out list of all data sets that supports data refresh read this link. To schedule a refresh in Power BI website, under Datasets click on ellipsis besides the data source that you want. and then choose Schedule Refresh.

Set the Data Source Credentials for Azure SQL Database

And then you can schedule refresh. You can choose the frequency to be daily or weekly. and you can add multiple times on the day under that.

If you have a scheduled refresh set up for a while then you can see the history of refresh as well

After setting up the schedule refresh, you can see the latest refresh and the next refresh information easily with ellipsis button on the data set

Direct Connection to Azure SQL Database from Power BI Website

So far you’ve learned how to connect from Power BI Desktop or Power Query to Azure SQL database. However the connection in that way is off-line and you need to set up a schedule refresh to keep data up-to-date. Fortunately there is another way of connection which is Direct Connection. Direct connection won’t load data into Power BI model, it directly bring data into the report, and you won’t need to schedule refresh anymore, because data is always up-to-date. There will be however a lag for loading the report depends on the data and volume required loaded in the report.

You can set Direct connection only from Power BI website at the moment. And this is one of Power BI Pro features, so you need to buy the pro plan (which costs 9.99$ per user per month at the moment). To set a direct connection, click on Get Data in Power BI website. you will be redirected to Get Data Page

Click on the Get option from Databases. You can see that some databases are supported through this type of connection; Azure SQL Database, Azure SQL Data Warehouse, SQL Server Analysis Services, and Spark on Azure HDInsight. Click on Azure SQL Database and then Connect.

Set the connection information such as server, database name, and user and password

After creating the connection you will see Azure SQL Database in your dashboard and also the dataset in list of datasets.

by click on any of these items you will be redirected to online Power BI report designer. You can see that all tables and views now are listed in the Fields pane, and ready for you to build reports from them.

Summary

In this lesson you’ve learned how to connection from Power BI Desktop or Power Query in Excel to Azure SQL Database. You’ve also learned some basics about Azure SQL Database settings in Azure Portal. Then you’ve learned that you need to set firewall in Azure Portal for server to pass your IP for connections. You then learned how you can set scheduled refresh for the data in Power BI website. At the last section you’ve learned that you can create direct connection from Power BI website to Azure SQL Database that doesn’t load data into a model and works online with the data. In next sections of this chapter you will learn about other data sources that you can connect from Power BI.

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.