Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
We are working with Power BI Server (on-prem). For this particular report, we are importing data into the pbix file.
Our ETL is done with SSIS. What we want is the Power Bi Report to be refreshed only when ETL completes successfully.
In other words, we would like SSIS to trigger the Power BI report refresh. Has anybody done it?
Thanks for any help with this.
Roque
Solved! Go to Solution.
Hi,
One option is to use execute a PowerShell script in SSIS.
Credit to Cataster on stackoverflow: powershell - How to refresh a PowerBI Report? - Stack Overflow
# Retrieve the scheduled refresh data for a specific report and set it to a variable to get the Id value
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri "[ReportServerURL]/reports/api/v2.0/PowerBIReports(path='[REPORTPATH]')/CacheRefreshPlans"
# This is how you can reference the Id value in the results from above
$refreshplan.value.Id
$refreshplan.value.LastRunTime
$refreshplan.value.LastStatus
# Using the above Id value, create the URI string to run the Model.Execute method
try {
$refreshuri = "[ReportServerURL]/reports/api/v2.0/CacheRefreshPlans(" + $refreshplan.value.Id + ")/Model.Execute"
}
catch {$error[0]}
# Invoke the Model.Execute method to start the scheduled refresh for the PBIX report
Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -method POST -uri $refreshuri -verbose
# To check on the scheduled refresh status, you can update the data in $refreshplan by running the CacheRefreshPlans again
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri "[ReportServerURL]/api/v2.0/PowerBIReports(path='[REPORTPATH]')/CacheRefreshPlans"
# Get the LastRuntime and LastStatus values to check the status
$refreshplan.value.LastRunTime
$refreshplan.value.LastStatus
Regards Taico
Hi,
if you already created a refresh plan, then you can do this:
First, create a package parameter. Then create a package and insert a execute sql task. Execute this statement:
exec [pbiReportServer].dbo.AddEvent @EventType='DataModelRefresh', @EventData=?
In parameter mapping map your package paramete and deploy.
Now you can add this package to all your jobs. Simply put your subscription ID in the parameter and execute.
If you need the ID, heres a SQL script to get it:
SELECT [SubscriptionID]
,c.Name AS ReportName
FROM [dbo].[ReportSchedule] r
LEFT JOIN [dbo].[Catalog] c ON r.ReportID = c.ItemID
WHERE c.Name = 'YOUR REPORTNAME'
Hope this helps
Hi,
if you already created a refresh plan, then you can do this:
First, create a package parameter. Then create a package and insert a execute sql task. Execute this statement:
exec [pbiReportServer].dbo.AddEvent @EventType='DataModelRefresh', @EventData=?
In parameter mapping map your package paramete and deploy.
Now you can add this package to all your jobs. Simply put your subscription ID in the parameter and execute.
If you need the ID, heres a SQL script to get it:
SELECT [SubscriptionID]
,c.Name AS ReportName
FROM [dbo].[ReportSchedule] r
LEFT JOIN [dbo].[Catalog] c ON r.ReportID = c.ItemID
WHERE c.Name = 'YOUR REPORTNAME'
Hope this helps
Perfect. That's the approach that we will take. Any concern about updates to the PBIX file? Say, if we add a new visual and republish it to Power BI Server, will the same db task still work, refresh the new version of the pbix?
Hi, as long as the refresh plan stays the same, there should be no issue. I update my reports regularly and never ran into an issue.
Still looking forward to hear the community's experience with report's refresh triggered by SSIS.
In the meantime, we are playing with the fact that a refresh schedule in Power BI Report Server is implemented by a schedule job in sql server. Based on this, what we have designed so far is as follows:
- in Power BI server, create a scheduled refresh
- find the job in SQL Server, rename it and remove the schedule
- at the end of the ETL job in SSIS, configure a Execute SQL Server Agent Job Task that calls the job renamed above.
Hi,
One option is to use execute a PowerShell script in SSIS.
Credit to Cataster on stackoverflow: powershell - How to refresh a PowerBI Report? - Stack Overflow
# Retrieve the scheduled refresh data for a specific report and set it to a variable to get the Id value
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri "[ReportServerURL]/reports/api/v2.0/PowerBIReports(path='[REPORTPATH]')/CacheRefreshPlans"
# This is how you can reference the Id value in the results from above
$refreshplan.value.Id
$refreshplan.value.LastRunTime
$refreshplan.value.LastStatus
# Using the above Id value, create the URI string to run the Model.Execute method
try {
$refreshuri = "[ReportServerURL]/reports/api/v2.0/CacheRefreshPlans(" + $refreshplan.value.Id + ")/Model.Execute"
}
catch {$error[0]}
# Invoke the Model.Execute method to start the scheduled refresh for the PBIX report
Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -method POST -uri $refreshuri -verbose
# To check on the scheduled refresh status, you can update the data in $refreshplan by running the CacheRefreshPlans again
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri "[ReportServerURL]/api/v2.0/PowerBIReports(path='[REPORTPATH]')/CacheRefreshPlans"
# Get the LastRuntime and LastStatus values to check the status
$refreshplan.value.LastRunTime
$refreshplan.value.LastStatus
Regards Taico
Hello , Would you mind to share how to use this script? Step by step in SSIS ?
Hi @kucluk_ok
EDIT: Before this works. You must have a refresh plan on the report.
I don't have one at hand. I switched to using a 'Windows Tasks' calling the PowerShell script below.
See this guide How to Schedule PowerShell Script Using Task Scheduler (o365reports.com)
But it could also be a SSIS job (search after PowerShell script in SSIS) or SQL agent job.
PowerShell script:
$path_array =
"report_path1",
"report_path2",
...
"report_pathx"
foreach ($path in $path_array)
{
$uri = "[InsertURL]/reports/api/v2.0/PowerBIReports(path='" + $path + "')/CacheRefreshPlans"
$refreshplan = Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -uri $uri
try {
$refreshuri = "[InsertURL]/reports/api/v2.0/CacheRefreshPlans(" + $refreshplan.value.Id + ")/Model.Execute"
}
catch {$error[0]}
Invoke-RestMethod -UseDefaultCredentials <# -Credential $creds #> -method POST -uri $refreshuri -verbose
}
Hope it helps. Regards Taico
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
4 | |
4 | |
3 | |
3 |
User | Count |
---|---|
14 | |
9 | |
6 | |
3 | |
3 |