Deploy: Set refresh schedule for datasets

[luclleal]

Hello Mathias! As we spoke before, I would like to suggest to expose "Workspace ID" and "Dataset ID" during execution, since these two informations are availabe only after deploy and are already resolved by pbi-tools. Follows our actual approach to set the refreshs (using a job with Power Shell inline after Deploy job to re-resolve this ID's and make the Update Refresh Schedule api call):

trigger:
- master
- test
- dev

variables:
- group: 'Power BI Principals'
- name: TenantID
  value: ''
- name: ClientID
  value: ''
- name: WorkspaceName
  ${{ if eq(variables['Build.SourceBranchName'], 'main') }}:
    value: 'PROD'
  ${{ elseif eq(variables['Build.SourceBranchName'], 'test') }}:
    value: 'TEST'
  ${{ else }}:
    value: 'DEV'
- name: ReportName
  value: 'Test-Refresh-Schedule'

- job: RefreshSchedule
  displayName: Refresh Schedule
  #DependsOn Deploy can be implemented in order to only set the refresh schedule after a sucessful deploy
  #dependsOn: Deploy
  pool:
    vmImage: windows-latest 
  steps:
  - powershell: |
     #Install authentication cmdlet for Power BI on Power Shell ~2 minutes, -Force removes the need for human interaction
     Install-Module -Name MicrosoftPowerBIMgmt.Profile -Force

     #Retrieve Client Secret From ADO Library on Power BI Principals group
     $ClientSecret = "$(PBI_CLIENT_SECRET)" | ConvertTo-SecureString -AsPlainText -Force 

     #Define body for post api call (update refresh schedule) more infos on: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-refresh-schedule and 
     $Body =
      "{
        'value':{
          'notifyOption':'NoNotification',
          'enabled': true,
          'localTimeZoneId': 'E. South America Standard Time',
          'times': [
            '09:30',
            '13:30',
            '17:30',
            '21:30']
          }
      }"   

      #Define credentials in order to make this script part of CI/CD pipeline (post deploy)   
      $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $(ClientID), $ClientSecret

      #Connect to Power BI Services using Service Principal Account
      Connect-PowerBIServiceAccount -Tenant $(TenantID) -ServicePrincipal -Credential $Credentials

      #Get Workspace ID based on Workspace Name (pipeline variable)
      $AllWorkspacesResponse = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups" -Method GET
      $AllWorkspacesArray = ConvertFrom-Json -InputObject $AllWorkspacesResponse
      $WorkspaceData = $AllWorkspacesArray.value | where { $_.name -eq "$(WorkspaceName)" }
      $WorkspaceID = $WorkspaceData.id   

      #Uses Workspace ID and Report Name (obtained above and pipeline variable) in order to retrieve Dataset ID   
      $urlGetReportsInGroup = "https://api.powerbi.com/v1.0/myorg/groups/" + $WorkspaceID.ToString() + "/reports"
      $ReportsResponse = Invoke-PowerBIRestMethod -Url $urlGetReportsInGroup -Method GET
      $ReportsArray = ConvertFrom-Json -InputObject $ReportsResponse
      $ReportsData = $ReportsArray.value | where { $_.name -eq "$(ReportName)" }
      $DatasetID = $ReportsData.datasetId

      #Uses Workspace ID, Dataset ID and Body Variable to make the Update Refresh Schedule api call, in order to set the refresh schedule
      $urlUpdateRefreshSchedule = "https://api.powerbi.com/v1.0/myorg/groups/" + $WorkspaceID.ToString() + "/datasets/" + $DatasetID.ToString() + "/refreshSchedule"
      Invoke-PowerBIRestMethod -Method PATCH -Url $urlUpdateRefreshSchedule -Body $Body

    displayName: 'Refresh Schedule for $(ReportName)'

[turnersale]

@luclleal Have you used this method for multiple reports and refresh schedules? If so, have you designed a set of reusable schedules and how would one go about pulling those for each report?

Perhaps a quick example:

• Report 1
  • Dataset 1
  • I would like it to refresh at 09:00 and 15:00 (call this Refresh Schedule1)
• Report 2
  • Dataset 2
  • I would like it to refresh at 07:00 (call this Refresh Schedule 2)

Could I define each desired schedule in some location as well as a reference to those schedules for each report separately and avoid having a PowerShell job for each and the manual effort to maintain it?

It would be amazing if we could define the reports and schedules in code and apply changes upon a merge to potentially many reports at once, but I am not experienced with pbi-tools and PowerShell enough to know how feasible that is or how to do so.

[luclleal]

@turnersale We've used similar method in order to define different configurations for each report, but in order to do this you would have to use a for each loop on power shell and also have some fixed parameters for each report (e.g: Workspace name and report names defined on the start of the code) so I would say that what you want to do is perfectly possible

[turnersale]

@luclleal Thanks for the info! I figured PowerShell loops would be reasonable. Time to do some testing! :)

[TheBigJones]

Hey, this topic is also of interest for me and my team. Is there any planning regarding when this feature is going to be implemented?

[mthierba]

Can't provide any timelines at the moment.

[turnersale]

@TheBigJones I have a workaround using PowerShell that needs a bit more tweaking for performance but allows us to use a simple json structure to define the refresh schedule (like seen in body of @luclleal's comment above) for each report as an array. That schedule is read into the script and used to update the schedule in the Service. I also added a process to ensure our service principal that deploys the reports/datasets can assume ownership and patch the connections. This way the BI Dev doesn't have to assume ownership to ensure the refreshes work.

If you are interested in that implementation I could share a cleaned up version of it