Mastering Power BI Usage Metrics and Audit Logs: Tracking Report Adoption, User Activity, and Governance at Scale

Introduction

You've spent weeks building a gorgeous Power BI report. The data model is tight, the visuals are polished, and your stakeholders applauded the demo. Six months later, someone in leadership asks: "Is anyone actually using this thing?" You open your mouth, and nothing comes out — because you genuinely don't know. This scenario plays out constantly in enterprise Power BI environments, and it represents a fundamental governance gap: organizations invest heavily in building analytics, but invest almost nothing in understanding whether those analytics are being used, by whom, and to what effect.

This lesson closes that gap. We're going to work through the complete landscape of Power BI observability — from the built-in Usage Metrics reports that give you a quick view inside a single workspace, all the way to the Microsoft 365 Unified Audit Log and the Power BI Activity Log, which give you a full, organization-wide picture of everything happening across your tenant. You'll learn how to build a proper usage analytics pipeline, how to detect governance risk in your audit data, and how to operationalize adoption tracking as a real business practice rather than a one-time curiosity.

What you'll learn:

• How Power BI's built-in Usage Metrics work, what they capture, and where their limits are
• How to access and query the Power BI Activity Log and Microsoft 365 Unified Audit Log via REST API and PowerShell
• How to design a scalable usage data pipeline that persists historical data in a lakehouse or Azure SQL database
• How to build governance dashboards that surface risk signals like oversharing, stale content, and shadow BI proliferation
• How to connect adoption data to business outcomes and communicate the ROI of your analytics program

---

Prerequisites

This lesson assumes you are comfortable with:

• Power BI Service administration (you either have Power BI Admin or Fabric Admin role, or you can work with someone who does)
• Writing DAX at an intermediate-to-advanced level
• Basic PowerShell scripting or willingness to follow along with provided scripts
• Familiarity with REST APIs and JSON responses
• Some exposure to Azure Data Factory, Fabric pipelines, or equivalent orchestration tools is helpful but not required

You do not need to be a security engineer to benefit from this lesson, but you should understand that audit data contains sensitive user behavior information and should be treated accordingly.

---

Understanding the Two Layers of Power BI Observability

Before writing a single line of code, you need to understand the conceptual architecture of what Microsoft exposes for monitoring. There are two distinct layers, and confusing them leads to bad decisions about what to build.

Layer 1: Usage Metrics — These are Power BI's native, workspace-scoped reports that show view counts, unique viewers, and trending data for reports and dashboards within a single workspace. They are designed for report authors and workspace admins. They're easy to access, require no setup, and are good enough for answering basic questions like "which of my reports are popular?" The major limitation is that they are per-workspace, cover only 90 days of history, and cannot be aggregated across workspaces without extra work.

Layer 2: Activity Log / Unified Audit Log — These are tenant-level event streams that record every meaningful action taken in Power BI: every view, every export, every sharing action, every app installation, every gateway refresh. They require admin access to retrieve and are not available through the Power BI Service UI directly. They are the authoritative source of truth for governance, security investigation, and enterprise-scale adoption analytics.

Most organizations start with Layer 1 because it requires no effort. Mature organizations build on Layer 2 because it gives them real power. The goal of this lesson is to get you to Layer 2.

---

Working with Built-in Usage Metrics Reports

Let's start at the beginning. Every workspace in Power BI Service has a built-in Usage Metrics report that you can access by navigating to a report or dashboard and selecting the "Usage metrics report" option from the "More options" menu (the three-dot ellipsis). When you do this for the first time, Power BI generates the report and creates a dataset behind it that you can actually customize.

What Usage Metrics Actually Captures

The default Usage Metrics report shows you:

• Views: the total number of times a report or dashboard was opened
• Viewers: the count of unique users who viewed it
• View trend: daily views over a trailing 90-day window
• Distribution method: whether users are reaching the report directly, through an app, or through a shared link
• Platform: whether views came from desktop browser, mobile app, or embedded contexts

What it does not show you:

• Which specific pages within a multi-page report were viewed
• How long users spent on the report
• Whether users interacted with filters or drilled down
• Any data export activity
• Cross-workspace aggregation

The Customizable Usage Metrics Dataset

Here's where things get interesting. When Power BI generates the Usage Metrics report, it creates a real dataset in your workspace called "Report Usage Metrics Model." This is a live, queryable dataset with a schema you can explore in Power BI Desktop by connecting to it using the Power BI dataset connector.

The core tables you care about:

