{"id":931,"date":"2021-02-22T17:30:19","date_gmt":"2021-02-22T15:30:19","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=931"},"modified":"2021-02-22T17:30:56","modified_gmt":"2021-02-22T15:30:56","slug":"orchestrate-schedule-refreshes-in-power-bi","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=931","title":{"rendered":"Orchestrate schedule refreshes in Power BI"},"content":{"rendered":"\n<p>In my last Blog Post I showed you, how you can get every scheduled refresh of your Power BI tenant to organize them better and to avoid the eviction when you refresh too many datasets at the same time: <a href=\"https:\/\/www.flip-design.de\/?p=921\">https:\/\/www.flip-design.de\/?p=921<\/a><br>To get a better timeslot, it is also helpful to know how much time are needed when a particular dataset is refreshing. For this task you can also use the Power BI REST API. The following Power Shell script exports the history and the planned refreshes as well:<\/p>\n\n\n\n<p>After you imported both files into Power BI Desktop, you can analyze them better to find a good timeslot:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><span class=\"has-inline-color has-light-gray-color\">$myPassword = '&lt;Password>'\n$myUsername = '&lt;Username>'\n\n$password = ConvertTo-SecureString $myPassword -AsPlainText -Force\n$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)\n\nLogin-PowerBIServiceAccount -Credential $credential\n\n$Workspaces = Get-PowerBIWorkspace\n\n$date = Get-Date -Format \"ddMMyyyy\"\n\n$HistoryFile = '&lt;Path>\\history' + $date  + '.csv'\n$RefreshFile = '&lt;Path>\\Refresh' + $date + '.csv'\n\nforeach($workspace in $Workspaces)\n{\n    $DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id | where {$_.isRefreshable -eq $true}    \n    foreach($dataset in $DataSets)\n    {\n        $RefreshURI = \"groups\/\" + $workspace.Id + \"\/datasets\/\" + $dataset.id + \"\/refreshes\"\n        $ScheduleURI = \"groups\/\" + $workspace.Id + \"\/datasets\/\" + $dataset.id + \"\/refreshSchedule\"\n       \n        $Results = Invoke-PowerBIRestMethod -Url $RefreshURI -Method Get | ConvertFrom-Json\n\n        #\n        $RefreshURISchedule = \"groups\/\" + $workspace.Id + \"\/datasets\/\" + $dataset.id + \"\/refreshSchedule\"      \n        $ResultsSchedule = Invoke-PowerBIRestMethod -Url $RefreshURISchedule -Method Get | ConvertFrom-Json\n\n        # Only enabled datasets fro refreshing\n        if($ResultsSchedule.enabled -eq $true) {\n\n            foreach($content in $Results.value) {           \n                $row = New-Object psobject\n                $row | Add-Member -Name \"Workspace\" -Value $workspace.Name -MemberType NoteProperty \n                $row | Add-Member -Name \"WorkspaceId\" -Value $workspace.Id -MemberType NoteProperty    \n                $row | Add-Member -Name \"Dataset\" -Value $Dataset.Name -MemberType NoteProperty   \n                $row | Add-Member -Name \"DatasetId\" -Value $Dataset.Id -MemberType NoteProperty  \n                $row | Add-Member -Name \"refreshType\" -Value $content.refreshType -MemberType NoteProperty \n                $row | Add-Member -Name \"Status\" -Value $content.status -MemberType NoteProperty\n                $row | Add-Member -Name \"StartTime\" -Value $content.startTime -MemberType NoteProperty  \n                $row | Add-Member -Name \"EndTime\" -Value $content.endTime -MemberType NoteProperty           \n                $row | Export-Csv -Path $HistoryFile -Append -Delimiter ';' -NoTypeInformation  \n            }\n        }\n\n        $refreshScheduleURI = \"groups\/\" + $workspace.Id + \"\/datasets\/\" + $dataset.id + \"\/refreshSchedule\"\n       \n        $Results = Invoke-PowerBIRestMethod -Url $refreshScheduleURI -Method Get | ConvertFrom-Json\n        # Only enabled datasets fro refreshing\n        if($Results.enabled -eq $true) {\n            $days = $Results.days -join ','\n            $time = $Results.times -join ','\n            $row = New-Object psobject\n            $row = New-Object psobject\n            $row | Add-Member -Name \"Workspace\" -Value $workspace.Name -MemberType NoteProperty \n            $row | Add-Member -Name \"WorkspaceId\" -Value $workspace.Id -MemberType NoteProperty    \n            $row | Add-Member -Name \"Dataset\" -Value $Dataset.Name -MemberType NoteProperty   \n            $row | Add-Member -Name \"DatasetId\" -Value $Dataset.Id -MemberType NoteProperty    \n            $row | Add-Member -Name \"Days\" -Value $days -MemberType NoteProperty \n            $row | Add-Member -Name \"Time\" -Value $time -MemberType NoteProperty         \n            $row | Export-Csv -Path $RefreshFile -Append -Delimiter ';' -NoTypeInformation  \n        }\n    }\n}\n<\/span><\/code><\/pre>\n\n\n\n<p>After you imported both files into Power BI Desktop, you can analyze them better to find a good timeslot:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9.png\"><img decoding=\"async\" loading=\"lazy\" width=\"1024\" height=\"577\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9-1024x577.png\" alt=\"\" class=\"wp-image-932\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9-1024x577.png 1024w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9-300x169.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9-768x433.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9-500x282.png 500w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2021\/02\/image-9.png 1309w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>In my last Blog Post I showed you, how you can get every scheduled refresh of your Power BI tenant to organize them better and to avoid the eviction when you refresh too many datasets at the same time: https:\/\/www.flip-design.de\/?p=921To &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=931\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/931"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=931"}],"version-history":[{"count":2,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/931\/revisions"}],"predecessor-version":[{"id":934,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/931\/revisions\/934"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=931"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=931"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=931"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}