Parallel Power BI Scripts

PowerShell 7.0 was made generally available last month and one of it’s new features is pipeline parallelization for the ForEach-Object cmdlet. Scripts running on PowerShell 7 or higher can simply specify the new Parallel parameter (ForEach-Object – Parallel) to significantly reduce the duration of these scripts.

Although there are some options for avoiding looping over Power BI workspaces such as the GetGroupsAsAdmin REST API and the Include parameter that was recently added to the Get-PowerBIWorkspace cmdlet, if you’re still looping over workspaces or other objects either via scheduled processes or in ad hoc scenarios, you might use this blog as some motivation to consider moving over to PowerShell 7.

Test Script and Results

To compare scripts with and without the Parallel parameters I accessed a Power BI tenant (as a PBI admin) with 1,545 active workspaces, 1,909 datasets in these workspaces, and 2,226 reports in these workspaces. The requirement of the test script was simple and realistic – retrieve all of these artifacts and write the data to three CSV files (one for each artifact type) including the workspace ID for the reports and datasets. (Including the workspace ID makes it possible to define relationships and write queries against this data)

You can download and compare/test both scripts from my GitHub repository. (The .ps1 files are formatted, unlike the code blocks below)

One of the two scripts used the ForEach language keyword for the datasets and reports (which cannot be parallelized currently) like the following sample:

$Datasets = ForEach ($WS in $ActiveWorkspaces)
    {
        $WSID = $WS.Id
        Get-PowerBIDataset -Scope Organization -WorkspaceId $WSID | Where-Object {$_.Name -ne "Report Usage Metrics Model" -and $_.Name -ne "Dashboard Usage Metrics Model"} |`
        Select-Object Id,Name,ConfiguredBy,DefaultRetentionPolicy,AddRowsApiEnabled,Tables,WebUrl,Relationships,Datasources,DefaultMode,IsRefreshable, `       IsEffectiveIdentityRequired,IsEffectiveIdentityRolesRequired,IsOnPremGatewayRequired,TargetStorageMode,ActualStorage, `
        @{Name="WorkspaceID";Expression={$WSID}},@{Name="DateRetrieved";Expression={$RetrieveDate}}
    }

The script that utilized this (non paralell) approach took 28 minutes to complete. The other script, which uses the new Parallel parameter from PowerShell 7, completed in under 8 minutes – 71% faster. The parallel script (of course) uses the ForEach-Object cmdlet like the following:

$Datasets = $ActiveWorkspaces | ForEach-Object -Parallel {$WSID = $_.Id; Get-PowerBIDataset -Scope Organization -WorkspaceId $WSID | `
    Where-Object {$_.Name -ne "Report Usage Metrics Model" -and $_.Name -ne "Dashboard Usage Metrics Model"} | `
    Select-Object Id,Name,ConfiguredBy,DefaultRetentionPolicy,AddRowsApiEnabled,Tables,WebUrl,Relationships,Datasources,DefaultMode,IsRefreshable, `   IsEffectiveIdentityRequired,IsEffectiveIdentityRolesRequired,IsOnPremGatewayRequired,TargetStorageMode,ActualStorage, `
    @{Name="WorkspaceID";Expression={$WSID}},@{Name="DateRetrieved";Expression={$RetrieveDate}}}

It’s important to note that the ForEach-Object cmdlet requires piped input like the $ActiveWorkspaces variable in the above example. It’s easy to get them confused but the ForEach-Object cmdlet is completely distinct from the ForEach keyword used in the first example above.

The performance impact for your scenario will of course vary based on the size of the tenant being accessed, threads available (I have 4 cores on my machine) and whether you use the optional -ThrottleLimit parameter. If you’re an admin over a very large Power BI deployment and your script retrieves not just datasets and reports but also dashboards, dataflows, and data sources then switching to the parallel method via PowerShell 7 could be quite the benefit to nightly Power BI admin loads and general admin analysis scenarios. (For a bit of context on ‘nightly admin loads’, you might read Power BI Admin datasets)

Moving Forward

PowerShell 7 is not ‘PowerShell Core’, it’s now the GA and supported version of PowerShell with backward compatibility to Windows PowerShell modules and many new features to come in upcoming releases. The ability to run PowerShell 7 on multiple platforms (Mac, Linux) along with the roadmap makes it the clear choice for new PowerShell script development.

Thus, though I became very comfortable with Windows PowerShell and the Windows PowerShell ISE, moving forward I’m using VS Code as my primary script editor for working with PowerShell 7 and higher.

PBI World Tour: New York City

I’ll be presenting at next month’s Power BI World Tour event in New York City regarding the development of dynamic paginated reports. This will be a virtual event so if you’re interested in this topic and/or others being presented you can just log in like we’re all doing with everything else at the moment.

Wrapping Up

Thanks for visiting Insight Quest and I hope you found this post or something from the archive useful. If you’re interested in getting notified of future blog posts, you can click the ‘Follow’ button on the home page.