ReportGuidList       - maps report GUIDs to human-readable names
DateTable            - standard date dimension
ReportUsageMetrics   - fact table with rows per view event, containing:
                         ReportGuid, ReportName, WorkspaceName,
                         ViewerUserId, ViewerUserName, ReportType,
                         ConsumptionMethod (app vs direct),
                         ViewDate, Platform

You can connect to this dataset from a new Power BI Desktop file and build your own custom views on top of it. This is a legitimate approach for workspace-level reporting, but remember: one dataset per workspace. If you have 50 workspaces, you'll need to aggregate 50 separate datasets, which is where this approach breaks down at scale.

The 90-Day Cliff and Data Retention

Usage Metrics data expires after 90 days with no native archiving option. If you want historical continuity, you need to extract and persist the data yourself. The earliest you should do this is immediately — set up a recurring export pipeline before you reach that 90-day boundary and lose the baseline.

Warning: If you disable and re-enable the tenant setting "Usage metrics for content creators," Power BI may reset the underlying datasets. Coordinate with your admin before toggling this setting.

---

The Power BI Activity Log: Your Real Governance Data Source

The Power BI Activity Log is the right tool for enterprise governance. It captures every user action across the entire tenant and is available through the Power BI Admin REST API. The retention window is 30 days in the raw API (though the underlying Unified Audit Log in Microsoft 365 retains 90 days for standard licenses and up to one year for Microsoft 365 E3/E5).

Activity Log vs. Unified Audit Log: Choosing the Right Source

These two sources overlap significantly, but they differ in important ways:

Dimension	Power BI Activity Log	Unified Audit Log
API Endpoint	api.powerbi.com/v1.0/myorg/admin/activityevents	Microsoft 365 Compliance Center / Search-UnifiedAuditLog
Scope	Power BI only	All Microsoft 365 workloads
Retention	30 days	90 days (standard), 1 year (E3/E5)
Latency	15–30 minutes	Up to 24 hours
Access	Power BI Admin role	Microsoft 365 Compliance or Security Admin
Output Format	JSON array	JSON with additional envelope
Record Limit per Call	5000 events	5000 events

For a Power BI governance pipeline, the Power BI Activity Log is generally the right first choice because it's lower latency, scoped to what you care about, and doesn't require crossing organizational boundaries to get Compliance admin access. However, if you're doing a security investigation or want to correlate Power BI events with SharePoint, Teams, or Azure AD events, you need the Unified Audit Log.

Setting Up API Access

You'll access the Activity Log via service principal authentication. Here's what you need:

1. Register an app in Azure Active Directory (Entra ID)
2. Add the Power BI Service permission Tenant.Read.All (Application permission, not Delegated)
3. Get admin consent granted by your Azure AD admin
4. Note your TenantId, ClientId, and ClientSecret
5. In the Power BI Admin portal, enable "Allow service principals to use read-only Power BI admin APIs" and add your service principal to a security group that has been granted this access

Security note: The service principal that accesses the Activity Log can see the activity of every user in your tenant. Treat its credentials with the same care as a database administrator password. Store the client secret in Azure Key Vault, never in source code or flat files.

Extracting Activity Log Data with PowerShell

The following script extracts a full day of activity log data. Note that the API returns data in one-hour chunks, and each call can return at most 5000 events — in a very active tenant during business hours, you can hit this limit, which is why we process one hour at a time.

# Authenticate and get access token
$tenantId     = "YOUR_TENANT_ID"
$clientId     = "YOUR_CLIENT_ID"
$clientSecret = "YOUR_CLIENT_SECRET"

$tokenUrl = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$tokenBody = @{
    grant_type    = "client_credentials"
    client_id     = $clientId
    client_secret = $clientSecret
    scope         = "https://analysis.windows.net/powerbi/api/.default"
}

$tokenResponse = Invoke-RestMethod -Uri $tokenUrl -Method Post -Body $tokenBody
$accessToken   = $tokenResponse.access_token

# Build headers
$headers = @{ Authorization = "Bearer $accessToken" }

# Extract one day, one hour at a time
$targetDate = (Get-Date).AddDays(-1).ToString("yyyy-MM-dd")
$allEvents  = [System.Collections.Generic.List[PSObject]]::new()

