One of the tasks of a consultant is to build a proof-of-concept sample or a demonstration to showcase the customer. I was asked today by a friend of mine about the sample Azure SQL Database and PowerBI integration to demonstrate the powerful capability of Azure SQL Database. Microsoft provides you a large library of content packs in which you can connect your Azure Enterprise subscription data from PowerBI. However, if you do not have Azure Enterprise subscription, you cannot show to your customer. Fortunately, in Azure SQL Database service Microsoft allows you to deploy a renowned database pack called AdventureWork.
This article is going to have step-by-step guide on how to create an AdventureWork database in Azure SQL database and explore it in PowerBI. I would assume that you have fundamental knowledge in Azure SQL Database service at least to understand what it is.
Create your database in Azure SQL Database Service
Log into your Azure portal using your administrator account. From the left panel, click SQL databases then click Add
From the SQL Database panel, enter your database name. Select a subscription where the database belongs to. Choose an existing resource group or create a new one depending your choice. The main thing is to select Sample (AdventureWorksLT) under Select source setting. Choose existing server or create a new one. Keep the rest by default for testing purpose.
Connect your Azure SQL Database with PowerBI
Log into PowerBI portal using your administrator account. From the left panel, click Get Data. Under Databases, click Get.
From the list of data sources, select Azure SQL Database.
You will then be asked to download PowerBI Desktop to connect your Azure SQL Database before publishing to PowerBI web-based service. Go download the tool by clicking Download Desktop
After opening PowerBI Desktop, you are asked to enter the password again. From the ribbon, click Get Data > More. In Get Data windows, select Azure > Microsoft Azure SQL Database. Click Connect.
From SQL Server Database, enter server that your newly created database is stored. Enter database name and select Import. Keep settings in advanced options by default. Click OK.
From the windows, select Database. Enter your Azure SQL Database server credential and select level to apply (including your database). Click Connect.
After you click Connect, you will get an error saying your client computer along with its IP address is not allowed to access to this database. You need to go back to your database to start creating a firewall rule. Click Set server firewall.
You would be surprised to realize that Azure recognizes the client computer’s IP address you have tried to connect from PowerBI Desktop tool. Enter rule name and IP range that contains your client computer’s IP address. Click Save to save the new rule
Go back to your PowerBI Desktop tool and click Retry. Navigator windows appears. Select tables you would like to import to. Click Load.
Next step is to publish your database to PowerBI online. Click Publish on the ribbon. Go back to your PowerBI portal. There is a new link to your newly create database on the left panel.
Not only SQL Database, Azure SQL Database Warehouse also has a sample packet that you can showcase to your customer from PowerBI.
From directly your data warehouse you can open your database in PowerBI
From PowerBI online, you are asked to register PowerBI Pro for 60 trial days before connecting to Azure SQL Database Warehouse. You will be able to connect to your data warehouse hosted in Azure after completing the trial registration.