, I explored techniques for using PowerShell to access data in SQL Server. I covered topics like reading data from a SQL Server table and inserting data into a table. Of course, there are plenty of ways to access SQL Server data without using PowerShell.
Whether you are interested in data analytics or data-driven orchestrations, you will need to know how to to give PowerShell what it needs. Filtering can be done either at the SQL Server level or within PowerShell itself. However, for handling large datasets, it’s usually best to filter at the SQL level. In this article, I will demonstrate both techniques.
In Figure 1, you can see that I have used a simple PowerShell command to read data from a table in a SQL Server database. I added that data to a PowerShell variable named $Data and then outputted the contents by typing the variable name.
PowerShell SQL Filtering 1
I have copied data from a SQL Server table into a PowerShell variable.
Since we now have this data in a PowerShell variable, we can apply all the usual techniques to access the specific data we need.
In the figure above, the quantity of items sold in July is 35. Let’s suppose that, for whatever reason, we need to isolate this quantity so that we can take action on it.
To do so, we could use a command like this:
Although this command returns both the month (July) and the quantity of items sold (35), our goal is to isolate just the quantity. One way to accomplish this is to map the command to another variable, which I will name $RawData. By doing this, we can access the desired data by calling $RawData.ItemsSold. An example can be seen in Figure 2.
PowerShell SQL Filtering 2
I have referenced a single data point from a SQL Server database.
Notice that I had to change the operator from -eq to -like. You can see what this looks like in Figure 3.
PowerShell SQL Filtering 3
You can filter the data based on any criteria that you choose.
In the Power BI service, I see I can right-click a dataset and choose “Download this file”. How can I use PowerShell to do this?
I have seen Microsoft’s documentation on this. I see Export-PowerBIDataFlow and Export-PowerBIReport, but there is no Export-PowerBIDataset.
Let’s assume I don’t have a report that uses the dataset in question, so Export-PowerBIReport will not work for me.
Passing the ID for a dataset to Export-PowerBIReport doesn’t appear to work, even though a report and a dataset are the same thing from the perspective of interacting with them in Power BI Desktop. And both (manually) export from the service as .pbix files.
Login-PowerBIServiceAccount -Environment USGov
$ws = Get-PowerBIWorkspace -Scope Organization -Name "My Office Workspace"
$wsid = $ws.Id
foreach ($d in $ds) { if ($d.Name = $name) { $dsid = $d.Id }
}
$fldr = Join-Path $env:USERPROFILE "Downloads"
$file = Join-Path $fldr $name
if (Test-Path "$file.pbix") { Remove-Item "$file.pbix"
}
Export-PowerBIReport -Id $dsid -OutFile "$file.pbix"Export-PowerBIReport : Operation returned an invalid status code 'NotFound'I suspect the error is caused by $dsid being the ID of a dataset, not a report.
Side note, related to my efforts: It appears that Get-PowerBIDataset is broken. With many datasets in the workspace, these two lines of code do exactly the same thing (return all of the datasets), contrary to the documentation:
$ds = Get-PowerBIDataset -WorkspaceId $wsid
$ds = Get-PowerBIDataset -WorkspaceId $wsid -Name "My dataset"Is that expected or a known problem?
Update
based on Sam Nseir’s answer.
Login-PowerBIServiceAccount -Environment USGov
$dt = (Get-Date).ToString("yyyyMMdd")
$wsname = "My Office Workspace"
$dsname = "My Dataset"
$rptname = $dsname + $dt
$fldr = Join-Path $env:USERPROFILE "Downloads"
$fileout = Join-Path $fldr "$dsname.pbix"
$fileseed = Join-Path $fldr "seed.pbix"
$seedname = "seed$dt" # lazily trying to avoid naming conflicts
$copyname = "copy$dt" # lazily trying to avoid naming conflicts
$ws = Get-PowerBIWorkspace -Scope Organization -Name $wsname
$wsid = $ws.Id
$ds = Get-PowerBIDataset -WorkspaceId $wsid
foreach ($d in $ds) { if ($d.Name = $dsname) { $dsid = $d.Id }
}
$dsbefore = Get-PowerBIDataset -WorkspaceId $wsid
$r = New-PowerBIReport -Path $fileseed -Name $seedname -WorkspaceId $wsid
$rid = $r.Id
#$r
$dsafter = Get-PowerBIDataset -WorkspaceId $wsid
foreach ($dsa in $dsafter) { $found = 0 foreach ($dsb in $dsbefore) { if ($dsa.Id -eq $dsb.Id) { $found = 1 break } } if ($found -eq 0) { #"New dataset found: $($dsa.Name)" $dsnewid = $dsa.Id break }
}
$rc = Copy-PowerBIReport -Name $copyname -Id $rid -WorkspaceId $wsid -TargetWorkspaceId $wsid -TargetDatasetId $dsid
$rcid = $rc.Id
$rcid
if (Test-Path $fileout) { Remove-Item $fileout
}
Export-PowerBIReport -Id $rcid -OutFile $fileout
Remove-PowerBIReport -Id $rcid -WorkspaceId $wsid
Remove-PowerBIReport -Id $rid -WorkspaceId $wsid
# There is no Remove-PowerBIDataset cmdlet, so let's try the Power BI REST API
Invoke-PowerBIRestMethod -Url "groups/$wsid/datasets/$dsnewid" -Method DeleteSo I have:
created a report
copied the report and wired it up to the desired dataset
exported the copied report (desired dataset)
deleted the copied report
deleted the new report
deleted the new dataset
Writing Headers to Data Files
For capacities, we create a CSV file named PowerBICapacityInfo.csv. The headers for this file include:
- CapacityId: Unique identifier for the capacity.
- CapacityName: The name of the capacity.
- SKU: Stock Keeping Unit – a code representing the capacity type.
# Prepare the Capacity data file
$CapacityFileCsv = '.\PowerBICapacityInfo.csv'
If (Test-Path $CapacityFileCsv) { Remove-Item -Path $CapacityFileCsv
}
$DataLine = '"{0}","{1}","{2}"' -f ` 'CapacityId', ` 'CapacityName', ` 'SKU'
$DataLine | Add-Content $CapacityFileCsv -Encoding UTF8 -ErrorAction SilentlyContinueTo store workspace data, we use the file PowerBIWorkspaceInfo.csv. Its headers are:
- WorkspaceId: The unique identifier for the workspace.
- WorkspaceName: The name of the workspace.
- Type: The type of the workspace.
- State: The state of the workspace.
- IsReadOnly: Indicates if the workspace is read-only.
- IsOrphaned: Reflects whether the workspace is orphaned.
- IsOnDedicatedCapacity: Shows if the workspace is on dedicated capacity.
- CapacityId: Links the workspace to its associated capacity.
# Prepare the Workspace data file
$WorkspaceFileCsv = '.\PowerBIWorkspaceInfo.csv'
If (Test-Path $WorkspaceFileCsv) { Remove-Item -Path $WorkspaceFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f ` 'WorkspaceId', ` 'WorkspaceName', ` 'Type', ` 'State', ` 'IsReadOnly', ` 'IsOrphaned', ` 'IsOnDedicatedCapacity', ` 'CapacityId'
$DataLine | Add-Content $WorkspaceFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue- WorkspaceId: The identifier of the workspace.
- Identifier: Unique identifier for the user.
- PrincipalType: The type of principal (e.g., user or group).
- AccessRight: Describes the user’s access rights within the workspace.
# Prepare the User data file
$UserFileCsv = '.\PowerBIUserInfo.csv'
If (Test-Path $UserFileCsv) { Remove-Item -Path $UserFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f ` 'WorkspaceId', ` 'Identifier', ` 'PrincipalType', ` 'AccessRight'
$DataLine | Add-Content $UserFileCsv -Encoding UTF8 -ErrorAction SilentlyContinueReports are saved in PowerBIReportInfo.csv, which contains these headers:
- WorkspaceId: The identifier of the workspace.
- ReportId: Unique identifier for the report.
- ReportName: The name of the report.
- DatasetId: Associates the report with its underlying dataset.
# Prepare the Report data file
$ReportFileCsv = '.\PowerBIReportInfo.csv'
If (Test-Path $ReportFileCsv) { Remove-Item -Path $ReportFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f ` 'WorkspaceId', ` 'ReportId', ` 'ReportName', ` 'DatasetId'
$DataLine | Add-Content $ReportFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue- WorkspaceId: The identifier of the workspace.
- DatasetId: Unique identifier for the dataset.
- DatasetName: The name of the dataset.
- ConfiguredBy: Shows who configured the dataset.
- IsRefreshable: Indicates if the dataset is refreshable.
- IsEffectiveIdentityRequired: Reflects whether effective identity is required.
- IsEffectiveIdentityRolesRequired: Shows if effective identity roles are required.
- IsOnPremGatewayRequired: Indicates if an on-premises gateway is needed.
# Prepare the Dataset data file
$DatasetFileCsv = '.\PowerBIDatasetInfo.csv'
If (Test-Path $DatasetFileCsv) { Remove-Item -Path $DatasetFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f ` 'WorkspaceId', ` 'DatasetId', ` 'DatasetName', ` 'ConfiguredBy', ` 'IsRefreshable', ` 'IsEffectiveIdentityRequired', ` 'IsEffectiveIdentityRolesRequired', ` 'IsOnPremGatewayRequired'
$DataLine | Add-Content $DatasetFileCsv -Encoding UTF8 -ErrorAction SilentlyContinueFor dashboard data, we create PowerBIDashboardInfo.csv, which includes these headers:
- WorkspaceId: The identifier of the workspace.
- DashboardId: Unique identifier for the dashboard.
- DashboardName: The name of the dashboard.
- IsReadOnly: Indicates if the dashboard is read-only.
# Prepare the Dashboard data file
$DashboardFileCsv = '.\PowerBIDashboardInfo.csv'
If (Test-Path $DashboardFileCsv) { Remove-Item -Path $DashboardFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f ` 'WorkspaceId', ` 'DashboardId', ` 'DashboardName', ` 'IsReadOnly'
$DataLine | Add-Content $DashboardFileCsv -Encoding UTF8 -ErrorAction SilentlyContinueTiles (within Dashboards)
- WorkspaceId: The identifier of the workspace.
- TileId: Unique identifier for the tile.
- TileTitle: The title of the tile.
- DashboardId: Links the tile to its parent dashboard.
- ReportId: Associates the tile with its underlying report.
- DatasetId: Links the tile to its dataset.
# Prepare the Tile data file
$TileFileCsv = '.\PowerBITileInfo.csv'
If (Test-Path $TileFileCsv) { Remove-Item -Path $TileFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}"' -f ` 'WorkspaceId', ` 'TileId', ` 'TileTitle', ` 'DashboardId', ` 'ReportId', ` 'DatasetId'
$DataLine | Add-Content $TileFileCsv -Encoding UTF8 -ErrorAction SilentlyContinueFinally, dataflow information is written to PowerBIDataflowInfo.csv with the headers:
- WorkspaceId: The identifier of the workspace.
- DataflowId: Unique identifier for the dataflow.
- DataflowName: The name of the dataflow.
- ConfiguredBy: Indicates who configured the dataflow.
# Prepare the Dataflow data file
$DataflowFileCsv = '.\PowerBIDataflowInfo.csv'
If (Test-Path $DataflowFileCsv) { Remove-Item -Path $DataflowFileCsv
}
$DataLine = '"{0}","{1}","{2}","{3}"' -f ` 'WorkspaceId', ` 'DataflowId', ` 'DataflowName', ` 'ConfiguredBy'
$DataLine | Add-Content $DataflowFileCsv -Encoding UTF8 -ErrorAction SilentlyContinueUnderstanding these headers is crucial as they form the basis of data collection in our PowerShell script. Now, let’s explore how the script collects and organizes data within these categories.
Comprehensive PowerShell script that interacts with the Power BI Service
automate this data collection process using Power BI
Capacity Data Collection
We collect information about Power BI capacities. This includes the capacity’s ID, name, and SKU (Stock Keeping Unit).
# Collect a list of capacities from the Power BI Service
# Comment the below line if you do not have Administrator privileges
Get-PowerBICapacity -Scope Organization | ForEach-Object {
# Uncomment the below line if you do not have Administrator privileges
# Get-PowerBICapacity -Scope Individual | ForEach-Object { # Save the capacity info If ($_.DisplayName) { $CapacityName = ($_.DisplayName).Replace('"',' ').Trim() } Else { $CapacityName = $Null } $DataLine = '"{0}","{1}","{2}"' -f ` $_.Id, ` $CapacityName, ` $_.SKU $DataLine | Add-Content $CapacityFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue
}Wrapping It Up
The CSV files now contain a wealth of information about your Power BI resources, making it easier to track, manage, and optimize your Power BI environment.
Managing a Power BI environment can be complex, but with the right tools, it doesn’t have to be. By scheduling and running this PowerShell script on a regular basis, you can build a systematic approach for monitoring your Power BI ecosystem. The script eliminates manual tracking of resources, while the outputted CSV files give you an organized reference to analyze adoption, usage, and performance over time.
As Power BI usage grows within an organization, oversight is critical not only for optimization, but for security and compliance as well. This PowerShell script serves as a foundational piece for gaining control over your Power BI landscape. In the fast-moving world of business intelligence and analytics, being able to tame your Power BI environment is essential. Take control with automation using this PowerShell approach.
Installing Required Modules
Before we dive into Power BI management, we start by installing the necessary PowerShell module for Power BI.
# Install the required module for Power BI
Install-Module -Name MicrosoftPowerBIMgmt -Force -AllowClobberSQL Server Filtering
The filtering techniques that I have demonstrated so far work well when dealing with small datasets. However, what if you need to interact with a table containing millions of rows? Depending on the volume of data, it may exceed PowerShell’s capacity. Even if PowerShell handles such volumes, filtering the data could strain your system. As such, it is often more practical to let SQL Server handle the filtering task.
Countless techniques exist for filtering SQL Server data. In fact, entire books have been written on the subject. Even so, I want to illustrate how we can perform some basic SQL Server filtering by simply modifying our query.
$Data=Invoke-SQLCmd -ServerInstance Win11SQLSQLEXPRESS -Database MyAppDB -Query “Select Month, ItemsSold From Table1”In this command, the Query portion specifies the data to retrieve. Therefore, if we want to give PowerShell a smaller dataset to work with, we can simply modify the Query statement to include a filter. This can be done using a Where statement
Suppose that I want to repeat the earlier task of isolating the number 35, which represents the items sold in July. On the surface, it seems that the query statement should be something like this: Select ItemsSold From Table1 Where Month = ‘July’. However, things aren’t quite so simple.
There are two problems with the above Select statement. The first problem is that the Where statement uses the Month column as a filter. Even though we are only interested in returning the ItemsSold data, we must include the Month column in the Select portion of the statement (as opposed to only including the ItemsSold column). Otherwise, SQL can’t filter the data based on the month.
The other problem with the statement is that, as you may recall from my original article, the Month column in the table is a text data type. If you try to perform a comparison operation on a text column, you will receive an error message stating, “The data types text and varchar are incompatible in the equal to operator.” In other words, the filter (July) is being treated as VARCHAR data, while the data in the table’s Month column is text data, and you can’t compare the two.
To resolve this problem, we can convert the text data into VARCHAR data. Thankfully, this is easy to do and doesn’t require any modifications to the database table. We only need to add a convert command to the Select statement.
Hence the final Query statement is:
Select Month, ItemsSold From Table1 Where Convert(VARCHAR,Month) = ‘July’;In Figure 4, you can see the original command that produced the error and the correct command. As shown, accessing the number 35 is simply a matter of typing $Data.ItemsSold.
PowerShell SQL Filtering 4
This is how you filter the data within the SQL Select statement.
Select Month, ItemsSold From Table1 Where Convert(VARCHAR,Month Like ‘J%’;You can see the full command in Figure 5.
PowerShell SQL Filtering 5
SQL Server has filtered the results to only include months beginning with the letter J.
Workspace Data Collection
We move on to gathering details about Power BI workspaces. This includes workspace IDs, names, types, states, and related properties.
# Collect list of workspaces from the Power BI Service
# Comment the below line if you do not have Administrator privileges
Get-PowerBIWorkspace -Scope Organization -Include All -All | ForEach-Object {
# Uncomment the below line if you do not have Administrator privileges
# Get-PowerBIWorkspace -Scope Individual -All | ForEach-Object { # Save the workspace info If ($_.Name) { $WorkspaceName = ($_.Name).Replace('"',' ').Trim() } Else { $WorkspaceName = $Null } $DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f ` $_.Id, ` $WorkspaceName, ` $_.Type, ` $_.State, ` $_.IsReadOnly, ` $_.IsOrphaned, ` $_.IsOnDedicatedCapacity, ` $_.CapacityId $DataLine | Add-Content $WorkspaceFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue # <!--Include Sub-Sections Here-->
}4.1 Sub-Section: Users Data Collection
# Save the user info ForEach ($User In $_.Users) { $DataLine = '"{0}","{1}","{2}","{3}"' -f ` $_.Id, ` $User.Identifier, ` $User.PrincipalType, ` $User.AccessRight $DataLine | Add-Content $UserFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue }4.2 Sub-Section: Reports Data Collection
In this subsection, we collect information about reports within each workspace. This includes the report ID, name, and dataset ID.
# Save the report info ForEach ($Report In $_.Reports) { If ($Report.Name) { $ReportName = ($Report.Name).Replace('"',' ').Trim() } Else { $ReportName = $Null } $DataLine = '"{0}","{1}","{2}","{3}"' -f ` $_.Id, ` $Report.Id, ` $ReportName, ` $Report.DatasetId $DataLine | Add-Content $ReportFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue }4.3 Sub-Section: Datasets Data Collection
Here, we gather details about datasets within the workspace. This includes dataset ID, name, and various properties.
# Save the dataset info ForEach ($Dataset In $_.Datasets) { If ($Dataset.Name) { $DatasetName = ($Dataset.Name).Replace('"',' ').Trim() } Else { $DatasetName = $Null } $DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}"' -f ` $_.Id, ` $Dataset.Id, ` $DatasetName, ` $Dataset.ConfiguredBy, ` $Dataset.IsRefreshable, ` $Dataset.IsEffectiveIdentityRequired, ` $Dataset.IsEffectiveIdentityRolesRequired, ` $Dataset.IsOnPremGatewayRequired $DataLine | Add-Content $DatasetFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue }4.4 Sub-Section: Dashboards Data Collection
We gather information about dashboards and tiles in this sub-section. This includes the dashboard ID, name, and whether it is read-only.
# Save the dashboard info ForEach ($Dashboard In $_.Dashboards) { If ($Dashboard.Name) { $DashboardName = ($Dashboard.Name).Replace('"',' ').Trim() } Else { $DashboardName = $Null } $DataLine = '"{0}","{1}","{2}","{3}"' -f ` $_.Id, ` $Dashboard.Id, ` $DashboardName, ` $Dashboard.IsReadOnly $DataLine | Add-Content $DashboardFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue # <!--Include Tiles Sub-Section Here--> }4.4.1 Sub-Section: Tiles Data Collection (Within Dashboards)
This sub-section deals specifically with tiles within the dashboards. It gathers tile information, including title, associated dashboard, report, and dataset.
# Save the tile info $WorkspaceId = $_.Id Try { # Comment the below line if you do not have Administrator privileges Get-PowerBITile -Scope Organization -DashboardId $Dashboard.Id | ForEach-Object { # Uncomment the below line if you do not have Administrator privileges # Get-PowerBITile -Scope Individual -DashboardId $Dashboard.Id | ForEach-Object { If ($_.Title) { $TileTitle = ($_.Title).Replace('"',' ').Trim() } Else { $TileTitle = $Null } $DataLine = '"{0}","{1}","{2}","{3}","{4}","{5}"' -f ` $WorkspaceId, ` $_.Id, ` $TileTitle, ` $Dashboard.Id, ` $_.ReportId, ` $_.DatasetId $DataLine | Add-Content $TileFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue } } Catch { }4.5 Sub-Section: Dataflows Data Collection
# Save the dataflow info ForEach ($Dataflow In $_.Dataflows) { If ($Dataflow.Name) { $DataflowName = ($Dataflow.Name).Replace('"',' ').Trim() } Else { $DataflowName = $Null } $DataLine = '"{0}","{1}","{2}","{3}"' -f ` $_.Id, ` $Dataflow.Id, ` $DataflowName, ` $Dataflow.ConfiguredBy $DataLine | Add-Content $DataflowFileCsv -Encoding UTF8 -ErrorAction SilentlyContinue }Connecting to the Power BI Service
We establish a connection to the Power BI Service. This connection allows us to interact with Power BI resources programmatically.
# Connect to the Power BI Service
Connect-PowerBIServiceAccountGathering Power BI Data
The script is divided into sections, each responsible for gathering specific types of data. Let’s break it down step by step:
About the Author(s)
Brien Posey is a bestselling technology author, a speaker, and a 20X Microsoft MVP. In addition to his ongoing work in IT, Posey has spent the last several years training as a commercial astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space.
