Easy Analytics and Data Visualisation Part 3 – Visualising Azure Synapse Analytics Data in Power BI

Loading Data from Azure Synapse Analytics into Power BI

In this section, the Azure Synapse data will be imported and loaded into Power BI. The first step is to open Power BI Desktop and use the Get Data button to connect to Azure Synapse Analytics. Here is an illustration:

Figure 26 Azure Synapse Analytics as a data source in the Get Data window

After clicking the Connect button, there is some configuration to be done. The workspace name goes into the Server field and the user can click the OK button to proceed.

Figure 27 Azure Synapse Analytics details for Power BI

The next step is to view the Navigator and select the tables and views to import the data.

Figure 28 Navigator functionality in Power BI

In this example, the three views which refer to Units, Percentages in GDP and Billions are selected along with the dim.DimCountry table. Once these items are selected, the Load button will import the data into Power BI.

Once the data is loaded, the Fields pane displays the tables and views. The next step is to use the columns to create visualisations in the Power BI canvas.

Creating Tables on the Power BI Canvas

It is possible to create tables, graphs and charts using the fields in the Power BI canvas. Here is a list of the imported data in Power BI.

Figure 29 Field List in Power BI

The dim DimCountry table can be opened by clicking on the downward-facing arrow at the left-hand side.

Figure 30 Power BI columns

The CountryName can be selected, which will produce a small map on the canvas. There are bubbles on the canvas where the countries are located. Here is an example:

Figure 31 The Map visualisation in Power BI

It is possible to change this to a table. To change the visualisation from a map to a table, click on the map and navigate to the Visualisations pane on the right-hand side. If the Visualisations pane is closed, click the right facing arrow at the top of the screen. Select the table visualisation, which is illustrated in the next image.

Figure 32 The Table Visualisation

The map will now turn into a table the table will contain the country name. Here is an example of the Table visualisation:

Figure 33 Country names listed in a Power BI table

The table only contains country names, and new columns can be added to make the table more useful.

Adding new columns to the table

It is possible to add the Percentage GDP in the table.   To add the Percentage of GDP, right-click on the table called IMFData vIMF Percent of GDP and select the option to Rename the table.

Figure 34 Selecting a column from the Fields pane in Power BI

Now that the table is renamed, it is clearer for Power BI users to understand. Selecting the Value column adds it to the table, which now appears as follows:

Figure 35 Viewing the table in Power BI

The tables can be made clearer by renaming columns.

Renaming Columns in Power BI

The column can also be renamed.  The Visualisation pane displays the columns that are located in the table. Right-clicking the Value field brings up a menu which contains an option Rename for this visual.

Figure 36 Renaming a field in Power BI

The column can be renamed to GDP Percentage so that is clear.

Removing Totals in the Table

The table needs to be improved further.  The GDP Percentage is aggregated, and it appears at the bottom of the table. This makes no sense for the purpose of working with the percentages so it is good to get rid of the totals. To get rid of the totals, click on the table. In the Visualisations pane, there is a Format option denoted by a paint roller icon. In the Format option in the Visualisations pane, there is a Total option which toggles the total on or off. The total should not be aggregated, so, in this example, the Total option should be toggled off. Here is an illustration:

Figure 37 Totals Option in Power BI

The data is still aggregated, however. It’s possible to see the data for each year by adding the Year column to the table.

To add the Year column to the table, click on the table and then click on the Year column. The Year column will go at the end of table, and it would be clearer if the Year column was located between the Country Name and GDP Percentage columns.

To move the column, select the Year column and drag it above the GDP Percentage column. When you do this, the year column will go between the country column and the GDP Percentage column.

The CountryName column would be clearer if there was a space inserted at an appropriate point. Right-click on the CountryName field, and then select the Rename for this visual option and change it to Country Name.

The table will appear as follows:

Figure 38 GDP Percentage as a simple table

It is possible to add in filters to add some interactivity to the table, and this is covered in the next steps.

Adding filters to a Table