for ($hour = 0; $hour -lt 24; $hour++) {
    $startTime = "{0}T{1:D2}:00:00.000Z" -f $targetDate, $hour
    $endTime   = "{0}T{1:D2}:59:59.999Z" -f $targetDate, $hour

    $uri = "https://api.powerbi.com/v1.0/myorg/admin/activityevents" +
           "?startDateTime='$startTime'&endDateTime='$endTime'"

    do {
        $response      = Invoke-RestMethod -Uri $uri -Headers $headers -Method Get
        $events        = $response.activityEventEntities
        $allEvents.AddRange($events)

        # Handle pagination via continuationUri
        $uri = $response.continuationUri
    } while ($null -ne $uri)
}

# Convert to JSON and save
$allEvents | ConvertTo-Json -Depth 10 |
    Out-File -FilePath ".\activitylog_$targetDate.json" -Encoding utf8

Write-Host "Extracted $($allEvents.Count) events for $targetDate"

Notice the do...while loop handling continuationUri. When a single hour contains more than 5000 events (rare but possible in very large tenants), the API returns a continuation token rather than truncating silently. If you forget to handle pagination, you'll have incomplete data and never know it.

Key Activity Event Types to Monitor

The Activity Log contains dozens of event types. Not all are equally important. Here are the ones that matter most for adoption and governance:

Adoption signals:

• ViewReport — a user opened a report
• ViewDashboard — a user opened a dashboard
• ExportReport — someone exported to PDF, PowerPoint, or CSV
• AnalyzeInExcel — someone used Analyze in Excel
• PrintReport — someone printed a report page

Governance risk signals:

• ShareReport — a user shared a report directly with another user or group
• ShareDashboard — same for dashboards
• PublishToWebReport — someone published a report to the public web (high risk)
• CreateOrgApp — a new Power BI app was published
• ExportArtifact — a PBIX file was downloaded (contains your data model and potentially cached data)
• DeleteReport / DeleteDataset — content was deleted
• SetScheduledRefresh — someone reconfigured a scheduled refresh
• CreateGateway / AddGatewayDataSource — new gateway infrastructure was registered
• CreateDataflow — a new dataflow was created

Shadow BI signals:

• CreateReport — someone created a new report (who? in which workspace?)
• CreateDashboard — new dashboard created
• PublishToWebReport — public web publishing (requires separate admin policy but is worth monitoring regardless)

---

Building a Scalable Usage Data Pipeline

One-off PowerShell extractions are fine for investigations. For operational governance, you need a pipeline that runs daily, handles failures gracefully, and stores data somewhere queryable long-term. Let's design that.

Recommended Architecture

The architecture that works well for most organizations at scale:

Power BI Activity Log API
        |
        v
Azure Data Factory / Fabric Pipeline (daily trigger)
        |
        v
Azure Data Lake Storage Gen2 / OneLake
  (raw JSON, partitioned by date: /activitylog/year=2024/month=11/day=15/)
        |
        v
Fabric Lakehouse / Azure Synapse Analytics (Bronze → Silver transformation)
        |
        v
Power BI semantic model (Governance Dashboard)

The key design principles here:

Keep raw data immutable. Store the JSON exactly as it came from the API in your raw zone. Do not transform in place. If your schema changes (Microsoft has been known to add fields to event payloads without notice), you still have the original data and can reprocess.

Partition by date. The Activity Log has a natural time boundary. Partitioning by year/month/day makes incremental processing straightforward and keeps query costs manageable as the dataset grows.

Normalize in a Silver layer. The raw JSON is nested and inconsistent across event types. Parse it into a flat, columnar format in your Silver layer where each row is one event with typed columns.

Silver Layer Schema Design

Here's a production-ready schema for your normalized activity events table:

CREATE TABLE silver.PowerBIActivityEvents (
    EventId               NVARCHAR(100) NOT NULL,
    RecordType            INT,
    CreationTime          DATETIME2     NOT NULL,   -- UTC timestamp of the event
    Operation             NVARCHAR(200) NOT NULL,   -- e.g., 'ViewReport'
    OrganizationId        NVARCHAR(100),
    UserType              INT,                      -- 0=Regular, 2=ServicePrincipal
    UserKey               NVARCHAR(200),            -- immutable user identifier
    Workload              NVARCHAR(50),             -- always 'PowerBI' here
    UserId                NVARCHAR(500),            -- UPN, e.g., jane.smith@contoso.com
    ClientIP              NVARCHAR(50),
    UserAgent             NVARCHAR(1000),
    Activity              NVARCHAR(200),            -- human-readable version of Operation
    IsSuccess             BIT,
    RequestId             NVARCHAR(100),
    ActivityId            NVARCHAR(100),
    ItemName              NVARCHAR(1000),           -- Report/Dashboard name
    WorkSpaceName         NVARCHAR(500),
    DatasetName           NVARCHAR(500),
    ReportType            NVARCHAR(100),            -- PowerBIReport, PaginatedReport
    ObjectId              NVARCHAR(100),            -- GUID of the artifact
    DatasetId             NVARCHAR(100),
    WorkspaceId           NVARCHAR(100),
    AppName               NVARCHAR(500),
    AppReportId           NVARCHAR(100),
    ConsumptionMethod     NVARCHAR(100),            -- 'App', 'Workspace', 'Embed'
    DistributionMethod    NVARCHAR(100),
    ExportedArtifactType  NVARCHAR(100),            -- for ExportReport events
    SharingScope          NVARCHAR(100),            -- for share events
    RecipientEmail        NVARCHAR(500),            -- for share events
    LoadedAt              DATETIME2 DEFAULT GETUTCDATE(),
    SourceDate            DATE NOT NULL             -- partition key
);

