Azure SQL Data Warehouse and Power BI

Azure SQL Data Warehouse and Power BI

Without a doubt cloud computing is going to change the future of data analytics and data visualisation very significantly. Microsoft Azure SQL Data Warehouse recently released for public preview. Combining Power BI as a powerful data visualisation tool with Azure SQL Data Warehouse will give the users the ability to see data insights of their data stored in Azure Data Warehouse very easily. In this post I explain how to install Azure SQL Data Warehouse and the the way it works with Power BI. Before going any further I’d like to have a look at the Azure SQL Data Warehouse very briefly.

What Is Azure SQL Data Warehouse?

Based on Microsoft documentation a SQL Data Warehouse is

Azure SQL Data Warehouse is an enterprise-class distributed database capable of processing petabyte volumes of relational and non-relational data.

Azure SQL Data Warehouse supports stored procedures, user-defined functions, indexes and collations. It uses columnstore index technology which significantly improves query performance as well as getting you up to 5 times compression in compare with traditional row based indexing.

I leave it to you learn more about Azure SQL Data Warehouse. But, it is important to keep in mind that there are some features like primary keys and foreign keys that are NOT supported in Azure SQL Data Warehouse which affect the way we use Power BI as a data visualisation tool over Azure SQL Data Warehouse. Without primary keys and foreign keys there is no physical relationships between the tables so Power BI service cannot detect any relationships by itself. There is a workaround for this that we can create some SQL views in Azure side to make it work. This can be an expensive solution. The other way is to load the data warehouse into a Power BI Desktop model which can detect the relationships automatically.

Now you know a bit bout Azure SQL Data Warehouse let’s get back to the subject and talk more about Power BI and Azure SQL Data Warehouse.

First things first. You need to have a Microsoft Azure subscription. If you don’t already have it you can use it for a one month trial here. You’ll also get $250 credit. But, remember that if you succeed the $250 in less than a month then you’ll need to pay for it if you want to use it longer.

Install Azure SQL Data Warehouse

After you get your Azure subscription, login to your account and you should see a dashboard like this

Install Azure SQL Data Warehouse

I’m not going to explain the above dashboard as it is out of scope of this article.

  • Click New

Install Azure SQL Data Warehouse 01

  • Click “Data + Storage” then click “SQL Data Ware House”

Install Azure SQL Data Warehouse 02

  • Enter a name for your database
  • Select a performance value

Note: Lager Data Warehouse unit values improve your workload performance with more compute resources. Remember, the larger value the more money you pay. So for our experimental sample I select the minimum possible value which is “100” that will cost me 0.70 USD per hour! YES, per hour.  🙂

  • Click “Server” to configure required settings
  • Click “Create a new server”
  • Enter the server name
  • Enter “Server admin login”
  • Type in a password
  • Confirm the password
  • Select “Location”. The default is “East US 2”

Install Azure SQL Data Warehouse 03

  • So far we configures a new server to host our SQL Data Warehouse
  • Click OK
  • Now we can see the server name and location appear under the “Server” section

Install Azure SQL Data Warehouse 05

  • Click “Select source”
  • You can now select a blank database or select a sample database. I select “Sample”.

Install Azure SQL Data Warehouse 06

  • As you can see “AdventureWorksDW: is appears under the “Select sample” section

Install Azure SQL Data Warehouse 07

  • Click “Resource Group”
  • Click “Create a new resource group”
  • Type a name for your new resource group then click OK

Install Azure SQL Data Warehouse 08

  • Now we configured all needed settings and we’re ready to create a new Azure SQL Data Warehouse service. Click “Create”

Install Azure SQL Data Warehouse 09

  • Now you’ll see a new icon on the dashboard showing the SQL Data Warehouse is creating

Install Azure SQL Data Warehouse 10

Now we successfully created a new Azure SQL Data Warehouse.

Install Azure SQL Data Warehouse 11