The report can start to appear more like a dashboard by moving the table to the right-hand side of the canvas. The next step is to click on the white canvas and select the CountryName field in the Dim Country table. Initially, this action will create a map on the Power BI canvas.

It is possible to change the table to a filter by clicking on the new CountryName table and select the Filter visualisation in the Visualisation pane, and the CountryName filter will appear on the canvas. Clicking on an individual country will filter the table so that it only shows the data for the country that you have selected. Here is an illustration:

Figure 39 Table with filter

It’s possible to add in a Year filter as well. The existing Country filter can be resized to be made smaller so that another filter can use the space.

To create a new filter, the report author simply needs to click on the white canvas. Clicking on the Year column will create a table. The Year table can be converted to a filter by selecting the table and clicking on the Filter option in the Visualisation pane.

Improving the Table Filter

The Power BI filter can be further improved by adding in further optional selection controls. Clicking on the filter will reveal the filter selection controls option in the Format section of the Visualisations pane. The filter is currently set to Single Select, which means that the table would only show data for a single country at a time.

Users like the opportunity to multi-select more than one Country, and this option is enabled by switching the Single Select option to Off. There is another option that allows users to Multi Select using the control button, and in this article, this option will be switched off.

There is an option to Select All, and this option should be switched On. The filter now has the ability to select all of the countries in the filter.

Creating a Title on the Worksheet

We can make the reports look clearer by adding in a title box. To do this, click in the white Canvas at the top of the screen. On the Home tab, select the Text Box option and the text box will appear on the screen with a black bar appears with formatting options. The text box can be located to the top of the screen, with the font size enlarged to size 24 font. The text can be changed to read Percent GDP Percentage Detail Table so that it is more descriptive.

The final worksheet appears as follows:

Figure 40 Percentage GDP Percentage Detail Table

Creating a Chart in Power BI

Power BI helps the report author to save some time by creating a new worksheet which duplicates the existing worksheet. To do this, right click on the tab at the bottom of the workbook.

Figure 41 Renaming the Page in Power BI

Currently, it says Page 1 but it can be changed by right-clicking and selecting the Rename option to rename the page to Percentage GDP Detail.

To duplicate the worksheet, it is as simple as right-clicking on the tab and select the option to Duplicate. This will present you with a new worksheet which can form the basis of further work. In this duplicated worksheet, the report author can create new visualisations while retaining the filters, thereby saving some time. This report will provide a good starting point for creating the next report. The filters should be retained.

Creating a Line Chart

The duplicated Report can be changed to display a Line chart. To do this, it is very straightforward. To do this, the report author simply needs to select the table, and then select the Line Chart option in the Visualisation Pane. Here is an illustration of the Line Chart option in the Visualisation Pane:

Figure 42 The Line Chart option in the Visualization Pane

The line chart needs to be configured so that it displays correctly. On a line chart, the time can go on the X-Axis, and to do this, the Year column goes into the Axis column so the time should display in increasing order from left to right. The Value field can be renamed to % GDP so that it matches the previous visualization. The Country Name goes in the Legend field.

Figure 43 Line Chart configuration

The title can be reworded to read: % GDP Percentage Over Time. The report appears as follows. In this example, Algeria is selected in the filter and the chart only displays the data for Algeria.

Figure 44 % GDP Percentage Chart

As a final step, the worksheet can be renamed to % GDP Percentage Over Time by right-clicking on the tab at the foot of the worksheet and selecting the Rename option.

So far, there has been no coding to create interactive, straightforward visualisations. In the next section, more analytical features are added to build on the Power BI dashboard that has been created.

Creating a Top N Filter in Power BI

In this section, some of Power BI’s analytical features will be introduced. In Business Intelligence, business users often want to know descriptive information about the data, such as the top number of values, the bottom number of values, or the average value.

In this visualisation, the report will only display the top ten GDP percentage values for a specific year.  The top ten values will change by in response to the filters, so it is possible to interact with the data.

The first step is to duplicate the % GDP Percentage Over Time by right-clicking on the tab at the foot of the worksheet and selecting the Rename option.

The new worksheet can be set to Top 10 % GDP. The title can be reworded to the same value. Since the sheet will be configured to show the top ten GDP % by country, the Country filter is not necessary so it can be deleted from the sheet.

The line chart should be changed back to a table by clicking on the line chart and selecting the Table visualisation option.

The GDP % will be displayed for a single year at a time, so the filter needs to be configured to select only year at a time.

The first step is to remove the slider. To set the configuration, the Year filter should be selected, and the Format option in the Visualization pane needs to be open. There is a Slicer option that can be toggled on or off. The slicer is not appropriate here because the chart should only show data for one year at a time. Here is an illustration of the Slicer option set to Off:

Figure 45 Slicer option in Power BI

The next step is to set the Year slicer so it displays a List rather than a drop-down. This is done by clicking on the top right-hand side of the slider, where a drop-down list will appear. The List option should be chosen.

Figure 46 Changing Date Selection

The next step is to change the filter so that it can select one year only at a time. In the Visualization pane, navigate to the Selection Controls option and toggle the Single Select option to On. Here is an example:

Figure 47 Single Select option

The Top N filter is accessed from the Filter pane using the Top N option which is found in the Country filter. Here is an illustration which explains where to find it:

Figure 48 Top N Filtering in Power BI

In this example, the top 10 values will be displayed if the filter is configured with the correct information. In this example, to display the top ten GDP % values, the filter requires that the number 10 is entered into the text box, the Value column is placed in the By Value textbox and the Apply Filter option is pressed.

The table itself can be formatted by toggling the Totals option, which is found in the Visualizations pane.

Figure 49 Toggling the Totals Option

The table can be improved by adding data bars to the table. In the Conditional Formatting option, the % GDP field can be found in the drop-down list under the Conditional Formatting header. The Data Bars toggle should set to On. This will add data bars to the table, and the length of each bar will be determined by the percentage value. Here is an illustration:

Figure 50 Data bars

It is possible to format the data bars using the Advanced Controls option. When this option is selected, the Data bars option appears as follows:

Figure 51 Data Bars option

The colour of the data bars can be changed by selecting the colour palette dropdown for Positive bar or Negative bar. In this example, the colour red is selected. Here is an example of the table:

Figure 52 Table with sample data bars

It’s possible to add in a map to the dashboard by selecting the table and pasting it, and then changing the copied table to a map. To do this, Copy the selected table and Paste. The new copied table can be changed to a map by choosing the Map visualisation in the Visualization pane. The dashboard now appears as follows:

Figure 53 Completed Dashboard

To Wrap-Up

In this blog series, the route from Excel data to Azure Synapse Analytics and then Power BI has been completed. These steps have involved:

• Shaping data with Power Query
• Importing data with Azure Synapse Analytics
• Shaping data in Azure Synapse Analytics in Power BI
• Displaying data on the Power BI dashboard

Cleaning up Azure Resources

Your organisation is being charged for data warehouse units and data stored the SQL pool.

• If you want to keep the data in storage, pause compute.
• If you want to remove future charges, you can delete the SQL pool.

Here are some links to the Microsoft official resources for cleaning up the Azure Synapse Analytics resources:

Clean up Azure Synapse Analytics SQL Pool

Manage compute for dedicated SQL pool in Azure Synapse Analytics

Manage Azure Resources using the Azure portal

Blog Post Series Wrap up

Along the way, the series has explored common use cases which often occur in business intelligence, such as pivoting data, changing the format, and working with dates. It’s also evident that Azure Synapse Analytics is an easy-to-use technology for people who already have SQL expertise, so the learning curve is manageable.

Power BI can work with many different technologies, but it is particularly suited to Azure Synapse Analytics, which can serve the data quickly and scale effortlessly to meet the need. It is also accessible to business users who may not be comfortable in coding, but who are familiar with Excel. For these users, Power BI is a good step for Excel users, and this blog series has covered some of the most common scenarios that an Excel user might come across during analytical work.

We hope you have enjoyed the series. As an exercise for yourself, why not try to do some analytics on the Units and Billions data that we imported at the same time? If you do, please send us your screenshots, we’d love to see them!