Forum Discussion

ChrisRenlund's avatar
ChrisRenlund
Advocate I
5 years ago
Solved

PowerShell script example on how to run full refresh of incremental dataset?

Is there anyone who can provide a code example on how to write a Powershell script that runs a full refresh of a dataset that is configured for incremental refresh?

 

We refresh our datasets by powershell scripts using RestAPI-commands, but they only refresh the dataset according to the refresh policy set up (which is incremental).  I've understood that you should be able to use a TMSL object where you override/ignore the refresh policy (https://docs.microsoft.com/sv-se/analysis-services/tmsl/refresh-command-tmsl?view=power-bi-premium-current#optional-parameters), but i'm unable to put it together with my datasets and ps-script. Anyone who can show how it's done?

 

Why we need it? Some of our data is re-classified every now and then and we want the historical data to reflect our current classifications, therefore we'd like to schedule a full refresh with regular intervals .

Need Help
  • ChrisRenlund's avatar
    ChrisRenlund
    5 years ago

    Found out how to pull it off.

     

    You create a .json file with this information:

    {
"refresh": {
"type": "full",

		"applyRefreshPolicy": false,

		"objects": [
			{
				"database": "Your Dataset Name"
			}
		]
	}
}

    Then you create an Powershell script-file:

    #Set Credentials
$userName = "[email protected]"
$password = ConvertTo-SecureString -String "YourPassword" -AsPlainText -Force
$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $userName, $password


#Execute refresh 
$InputFileRefresh ="D:\MyJsonFile.json"
invoke-ascmd -Server "powerbi://api.powerbi.com/v1.0/myorg/Your Workspace Name" -InputFile $InputFileRefresh -Credential $Credential

    That runs a full refresh of the dataset (but you won't see it running in the Power BI service and the Last Refresh Date in the PBI service will not be updated, just the data in the dataset).

13 Replies

  • ChrisRenlund's avatar
    ChrisRenlund
    Advocate I
    5 years ago

    Found out how to pull it off.

     

    You create a .json file with this information:

    {
"refresh": {
"type": "full",

		"applyRefreshPolicy": false,

		"objects": [
			{
				"database": "Your Dataset Name"
			}
		]
	}
}

    Then you create an Powershell script-file:

    #Set Credentials
$userName = "[email protected]"
$password = ConvertTo-SecureString -String "YourPassword" -AsPlainText -Force
$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $userName, $password


#Execute refresh 
$InputFileRefresh ="D:\MyJsonFile.json"
invoke-ascmd -Server "powerbi://api.powerbi.com/v1.0/myorg/Your Workspace Name" -InputFile $InputFileRefresh -Credential $Credential

    That runs a full refresh of the dataset (but you won't see it running in the Power BI service and the Last Refresh Date in the PBI service will not be updated, just the data in the dataset).

    • Clement_lsc's avatar
      Clement_lsc
      Frequent Visitor
      4 years ago

      Hello Chris, 

      Thank you for the script. 
      Unfortunately I have an issue trying to execute the full refresh using "invoke-ascm". I don't understand how to trigger the json created just before in the ascm command. 

      Thank you in advance. 😀

       

       



      • ChrisRenlund's avatar
        ChrisRenlund
        Advocate I
        4 years ago

        Hi Clement_lsc ,
        It seems as you are adding the .json-content directly to a variable in the Powershell script?
        The way I did it was that I created a separate .json-file containing the .json-commands, then I created a variable in the Powershell-script that pointed out the .json-file:

        $InputFileRefresh ="D:\MyJsonFile.json"

        And then I used that variable in the invoke-ascmd-command (the -InputFile tag):

        invoke-ascmd -Server "powerbi://api.powerbi.com/v1.0/myorg/Your Workspace Name" -InputFile $InputFileRefresh -Credential $Credential

        Maybe there is a better way, but this is what made it work for me!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      "but you won't see it running in the Power BI service and the Last Refresh Date in the PBI service will not be updated, just the data in the dataset"  it is really? If I use Powershell single table full brush, there is no response, and the html code appears, is it the meaning of the refresh success?

      • ChrisRenlund's avatar
        ChrisRenlund
        Advocate I
        3 years ago

        Hi Jackson,
        Is the script not working for you? I can verify that my data is fully refreshed by checking the partitions through Microsoft SQL Server Management studio and all partitions get processed when i run the script:

         

        Nowadays you can also see a successful refresh in the refresh history in the Power BI Service and it states that it is initiaded via an xmla-endpoint:

        If you're having issues with the script - check that you have the powershell modules referred to in my discussion with Clement_Isc in this thread and that you have enabled read-write for XMLA-endpoints in the Admin Portal.

    • afm_bcn's avatar
      afm_bcn
      New Member
      2 years ago

      Hi Chris:

       

      This sounds fine when refreshing a PowerBI dataset.
      But do you know how to do the same for a PowerBI dataflow?
      Even though, I've properly copied the script and changing the name of the dataflow instead of "Your dataset name", it doesn't work at all.
      Do you have any idea?

      • ChrisRenlund's avatar
        ChrisRenlund
        Advocate I
        2 years ago

        Hi, afm_bcn ,
        Sorry, haven't tried it for a dataflow. But Dataflows doesn't show up as a database when you connect to a workspace with SQL Server management studio either, so I guess it's not the same type of workspace object.

        I do know that we're able to refersh dataflows with API-calls, but don't have any with incremental refresh set up, so haven't looked into that.