Aveek Das
Creating the report from the Data Model

Migrate Power BI reports between multiple workspaces

July 14, 2020 by

In this article, I am going to explain how we can create a Power BI Report using the Power BI Desktop and then publish it to the Power BI service workspace. Once the report is published to a workspace, we can also consider migrating the same report across several workspaces and all this can be done programmatically by using the Power BI REST APIs. This article is specially targeted towards Power BI admins or DevOps team whose task is to migrate dashboards between various environments like Development, QA, Production, etc. without any manual intervention of the reports.

Creating the Power BI report

Let us create a sample report in Power BI Desktop, and then try to deploy the same in the Power BI workspace. Head over to Power BI Desktop and create a sample report. For the sake of this tutorial, I am going to use the famous WideWorldImportersDW database, as provided by Microsoft. You can download and install the tutorial by visiting https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-dw-install-configure?view=sql-server-ver15.

Open the Power BI Desktop application and click on Get Data and select SQL Server from the drop-down menu.

Get Data from SQL Server

Figure 1 – Get Data from SQL Server

Select the tables from the WideWorldImportersDW database. I am going to select a few tables from the list as follows and click on Load once done.

Select tables from WideWorldImportersDW Database

Figure 2 – Select tables from WideWorldImportersDW Database

The Power BI Desktop will load all the data within the data model, and you can see all the tables being listed up in the Fields pane on the right-hand pane.

Loading data into the Power BI Data Model

Figure 3 – Loading data into the Power BI Data Model

Drag and drop few fields into the canvas and create the report as required.

Creating the report from the Data Model

Figure 4 – Creating the report from the Data Model

Now that the report has been created, let us go ahead and publish the same to the Power BI Service using the Publish button on the Power BI Desktop window.

Publish the Power BI Report to the Power BI Service

Figure 5 – Publish the Power BI Report to the Power BI Service

Once you click the Publish button, you can see the option to publish to different workspaces available in your account will be displayed. I am going to select the Development Workspace here and publish the report to the Development Workspace in the Power BI service account. Click Select and the report will be pushed to the Power BI service. You can publish to any of your existing workspaces and that should be fine.

Publishing the report to the Power BI Service

Figure 6 – Publishing the report to the Power BI Service

If the publish to the Power BI Service is successful, you will receive the following message.

Publishing to the Power BI Desktop successful

Figure 7 – Publishing to the Power BI Desktop successful

You can also verify the same by navigating to https://app.powerbi.com and click on Development Workspace to view the reports within that workspace. As you can see, the report has been successfully published, and you can see it on the Power BI service as well.

Power BI report in Development Workspace

Figure 8 – Power BI report in Development Workspace

Working in the Power BI Service Account

Now, the fun part begins. We have already published our report to the Power BI service into the Development Workspace. We can also create as many workspaces as we need. These workspaces can be used to isolate the Power BI reports across various environments and used by the teams accordingly.

Now let us try to migrate the Power BI Report from Development Workspace to the Quality Workspace and we will try to do it programmatically using the REST APIs. For this, we will run a PowerShell script that is provided by Microsoft and can leverage the functionalities within it. We will also need to have a ClientID, using which we will be doing all the tasks. Head over to https://dev.powerbi.com/apps if you do not have a ClientID and create one. You can select the following options to create a ClientID for your Power BI account.

  • App Type – Native app
  • Redirect URI – urn:ietf:wg:oauth:2.0:oob
  • Access Level – Select all the checkboxes

Click on Register and your ClientID will be generated. Save this ClientID at some secured place as we will need this to do all the operations later in the tutorial.

Copying the workspace using PowerShell

Now that most of our configuration is done, let us head over to https://github.com/Azure-Samples/powerbi-powershell/ and download the “copyWorkspace.ps1” file to our local machine.

Download the PowerShell file

Figure 9 – Download the PowerShell file

Once you have downloaded the PowerShell script to your local machine, you need to update the ClientID in the script before running it.

Enter the ClientID

Figure 10 – Enter the ClientID

Start Visual Studio Code as an administrator to execute the above PowerShell script. You can also use the default PowerShell ISE, but I personally prefer using the VS Code.

Executing the PowerShell Script

Figure 11 – Executing the PowerShell Script

As soon as you start executing the PowerShell script, you will be prompted to log in to your Power BI Service account. Select the appropriate account and allow consent to all the permissions required by the application.

Authenticating the Power BI Service Account

Figure 12 – Authenticating the Power BI Service Account

Once the login to the account is successful, you can head over to Visual Studio Code again, where you will need to provide the name of the workspace from where you would like to copy the report from. In this case, it will be “Development Workspace”. This will allow the script to connect to this specific workspace under your account and copy all the contents to a local directory.

Source Workspace

Figure 13 – Source Workspace

In the next step, you need to define the name of the workspace in which the Power BI Report needs to be migrated. Let us assume that we need to give this to the QA team and so let us migrate the same to a workspace called “Quality Workspace”.

As soon as you enter the name of the workspace and hit Enter, the PowerShell script will connect to the Power BI service account and start exporting the files to the local directory.

Exporting files from Development Workspace

Figure 14 – Exporting files from Development Workspace

Once the files are exported to the local directory, it will then start creating the new workspace in the account and start uploading the same files to the new workspace.

Moving files to the new workspace

Figure 15 – Moving files to the new workspace

The report files will be uploaded to your new workspace within a few minutes, depending on the size of the PBIX file. Once completed, you can see the output as something below.

Power BI Reports successfully uploaded

Figure 16 – Power BI Reports successfully uploaded

Now, let us head over to the Power BI Service account again and verify the same. Navigate to https://app.powerbi.com and click on the Workspaces menu. You can now see that Quality Workspace has been created automatically. This workspace has been created by the Power Shell script while uploading the files to the workspace. In this way, you can migrate the same Power BI files across multiple workspaces programmatically and there is no need to copy or publish the files manually.

Power BI files uploaded to the new workspace

Figure 17 – Power BI files uploaded to the new workspace

Conclusion

In this article, we have seen how we can create reports using the Power BI Desktop and then publish the same to Development Workspace in the Power BI service. Since it is always advisable that we keep isolated environments for development and production, it always becomes a messy task for the Power BI administrators to work around moving these reports across multiple environments. This article will help them understand the concepts of using the Power BI REST APIs and leverage the services to migrate these reports across multiple environments within the same Power BI service.

Aveek Das
Latest posts by Aveek Das (see all)
Migration, PowerBI

About Aveek Das

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interest are SQL Server, SSIS, and SSAS. In his leisure, he likes to keep his skills sharp by writing articles on his blog and other forums. He is also an amateur photographer and hops around to do street photography. You can find him on LinkedIn View all posts by Aveek Das

4,344 Views