This article is contributed. See the original author and article here.

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.


 


KnowledgeCenter.jpg


 


Select Browse gallery. Then, select Pipelines. Search for the Movie Analytics pipeline. Select the pipeline and then select Continue.


 


Gallery.jpg


 


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.


 


ManageTab.jpg


Manage option on Azure Synapse


 


On the resulting window select Linked Services.


 


LinkedServices.jpg


 

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.


 


LinkPowerBI.jpg


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.


 


NewLinkedServicePowerBI.jpg


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.


 


ShowLinkedService.jpg


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.


NewPowerBIdataset.jpg


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.


 


NewPowerBIdatasetSynapse.jpg


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.


 


NewPowerBIReport.jpg


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.


 


ViewReportInSynapse.jpg


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.


 


AddVisualReport.jpg


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!


 


ReportinPBI.jpg


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.


 


CleanUpReports.jpg


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.


 


PauseSQLPool.jpg


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.



10days.jpg


 


 

 


 


 


 


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: