How to Integrate Power BI with Your Headless CMS

What is Power BI?

Power BI is Microsoft’s analytics and business intelligence platform for building dashboards, reports, semantic models, and data apps. Analysts, finance teams, operations teams, marketing teams, and executives use it to combine data from systems like Azure, SQL Server, Excel, Salesforce, and APIs. It’s one of the most common BI tools in Microsoft-centered organizations, especially where reporting needs to connect to Teams, Excel, Fabric, and Microsoft Entra ID.

Why integrate Power BI with a headless CMS?

Content teams often make decisions with stale numbers. A campaign page goes live, product copy changes, or 80 localized articles publish, but the reporting layer still depends on someone exporting a CSV on Friday. Connecting Power BI to a headless CMS category system gives analytics teams a live view of content operations, including publish counts, content types, owner fields, campaign metadata, localization status, and downstream performance data.

The integration works best when content is structured as typed JSON instead of HTML blobs. With Sanity’s AI Content Operating System, content lives in the Content Lake, GROQ selects the exact fields Power BI needs, and webhooks can fire when documents are published, updated, or deleted. That means you can send clean rows into a Power BI semantic model when content changes, rather than polling every 15 minutes or parsing rendered pages.

There are trade-offs. Power BI is built for analytics, not as an operational event queue, so you’ll want to design the semantic model carefully and respect REST API limits. Schema changes also need coordination because a new Sanity field may require a new Power BI column, table, or relationship. Still, the connected setup beats copy-paste reporting because it gives teams one consistent source for content data and one familiar place to analyze it.

Architecture overview

A typical Sanity and Power BI integration starts with structured documents in the Content Lake, such as articles, product pages, authors, campaigns, and locales. A GROQ query selects only the reporting fields Power BI needs, for example title, slug, content type, campaign name, publish date, locale, author, and status. References can be joined in the query, so Power BI receives a flat analytics-friendly row instead of nested editorial data. When a relevant document is published or updated, a Sanity webhook fires. You can point that webhook at a Sanity Function or a small API route. The server-side handler reads the webhook payload, fetches the latest document from Sanity with @sanity/client, maps it to the table shape expected by Power BI, gets a Microsoft Entra access token, and calls the Power BI REST API. For push-style reporting, the handler can call POST /v1.0/myorg/groups/{groupId}/datasets/{datasetId}/tables/{tableName}/rows to add rows to a Power BI semantic model table. From there, Power BI reports and dashboards read the updated semantic model, and end users see content analytics in the Power BI service, Teams, SharePoint, or embedded reports. If you need historical corrections instead of append-only rows, you may choose a scheduled refresh from a warehouse such as Fabric, Azure SQL, or Snowflake instead of pushing every mutation directly.

Common use cases

Step-by-step integration

1. 1

   Set up Power BI API access

   Create or choose a Power BI workspace, then register an app in Microsoft Entra ID. Add the Dataset.ReadWrite.All application permission, grant admin consent, enable service principal access in the Power BI admin portal if your tenant requires it, and add the service principal to the workspace. Create a semantic model with a table that matches the rows you plan to send, such as ContentEvents.

2. 2

   Install the integration dependencies

   In your webhook handler, Sanity Function, or middleware project, install @sanity/client and @azure/identity. You’ll use @sanity/client to fetch typed content with GROQ and @azure/identity to request a Power BI REST API access token.

3. 3

   Model reporting fields in Sanity Studio

   Add schema fields that analytics teams can group and filter by, such as contentType, campaign, locale, owner, publishDate, status, and businessUnit. Keep these as structured fields, not text hidden inside Portable Text, so Power BI can use them as dimensions.

4. 4

   Create the webhook or Function trigger

   Create a Sanity webhook that fires on publish, update, and delete mutations for the document types you want to report on. Use a GROQ-powered filter such as _type in ['article', 'product', 'landingPage'] so the integration doesn’t run for unrelated content.

5. 5

   Map Sanity documents to Power BI rows

   Write a small mapper that turns Sanity documents into the exact Power BI table shape. For example, map a referenced author to authorName, convert publishDate to an ISO string, and include _id as contentId so reports can group repeated events.

6. 6

   Test the report path end to end

   Publish one test document in Sanity Studio, confirm the webhook runs, check that Power BI receives a new row, and build a report using fields like publishDate, contentType, locale, and campaign. Also test updates, deletes, API failures, and duplicate webhook deliveries.

How Sanity + Power BI works

CMS approaches to Power BI

Keep building

Explore related integrations to complete your content stack.