How to configure Power BI dataset to connect to different databases in different stage workspaces?

When working in Power BI deployment pipeline, different stages may have different configurations. For example, each stage can have different databases. DEV workspace Power BI can connect to dev database while PRD workspace Power BI will connect to PRD database. How could you deploy Power BI content in different stage workspaces that will connect to different databases automatically? Here are the steps to configure.

First, make the database serve name and password as parameters inside Power BI. This will enable to configure them in the next steps.

You can go to Power BI desktop “Home”->”Transform data”-> ”Transform data”->”Manager Parameters”->”New Parameter”. Enter the parameter as the screenshot below. Please note you could enter a list of values for one parameter. See reference for details.

Second, configure Power BI connection to use the parameters. When you select database, select the parameters as below screenshot.

Third, configure table to store the parameters and cards to display the parameters. Add this page to the report so you could use it to debug if there is data issue.

You can go to Power BI desktop “Home”->”Transform data”->Right click the Parameter->”Convert To Query”. Then the parameters will be stored in a table like below.

Forth, deploy the Power BI .pbix file to workspace and identify if the parameter is displayed in Power BI workspace setting.

You can go to Power BI workspace->”Datasets + dataflows”->”More options”->”Settings”->”Parameters”. Verify the parameter values. You could also change the value but need to refresh the dataset afterword to pick up the right parameter. See more here.

At this point, you already have a configurable database connection parameters you could change in the workspace. However, this is still manual process. The next few steps will automate the process.

Fifth, create a pipeline and create different workspaces as different environments like DEV, TEST, and PRD. In our example, we created three environments like below. For details to create pipeline, please refer Microsoft Power BI Pipeline document.

Sixth, configure the Dataset rule for each environment. The parameter will change to the value configured in the rule automatically!

• In the pipeline stage you want to create a dataset rule for, select Deployment settings.
• From the Deployment settings pane, select the dataset you want to create a rule for.
• Select the type of rule you want to create, expand the list, and then select Add rule.

Now, you could configure the parameter to use DEV and deploy to DEV workspace. Then use pipeline to deploy to TEST workspace. Verify the parameter changed to TEST database automatically. The use pipeline to deploy to PRD workspace. The database will automatically point to PRD now!

Please note pipeline deployment is different from .pbix file. After .pbix file deployed, it will create two components. One for report and another for dataset. In pipeline deployment, you will need to select BOTH report and dataset and then deploy! If you only select the dataset, the report will not be deployed.