Azure SQL Knowledge Warehouse and Energy BI


Azure SQL Data Warehouse and Power BI

Undoubtedly cloud computing goes to alter the way forward for knowledge analytics and knowledge visualisation very considerably. Microsoft Azure SQL Knowledge Warehouse lately launched for public preview. Combining Energy BI as a robust knowledge visualisation instrument with Azure SQL Knowledge Warehouse will give the customers the flexibility to see knowledge insights of their knowledge saved in Azure Knowledge Warehouse very simply. On this publish I clarify the way to set up Azure SQL Knowledge Warehouse and the the way in which it really works with Energy BI. Earlier than going any additional I’d like to take a look on the Azure SQL Knowledge Warehouse very briefly.

Based mostly on Microsoft documentation a SQL Knowledge Warehouse is

Azure SQL Knowledge Warehouse is an enterprise-class distributed database able to processing petabyte volumes of relational and non-relational knowledge.

Azure SQL Knowledge Warehouse helps saved procedures, user-defined capabilities, indexes and collations. It makes use of columnstore index know-how which considerably improves question efficiency in addition to getting you as much as 5 instances compression in examine with conventional row based mostly indexing.

I depart it to you study extra about Azure SQL Knowledge Warehouse. However, you will need to remember that there are some options like major keys and overseas keys which are NOT supported in Azure SQL Knowledge Warehouse which have an effect on the way in which we use Energy BI as an information visualisation instrument over Azure SQL Knowledge Warehouse. With out major keys and overseas keys there isn’t a bodily relationships between the tables so Energy BI service can not detect any relationships by itself. There’s a workaround for this that we will create some SQL views in Azure facet to make it work. This may be an costly answer. The opposite method is to load the info warehouse right into a Energy BI Desktop mannequin which might detect the relationships robotically.

Now you already know a bit bout Azure SQL Knowledge Warehouse let’s get again to the topic and speak extra about Energy BI and Azure SQL Knowledge Warehouse.

First issues first. You must have a Microsoft Azure subscription. Should you don’t have already got it you should use it for a one month trial right here. You’ll additionally get $250 credit score. However, do not forget that should you succeed the $250 in lower than a month you then’ll must pay for it if you wish to use it longer.

After you get your Azure subscription, login to your account and it’s best to see a dashboard like this

Install Azure SQL Data Warehouse

I’m not going to elucidate the above dashboard as it’s out of scope of this text.

Install Azure SQL Data Warehouse 01

Install Azure SQL Data Warehouse 02

Word: Lager Knowledge Warehouse unit values enhance your workload efficiency with extra compute assets. Keep in mind, the bigger worth the more cash you pay. So for our experimental pattern I choose the minimal potential worth which is “100” that may value me 0.70 USD per hour! YES, per hour.  🙂

  • Click on “Server” to configure required settings

  • Click on “Create a brand new server”

  • Enter the server identify

  • Enter “Server admin login”

  • Sort in a password

  • Affirm the password

  • Choose “Location”. The default is “East US 2”

Install Azure SQL Data Warehouse 03

Install Azure SQL Data Warehouse 05

Install Azure SQL Data Warehouse 06

Install Azure SQL Data Warehouse 07

  • Click on “Useful resource Group”

  • Click on “Create a brand new useful resource group”

  • Sort a reputation in your new useful resource group then click on OK

Install Azure SQL Data Warehouse 08

Install Azure SQL Data Warehouse 09

Install Azure SQL Data Warehouse 10

Now we efficiently created a brand new Azure SQL Knowledge Warehouse.

Install Azure SQL Data Warehouse 11

However, we’re not finished but. We nonetheless must configure firewall settings to have the ability to connect with the info warehouse from an utility like Energy BI Desktop, Excel and many others.

Azure SQL Knowledge Warehouse Service Firewall Settings

Azure SQL Data Warehouse Firewall Settings

Azure SQL Data Warehouse Firewall Settings 01

      1. Click on “Add consumer IP”

      2. Be certain “Enable entry to Azure service” is switched to “ON”

      3. You must see your IP tackle added to the checklist

      4. Click on “Save”

