Create Azure SQL DB And Generate Power BI Reports Using Table Data

Microsoft Azure SQL DB is the cloud managed database that can be used for app development as well as online storage. It is offered as a Cloud Database as a Service. This makes maintaining apps online much easier. You can create the SQL Database from the Azure Portal Section “SQL Databases”. Select Create a SQL Database to get started with the creation of the database.

This will open up the page where we can specify the database settings like the name of the DB, Azure Subscription, DB Size etc.

Specify the login name and password that will be used for accessing the database.

Click on the finish tick mark which will start the provisioning of the Azure SQL database.

After a few minutes the database will be up and running.

In case we try to open the Azure SQL Data Base from one of the Azure machines we are likely to get the below error if the IP is not in the allowed IP list.

In order to resolve this go to the Configure section of the database created and add the IP of the server to the allowed ip address section.

Connect to Azure SQL DB

In order to connect to Azure SQL DB, let’s connect to one of the Azure Virtual Machines and from there open up the SQL Server management studio. In the Server name text box specify the fully qualified name of the database which can be found in the Azure Portal.

Use SQL Server authentication and specify the login name and password which was entered during the provisioning of Azure SQL Server.

This will create a connection to the recently created Azure SQL Database as shown below. It has listed out the database which we had created by the name BI.

Populate Data

To consume the data from Power BI let's create a table and populate it with some data.

We have created a table that shows the Car Sales Statistics and have named it as Q1CarSales.

Populate some data to the table so that it can be pulled from Power BI

The back end is ready with Azure SQL DB

Spin Up Power BI

We will use Power BI desktop application to connect to Azure and derive business intelligence out of the DB Data.

Select Get Data option.

This will open up the data source collection. Select Microsoft Azure SQL Database as the option.

We will have to specify the Azure SQL Server to which we will be connecting from Power BI. In order to do that go to the Server Dashboard.

Get the Server name as shown below.

We will specify the Server name and the Database name to which we will connect.

In the next page, specify the user name and password.

Click on Connect to connect to the Azure SQL Database.

It will show the data that has been fetched from the database in a table format.

Create Power BI Report

The fields in the data base will be shown in the Fields section in the right pane.

From the Visualizations section select a chart option. We will go with bar chart this time.

Check all the fields that have to be part of the chart. You can drag the Sales Target and Total Sales fields to the value section so that it will be configured to appear on the Y-axis as shown below. Car field will come up in the X-Axis.

After setting the X and Y-Axis, the chart will be populated as shown below.

In case formatting of the chart has to be changed we can do it from the below section.

Finally we are done with the creation of Azure SQL DB Chart and we can publish it to Power BI online.

Select the destination to publish the report.

The report has now been published to Power BI Online.

View report in Power BI Online

Heading to the Power BI Online and selecting the report, we may come across the below issue.

It is because the credentials to connect to Azure SQL DB has to be entered. Select ‘Edit Credentials’.

Specify the Azure SQL DB user name and password.

Now click on the Azure SQL DB report that has become available in the left pane.

It will display the Power BI report that connects to Azure SQL DB.

Summary

Thus we saw how to create an Azure SQL DB and user Power BI to derive business intelligence reports out of it.