Notice we're capturing UserKey separately from UserId. The UserKey is an opaque, immutable identifier that remains stable even if a user changes their email address — important for tracking behavior across organizational changes.

Handling Schema Variability

One of the trickiest aspects of the Activity Log is that different event types carry different additional fields. A ViewReport event includes ReportType and ConsumptionMethod. A ShareReport event includes RecipientEmail and SharingScope. A ExportReport event includes ExportedArtifactType.

These are all in a nested ArtifactAccessRequestInfo or within the root JSON object depending on the event type. The safest extraction approach is to:

1. Parse the guaranteed top-level fields first (Operation, UserId, CreationTime, etc.)
2. Use conditional extraction for event-specific fields with null handling
3. Store the full original JSON blob in a RawPayload column during Bronze processing so you can always re-derive anything you missed

In a Fabric notebook using PySpark:

from pyspark.sql import functions as F
from pyspark.sql.types import StructType, StringType, IntegerType, TimestampType

# Read raw JSON files from Bronze layer
raw_df = spark.read.json(
    "abfss://raw@yourstorage.dfs.core.windows.net/activitylog/year=2024/month=11/day=15/"
)

# Extract normalized silver layer
silver_df = raw_df.select(
    F.col("Id").alias("EventId"),
    F.col("RecordType").cast(IntegerType()),
    F.to_timestamp(F.col("CreationTime")).alias("CreationTime"),
    F.col("Operation"),
    F.col("OrganizationId"),
    F.col("UserType").cast(IntegerType()),
    F.col("UserKey"),
    F.col("Workload"),
    F.col("UserId"),
    F.col("ClientIP"),
    F.col("UserAgent"),
    F.col("Activity"),
    F.col("IsSuccess").cast("boolean"),
    F.col("RequestId"),
    F.col("ActivityId"),
    F.col("ItemName"),
    F.col("WorkSpaceName"),
    F.col("DatasetName"),
    F.col("ReportType"),
    F.col("ObjectId"),
    F.col("DatasetId"),
    F.col("WorkspaceId"),
    # Conditional fields — use coalesce to handle absence gracefully
    F.coalesce(
        F.col("AppName"),
        F.lit(None).cast(StringType())
    ).alias("AppName"),
    F.col("AppReportId"),
    F.col("ConsumptionMethod"),
    F.col("DistributionMethod"),
    F.col("ExportedArtifactType"),
    F.col("SharingScope"),
    F.col("RecipientEmail"),
    F.current_timestamp().alias("LoadedAt"),
    F.lit("2024-11-15").cast("date").alias("SourceDate")
)

# Write to Silver Delta table with merge to avoid duplicates
silver_df.write \
    .format("delta") \
    .mode("append") \
    .partitionBy("SourceDate") \
    .save("abfss://silver@yourstorage.dfs.core.windows.net/powerbi_activity_events/")

Tip: Use Delta Lake format (or Fabric Lakehouse Delta tables) rather than Parquet for your Silver layer. Delta's ACID transactions and MERGE capability make idempotent pipeline reruns trivial, which matters when your pipeline occasionally fails mid-run and you need to reprocess a day without creating duplicate records.

---

Building the Governance Dashboard

Now that you have clean, historical activity data, let's talk about what to build on top of it. A governance dashboard for Power BI usage should answer four categories of questions:

1. Adoption Analytics

The fundamental adoption questions:

-- Active Users (30-day rolling window)
ActiveUsers_30d = 
CALCULATE(
    DISTINCTCOUNT( ActivityEvents[UserId] ),
    DATESINPERIOD(
        'Date'[Date],
        LASTDATE( 'Date'[Date] ),
        -30,
        DAY
    ),
    ActivityEvents[Operation] IN {
        "ViewReport", "ViewDashboard", "ExportReport", "AnalyzeInExcel"
    }
)

-- Report Adoption Rate (% of licensed users who viewed at least one report this month)
AdoptionRate = 
VAR TotalLicensedUsers = [LicensedUserCount]  -- from your HR/Azure AD dimension
VAR ActiveViewers = 
    CALCULATE(
        DISTINCTCOUNT( ActivityEvents[UserId] ),
        ActivityEvents[Operation] = "ViewReport"
    )
RETURN
    DIVIDE( ActiveViewers, TotalLicensedUsers )

-- Views per Active User (engagement depth metric)
ViewsPerActiveUser = 
DIVIDE(
    CALCULATE( COUNTROWS( ActivityEvents ), ActivityEvents[Operation] = "ViewReport" ),
    CALCULATE( DISTINCTCOUNT( ActivityEvents[UserId] ), ActivityEvents[Operation] = "ViewReport" )
)

A useful pattern for adoption analytics is cohort analysis: track the first time each user viewed any report, then measure how many of those users returned in subsequent weeks. Users who view once and never return represent a different problem than users who never adopted at all.

-- First View Date per User (used in cohort tables)
FirstViewDate = 
CALCULATE(
    MIN( ActivityEvents[CreationTime] ),
    ActivityEvents[Operation] = "ViewReport"
)

2. Content Health Metrics

Which reports are being actively used, and which are digital landfill?

-- Reports with Zero Views in Last 90 Days
StaleReports_90d = 
CALCULATE(
    DISTINCTCOUNT( Reports[ReportId] ),
    FILTER(
        Reports,
        CALCULATE(
            COUNTROWS( ActivityEvents ),
            ActivityEvents[Operation] = "ViewReport",
            DATESINPERIOD( 'Date'[Date], TODAY(), -90, DAY )
        ) = 0
    )
)

The flip side is identifying your most critical reports — those viewed by a large fraction of your user base. These are high-stakes: if they break or go stale, many people notice.

Content health scorecard columns to track:

• Last refreshed (from dataset refresh history)
• Last viewed date
• Total unique viewers (30d / 90d / all-time)
• Owner (from workspace metadata)
• Workspace tier (Premium vs. Pro vs. shared capacity)
• Certified vs. promoted vs. uncertified status

3. Governance Risk Signals

This is where the Activity Log earns its keep. Build a dedicated governance risk view that surfaces events requiring attention:

Public web publishing — Any PublishToWebReport event should generate an immediate alert. These reports are accessible to the entire internet, including unauthenticated users. Even if the report contains no sensitive data today, this is a configuration that needs to be consciously reviewed and documented.

PBIX export events — ExportArtifact events where the export type is PBIX deserve scrutiny. A PBIX file can contain cached data, your full data model logic, and potentially embedded credentials. Monitoring who downloads PBIX files and from which workspaces is basic IP protection.

-- PBIX Downloads in Last 30 Days by User
PBIXDownloads_30d = 
CALCULATE(
    COUNTROWS( ActivityEvents ),
    ActivityEvents[Operation] = "ExportArtifact",
    ActivityEvents[ExportedArtifactType] = "PowerBIReport",
    DATESINPERIOD( 'Date'[Date], TODAY(), -30, DAY )
)

Sharing velocity — When a single user shares many reports in a short window, that's worth investigating. It could be legitimate bulk-onboarding of a new team, or it could be a user sharing sensitive content inappropriately.

-- Users with >10 Share Events in Last 7 Days
HighSharingUsers = 
CALCULATE(
    DISTINCTCOUNT( ActivityEvents[UserId] ),
    ActivityEvents[Operation] IN { "ShareReport", "ShareDashboard" },
    DATESINPERIOD( 'Date'[Date], TODAY(), -7, DAY ),
    FILTER(
        SUMMARIZE(
            ActivityEvents,
            ActivityEvents[UserId],
            "ShareCount", COUNTROWS( ActivityEvents )
        ),
        [ShareCount] > 10
    )
)

Workspace proliferation — Track the count of CreateReport and CreateDashboard events over time. A sudden spike often indicates that a team has started doing shadow BI work in a personal workspace rather than following your governed workspace structure.

4. Capacity and Performance Tracking

