Author(s):
Josh Ndemenge is Cloud Developer Advocate for Data at Microsoft and David Abu is a Cloud Advocate Power BI at Microsoft.
This is the last blog in a two-part series on building an end-to-end data analytics and machine learning solution on Azure Synapse Analytics. If you haven't already, be sure to check out the first blog at https://aka.ms/synapseserengeti before proceeding.
In the first blog, we covered how to create the Synapse workspace and use notebooks to load data into Azure Data Lake Gen2 and the SQL Data Warehouse. In this blog, we will explore how to integrate with Power BI and the Azure Machine Learning service.
To get started, let’s connect the SQL data warehouse to Power BI and create a few reports!
To connect to Azure Synapse Analytics using Power BI Desktop, first open the application and click on the “Get Data” button. Then, select “More” to see a wider range of data source options. In the search bar, type in “Synapse” to filter the options and select “Azure Synapse Analytics (SQL)” from the list.
Next you will be prompted to enter your server name. Type in the name of your server and then click on the “Direct Query” option.
*Note that Direct Query is a connection mode that allows you to query data directly from the data source in real-time, without the need to import it into Power BI.
On the other hand the Import mode, data is first loaded into Power BI’s internal data model before it can be queried and visualized. Check out DirectQuery in Power BI to learn more,
Click Ok and the open Power Query Editor to see the data.
Click on the Annotations table, next on the dropdown next to Category Id uncheck 0 and 1. This is to remove the empty and human categories from the dataset.
Repeat this for the categories table, the click Close and Apply to navigate to the Power BI homepage.
Our objective is to link the different tables within the model view to create a model link similar to the one below.
To model the data, follow these steps:
Now we have completed the modelling of this data and we want to start analyzing the data. Click the top left report view icon to go back to the blank white canvas.
*Note: As of March 2023, the Power BI interface as changed, and you might notice during the exercise. Kindly update your Power BI desktop.
We will create a simple report and we will use some DAX measures to count the rows in the annotation, images, train and Val tables. To achieve this we’ll leverage the New Quick Measures AI functionality within Power BI
Next, we’ll create visualizations to explore the variations in animal images from Snapshot Serengeti across different seasons, locations and species.
To learn more about on-object visual, check out Use on-object interaction with visuals in your report (preview).
1. Click a card visual, click the measure called Images to the visual
2. Add 3 card visuals to display the measures created above:
3. Add 2 slicer visuals:
4. To show the Annotation count by Animals, use a clustered bar chart.
5. To show the Annotation count by Season, use a clustered bar chart.
6. To show the images count by location, use a clustered bar chart.
7. To show the images count by season, use a clustered bar chart.
8. To compare the Train and Val tables, use a Line and Clustered Column chart.
Finally, this results in:
Now that we have created the Power Bi reports, publish them to the Power BI service.
Navigate back to the synapse workspace and click on the “Linked Services” option under the “Manage” section.
Now you can access your Power BI reports directly in Azure Synapse Analytics. Check out Quickstart: Linking a Power BI workspace to a Synapse workspace to learn more.
In this article, we've covered how to link Power BI to Azure Synapse Analytics to create a data pipeline, as well as how to create a Power BI report and publish it to the Power BI service.
For additional resources to get an in-depth understanding of the services discussed in this article take a look at this handy collection of resources:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.