In Power BI dataset, you could use PowerShell to refresh the dataset. Here is the sample script to refresh Here is the script to refresh dataset named “MyReport” inside workspace named “My Workspace”.  

$User = "admin@mycompany.com"

$PW = "password"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

# Get Power BI Workspace ID

$WSID = Get-PowerBIWorkspace  -Scope Organization -Name 'My Workspace'| ForEach {$_.Id}

# Get dataset ID

$DSIDRefresh = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | Where {$_.Name -eq "MyReport"} | ForEach {$_.Id}

$RefreshDSURL = 'groups/' + $WSIDAdmin + '/datasets/' + $DSIDRefresh + '/refreshes'

$MailFailureNotify = @{"notifyOption"="MailOnFailure"}

Invoke-PowerBIRestMethod -Url $RefreshDSURL -Method Post -Body $MailFailureNotify

Please note after refresh done from PowerShell, if you look at the refresh history, you will see the refresh  type is empty. It seems like Power BI could not decide if the refresh is done by on demand or Scheduled.

PowerShell script to update Power BI Dataset parameters

In Power BI dataset or reports, parameters serve as a way to easily store and manage a value that can be changed after deployed. You could set up the parameters like database server name and database names inside Power BI solution and update through Power BI Settings as discussed before.

In this blog, we will use PowerShell to update the parameters. Here is the script to update the following two parameters on dataset named “MyReport” inside workspace named “My Workspace”. 

# Install-Module -Name MicrosoftPowerBIMgmt

# Connect to Power BI As admin

$User = "admin@mycompany.com"

$PW = "Password"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

# Get Power BI Workspace ID

$WSID = Get-PowerBIWorkspace  -Scope Organization -Name 'My Workspace'| ForEach {$_.Id}

# Get Power BI Dataset ID

$DSIDMyReport = Get-PowerBIDataset -Scope Organization -WorkspaceId $WSIDAdmin | Where {$_.Name -eq "MyReport"} | ForEach {$_.Id}

# Construct Url with Dataset ID

$urlUpdateParams = "/datasets/" + $DSIDMyReport + "/Default.UpdateParameters"

# Update Parameters Body

$body = '{

  "updateDetails": [

    {

      "name": "DatabaseName",

      "newValue": "NewDBNamev"

    },

    {

      "name": "ServeName",

      "newValue": "myserver.database.windows.net"

    }

  ]

}'

$content = 'application/json'

# Update Dataset Parameters

Invoke-PowerBIRestMethod -Url $urlUpdateParams -Method Post -Body $body -ContentType $content

As enhancement, you could also use service principal as discussed in here to facilitate the login.

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.