Azure DevOps: Enterprise Power BI report deployment with connections to Shared datasets

(2023-July-15) Let's say you have a collection of Power BI .pbix files stored in a git-based source control system (GitHub, Azure DevOps, or any other system). Among these files, one is your data model, while the others are Power BI visual reports and dashboards connected to the published dataset from your data model. Your published dataset is located in a separate workspace dedicated to shared content, and the visualization Power BI reports are placed in another workspace with appropriate permissions to access them.

Now, let's consider an additional complexity: you have this collection of files not only in one development environment but also in two others. These environments support your Power BI reporting testing (UAT) and the release of your Power BI reports to end-users (Production).

The questions that arise are: How do you deploy your solution, and most importantly, how do you automate it?

About a year ago, I wrote a post where I mentioned different methods to deploy your Power BI report: https://datanrg.blogspot.com/2022/08/azure-devops-deploying-power-bi-reports.html

In this post, however, I will only discuss DevOps automation, focusing specifically on using Azure DevOps to create your CI/CD process for eventually releasing your Power BI solution to end users in the Production environment.

But before I do that, let me give full credit to the Power BI extension that enabled our team to accomplish all this work (https://marketplace.visualstudio.com/items?itemName=maikvandergaag.maikvandergaag-power-bi-actions), which was developed by Maik van der Gaag from the Netherlands. I would also like to give a big shout-out to Salehabanu Shaikh, my very good colleague with never-ending curiosity and enthusiasm, who worked alongside me to stabilize our CI/CD pipelines for Power BI reports in Azure DevOps.

Just another note: The Power BI project concept, which includes both a data model and dependent visualization reports, is still in Preview (https://learn.microsoft.com/en-us/power-bi/developer/projects/projects-overview). Once it becomes generally available, it may change the concept of Power BI reporting solution deployment. However, currently, we rely on one of the guiding principles from Microsoft's best practices for deployment, which is "Separate modeling development from report and dashboard development" (https://learn.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-best-practices).

Here is an extract from their documentation:

For enterprise scale deployments, we recommend that you separate dataset development from the development of reports and dashboards. To promote changes to only a report or a dataset, use the deployment pipelines selective deploy option.

Start by creating a separate .pbix file for datasets and reports in Power BI Desktop. For example, create a dataset .pbix file and upload it to the development stage. Later, the report authors can create a new .pbix only for the report and connect it to the published dataset by using a live connection. This technique allows different creators to separately work on modeling and visualizations, and deploy them to production independently.

Power BI data model deployment to a Workspace with Shared Datasets.

1. Development deployment stage:
   1. Action: Upload Power BI report. Set destination Development PBI workspace and location for the sourcing *.pbix files from your release build
2. Testing and Production deployment stages:
   1. Action: Upload Power BI report. Set destination Test or Production PBI workspace and location for the sourcing *.pbix files from your release build
   2. Action: Update parameters. Since your source control-based PBIX data model file has a reference to the Development database, we need to update the existing data connection parameters and switch it to your Testing and Production database.
   3. Action: Update gateway. For some reason, a simple change of data source parameter is not sufficient and doesn't complete a data source switch from Dev to Test or Production environments. So the [Update gateway] is a necessary step.
   4. Action: Refresh a dataset. After a successful switch of your PBI data model file from Dev to your Test or Production database, it still holds a snapshot of previously imported data from the Development environment. This step will ultimately turn your published PBI data model in the Test or Production workspaces to represent their corresponding datasets.

Power BI reports deployment to a Reporting Workspace.

1. Development deployment stage:
   1. Action: Upload Power BI report. Set destination Development PBI workspace and location for the sourcing *.pbix files from your release build
2. Testing and Production deployment stages:
   1. Action: Upload Power BI report. Set destination Test or Production PBI workspace and location for the sourcing *.pbix files from your release build. Note that [Skip report uploading] option will need to be selected to deploy your PBI visualization report that is connected to a published live dataset.
   2. Action: Upload Power BI report. You will ask me why we need the same step to execute again. Well, in this case, we will set the [Skip report uploading] option off. Otherwise, we couldn't resolve a very not descriptive error message to proceed with further steps.
   3. Action: Take dataset ownership. Pass the necessary access rights for a Power BI dataset to a service connection under which you run your deployment process.
   4. Action: Rebind. That's where all the magic happens to repoint your published Power BI reports in the Test or Production workspaces to their corresponding data models described in the previous section.

Nothing beats seeing all your deployment stages turn green. That's when you know that Power BI developers will continue their development work using the Development dataset in your Development Power BI workspace, along with UAT and Production users having a similar experience using the same Power BI reports in their isolated environments. And to achieve this, Azure DevOps handles all the work automatically, with minimal human manual efforts involved!

As a final note, I highly recommend reading this book for any IT professionals: "The Phoenix Project: A Novel About IT, DevOps, and Helping Your Business Win" (https://www.goodreads.com/book/show/17255186-the-phoenix-project). Who knows how many more DevOps automation enthusiasts it may inspire? 🙂