Azure SQL Data Warehouse Firewall Settings 02

Word: You’ll be able to add an IP vary as a substitute of only one IP tackle. You simply must enter a “Begin IP” and an “Finish IP” tackle then what ever units exist in that IP vary will be capable of entry the Azure SQL Knowledge Warehouse service.

Azure SQL Data Warehouse Firewall Settings 03

To have the ability to join from a consumer utility we have to have the server identify. To search out the server identify you possibly can click on on the Azure SQL Knowledge Warehouse from the dashboard.

Azure SQL Data Warehouse

Then click on on the copy icon underneath “Server Identify”.

Azure SQL Data Warehouse Server Name

Should you don’t see your occasion of Azure SQL Knowledge Warehouse on the dashboard:

Azure SQL Data Warehouse Server Name 01

As you may already seen there’s a “Open In PowerBI” obtainable on prime your occasion of Azure SQL Knowledge Warehouse.

Azure SQL Data Warehouse and Power BI

Azure SQL Data Warehouse and Power BI 01

Azure SQL Data Warehouse and Power BI 02

Now we related our Azure SQL Knowledge Warehouse to Energy BI efficiently.

Create a Pattern Report on Energy BI Web site

Azure SQL Data Warehouse and Power BI 03

  • Increase “FactInternetSales”

  • Choose “Gross sales Quantity”

  • Increase “DimProductCategory”

  • Choose “EnglishProductCategoryName”

Azure SQL Data Warehouse and Power BI 04

OOPS! It seems to be nasty.

Do not forget that I discussed earlier than that Azure SQL Knowledge Warehouse does NOT assist major keys and overseas keys. So Energy BI net service can not auto detect any relationships. Subsequently, it exhibits the identical Gross sales Quantity for all Product Classes. Sadly, enhancing relationships is NOT obtainable in Energy BI Web site. So at this stage, it may not be a good suggestion to attach Energy BI Web site to an occasion of Azure SQL Knowledge Warehouse straight. I posted an thought so as to add the flexibility to create or edit relationships in Energy BI Web site. Should you suppose like me and want to see this function sooner or later releases of Energy BI please vote for the concept. Smile

There’s workaround which you can nonetheless use the Energy BI net service straight related to your Azure SQL Knowledge Warehouse. You’ll be able to create SQL views on Azure facet and create your visualisations on prime of the views.

Schedule Refresh

Direct connection to Azure SQL Knowledge Warehouse makes the dataset to be all the time up-to-date. To see if that is actually the case do the next easy steps:

  • Click on open menu ellipsis button on the suitable facet of the dataset

  • As you possibly can see it says “This dataset connects to a supply with direct join which is all the time up-to-date. You do not need to schedule a refresh on this dataset.”  so we don’t must do something because the dataset is all the time up-to-date.This dataset connects to a supply with direct join which is all the time up-to-date. You do not need to schedule a refresh on this dataset.

Azure SQL Data Warehouse and Power BI 05

I inserted some knowledge into FactInternetSales and the dataset received up to date instantly. It’s actually superior isn’t it?

Let’s proceed and see how Azure SQL Knowledge Warehouse works with Energy BI Desktop.

Azure SQL Data Warehouse and Power BI 06

Azure SQL Data Warehouse and Power BI 07

Azure SQL Data Warehouse and Power BI 08

Azure SQL Data Warehouse and Power BI 09

Azure SQL Data Warehouse and Power BI 10

  • As you possibly can see Energy BI Desktop detected plenty of relationships robotically. However, there are nonetheless some lacking relationships. As an illustration no relationships detected between FactInternetSales and DimDate. Nevertheless, we would not expertise the identical in an actual world venture. So I depart it to you to create the lacking relationships I the AdventjureWorksDW knowledge mannequin for extra experiments.

  • Click on Report view

  • Increase FactInternetSales

  • Tick SalesAmount

  • Increase DimProductCategory

  • Drag and drop EnglishProductCategoryName area into Axis

  • Increase DimProductSubCategory

  • Drag and drop EnglishProductSubCategoryName area into Axis proper under the EnglishProductCategoryName area so as to add Drill down motion to the report

