跨工作区Power BI仪表盘使用情况统一监控方案咨询

一、前置准备 #

• 权限配置：确保拥有Power BI管理员权限，或目标工作区的成员/管理员权限；注册Azure AD服务主体，在Power BI管理门户中启用服务主体访问权限，记录租户ID、客户端ID、客户端密钥。
• API权限确认：服务主体需配置Dashboard.Read.All、Group.Read.All、Workspace.Read.All等应用权限。

二、核心API调用逻辑 #

通过Power BI REST API批量拉取跨工作区数据，关键端点及流程：

1. 获取所有工作区：调用GET https://api.powerbi.com/v1.0/myorg/groups，遍历返回的所有工作区ID。
2. 获取工作区内仪表盘：针对每个工作区ID，调用GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/dashboards，获取仪表盘ID和名称。
3. 获取仪表盘使用详情：针对每个仪表盘ID，调用GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/dashboards/{dashboardId}/usageDetails，获取访问时间、用户ID、访问类型等数据。

注意：API返回结果存在分页，需处理@odata.nextLink参数实现全量数据拉取。

三、数据获取实现方式 #

方式1：Power Query（Power BI原生场景） #

在Power BI Desktop中新建空白查询，使用Power Query调用API，示例代码（替换占位符）：

let
    TenantID = "你的租户ID",
    ClientID = "你的客户端ID",
    ClientSecret = "你的客户端密钥",
    // 获取访问令牌
    TokenResponse = Json.Document(Web.Contents("https://login.microsoftonline.com/" & TenantID & "/oauth2/v2.0/token", 
        [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], 
         Content=Text.ToBinary("grant_type=client_credentials&client_id=" & ClientID & "&client_secret=" & ClientSecret & "&scope=https://analysis.windows.net/powerbi/api/.default")])),
    AccessToken = TokenResponse[access_token],
    // 获取所有工作区
    GroupsResponse = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups", 
        [Headers=[#"Authorization"="Bearer " & AccessToken]])),
    GroupsTable = Table.FromList(GroupsResponse[value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedGroups = Table.ExpandRecordColumn(GroupsTable, "Column1", {"id", "name"}, {"GroupID", "GroupName"}),
    // 遍历工作区获取仪表盘
    GetDashboards = (GroupID as text) as table =>
        let
            DashboardsResponse = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/" & GroupID & "/dashboards", 
                [Headers=[#"Authorization"="Bearer " & AccessToken]])),
            DashboardsTable = Table.FromList(DashboardsResponse[value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedDashboards = Table.ExpandRecordColumn(DashboardsTable, "Column1", {"id", "displayName"}, {"DashboardID", "DashboardName"})
        in
            ExpandedDashboards,
    AddDashboards = Table.AddColumn(ExpandedGroups, "Dashboards", each GetDashboards([GroupID])),
    ExpandedDashboards = Table.ExpandTableColumn(AddDashboards, "Dashboards", {"DashboardID", "DashboardName"}, {"DashboardID", "DashboardName"}),
    // 遍历仪表盘获取使用数据
    GetUsageDetails = (GroupID as text, DashboardID as text) as table =>
        let
            UsageResponse = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/groups/" & GroupID & "/dashboards/" & DashboardID & "/usageDetails", 
                [Headers=[#"Authorization"="Bearer " & AccessToken]])),
            UsageTable = Table.FromList(UsageResponse[value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedUsage = Table.ExpandRecordColumn(UsageTable, "Column1", {"userId", "userName", "eventTimestamp", "eventType"}, {"UserID", "UserName", "EventTime", "EventType"})
        in
            ExpandedUsage,
    AddUsage = Table.AddColumn(ExpandedDashboards, "UsageDetails", each GetUsageDetails([GroupID], [DashboardID])),
    ExpandedUsage = Table.ExpandTableColumn(AddUsage, "UsageDetails", {"UserID", "UserName", "EventTime", "EventType"}, {"UserID", "UserName", "EventTime", "EventType"})
in
    ExpandedUsage

方式2：Python脚本（复杂自动化场景） #

使用msal库实现身份验证，调用API并存储数据，示例代码：

import msal
import requests
import pandas as pd

# 配置信息
tenant_id = "你的租户ID"
client_id = "你的客户端ID"
client_secret = "你的客户端密钥"
scope = ["https://analysis.windows.net/powerbi/api/.default"]

# 获取访问令牌
app = msal.ConfidentialClientApplication(client_id, authority=f"https://login.microsoftonline.com/{tenant_id}", client_credential=client_secret)
result = app.acquire_token_for_client(scopes=scope)
access_token = result["access_token"]
headers = {"Authorization": f"Bearer {access_token}"}

# 获取所有工作区
groups_url = "https://api.powerbi.com/v1.0/myorg/groups"
groups_response = requests.get(groups_url, headers=headers).json()
groups = groups_response.get("value", [])

all_usage_data = []

# 遍历工作区
for group in groups:
    group_id = group["id"]
    group_name = group["name"]
    
    # 获取工作区仪表盘
    dashboards_url = f"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/dashboards"
    dashboards_response = requests.get(dashboards_url, headers=headers).json()
    dashboards = dashboards_response.get("value", [])
    
    # 遍历仪表盘获取使用数据
    for dashboard in dashboards:
        dashboard_id = dashboard["id"]
        dashboard_name = dashboard["displayName"]
        
        usage_url = f"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/dashboards/{dashboard_id}/usageDetails"
        usage_response = requests.get(usage_url, headers=headers).json()
        usage_data = usage_response.get("value", [])
        
        # 添加工作区和仪表盘信息
        for item in usage_data:
            item["GroupID"] = group_id
            item["GroupName"] = group_name
            item["DashboardID"] = dashboard_id
            item["DashboardName"] = dashboard_name
            all_usage_data.append(item)

# 转换为DataFrame并存储
df = pd.DataFrame(all_usage_data)
df.to_csv("powerbi_dashboard_usage.csv", index=False)

四、数据建模与可视化 #

1. 将获取到的使用数据导入Power BI Desktop，建立数据模型：关联工作区、仪表盘、用户维度。
2. 创建核心度量值：
   • 总访问次数：COUNTROWS(UsageDetails)
   • 独立用户数：DISTINCTCOUNT(UsageDetails[UserID])
   • 最近访问时间：MAX(UsageDetails[EventTime])
3. 搭建可视化报表：用卡片图展示总览数据，矩阵图按工作区/仪表盘分组展示访问情况，折线图展示访问趋势。

五、自动化更新 #

• Power Query方案：将数据集发布到Power BI服务，配置服务主体凭据后设置刷新计划。
• Python脚本方案：用Azure Functions或Windows任务计划定时运行脚本，将数据同步到Azure SQL/Excel，再设置Power BI报表的数据源刷新。

内容的提问来源于stack exchange，提问作者Yasasvi Karunarathne