使用Powershell cmdlet从Power Bi服务获取数据

We had some cases where we would like to check what information was within the Power BI service and be able to make a decision in the BI area, based on this data.

在某些情况下，我们希望检查Power BI服务中包含哪些信息，并能够基于此数据在BI区域中做出决策。

Therefore, one way we found to access this data and be able to work it in a database or even within an Excel spreadsheet, was extracting the data through a script in PowerShell and exporting it in a CSV file.

因此，我们发现一种访问该数据并能够在数据库甚至Excel电子表格中工作的方法是通过PowerShell中的脚本提取数据并将其导出为CSV文件。

I’m not a PowerShell expert, but after looking at forums, official documentation and even talking to Microsoft support, I managed to solve the problem we had with the solutions below. So if there is any easier way to complete this proposed solution or good practices that have not been carried out, I apologize in advance.

我不是PowerShell专家，但是在浏览了论坛，官方文档甚至与Microsoft支持交谈之后，我设法解决了以下解决方案所带来的问题。 因此，如果有任何更简便的方法来完成此建议的解决方案或尚未执行的良好做法，我向您致歉。

So, before showing the PowerShell script, it is necessary that the user who will access the service must be an admin user in order to have access to the data without restriction.

因此，在显示PowerShell脚本之前，有必要访问该服务的用户必须是管理员用户才能不受限制地访问数据。

To run the PowerShell script it will also be necessary to install the PowerBI modules available at the link below, if the module is not installed, it will not be possible to run the script successfully. Preferably install the module “MicrosoftPowerBIMgmt.Admin”, which is the Admin module for Power BI Cmdlets, but it’s necessary to install the modules for each problem.

要运行PowerShell脚本，还必须安装下面链接中提供的PowerBI模块，如果未安装该模块，将无法成功运行该脚本。 最好安装模块“ MicrosoftPowerBIMgmt.Admin”，它是Power BI Cmdlet的管理模块，但是有必要为每个问题安装模块。

After installing the module successfully, you should save the script below and run it in PowerShell.

成功安装模块后，应保存以下脚本并在PowerShell中运行它。

The first script, obtains the data using the module “Get-PowerBIWorkspace”, and its function is to return a list of Power BI workspaces, all documentation can be found in the link below as well.

第一个脚本使用“ Get-PowerBIWorkspace”模块获取数据，其功能是返回Power BI工作空间列表，所有文档也可以在下面的链接中找到。

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process
# Import the Power BI admin module
Import-Module MicrosoftPowerBIMgmt.Admin
# Your email to connect to the Power BI service
$username = "Your-Email" 
# Your password to connect to the PowerBI service
$password = "Your-Password" | ConvertTo-SecureString -asPlainText -Force
# Run the credential according to the login and password above
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
# Connect to the Power BI service using credentials
Connect-PowerBIServiceAccount -Credential $credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
    -ConnectionUri https://outlook.office365.com/powershell-liveid/ `
    -Credential $credential `
    -Authentication Basic `
    -AllowRedirection
Import-PSSession $Session
# Variable to receive the codes of the Power BI workspaces
$Workspace = Get-PowerBIWorkspace -Scope Organization -Include All
# Variable to save the final result
$Result = @()
# Loop for each workspace, get the data from the column below
ForEach ($workspace in $Workspace)
    {
        # Create a new object that would store the information
        $ItemResult = New-Object System.Object
        $ItemResult | Add-Member -type NoteProperty -name WorkspaceID -value $workspace.Id
        $ItemResult | Add-Member -type NoteProperty -name WorkspaceName -value $workspace.Name
        $ItemResult | Add-Member -type NoteProperty -name IsReadOnly -value $workspace.IsReadOnly
        $ItemResult | Add-Member -type NoteProperty -name IsOnDedicatedCapacity -value $workspace.IsOnDedicatedCapacity
        $ItemResult | Add-Member -type NoteProperty -name CapacityId -value $workspace.CapacityId
        $ItemResult | Add-Member -type NoteProperty -name Description -value $workspace.Description
        $ItemResult | Add-Member -type NoteProperty -name WorkspaceType -value $workspace.Type
        $ItemResult | Add-Member -type NoteProperty -name State -value $workspace.State
        $ItemResult | Add-Member -type NoteProperty -name IsOrphaned -value $workspace.IsOrphaned
        # Put the item result and append it to the result object
        $Result +=$ItemResult
    }
# To check the final result on the screen
#$Result | Select WorkspaceID, WorkspaceName, IsReadOnly, IsOnDedicatedCapacity, CapacityId, Description, WorkspaceType, State, IsOrphaned, Users, Reports, Dashboards, Datasets, Dataflows, Workbooks | format-table -auto -wrap | Out-String      
# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\WORKSPACE_CSV.csv"
# Exports the result to the CSV file in the directory informed above
$Result | Export-Csv $Dir -NoTypeInformation -Encoding UTF8
# Disconnects from the session
Remove-PSSession $Session
# Disconnects from PowerBI service
Disconnect-PowerBIServiceAccount

For the second script, obtains the data using the module “Get-PowerBIWorkspace”, and its function is to return a list of Power BI workspaces, all documentation can be found in the link below as well. And it is worth noting that the workspace is the maximum hierarchy and then we have datasets, then datasources, etc. So to get a dataset, we need to know which workspace it belongs to.

对于第二个脚本，使用“ Get-PowerBIWorkspace”模块获取数据，其功能是返回Power BI工作区列表，所有文档也可以在下面的链接中找到。 值得注意的是，工作空间是最大的层次结构，然后我们有数据集，然后是数据源等。因此，要获取数据集，我们需要知道它属于哪个工作空间。

To get a datasource, we need to know which workspace it belongs to and also which dataset it pertains to, and so on.

要获取数据源，我们需要知道它属于哪个工作区，以及它属于哪个数据集，依此类推。

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process
# Import the Power BI admin module
Import-Module MicrosoftPowerBIMgmt.Admin
# Your email to connect to the Power BI service
$username = "Your-Email" 
# Your password to connect to the PowerBI service
$password = "Your-Password" | ConvertTo-SecureString -asPlainText -Force
# Run the credential according to the login and password above
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
# Connect to the Power BI service using credentials
Connect-PowerBIServiceAccount -Credential $credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange `
    -ConnectionUri https://outlook.office365.com/powershell-liveid/ `
    -Credential $credential `
    -Authentication Basic `
    -AllowRedirection
Import-PSSession $Session
# Variable to receive the codes of the Power BI workspaces
$Workspace = Get-PowerBIWorkspace -Scope Organization -Include All
# Variable to save the final result
$Result = @()
# Loop for each workspace and for each dataset, get the data from the column below
$DataSets =
ForEach ($workspace in $Workspace)
    {
    Write-Host $workspace.Name
    ForEach ($dataset in (Get-PowerBIDataset -Scope Organization -WorkspaceId $workspace.Id))
        {
        [pscustomobject]@{
            WorkspaceID = $workspace.Id
            DatasetID = $dataset.Id
            DatasetName = $dataset.Name
            ConfiguredBy = $dataset.ConfiguredBy
            DefaultRetentionPolicy = $dataset.DefaultRetentionPolicy
            AddRowsApiEnabled = $dataset.AddRowsApiEnabled
            Tables = $dataset.Tables
            WebUrl = $dataset.WebUrl
            Relationships = $dataset.Relationships
            Datasources = $dataset.Datasources
            DefaultMode = $dataset.DefaultMode
            IsRefreshable = $dataset.IsRefreshable
            IsEffectiveIdentityRequired = $dataset.IsEffectiveIdentityRequired
            IsEffectiveIdentityRolesRequired = $dataset.IsEffectiveIdentityRolesRequired
            IsOnPremGatewayRequired = $dataset.IsOnPremGatewayRequired
            TargetStorageMode = $dataset.TargetStorageMode
            ActualStorage = $dataset.ActualStorage
            CreatedDate = $dataset.CreatedDate
            ContentProviderType = $dataset.ContentProviderType
            }
        }
    }
# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\DATASET_CSV.csv"
# Exports the result to the CSV file in the directory informed above
$Result | Export-Csv $Dir -NoTypeInformation -Encoding UTF8
# Disconnects from the session
Remove-PSSession $Session
# Disconnects from PowerBI service
Disconnect-PowerBIServiceAccount

In this third script, we will access the workspace and dataset using the ClientID, and this ClientID is obtained through an application created in Azure according to the link below, the process of this script is the same, but in different steps:

在第三个脚本中，我们将使用ClientID访问工作区和数据集，并且该ClientID是通过Azure中根据以下链接创建的应用程序获得的，该脚本的过程相同，但步骤不同：

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process
# Import the Power BI admin module
Import-Module MicrosoftPowerBIMgmt.Admin
# Your email to connect to the Power BI service
$username = "Your-Email" 
# Your password to connect to the PowerBI service
$password = "Your-Password" | ConvertTo-SecureString -asPlainText -Force
# Run the credential according to the login and password above
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
# Connect to the Power BI service using credentials
Connect-PowerBIServiceAccount -Credential $credential
# Variable to receive the codes of the Power BI workspaces
$Workspace = Get-PowerBIWorkspace -Scope Organization -Include All
# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\DATASOURCE_CSV.csv"
# Client ID obtained by creating an application in Azure
$clientId = "Your-ClientID"
# Your email to connect to the Power BI service
$username = "Your-Email" 
# Your password to connect to the PowerBI service
$password = "Your-Password"
$body = @{
    "resource" = “https://analysis.windows.net/powerbi/api";
    "client_id" = $clientId;
    "grant_type" = "password";
    "username" = $pbiUsername;
    "password" = $pbiPassword;
    "scope" = "openid"
}
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body
# Loop for each workspace and for each dataset, get the data from the column below
Remove-Item $ExportFile -Force -ErrorAction SilentlyContinue
foreach($workspace in $Workspaces)
{
    # Variable to receive Power BI datasets
    $DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id #| where {$_.isRefreshable -eq $true}
    foreach($dataset in $DataSets)
    {
        # Variable of the dataset ID that will be placed in the API URL
        $DatasetID = $dataset.Id
        # URL that will be used for data extraction, with the DatasetID variable
        $restURL = "https://api.powerbi.com/v1.0/myorg/datasets/$DatasetID/datasources"
        $headers = @{
            "Content-Type" = "application/json";
            "Authorization" = $authResponse.token_type + " " + $authResponse.access_token
        }
        # Receive records via URL
        $Results = Invoke-PowerBIRestMethod -Url $restURL -Method Get | ConvertFrom-Json
        foreach($result in $Results.value)
        {
            $errorDetails = $result.serviceExceptionJson | ConvertFrom-Json -ErrorAction SilentlyContinue
            $ItemResult = New-Object psobject
            $ItemResult | Add-Member -Name "WorkspaceID"       -Value $workspace.Id                     -MemberType NoteProperty
            $ItemResult | Add-Member -Name "DatasetID"         -Value $dataset.Id                       -MemberType NoteProperty
            $ItemResult | Add-Member -Name "connectionDetails" -Value $result.connectionDetails         -MemberType NoteProperty
            $ItemResult | Add-Member -Name "connectionString"  -Value $result.connectionString          -MemberType NoteProperty
            $ItemResult | Add-Member -Name "datasourceId"      -Value $result.datasourceId              -MemberType NoteProperty
            $ItemResult | Add-Member -Name "datasourceType"    -Value $result.datasourceType            -MemberType NoteProperty
            $ItemResult | Add-Member -Name "gatewayId"         -Value $result.gatewayId                 -MemberType NoteProperty
            $ItemResult | Add-Member -Name "name"              -Value $result.name                      -MemberType NoteProperty
            $ItemResult | Add-Member -Name "database"          -Value $result.database                  -MemberType NoteProperty
            $ItemResult | Add-Member -Name "server"            -Value $result.server                    -MemberType NoteProperty
            $ItemResult | Add-Member -Name "url"               -Value $result.url                       -MemberType NoteProperty
            $ItemResult | Add-Member -Name "errorDescription"  -Value $errorDetails.errorDescription    -MemberType NoteProperty
            $ItemResult | Export-Csv -Path $Dir -Append -NoTypeInformation -Encoding UTF8
        }
    }
}   
# Disconnects from PowerBI service
Disconnect-PowerBIServiceAccount

Thas it! We can now obtain Power BI service data in a variety of ways, through PowerShell using Cmdlets commands.

Thas！ 现在，我们可以通过PowerShell使用Cmdlet命令以多种方式获得Power BI服务数据。

The script for this post can be found on my github (@guimatheus92).

这篇文章的脚本可以在我的github(@ guimatheus92)上找到。

翻译自: https://medium.com/@guimatheus92/get-data-from-power-bi-service-with-powershell-cmdlets-6fe6bc5fabb5