There are a number of different use cases for Azure Synapse, which no doubt you have already read about. Of course you can use Azure Synapse to build a scalable Data warehouse, use it to create machine learning solutions with Spark, but have you thought of why you might want to use it with Power BI? There are two ways which you can integrate Azure Synapse with Power BI: connecting to a Power BI Workspace from within Azure Synapse or using Azure synapse to provide the source of all of the data used in the Power BI data set. We will explore both methods in this blog.
Linking to Power BI from within Azure Synapse
From within Synapse you have the ability to access a Power BI workspace so that you can use Power BI from within Synapse. Your Power BI tenant can be in a different data center than the Azure Synapse Workspace, but they both must be in the same Power BI Tenant. You can use Power BI to look at any data you wish, as the data you use can be from any location. When this blog was written, it was only possible to connect to one Power BI workspace from within Azure Synapse. In order to run Power BI as shown here, first I needed to create a Linked Service from within Synapse. Select Power BI from the list of options, and then select the workspace from the list available for your tenant and create the linked service. After the linked service is created, you can go to develop in Azure Synapse, and see the Power BI option listed on the left side of the screen.
Connecting Power BI to an Azure Data Lake Gen 2
As much of the functionality of Analysis services is being migrated to Power BI, many people are choosing to use Power BI instead of migrating to Analysis services, especially if you have Power BI Premium. Another option you have is to gather the data that you are going to load into Power BI into an Azure data lake Gen 2 and create a “Data Lakehouse” as the container where you gather and clean up the data you are going to use in Power BI. There are a few things that you will want to keep in mind if you want to configure Azure Synapse to do this. The first is that your Power BI tenant and your Azure Data Lake Gen 2 must be in the same location. Right now this is an issue as Azure Synapse is not available in all data centers. To configure Power BI to use Azure, you need to go to the Admin section as shown above and select the subscription, resource group and storage account. You will want to make sure that you have the proper permissions granted in Azure RBAC for user groups who need to access the data as they need storage blob owner permissions. The Power BI services also need reader and data access roles granted as well. You can use Azure Synapse to gather the data and then visualize it with Power BI. I see a lot of use cases for this model as I suspect more people are going to be moving away from creating a database for Power BI and instead moving the datastore to Azure.
Data aficionado et SQL Raconteur