Azure SQL Data Warehouse and Power BI 11

We efficiently created a easy report on Energy BI Desktop on prime of Azure SQL Knowledge Warehouse.

  • Click on Publish from the ribbon.

  • Now leap on-line and login to your Energy BI account

  • Discover the brand new report you simply printed now. All the things seems to be to be superb as anticipated.

Azure SQL Data Warehouse and Power BI 12

Word: You must have a Energy BI Professional account to have the ability to use an Azure SQL Knowledge Warehouse dataset in Energy BI service.

Schedule Refresh

On this state of affairs we related to the Azure SQL Knowledge Warehouse from Energy BI Desktop which implies the connection is NOT a direct connection. Subsequently, we have to configure “Schedule Refresh” on Energy BI web site.

Azure SQL Data Warehouse and Power BI 13

  • Increase “Knowledge Supply Credentials”

  • Click on “Edit Credentials”

  • Choose “Fundamental” from “Authentication Technique” drop down

  • Enter your legitimate Azure “Username” and “Password”

  • Click on “Signal In”

Azure SQL Data Warehouse and Power BI 14

  • Increase “Schedule Refresh”

  • Change “Hold your knowledge up-to-date” button to ON

  • Do your required schedule settings then click on “Apply”

  • Increase “Featured Q&A Questions”. This can be a new function added to Energy BI. What ever you sort right here will likely be prepared to make use of in Energy BI dashboard.

Azure SQL Data Warehouse and Power BI 15

Create a New Dashboard

Azure SQL Data Warehouse and Power BI 16

Azure SQL Data Warehouse and Power BI 17

  • Open the brand new dashboard from “Dashboards” pane

  • Click on on “Ask a query in regards to the knowledge on this dashboard”

  • The primary query can be the featured query we added earlier than

Azure SQL Data Warehouse and Power BI 18

  • Click on on the query and right here you go, your pie chart is able to use

  • You’ll be able to pin it to the dashboard

Azure SQL Data Warehouse and Power BI 19

Azure SQL Data Warehouse and Power BI 20

Azure SQL Data Warehouse and Power BI 21

Now we’re finished.

To date we created a helpful dashboard in Energy BI on prime of Azure SQL Knowledge Warehouse. We are able to make plenty of different knowledge visualisations and experiences in Energy BI Desktop and publish them to powerbi.com. Then we will create plenty of different dashboards there.

However, is that basically it? What occurs after we wish to do an actual world venture? Okay. Let’s take a look at some realities in regards to the present model of Azure SQL Knowledge Warehouse in mix with the present model of Energy BI. I do know that it’s a preview model, however, the next factors are legitimate for the present model:

  • As you noticed earlier than there’s a “Open In PowerBI” button obtainable to straight join an Azure SQL Knowledge Warehouse to Energy BI Web site. However, what’s the level of getting such a function when Energy BI Web site does NOT robotically detect relationships? Apart from, there isn’t a edit relationships function obtainable in Energy BI web site, so for the time being there isn’t a method we will repair the problem with present obtainable options. As I pointed earlier than, we will create some views on Azure facet, however, I don’t suppose it’s real looking. I newly heard that supporting auto detect relationships on Energy BI web site for Azure SQL Knowledge Warehouse is a piece in progress, however, till it’s not obtainable we can not use Energy BI web site and take pleasure in Direct Connect with Azure SQL Knowledge Warehouse. When a dataset connects to a supply with direct join we don’t need to configure a schedule refresh and the dataset in Energy BI facet can be all the time up-to-date.

image

Power Q&A

Consequently, regardless of I consider that the mix of Azure SQL Knowledge Warehouse and Energy BI would make an ideal answer for enterprise degree initiatives in close to future, I believe the present variations will not be mature sufficient to assist an actual knowledge visualisation venture on prime of an enterprise-class distributed database.

I’d be joyful to have your opinions and feedback.

Latest articles

Related articles

Leave a reply

Please enter your comment!
Please enter your name here