But, we are not done yet. We still need to configure firewall settings to be able to connect to the data warehouse from an application like Power BI Desktop, Excel etc.

Azure SQL Data Warehouse Service Firewall Settings

  • Click “All Resources” fro the dashboard
  • Click your Azure SQL Data Warehouse resource from the list

Azure SQL Data Warehouse Firewall Settings

  • Click one of the below for configuring firewall

Azure SQL Data Warehouse Firewall Settings 01

      1. Click “Add client IP”
      2. Make sure “Allow access to Azure service” is switched to “ON”
      3. You should see your IP address added to the list
      4. Click “Save”

Azure SQL Data Warehouse Firewall Settings 02

Note: You can add an IP range instead of just one IP address. You just need to enter a “Start IP” and an “End IP” address then what ever devices exist in that IP range will be able to access the Azure SQL Data Warehouse service.

Azure SQL Data Warehouse Firewall Settings 03

To be able to connect from a client application we need to have the server name. To find the server name you can click on the Azure SQL Data Warehouse from the dashboard.

Azure SQL Data Warehouse

Then click on the copy icon under “Server Name”.

Azure SQL Data Warehouse Server Name

If you don’t see your instance of Azure SQL Data Warehouse on the dashboard:

  • Click “SQL Databases”
  • Select the database from the list
  • Copy the server name

Azure SQL Data Warehouse Server Name 01

Connect to Power BI directly from Azure SQL Data Warehouse

As you might already noticed there is a “Open In PowerBI” available on top your instance of Azure SQL Data Warehouse.

  • Click on the “Open In PowerBI” button

Azure SQL Data Warehouse and Power BI

  • It redirects you to Power BI Website, opens “Connect to Azure SQL Data Warehouse” connections and automatically fills server and database names. Click Next.

Azure SQL Data Warehouse and Power BI 01

  • Enter the admin user name and password you created previously in Azure SQL Data Warehouse then click “Sign In”:

Azure SQL Data Warehouse and Power BI 02

Now we connected our Azure SQL Data Warehouse to Power BI successfully.

Create a Sample Report on Power BI Website

  • Click the new Azure Data Warehouse dataset from Datasets pane from the Power BI Website to create a new report

Azure SQL Data Warehouse and Power BI 03

  • Expand “FactInternetSales”
  • Select “Sales Amount”
  • Expand “DimProductCategory”
  • Select “EnglishProductCategoryName”

Azure SQL Data Warehouse and Power BI 04

OOPS! It looks nasty.

Remember that I mentioned before that Azure SQL Data Warehouse does NOT support primary keys and foreign keys. So Power BI web service cannot auto detect any relationships. Therefore, it shows the same Sales Amount for all Product Categories. Sadly, editing relationships is NOT available in Power BI Website. So at this stage, it might not be a good idea to connect Power BI Website to an instance of Azure SQL Data Warehouse directly. I posted an idea to add the ability to create or edit relationships in Power BI Website. If you think like me and would like to see this feature in the future releases of Power BI please vote for the idea. Smile

There is workaround that you can still use the Power BI web service directly connected to your Azure SQL Data Warehouse. You can create SQL views on Azure side and create your visualisations on top of the views.

Schedule Refresh

Direct connection to Azure SQL Data Warehouse makes the dataset to be always up-to-date. To see if this is really the case do the following simple steps:

  • Click open menu ellipsis button on the right side of the dataset
  • As you can see it says “This dataset connects to a source with direct connect which is always up-to-date. You do not have to schedule a refresh on this dataset.”  so we don’t need to do anything as the dataset is always up-to-date.This dataset connects to a source with direct connect which is always up-to-date. You do not have to schedule a refresh on this dataset.

Azure SQL Data Warehouse and Power BI 05

I inserted some data into FactInternetSales and the dataset got updated immediately. It’s really awesome isn’t it?

