Azure DevOps: Deploying Power BI reports with a parameterized gateway-based Data Source

(2022-Aug-08) You have developed your Power BI report, by connecting one or more sourcing datasets, building your data model and enriching it with interactive and engaging visualizations. Now you are ready to deploy this analytical complex to a new or existing Power BI workspace in a lower environment for testing and then to your production environment to bring the ultimate benefits to the end users.

Photo by Nejat Gunduc: https://www.pexels.com/photo/spiral-staircase-with-metal-railings-in-contemporary-building-5060922/

Possible ways for Power BI reports deployment (not a complete list):

• Manual: Nothing beats the bravery to look like a real superhero when you manually publish (deploy) your desktop Power BI reports directly to a workspace. How something that was just a finished product on your local computer, then becomes available for a wider user audience via website access or app on their mobile devices. But I won’t be discussing this deployment option in the post.
• Power BI deployment pipelines: Microsoft offers a solid framework to develop, test and deploy reports using deployment pipelines (https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-overview). They can support the lifecycle of your organizational content with some automation for deployment, however, the mechanism of using such deployment pipelines has some limitations too: https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-process#considerations-and-limitations-1. But I won’t be discussing this deployment option in the post
• API-based deployment: Power BI deployment pipelines functionality can also be used by REST API end-points (https://docs.microsoft.com/en-us/rest/api/power-bi/pipelines), as well as PowerShell cmdlets to perform similar operations (https://docs.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps). But I won’t be discussing this deployment option in the post
• Azure DevOps Extensions: Azure DevOps extensions can be used to perform various deployment pipelines operations and they can simplify all deployment options mentioned above:
• Power BI automation tools - https://marketplace.visualstudio.com/items?itemName=ms-pbi-api.pbi-automation-tools
• Power BI Build Tools - https://marketplace.visualstudio.com/items?itemName=bguidinger.powerbi-build-tools
• Power BI Actions - https://marketplace.visualstudio.com/items?itemName=maikvandergaag.maikvandergaag-power-bi-actions

Several Power BI actions created by Maik van der Gaag from this Azure DevOps will be further mentioned in this post, but before doing that, I need to describe a specific use case that I needed to solve.

Power BI dataset definition relies on a connection to its data source. In my case, this data source was a SQL Server database, that had reference to a SQL Server in my development environment and database name. If I were to deploy my Power BI report to another UAT or Production environment, a data source connection change would need to be made to support data analytics in the corresponding target environments. This data source change can be configured with a parameterized data source that can be easily altered during the deployment.

After configuring this parameterized data source, we can easily test the same Power BI visualizations using data from other environments. But here is a curveball to this setup: each Power BI deployment environment was expected to stream data through published datasets with the help of data gateways for security reasons.  And here is where Power BI actions created by Maik van der Gaag can shine and support this deployment scenario in Azure DevOps.

First, I need to publish my Power Bi reports using the “Upload Power BI report” action by specifying a path to my PBIX files from a source control package and a target Powe BI workspace required for the deployment. 

Then, the “Update Parameters” action will be used to change the “SQL Server” parameter in my published reports with the help of this JSON value [{"name": "SQLServer","newValue": "<my new SQL Server>.database.windows.net"}]. At the very beginning of using Maik van der Gaag’s Power BI actions, I thought that this second DevOps step to update parameters would conclude all of my deployment efforts. However, my published reports in higher environments would still complain that the data source setting was off vs the required setup for those target environments.

After struggling for some time I found a solution to this problem. There is an “Update Gateway” action that I can use to update or refresh metadata of all my published reports’ datasets connected to a particular data gateway. All I needed to do was to reference the name of the gateway in the new environment to do this job:

Eventually, this automated process allowed me to deploy my Power BI reports to other environments and visualize data correctly and this is how the “Update Gateway” Power BI action saved my day and helped to write this blog post!