With Power BI you can set up a scheduled refresh of any dataset which have an accessible data source. But you must be aware of, how many refreshes are at the same time. With every particular capacity you have only limited parallel refreshing resources. So, you can only refresh one dataset at the same time when you use an A1 capacity, up to 24 datasets when you use an P3 or A6. To manage this, it can be very hard, because it is mostly impossible to overview every workspace and if your users manage this, it is impossible!

But you can create an overview with the Power BI REST API by using PowerShell. In my example I created a workspace named “Test” with two datasets which have configured a scheduled refresh. The following PowerShell script calls the REST API and filters all workspaces and datasets in you tenant by a configured scheduled refresh.

The data from the following script will be written into a CSV file. The days and times which are configured to each dataset will be written comma delimited into one column. This can be split off with Power Query by using “Split into Columns/Rows”. I think this easier as writing one row for each day.

# if you are not authenticated, uncomment following two lines

#$myCred = Get-Credential
#Connect-PowerBIServiceAccount -Credential $myCred

$Workspaces = Get-PowerBIWorkspace

$ExportFile = 'c:\temp\ex.csv'

foreach($workspace in $Workspaces)
{
$DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id | where {$_.isRefreshable -eq $true}    
    foreach($dataset in $DataSets)
    {
        $URI = "groups/" + $workspace.Id + "/datasets/" + $dataset.id + "/refreshSchedule"
       
        $Results = Invoke-PowerBIRestMethod -Url $URI -Method Get | ConvertFrom-Json
        if($Results.enabled -eq $true) {
            $days = $Results.days -join ','
            $time = $Results.times -join ','
            $row = New-Object psobject
            $row | Add-Member -Name "Workspace" -Value $workspace.Name -MemberType NoteProperty    
            $row | Add-Member -Name "Dataset" -Value $Dataset.Name -MemberType NoteProperty   
            $row | Add-Member -Name "Days" -Value $days -MemberType NoteProperty 
            $row | Add-Member -Name "Time" -Value $time -MemberType NoteProperty         
            $row | Export-Csv -Path $ExportFile -Append -Delimiter ';' -NoTypeInformation  
        }
    }
}

After creating the Dataset with Power BI Desktop, you can analyze the configured refreshes and check how many are at the same time and reconfigure some datasets.

You can automate this with Azure Function Apps and write this file into a Blob Store to get automatically Power BI reports with actual data on you tenant. Also, it should be a good idea to get the refreshing duration of each dataset into this report to organize this a little bit better.

For more great content, check out the Resource Centre

Microsoft #SQLServer, Business Intelligence Techniques, #PowerBI, #SSAS, #SSRS, #SSIS, #SQLPASS DE, Data Platform #MVP. Find further information about me here.

Reference:

Lenz, P. (2021). Organize and monitor the Power BI scheduled refreshes. Available at: https://www.flip-design.de/?p=921 [Accessed: 4th March 2021].

Find more great Power Platform content here.