Build Appealing Dashboards using Azure Synapse Analytics


By:   |   Updated: 2021-02-03   |   Comments (1)   |   Related: > Azure


Problem

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.

Solution

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
display(data_df.limit(10))

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:

df2010=data_df.filter('year=2010').select('stateName','population')
df2010.coalesce(1).write.format('csv').mode('overwrite').option('header',True).save('/powerbidemo/UsPopulation2010')

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

from pyspark.sql.functions import *
dfAgg=df2010.groupBy('stateName').agg(sum('population').alias('TotalPopulation'))

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:

display(dfAgg)

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:

chart

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 dfAggTop20.select('stateName').collect()]
npPopulationList=[row.TotalPopulation for row in dfAggTop20.select('TotalPopulation').collect()] 
plt.subplots(figsize=(12,3))
plt.bar(npStateList,npPopulationList,0.5,1)
plt.xticks(rotation=45)
plt.show()

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


Last Updated: 2021-02-03


get scripts

next tip button



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.

View all my tips
Related Resources



Comments For This Article




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


download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks

Create a Python Wheel File to Package and Distribute Custom Code














get free sql tips
agree to terms