For organizations on Premium or Fabric capacity, you can correlate activity log data with capacity utilization metrics to understand which workloads are driving resource consumption. The Power BI Admin API exposes refresh history per dataset; combining that with your event data lets you identify datasets where users frequently trigger manual refreshes because scheduled refresh is unreliable — an operational signal that something is wrong upstream.

---

Enriching Activity Data with the Power BI Admin API

The Activity Log tells you what happened but often not the full context. To answer questions like "which workspace is this report in?", "is this dataset certified?", or "who owns this workspace?", you need to supplement your activity data with workspace and artifact metadata from the Admin API.

The key Admin API endpoints to call on a regular schedule (daily is sufficient):

# Get all workspaces with metadata
GET https://api.powerbi.com/v1.0/myorg/admin/groups?$top=5000&$expand=datasets,reports,users

# Get all datasets across the tenant
GET https://api.powerbi.com/v1.0/myorg/admin/datasets?$top=5000

# Get refresh history for a specific dataset
GET https://api.powerbi.com/v1.0/myorg/admin/datasets/{datasetId}/refreshes?$top=60

# Get all apps
GET https://api.powerbi.com/v1.0/myorg/admin/apps?$top=5000

The workspace inventory endpoint (/admin/groups) is particularly valuable because it returns the isOnDedicatedCapacity flag, the capacity GUID, the workspace type (PersonalGroup vs. Group), and the list of users with their roles. This lets you build a dimension table that links every artifact in your activity events back to its workspace context, owner, and capacity tier.

Tip: The Admin API's /admin/groups endpoint returns up to 5000 workspaces per call. If your tenant has more than 5000 workspaces (which happens more often than you'd expect in large enterprises), you need to paginate using $skip. Consider whether a tenant with 5000+ workspaces needs workspace governance as urgently as usage governance.

Building a Workspace Dimension

CREATE TABLE silver.PowerBIWorkspaces (
    WorkspaceId           NVARCHAR(100) PRIMARY KEY,
    WorkspaceName         NVARCHAR(500),
    WorkspaceType         NVARCHAR(100),  -- 'Group', 'PersonalGroup', 'AdminWorkspace'
    State                 NVARCHAR(50),   -- 'Active', 'Deleted', 'Orphaned'
    IsOnDedicatedCapacity BIT,
    CapacityId            NVARCHAR(100),
    CapacityName          NVARCHAR(200),
    IsReadOnly            BIT,
    DefaultDatasetStorageFormat NVARCHAR(50),
    AdminEmail            NVARCHAR(500),  -- derived from workspace users with Admin role
    MemberCount           INT,
    ReportCount           INT,
    DatasetCount          INT,
    LastActivityDate      DATE,           -- derived from activity events
    SnapshotDate          DATE NOT NULL
);

With this dimension in place, you can filter your governance dashboard by capacity (show me all at-risk content on my Premium P1), by workspace type (show me all suspicious activity in personal workspaces), or by admin (show me all workspaces owned by users who have left the organization — the "orphaned workspace" problem).

---

The Orphaned Workspace Problem

Here's a governance scenario that almost every enterprise faces and that pure usage metrics won't surface: workspaces whose admin has left the organization. When an employee departs, their Power BI content doesn't disappear — it stays in the service, potentially still running refresh jobs, still serving users, but with no one accountable for it.

You can detect this by joining your workspace dimension to your HR/Azure AD user data:

-- Find workspaces where the admin's account is disabled or deleted in Azure AD
SELECT 
    w.WorkspaceId,
    w.WorkspaceName,
    w.AdminEmail,
    w.ReportCount,
    w.DatasetCount,
    u.AccountEnabled,
    u.LastSignInDate
FROM silver.PowerBIWorkspaces w
LEFT JOIN silver.AzureADUsers u ON w.AdminEmail = u.UserPrincipalName
WHERE 
    w.State = 'Active'
    AND (u.AccountEnabled = 0 OR u.UserPrincipalName IS NULL)
ORDER BY w.DatasetCount DESC;

This query will often return surprising results — workspaces with dozens of reports and active refresh schedules, owned by users who left the company months ago. The refresh jobs are either failing silently or running on credentials that will eventually expire. This is exactly the kind of technical debt that audit logs help you systematically address.

---

Communicating Adoption: Connecting Metrics to Business Value

Data teams frequently make the mistake of presenting usage metrics as raw numbers — "we had 342 report views this month" — without connecting them to business value. Here's how to tell a more compelling story.

Adoption Funnel

Frame usage as a funnel with defined stages:

1. Provisioned: user has a Power BI license
2. Onboarded: user has logged into Power BI at least once
3. Activated: user has viewed at least one report
4. Engaged: user views reports at least weekly
5. Power User: user creates reports, builds dashboards, or uses Analyze in Excel

Each transition represents a conversion opportunity. When you present this funnel to leadership, you're not showing them a vanity metric — you're showing them where the friction is and where investment in training or content would have the most leverage.

Cost per Active User

If your organization is on a per-user Power BI Pro license at $10/user/month, and you have 500 licensed users but only 150 are "activated" (have viewed any report in the last 90 days), the implicit cost per active user is $10 × 500 / 150 = $33.33/month per active user. That's a conversation worth having with finance — either you drive adoption up, or you right-size the license count.

Report-Level ROI Stories

Some of your reports exist to replace a manual process. If the Finance team previously spent 8 hours per week manually compiling a report that is now automated in Power BI and viewed by 50 users, you can calculate a rough time savings value. Activity Log data gives you the user count and view frequency to make that calculation credible.

---

Hands-On Exercise

This exercise ties together everything we've covered. You will need Power BI Admin access (or access to a test tenant via the Microsoft 365 developer program).

Exercise: Build a 30-Day Governance Summary Report

Step 1: Extract Activity Log Data

Using the PowerShell script from earlier in this lesson, extract the last 7 days of activity log data from your tenant (or a test tenant). Save each day as a separate JSON file named activitylog_YYYY-MM-DD.json.

Step 2: Load into Power BI Desktop

Open Power BI Desktop and use "Get Data → Folder" to load all seven JSON files at once. Power BI will combine them into a single table. Expand the nested JSON so you have a flat table.

Step 3: Apply the Normalization

In Power Query, create the following calculated columns:

• EventDate = Date only portion of CreationTime
• IsViewEvent = Operation is one of ViewReport, ViewDashboard
• IsShareEvent = Operation is one of ShareReport, ShareDashboard
• IsExportEvent = Operation in ExportReport, ExportArtifact

Step 4: Build These Measures

Total View Events = 
CALCULATE( COUNTROWS( ActivityEvents ), ActivityEvents[IsViewEvent] = TRUE )

Unique Viewers = 
CALCULATE( DISTINCTCOUNT( ActivityEvents[UserId] ), ActivityEvents[IsViewEvent] = TRUE )

Share Events = 
CALCULATE( COUNTROWS( ActivityEvents ), ActivityEvents[IsShareEvent] = TRUE )

Export Events = 
CALCULATE( COUNTROWS( ActivityEvents ), ActivityEvents[IsExportEvent] = TRUE )

Views per Viewer = 
DIVIDE( [Total View Events], [Unique Viewers] )

Step 5: Build Four Visuals

1. A line chart showing daily view events over the 7 days
2. A bar chart of top 10 most-viewed reports (by ItemName)
3. A table of users with more than 2 share events, sorted descending
4. A KPI card showing total export events with a threshold line at your organization's expected baseline

Step 6: Add a Governance Flag Table

Create a calculated table that filters to only PublishToWebReport events:

PublicWebAlerts = 
FILTER(
    ActivityEvents,
    ActivityEvents[Operation] = "PublishToWebReport"
)

Display this as a table visual with columns: CreationTime, UserId, ItemName, WorkSpaceName.

If this table has any rows, that's your first governance finding. If it's empty, that's a good sign your tenant settings are working.

---

Common Mistakes & Troubleshooting

Mistake 1: Assuming Usage Metrics = All Activity

The built-in Usage Metrics report undercounts significantly. It doesn't capture embedded views (reports embedded in Teams tabs or SharePoint pages), it doesn't capture API-driven consumption, and it has quirks around session-level deduplication. Always validate Usage Metrics data against the Activity Log for the same time period before reporting either number to leadership.

Mistake 2: Ignoring Pagination in the Activity Log API

As mentioned earlier, the API returns a maximum of 5000 events per call and a continuationUri when there are more. Many sample scripts online omit the pagination loop. In a tenant with heavy activity during peak hours, this silently drops events. Always implement the do...while continuationUri pattern.

Mistake 3: Using UserPrincipalName as a Stable Identifier

User accounts change their email addresses. People get married, companies go through rebranding. If you join on UserId (which is the UPN in the Activity Log), you'll create identity fragmentation in your history. Always capture UserKey as your join key for user-dimension lookups, and use UPN only for display purposes.

Mistake 4: Not Accounting for Time Zones

