Build Appealing Dashboards using Azure Synapse Analytics

By:   |   Comments (1)   |   Related: > Azure Synapse Analytics


Azure Synapse Analytics allows you to unify data and produce visuals for analytics.  In this article we look at how to create a dashboard using various features of Azure Synapse Analytics.


Azure Synapse Analytics provides the following visualization capabilities:

  • Built-in data visualization for Spark SQL query results.
  • Language-specific data visualization libraries.
  • Power BI reporting integration.

I'll describe each tool from this list in the following sections.

Prepare Sample Data for Azure Synapse Analytics

Before we proceed to look into different tools, let's prepare some sample data. I'll use open-source US population data from Microsoft, which can be extracted using Python libraries.

Let's open the Synapse Studio, navigate to the Develop tab, and create a PySpark notebook:

pyspark notebook

Add the below code to create the data using the library and display a sample:

from azureml.opendatasets import UsPopulationCounty
data = UsPopulationCounty()
data_df = data.to_spark_dataframe()
# Display 10 rows

Here is the sample output:

sample output

Add another cell with the following code to filter out the state and population data for 2010, Write the results into the /powerbidemo/UsPopulation2010 folder within your default storage account, we'll need this output in the following sections:


Next, add a cell with the following code to calculate the total population by state name:

from pyspark.sql.functions import *

Built-in data visualization for Spark SQL query results and Azure Synapse Analytics

The Spark notebooks included in Synapse allow displaying code results in a tabular or chart mode.

For example, running the below code will show the results in the tabular format by default:


However, you can change the output to the chart format using the Chart button:

table list

Once in Chart mode, you can select several chart types and specify the key and value fields:


Language-specific data visualization libraries with Azure Synapse Analytics

Synapse supports languages like Python, Scala, .Net. You can use your favorite visualization library to build advanced charts. As an example, I've included the code that displays the query results for the top 20 states using Python's popular Matplotlib library:

from matplotlib import pyplot as plt
dfAggTop20=dfAgg.orderBy(desc('TotalPopulation')). limit(20)
npStateList=[row.stateName for row in'stateName').collect()]
npPopulationList=[row.TotalPopulation for row in'TotalPopulation').collect()] 

Here's the screenshot with the query output:

query output

Power BI Dashboard Reporting with Azure Synapse Analytics

The Azure Synapse Analytics Power BI integration allows building a more advanced and professional-looking dashboard without a single line of code. Moreover, these dashboards can be edited and saved from Synapse Analytics as well. You'll need to have a Power BI license and online account to proceed with the following demo.

Let's first create a simple Power BI report. Open Power Bi desktop and connect Azure Blob Storage and use CSV files from the folder /powerbidemo/UsPopulation2010/ that we saved in the previous section. Add a map component and configure it as follows:

power bi map

Next, publish the report into your Power BI online workspace using the Publish button.

The next step is to link the Synapse Analytics and Power BI workspaces. Open the Synapse Studio, navigate to the Manage pane, and click the New button:

linked services

Select the Connect to Power BI button at the top of the wizard:

new linked service

Select your tenant account and Power BI workspace:

power bi linked service

Once the link is created, you'll see the Power BI section at the Develop pane. Expand it and find the report we've published earlier under the Power BI reports node:

power bi map

As you can see, this page replicates all Power BI components and visualization capabilities. You can also edit the report and save it back to the Power BI workspace using the Save button:

power bi map
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002 and has earned two MCSE certifications. Hes currently working as a Solutions Architect at Slalom Canada.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, February 8, 2021 - 5:44:26 AM - Ilkin Jamalli Back To Top (88197)
Thanks Fikrat. Great article as usual.

get free sql tips
agree to terms