Let’s continue and see how Azure SQL Data Warehouse works with Power BI Desktop.

Azure SQL Data Warehouse and Power BI Desktop

  • Open Power BI Desktop
  • Click “Microsoft Azure Data Warehouse” then click “Connect”

Azure SQL Data Warehouse and Power BI 06

Azure SQL Data Warehouse and Power BI 07

  • Click “Database” then enter the user name and password for your Azure SQL Data Warehouse then click “Connect”

Azure SQL Data Warehouse and Power BI 08

  • Expand the database then select all then click “Load”

Azure SQL Data Warehouse and Power BI 09

  • Click “Relationships” view and scroll right

Azure SQL Data Warehouse and Power BI 10

  • As you can see Power BI Desktop detected lots of relationships automatically. But, there are still some missing relationships. For instance no relationships detected between FactInternetSales and DimDate. However, we might not experience the same in a real world project. So I leave it to you to create the missing relationships I the AdventjureWorksDW data model for more experiments.
  • Click Report view
  • Expand FactInternetSales
  • Tick SalesAmount
  • Expand DimProductCategory
  • Drag and drop EnglishProductCategoryName field into Axis
  • Expand DimProductSubCategory
  • Drag and drop EnglishProductSubCategoryName field into Axis right below the EnglishProductCategoryName field to add Drill down action to the report

Azure SQL Data Warehouse and Power BI 11

We successfully created a simple report on Power BI Desktop on top of Azure SQL Data Warehouse.

  • Click Publish from the ribbon.
  • Now jump online and login to your Power BI account
  • Find the new report you just published now. Everything looks to be fine as expected.

Azure SQL Data Warehouse and Power BI 12

Note: You need to have a Power BI Pro account to be able to use an Azure SQL Data Warehouse dataset in Power BI service.

Schedule Refresh

In this scenario we connected to the Azure SQL Data Warehouse from Power BI Desktop which means the connection is NOT a direct connection. Therefore, we need to configure “Schedule Refresh” on Power BI website.

  • Click on open menu ellipsis button on the right side of your data set
  • Click “Schedule Refresh”

Azure SQL Data Warehouse and Power BI 13

  • Expand “Data Source Credentials”
  • Click “Edit Credentials”
  • Select “Basic” from “Authentication Method” drop down
  • Enter your valid Azure “Username” and “Password”
  • Click “Sign In”

Azure SQL Data Warehouse and Power BI 14

  • Expand “Schedule Refresh”
  • Switch “Keep your data up-to-date” button to ON
  • Do your desired schedule settings then click “Apply”
  • Expand “Featured Q&A Questions”. This is a new feature added to Power BI. What ever you type here will be ready to use in Power BI dashboard.

Azure SQL Data Warehouse and Power BI 15

Create a New Dashboard

  • Open the report you published from Power BI Desktop
  • Click “Pin Visual”

Azure SQL Data Warehouse and Power BI 16

  • Click “New Dashboard” from the “Pin to Dashboard” page
  • Type a name for the new dashboard then click “Pin”

Azure SQL Data Warehouse and Power BI 17

  • Open the new dashboard from “Dashboards” pane
  • Click on “Ask a question about the data on this dashboard”
  • The first question would be the featured question we added before

Azure SQL Data Warehouse and Power BI 18

  • Click on the question and here you go, your pie chart is ready to use
  • You can pin it to the dashboard

Azure SQL Data Warehouse and Power BI 19

  • On the “Pin to Dashboard” page click “Pin”

Azure SQL Data Warehouse and Power BI 20

  • Back to the dashboard and you should see the new pie chart

Azure SQL Data Warehouse and Power BI 21

Now we are done.

Some Facts About Azure SQL Data Warehouse and Power BI

So far we created a useful dashboard in Power BI on top of Azure SQL Data Warehouse. We can make lots of other data visualisations and reports in Power BI Desktop and publish them to powerbi.com. Then we can create lots of other dashboards there.