The Activity Log timestamps are UTC. Your users are not. If you slice adoption data by "business hours" without converting to local time, you'll draw incorrect conclusions about when content is consumed. Add a time zone offset column to your Date/Time dimension and apply it consistently.

Mistake 5: Building Governance Reports No One Reads

The most technically perfect governance dashboard is worthless if no one is empowered to act on it. Pair your dashboard with a defined process: who reviews it, on what cadence, and what actions are they authorized to take? Common actions include revoking public web sharing, reassigning orphaned workspaces, and revoking licenses for inactive users. Document these processes alongside the dashboard.

Troubleshooting: "The API returns 403 Forbidden"

This usually means one of:

• The service principal hasn't been added to the security group that has admin API access (check the Power BI Admin portal → Tenant settings → "Allow service principals to use read-only Power BI admin APIs")
• The admin consent for the Tenant.Read.All scope hasn't been granted in Azure AD
• Your token is being requested with the wrong scope — make sure you're using https://analysis.windows.net/powerbi/api/.default, not https://graph.microsoft.com/.default

Troubleshooting: "Activity Log shows fewer events than I expect"

Remember that there is a 15–30 minute latency on Activity Log events. If you're querying events from the current day, very recent events may not appear yet. For production pipelines, always extract yesterday's data (not today's) to ensure completeness.

Also verify that your time window in the API call is UTC. If your pipeline runs at midnight local time and constructs the startDateTime as midnight local time rather than midnight UTC, you'll be querying a shifted window and missing events.

---

Advanced Patterns: Anomaly Detection on Usage Data

Once you have months of historical activity data, you can move beyond descriptive reporting into anomaly detection. A simple but effective approach is to calculate a rolling 28-day average view count for each report and flag any day where the actual count deviates by more than two standard deviations.

-- Rolling 28-day average views for the current report
AvgViews_28d = 
CALCULATE(
    AVERAGEX(
        DATESINPERIOD( 'Date'[Date], LASTDATE( 'Date'[Date] ), -28, DAY ),
        [Total View Events]
    )
)

-- Standard deviation of daily views (last 28 days)
StdDev_28d = 
CALCULATE(
    STDEVX.P(
        DATESINPERIOD( 'Date'[Date], LASTDATE( 'Date'[Date] ), -28, DAY ),
        [Total View Events]
    )
)

-- Z-score for current day
ZScore_Views = 
DIVIDE(
    [Total View Events] - [AvgViews_28d],
    [StdDev_28d]
)

A Z-score above 2 or below -2 on a normally-used report deserves investigation. A spike might mean a report was linked in a company-wide email. A drop might mean a data quality issue is discouraging users. Either way, these are signals worth surfacing.

---

Summary & Next Steps

You've covered a lot of ground in this lesson. Let's consolidate the key takeaways:

The layered nature of Power BI observability matters. Built-in Usage Metrics are good for quick, workspace-scoped insights, but the Activity Log is the authoritative source for tenant-wide governance and adoption analytics. Don't make strategic decisions based on Usage Metrics alone.

The Activity Log requires investment to operationalize. You need service principal authentication, a proper pagination strategy, and a persistence layer. The investment pays dividends immediately — you get 30 days of history on first access and can build forward from there.

Govern the pipeline as seriously as the data. The service principal that reads your Activity Log can see what every user in your organization does in Power BI. Protect those credentials, audit access to the governance dashboard itself, and document who is authorized to act on what findings.

Adoption is a business problem, not a technical one. The data tells you what's happening; the hard work is figuring out why and doing something about it. Pair your metrics with stakeholder interviews, training programs, and governance policies that have teeth.

Start small, build deliberately. Don't try to build the full architecture on day one. Start with a PowerShell extraction to CSV, load it into Power BI Desktop, and build three meaningful visuals. Then automate the extraction. Then add the workspace dimension. Then add anomaly detection. The incremental path is less glamorous but far more likely to succeed.

Next Steps

• Set up service principal authentication and run your first Activity Log extraction this week
• Inventory your current workspace estate using the Admin API and identify orphaned workspaces
• Build a simple 7-day adoption summary report and share it with your BI leadership team — let the data start a conversation
• Explore the Power BI Scanner API for even deeper metadata — it exposes column-level lineage and sensitivity labels that complement your activity data
• If your organization uses Microsoft Sentinel, investigate the Power BI connector that forwards audit events to Sentinel for security operations correlation

The data to run a well-governed, measurably impactful Power BI program is all there, waiting in your Activity Log. Now you know how to get at it.