Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ChrisRenlund
Advocate I
Advocate I

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-c..., 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 .

1 ACCEPTED SOLUTION
ChrisRenlund
Advocate I
Advocate I

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 = "youremail@outlook.com"
$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).

View solution in original post

11 REPLIES 11
ChrisRenlund
Advocate I
Advocate I

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 = "youremail@outlook.com"
$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).

"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?刷新报错.jpg

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:

ChrisRenlund_1-1667472894730.png

 

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:

ChrisRenlund_0-1667472812769.png

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.

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. 😀

Clement_lsc_0-1645438442154.png

 

 



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!

Ok thank you for your answer. 😀

What module did you downloaded in order to run "Invoke-ascm" ? 
I have an error message " invoke-ascmd' is not recognized as the name of a cmdlet" even with the Sqlserver and MicrosoftPowerBIMgmt.Profile modules. 

Sorry i'm a beginner... 

Thank you for your time. 

And just to double cross everything - sometimes you refer to the command as "invoke-ascm", make sure you have "invoke-ascmd" in your powershell code?

It seems as it is part of the SqlServer module (https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-ascmd?view=sqlserver-ps)

These are the modules I have on the server where the script is running (you can get yours with the Get-InstalledModule command):

VersionNameRepositoryDescription
3000.10... DataGateway PSGalleryData Gateway PowerShell cmdlets
3000.10...DataGateway.ProfilePSGalleryData Gateway PowerShell - Profile credential management cmdlets for Data Gateway
1.12 Microsoft.ADAL.PowerShell PSGallery ADAL module for PowerShell
21.1.18230SqlServerPSGallery This module allows SQL Server developers, administrators and business intelligence prof...

 

Hello Chris, 

 

I managed to fix my script. It works now !
I had some securities on my laptop that blocked the SQL SERVER module. 
Moreover, I had to change a parameter in Power BI service to make it work (XMLA authorizations). If some of you encoutered the same issue, here is the solution : 

Clement_lsc_2-1645711659276.png

 

Pass it to read/write. 

Thank you Chris, have a nice day. 

Clément 

Thank you. 
It seems that I have the module, now I have an error message saying the cmdlet was found in the sqlserver package but could not be charged...

I'll find a way, thank you for your time. 
Have a nice day. 😀
Clément 

v-easonf-msft
Community Support
Community Support

Hi, @ChrisRenlund 

 

If you still have this issue for Power BI, you'd better open a support ticket to contact MSFT for professional help.

https://powerbi.microsoft.com/en-us/support/ 

 

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors