PowerShell无法连接PowerBI流式数据集API问题求助

问题概述 #

我有一个流式数据集和对应仪表盘，用来展示用户创建的新工单，数据通过HTTP Post推送到数据集，仪表盘在IT办公室电视上展示。因为仪表盘更新不够明显，我写了PowerShell脚本希望在更新时播放提示音，但执行脚本时无法获取数据集数据，报错提示Failed to retrieve dataset。

所用脚本 #

# Define the necessary variables
$datasetId = "X"
$groupId = "X"
$soundFilePath = "C:\Temp\Wasted.mp3"  # Single backslashes for file path
$previousRowCountFile = "C:\Temp\previousRowCount.txt"  # Single backslashes for file path

# Service principal details
$clientId = "X"
$clientSecret = "X"
$tenantId = "X"

# Function to connect to Power BI using service principal
function Connect-PowerBI {
    $tokenBody = @{
        grant_type    = "client_credentials"
        client_id     = $clientId
        client_secret = [Net.NetworkCredential]::new('',$clientSecret).Password
        scope         = "https://analysis.windows.net/powerbi/api/.default"
    }

    $tokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -Method Post -Body $tokenBody
    $token = $tokenResponse.access_token

    # Return the access token
    return $token
}

# Function to get the current row count of the dataset
function Get-RowCount {
    $accessToken = Connect-PowerBI
    $header = @{
        'Authorization' = "Bearer $accessToken"
        'Content-Type'  = 'application/json'
    }
    
    try {
        $response = Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/tables" -Headers $header -ErrorAction Stop
        $rowCount = $response.Count  # Assuming 'Count' property exists in the response
        return $rowCount
    } catch {
        Write-Error "Failed to retrieve dataset."
        return 0
    }
}

# Function to play sound
function Play-Sound {
    param (
        [string]$soundFile
    )
    Add-Type -TypeDefinition @"
using System.Media;
public class SoundPlayer {
    public static void PlaySound(string path) {
        SoundPlayer player = new SoundPlayer(path);
        player.PlaySync();
    }
}
"@
    [SoundPlayer]::PlaySound($soundFile)
}

# Read the previous row count from the file, if it exists
if (Test-Path $previousRowCountFile) {
    $previousRowCount = [int](Get-Content $previousRowCountFile)
} else {
    $previousRowCount = 0
}

# Get the current row count
$currentRowCount = Get-RowCount

# Compare the current row count with the previous row count
if ($currentRowCount -gt $previousRowCount) {
    # Play the notification sound
    Play-Sound -soundFile $soundFilePath
}

# Save the current row count to the file
Set-Content -Path $previousRowCountFile -Value $currentRowCount

报错信息 #

Get-RowCount : Failed to retrieve dataset.
In C:\Temp\CheckDatasetUpdates.ps1:71 Zeichen:20
+ $currentRowCount = Get-RowCount
+                    ~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-RowCount

已完成的后端配置 #

• 在Azure AD中注册应用
  • 进入Azure门户，导航至“Azure Active Directory”>“App registrations”
  • 点击“New registration”，命名应用（如“PowerBIScriptApp”），选择单租户账户类型后注册
• 配置API权限
  • 注册完成后进入“API permissions”，点击“Add a permission”
  • 选择“Power BI Service”，添加“Application permissions”类型的Dataset.ReadWrite.All权限
  • 点击“Add permissions”后，授予管理员同意
• 创建客户端密钥
  • 进入“Certificates & secrets”，点击“New client secret”，设置描述和有效期后添加
  • 复制并安全存储密钥值
• 配置PowerBI服务主体权限
  • 进入PowerBI管理门户，导航至“Tenant settings”
  • 启用“Allow service principals to use Power BI APIs”，可选择限制为特定安全组

排查与修复步骤 #

1. 完善错误日志，定位具体问题 #

当前Get-RowCount函数的错误处理过于笼统，无法得知具体失败原因。修改函数，输出详细错误信息：

function Get-RowCount {
    $accessToken = Connect-PowerBI
    $header = @{
        'Authorization' = "Bearer $accessToken"
        'Content-Type'  = 'application/json'
    }
    
    try {
        $response = Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/tables" -Headers $header -ErrorAction Stop
        # 流式数据集的表结构返回格式不同，需调整行数获取逻辑
        $rowCount = $response.value[0].rows.Count
        return $rowCount
    } catch {
        Write-Error "Failed to retrieve dataset: $_"
        return 0
    }
}

执行修改后的脚本，会显示具体的错误（如权限不足、URL错误、令牌无效等）。

2. 验证API权限与服务主体权限 #

• 确认已授予的Dataset.ReadWrite.All权限是Application permissions类型，且已完成管理员同意
• 在PowerBI工作区中，将服务主体（应用注册的名称）添加为工作区成员（至少为参与者权限），确保其能访问目标数据集
• 检查PowerBI租户设置中，“Allow service principals to use Power BI APIs”是否已启用，且服务主体在允许的安全组内（如果设置了限制）

3. 修正流式数据集的行数获取逻辑 #

流式数据集的API返回结构与普通数据集不同，原脚本中$response.Count无法正确获取行数。正确的方式是获取第一个表的rows属性的数量：

$rowCount = $response.value[0].rows.Count

4. 验证令牌有效性 #

在Connect-PowerBI函数中添加令牌输出，验证是否成功获取有效令牌：

function Connect-PowerBI {
    $tokenBody = @{
        grant_type    = "client_credentials"
        client_id     = $clientId
        client_secret = [Net.NetworkCredential]::new('',$clientSecret).Password
        scope         = "https://analysis.windows.net/powerbi/api/.default"
    }

    try {
        $tokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" -Method Post -Body $tokenBody -ErrorAction Stop
        $token = $tokenResponse.access_token
        Write-Host "Successfully obtained access token"
        return $token
    } catch {
        Write-Error "Failed to get access token: $_"
        return $null
    }
}

如果无法获取令牌，检查clientId、clientSecret、tenantId是否正确，以及客户端密钥是否过期。

5. 确认数据集和工作区ID的正确性 #

• 登录PowerBI门户，进入目标工作区和数据集
• 从浏览器地址栏获取工作区ID（groups/后的字符串）和数据集ID（datasets/后的字符串），确保脚本中的$groupId和$datasetId与实际一致

内容的提问来源于stack exchange，提问作者Dominic Will