In this guide, you will be integrating an already-existing Power BI workspace with Azure Synapse Analytics so that you can quickly access datasets, edit reports directly in the Synapse Studio, and automatically see updates to the report in the Power BI workspace. We will be using a Power BI report developed using the Movie Analytics dataset of the previous guide to show the functionalities of the Power BI integration in Azure Synapse.
Obtaining the Movie Analytics Dataset from the Knowledge Center
You can use the Knowledge Center directly in Azure Synapse to get SQL scripts, pipelines, and sample datasets. We will be using the Movie Analytics Pipeline sample to build our Power BI report.
To access the Knowledge Center, navigate to the ? button in the upper right-hand corner of Synapse Studio. Knowledge center will be the first menu option.
Select Browse gallery. Then, select Pipelines. Search for the Movie Analytics pipeline. Select the pipeline and then select Continue.
Continue building the pipeline and ingesting the Movie Analytics dataset for use with Power BI. You can learn more about this step in the Manage Hub blog.
Azure Synapse + Power BI
We first will need to connect our existing Power BI workspace to a Synapse workspace. Navigate to the Synapse Studio and select Manage on the left of the user interface.
Manage option on Azure Synapse
On the resulting window select Linked Services.
Linked Services on Azure Synapse
In the side pane, select + New. This window will show different options for services you can connect to Azure Synapse. Connect to Power BI appears right at the top.
Connect to Power BI
Then, in New Linked Service, you can fill in the pertinent details regarding your new Azure Synapse workspace. Name the workspace Movie Analytics Workspace and connect it to the Movie Analytics Reporting Power BI workspace. Then, select Create.
Filling out information for new linked service
This process will take a few seconds to complete. Once it does, navigate to the list of Linked Services where you can see the artifact you just created.
Viewing Linked Services complete with new the new Power BI service
Now that we have integrated our Power BI workspace with Azure Synapse, let’s begin working with reports.
Adding Power BI datasets
We will first need to add the Movie Analytics dataset to a Power BI dataset so that we can build a report that can later be added to Power BI. Navigate to the Develop Hub blade and in the resulting window, under Power BI you will see our workspace, Movie Analytics Reporting and underneath,
Power BI datasets.
Creating new dataset
Select + New Power BI dataset and in the following side pane, select the dedicated SQL pool which contains the Movie Analytics data.
Creating new dataset
Download the resulting .pbids file. In Power BI Desktop, we can load the appropriate dataset and build a report, as seen here. Once that is complete, we can publish directly to our Power BI workspace, Movie Analytics Reporting.
Creating new dataset
Editing a Power BI report in Azure Synapse
Now, let’s say we want to edit our report. We can do this directly in Azure Synapse. Navigate to the Develop Hub blade and under the Power BI workspace Movie Analytics Reporting open Power BI reports. Here, we can see the Power BI report we just published.
Viewing report in Azure Synapse
We can create new visuals, edit existing ones, add filters, and much more. Let’s dive deeper.
Adding a Visual
Let’s say we would like to add a a pie chart which looks at the number of movies added per year. First, add this visual directly, as we would in Power BI Desktop, as seen below.
Making changes to report in Azure Synapse
Then, select Save on the far right of this window. The window will refresh, and toggling back to our Power BI workspace and the report, we see the changes that we made in Azure Synapse are now reflected in the Power BI workspace!
Changed report in app.powerbi.com
Clean up resources
You can delete Power BI datasets and reports easily within the Synapse Studio. Navigate to the Develop Hub blade, and under Power BI Reports find the correct report and select the ellipsis. Here, you can delete a report by choosing Delete as seen below.
Delete report in Synapse Studio
To pause or delete your dedicated SQL pool in Synapse Studio, simply navigate to Manage Hub and select SQL pool under Analytics pools. As you hover over the dedicated SQL pool, the pause icon will become visible. Select this icon to pause the dedicated SQL pool.
The pause icon is highlighted for the dedicated SQL pool.
Conclusion
This post discussed how to integrate an existing Power BI workspace directly into Azure Synapse. You can try out this tutorial today by creating a Synapse workspace using this blog as a guide.
- Sign up for an Azure free account and receive $200 of credit to try Azure Synapse
- Create an Azure Synapse workspace in minutes to access these new features
- Embark on your Azure Synapse self-study journey with the Microsoft Learn materials
- Check this blog daily to see a roundup of all the new tutorial blogs that have been posted as part of 10 days of Azure Synapse