But, is that really it? What happens when we want to do a real world project? Okay. Let’s have a look at some realities about the current version of Azure SQL Data Warehouse in combine with the current version of Power BI. I know that it is a preview version, but, the following points are valid for the current version:

  • As you saw before there is a “Open In PowerBI” button available to directly connect an Azure SQL Data Warehouse to Power BI Website. But, what is the point of having such a feature when Power BI Website does NOT automatically detect relationships? Besides, there is no edit relationships feature available in Power BI website, so at the moment there is no way we can fix the issue with current available features. As I pointed before, we can create some views on Azure side, but, I don’t think it is realistic. I newly heard that supporting auto detect relationships on Power BI website for Azure SQL Data Warehouse is a work in progress, but, until it’s not available we cannot use Power BI website and have the benefit of Direct Connect to Azure SQL Data Warehouse. When a dataset connects to a source with direct connect we don’t have to configure a schedule refresh and the dataset in Power BI side would be always up-to-date.

image

  • As mentioned before Power BI Desktop can automatically detect the relationships of an Azure SQL Data Warehouse. But, how precise it is? When we connected to our Adventure Works sample we saw that Power BI Desktop detected some relationships. But, I found out some problems with creating new relationships or edit the existing relationships. Try the following examples:
      1. As you might noticed, there is no relationship detected between FactInternetSales and DimDate. Try to define a relationship using OrderDateKey from FactInternetSales and DateKey from DimDate. image As you can see, the relationship is inactive. If you want to activate the relationship you’ll get the following message: “There is already an active relationship between the two tables. You can deactivate the existing relationship, and then  make this relationship active.” Actually this is absolutely alright. We all know that Power BI model doesn’t support multiple relationships between two tables. But, the problem is that it would be a pain to find out which relationship we need to deactivate first. As I stated in one of my previous posts we can use USERELATIONSHIP function to control the relationships, but, it would be time consuming if want to write DAX codes for each relationship.
      2. There is a relationship detected between FactSalesQuota and DimDate. Deactivate the relationship. Power BI Desktop Edit RelationshipsNow try to activate it again and see what happens. Power BI Desktop Edit Relationships 01 Oops! You’re not allowed to do that! Lets try another one.  Deactivate the relationship between FactInternetSales and DimProduct. Power BI Desktop Edit Relationships 02 Now try to activate it, the sale thing happens.Power BI Desktop Edit Relationships 03
  • UPDATE: I noticed that Q&A is NOT available for Azure SQL Data Warehouse direct connect. So when you create a new dashboard the Q&A is NOT there.

Power Q&A

As a result, despite I believe that the combination of Azure SQL Data Warehouse and Power BI would make a perfect solution for enterprise level projects in near future, I think the current versions are not mature enough to support a real data visualisation project on top of an enterprise-class distributed database.

I would be happy to have your opinions and comments.

5 thoughts on “Azure SQL Data Warehouse and Power BI”

  1. Hi Soheil! I am trying to integrate one of our vendor’s reporting so I can get management the details they are looking for, and have been trying to teach myself Power BI and database essentials. Now one of our vendors has released an API, and I’m wondering how best to integrate it with Power BI. I came across your website and was wondering if you had a couple of minutes to chat or answer some questions. If not, that is ok too, I appreciate the knowledge you have already posted!!

    Thank you!

    Alissa Wright

    1. Hi Alissa,

      Welcome to biinsight.com.
      To load data from website API’s into a Power BI model, open Power BI Desktop and click on “Web” from “Get Data”.
      After you loaded data you can easily create the reports and dashboard you need.
      I’ll be happy to consult you if need so I sent you an email.
      You can reply me back to discuss more in detail if necessary.

      Cheers

  2. Hi Sobheil,

    Great detailed analysis of Power BI & Azure SQL DW. Do you have any idea if we can apply row-level security in this scenario?

Leave a Reply