Overview
The Power BI Service data source template connects Validatar to Power BI through the Power BI and Fabric REST APIs, signed in as a Microsoft Entra service principal. It catalogs the workspaces the service principal belongs to, each semantic model with its tables, columns and measures, each report with its pages, and each dashboard with its tiles. Its 48 macros cover what usually breaks a Power BI report: stale data, totals that disagree with the warehouse, visuals bound to fields that no longer exist, orphan and duplicate keys on relationships, measures in an error state, row-level security that leaks, and pages that render blank or show an error.
This is a Python (Script) template. Every call to Power BI is made by Python running wherever the data source's scripts run.
Platform: Power BI Service (Microsoft Fabric)
Connection Category: Script (PythonScript)
Template version: 2.0
Marketplace: Power BI Service - Data Source Template
Note: Version 2.0 replaces version 1.0 on the marketplace. It keeps the macro reference keys of the 1.1 template that circulated before it, so tests built on 1.1 keep their macros, and macros still accept the
Data Sets: <workspace>schema names 1.1 created.
What works on which license
Which checks run depends on the workspace's license and on the role the service principal holds. Every macro's first parameter starts its help text with Requires: and one of these three names. For a one-page version to send to a Power BI admin, see Testing Power BI: What Works on Which License.
| Tier | Works on | What it unlocks |
|---|---|---|
| Any license | Pro (shared capacity), Premium Per User, Fabric, Premium and Embedded workspaces | Catalog ingestion; DAX queries; model tables, columns, measures and relationships; orphan keys, duplicate keys and measure errors; column profiling; refresh history and schedule; data sources and parameters; report pages; dashboard tiles |
| Contributor | Any license, with the service principal as Contributor, Member or Admin on the workspace | Report visuals and the fields each one uses, missing-field checks, the model definition (measure expressions, partitions, roles), row-level security role definitions, catalog contents of models that use row-level security |
| Capacity | Workspaces on Fabric (F), Premium (P) or Embedded (A, EM) capacity. Premium Per User and shared capacity cannot export | Rendering pages and single visuals, reading values off them with OCR, blank-page and error-text checks, visual drift, colour checks, paginated report data, rendering under a row-level security identity |
This article recommends Contributor for the service principal. A workspace Viewer can list items but cannot run DAX unless the model grants it Build permission, and the definition-based macros need write permission on the item.
How It Works
- Catalog. Ingestion builds five kinds of schema, described under Metadata Ingestion. Each semantic model's tables become catalog tables in their own
Model: <workspace> / <model>schema, so template tests and profile sets work over model tables the way they work over database tables. - Model structure without admin rights. Tables, columns, measures and relationships come from the DAX
INFO.VIEWfunctions through the Execute Queries REST API. No admin API is involved and no capacity is needed. - Report structure from the definition. Visual names, types, positions and the model fields each visual uses come from the report's Fabric item definition, in both the PBIR format and the older
report.jsonformat. - Rendering on capacity. The Render macros export a page or a single visual with Export To File, convert the PDF to an image, attach the image to the test result, and read it from the PDF's text layer or with OCR.
- Name lookups. Macros take catalog pickers. Choose a workspace by its schema and the model or report as a table under it. A bare workspace name also works. Names match exactly first, then without regard to case, then as an ID.
Prerequisites
In Microsoft Entra ID
- Register an app and create a client secret. Note the tenant ID, the application (client) ID and the secret value.
- Leave the app's API permissions empty. A service principal that calls the Power BI REST APIs needs no delegated or application permissions on the app registration; its access comes from tenant settings and workspace roles. Remove Power BI permissions such as
Tenant.Read.AllorTenant.ReadWrite.Allif the app has them. They are never used, and admin-consented Power BI permissions stop the service principal from calling the read-only admin APIs. - Create a security group and add the app's service principal to it. Tenant settings are granted to this group.
In the Fabric admin portal (Tenant settings)
A Fabric administrator enables these for the security group:
| Setting | Section | Needed for |
|---|---|---|
| Service principals can call Fabric public APIs (earlier names: Service principals can use Fabric APIs, Allow service principals to use Power BI APIs) | Developer settings | Everything |
| Semantic Model Execute Queries REST API (earlier name: Dataset Execute Queries REST API) | Integration settings | DAX macros, model structure, profiling, model contents in the catalog |
| Export reports as PowerPoint presentations or PDF documents | Export and sharing settings | Render macros (on by default) |
| Export reports as image files | Export and sharing settings | Optional. Lets Render: Visual Value by Name export a single visual as PNG; without it the macro crops the visual out of the page |
In each workspace
Add the service principal, or its security group, to every workspace you want to test, as Contributor (or Member or Admin). What works on which license lists what a lower role loses. A service principal cannot use My workspace.
On the Python runtime
- Every macro:
requests,pandasandnumpy. The template signs in with a plain token request and no longer usesazure-identity. - Render macros only:
pymupdf,pillowandpytesseract, and the Tesseract binary on the same host, which means a Data Agent you control. On Windows the template finds Tesseract inC:\Program Files\Tesseract-OCRwhen it is not onPATH.
pip install requests pandas numpy pymupdf pillow pytesseract
Connection Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| Tenant ID | String | Yes | Microsoft Entra tenant (directory) ID |
| Client ID | String | Yes | Application (client) ID of the app registration |
| Client Secret | Secret | Yes | Client secret value of the app registration |
| Workspace Filter | String | No | Comma-separated workspace names or IDs to ingest. Blank ingests every workspace the service principal is a member of |
| Include Model Contents | Dropdown (Yes/No) | No, default Yes | Yes ingests each model's tables, columns and measures into a Model: schema, at three DAX queries per model. No ingests only the lists of models, reports and dashboards |
| Profile DirectQuery | Dropdown (Yes/No) | No, default No | Yes also profiles DirectQuery and Dual tables. Every profiling query against them runs on the source database |
Execute Queries allows 120 queries a minute per identity. On a tenant with many models, use Workspace Filter, or set Include Model Contents to No and rely on the DAX macros.
Capabilities
Metadata Ingestion
| Schema | Tables | Columns |
|---|---|---|
Workspaces |
One per workspace | None |
Semantic Models: <workspace> |
One per semantic model | None |
Model: <workspace> / <model> |
One per model table, plus a (Measures) table |
The table's columns, typed with the model's data types; every measure in the model is a column of (Measures) |
Reports: <workspace> |
One per report, paginated reports included | The report's pages, in page order |
Dashboards: <workspace> |
One per dashboard | The dashboard's tiles, in tile order |
Auto date tables (LocalDateTable_*, DateTableTemplate_*) and the hidden RowNumber columns are left out. Table and column descriptions from the model land in Validatar's own Description field.
A model with row-level security cannot be queried by a service principal, so its contents come from the model definition (Contributor). When the definition cannot be read either, ingestion lists the model, prints the reason and skips its contents. A failing call for one workspace is printed and skipped rather than failing the run.
Object details land in the Power BI - Catalog Custom Fields set: item type and ID, workspace ID, link, owner, storage mode, whether the model enforces row-level security, column flags and formats, and measure state. Import that set after the template and before the first ingestion. Values whose field does not exist are dropped without an error.
Profiling
The template ships two profile scripts. A profile set runs one of them.
| Profile script | Runs on | Produces |
|---|---|---|
| Profile Model Tables | Tables in Model: schemas |
Table: record_count, column_count. Columns: null_count and null_percent, distinct_count and distinct_percent (blank not counted), and by data type min_numeric, max_numeric, mean_numeric, standard_deviation, zero_count, zero_percent, negative_count, negative_percent, min_date, max_date, blank_count, blank_percent, min_length, max_length, min_string, max_string. On the (Measures) table: measure_value, the grand total of every measure that evaluates |
| Profile Power BI Items | Tables in Semantic Models:, Reports: and Dashboards: schemas |
Models: hours_since_refresh, refresh_failed (1 when the latest attempt failed), measure_error_count. Reports: page_count, and with Contributor visual_count, missing_field_count and page_visual_count per page. Dashboards: tile_count |
Profile Model Tables runs one DAX query per batch of twelve columns, plus one COLUMNSTATISTICS() call for text minimums, maximums and lengths. DirectQuery and Dual tables are skipped unless Profile DirectQuery is Yes. Percents are stored as fractions, so 0.125 displays as 12.5%. The values land in Validatar's profile history, so a profile-based test can watch a measure's total, a table's row count or a report's missing-field count over time.
Macros
Display names carry a prefix so the macro picker groups them. Requires is the tier from What works on which license. Workspace, model, report and page parameters are catalog pickers; the rest are free text.
DAX. The workspace is a Semantic Models: schema and the model a table under it.
| Macro | Requires | Returns |
|---|---|---|
| DAX: Run DAX Statement | Any license | The result of any DAX query starting with EVALUATE. Column names are shortened from Table[Column] to Column where that stays unique |
| DAX: Measure by Column | Any license | Measures grouped by columns, with an optional DAX filter: the shape of most visuals and of the GROUP BY it is reconciled against |
| DAX: Measure Value | Any license | One measure at the grand total, with an optional DAX filter |
| DAX: Table Row Count | Any license | Rows in one model table |
| DAX: Table Rows | Any license | Every row of one model table (Execute Queries returns at most 100,000) |
| DAX: Distinct Count | Any license | Distinct values in one column, blank not counted |
| DAX: Distinct Values | Any license | The distinct values of one column |
| DAX: Row Count by Column | Any license | Rows per value of one column |
| DAX: Rows Matching a Value | Any license | Rows where a column equals a value, compared as text |
| DAX: Column Statistics | Any license | COLUMNSTATISTICS() for every column, or one table's: minimum, maximum, cardinality, maximum length |
Model Table. Built for template tests over Model: schemas: bind Model Schema to {{schema.name}}, Model Table to {{table.name}} and Model Column to {{column.name}}.
| Macro | Requires | Returns |
|---|---|---|
| Model Table: Row Count | Any license | Rows in the table |
| Model Table: Column Blank Count | Any license | Blank rows (BLANK or empty text), total rows and the blank percentage |
| Model Table: Column Distinct Count | Any license | Distinct values, total rows and duplicate rows |
| Model Table: Column Value Counts | Any license | The 1,000 most frequent values with their row counts |
| Model Table: Rows | Any license | The first N rows (default 1,000) |
Model.
| Macro | Requires | Returns |
|---|---|---|
| Model: Tables | Any license | Tables with storage mode, hidden flag and description |
| Model: Columns | Any license | Every column with data type, column type, key, unique and hidden flags, format, sort-by column and folder. Compare two models, or this run with the last, to catch columns added, removed or retyped |
| Model: Measures | Any license | Every measure with its state, data type, folder and home table |
| Model: Relationships | Any license | Every relationship with cardinality, direction and whether it is active |
| Model: Relationship Orphans | Any license | Per relationship: fact keys with no matching dimension row, the rows that carry them, and up to ten sample keys. These rows show as a blank member in visuals |
| Model: Relationship Duplicate Keys | Any license | Per relationship: repeated keys on the side that should be unique. Import models refuse them; DirectQuery models and many-to-many relationships accept them |
| Model: Measure Errors | Any license | Every measure in an error state or that fails at the grand total, with Power BI's message. No rows means every measure evaluates |
| Model: Definition | Contributor | The model definition as rows: measure expressions, partition modes and source queries, relationships, roles and parameters. Part narrows it to one object type |
Refresh.
| Macro | Requires | Returns |
|---|---|---|
| Refresh: Last Successful Refresh | Any license | When the last successful refresh ended, its type, Hours Since Refresh and duration. Import and composite models only |
| Refresh: Latest Refresh Attempt | Any license | The latest refresh whatever its outcome, with the error code and message when it failed |
| Refresh: Model Answers a Query | Any license | Counts one table's rows, choosing a DirectQuery table when there is one so the query reaches the source. Status is OK, Error, or RLS for a model the service principal cannot query. This is the freshness check for DirectQuery models |
| Refresh: Refresh History | Any license | Recent refreshes with start, end, status, duration and error |
| Refresh: Refresh Schedule | Any license | Whether the schedule is on, its days, times and time zone |
| Refresh: Data Sources | Any license | The model's data sources with server, database and gateway |
| Refresh: Model Parameters | Any license | Power Query parameters with their current values, such as the server a production model must point at |
Report. The workspace is a Reports: or Dashboards: schema.
| Macro | Requires | Returns |
|---|---|---|
| Report: Pages | Any license | Pages with display name, internal name and order |
| Report: Visuals | Contributor | Every visual: page, name, type, title, position, size and the model fields it uses |
| Report: Missing Fields | Contributor | Every field a visual uses that no longer exists in the bound model. No rows means every field resolves |
| Report: Bound Model | Any license | The semantic model and workspace the report is bound to |
| Report: Dashboard Tiles | Any license | Every tile with the report and model it points at, and whether those still exist |
Render. All of these need capacity. Visual Value by Name, Page Health, Page Text and Visual Fingerprint also take an optional report filter in URL filter syntax (Store/Territory eq 'NC') and an optional user and role to render under row-level security. Images are rendered at 144 dpi and attached to the result.
| Macro | Requires | Returns |
|---|---|---|
| Render: Visual Value by Name | Capacity | One visual read by its name or title: each text line, the number in it and the OCR confidence. Given the visual's name, it exports the visual on its own. Given a title, or a visual Power BI will not export alone, it finds the visual in the report definition and crops it from the page by position; that path also needs Contributor |
| Render: Page Health | Capacity | One row per page: blank render, Power BI's failure text ("Can't display this visual", "Couldn't retrieve the data for this visual", "Something went wrong", "See details" and others), word count and OCR confidence |
| Render: Page Text | Capacity | Every line of text on a page, from the PDF's text layer when Power BI wrote one, otherwise from OCR |
| Render: Visual Fingerprint | Capacity | Numbers that move when a page or visual changes: the share of pixels in eight luminance bands and the mean luminance of a 4 x 4 grid. Compare them with the average of the test's previous runs |
| Render: Color Share | Capacity | Percentage of a page's or visual's pixels within a tolerance of a colour, such as a brand colour |
| Render: Page Image | Capacity | The page's size, whether it rendered blank and its dominant colour, with the image attached |
| Render: Region Text by Coordinates | Capacity | OCR of one rectangle of a page. Run once with Coordinates blank, read the box off the attached Report.png, then enter (left, top, right, bottom) |
| Render: Region Text by Coordinates with Filter | Capacity | The same, with a report filter |
| Render: Region Text by Coordinates as User | Capacity | The same, rendered as a user under a role |
| Render: Paginated Report Data | Capacity | A paginated report exported to CSV, as rows. Parameters are Name=Value pairs separated by semicolons |
RLS and Action.
| Macro | Requires | Returns |
|---|---|---|
| RLS: Role Definitions | Contributor | Each role's table filter expressions and member count |
| RLS: Fact Tables Not Filtered by a Role | Contributor | For each role, the fact tables its filters do not reach through the model's relationships. Members of that role see every row of those tables |
| Action: Refresh Model and Wait | Any license | A test action that refreshes a model and waits for it, so the test that follows reads fresh data. It writes to Power BI and counts against the model's daily refresh limit |
Row-Level Security
Power BI does not let a service principal run DAX against a model with row-level security or single sign-on, whatever user it names; those models answer 401. Two paths cover the checks people need:
- Role definitions (Contributor, any license). RLS: Role Definitions reads each role's filters from the model definition, and RLS: Fact Tables Not Filtered by a Role follows them through the relationships to find fact tables a role leaves open. Nobody is impersonated.
- Rendering as a user (Capacity). The Render macros accept a user name and roles and pass them to Export To File as an effective identity. The service principal needs Contributor or Admin on the workspace and write permission on the model. For dynamic roles the user name does not have to be a real account.
Running DAX as a named user needs a signed-in Power BI user rather than a service principal, so the template does not offer it. Render the report as that user instead (path 2), or test the role definitions (path 1).
Installation
- Import the template. Open Marketplace in Validatar, find Power BI Service - Data Source Template and choose Get, then Import Directly. Validatar adds it under Settings > Data Source Templates.
- Import the custom fields. In Marketplace, import Power BI - Catalog Custom Fields. Its sections show only on data sources built from this template, and the rule that hides them names the template, so import the template first.
- Create a data source from the template. Fill in Tenant ID, Client ID and Client Secret, and Workspace Filter if you want only some workspaces. Assign a Data Agent group if you will use the Render macros.
- Run metadata ingestion and check the catalog: one
Semantic Models:,Reports:andDashboards:schema per workspace, and oneModel:schema per model. - Add profile sets if you want profiling: one on Profile Model Tables scoped to the
Model:schemas, one on Profile Power BI Items scoped to the other schemas. - Optional: import the Power BI Sample Tests pack for working examples of every tier.
To install from a file instead, download the template from the marketplace listing and import it under Settings > Data Source Templates > Import.
After importing a newer version of the template, open each Power BI data source, re-select the template, confirm the parameters and save. A data source keeps its own copy of the template's pre-execution script until it is saved.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
Sign-in for the service principal failed |
Wrong tenant ID, client ID or secret, or the secret expired | Create a new client secret and update the data source |
No workspaces to ingest |
The service principal is in no workspace, or Workspace Filter matches none | Add it to the workspaces as Contributor; check the filter's spelling |
| Execute Queries returns 401 and the message mentions row-level security | The model enforces RLS, which a service principal cannot query | Use the RLS macros, or a Render macro with a user and role |
| Execute Queries returns 401 on a model without RLS | The tenant setting is off, or the service principal lacks Build permission on the model | Enable Semantic Model Execute Queries REST API for its group; make it Contributor on the workspace |
Reading the ... definition was refused |
The service principal is a Viewer | Make it Contributor on the workspace |
| A model's definition cannot be read even as Contributor | The model carries an encrypted sensitivity label, which blocks the definition API | Test that model through DAX |
| Export returns 403 and mentions capacity | The workspace is on shared or Premium Per User capacity | Move the workspace, and every model its reports use, to Fabric, Premium or Embedded capacity |
| Export fails for one report only | The report has a sensitivity label, and a service principal cannot export a labelled report to PDF | Remove the label from a test copy, or test that report through DAX |
has no refresh history: it is a DirectQuery or live-connection model |
Refresh history exists only for import and composite models | Use Refresh: Model Answers a Query |
Throttled by Power BI in the log, runs slow |
Execute Queries allows 120 queries a minute per identity | The template waits and retries. Narrow Workspace Filter or set Include Model Contents to No |
Ingestion prints Skipped contents of '<model>' |
The model uses RLS and its definition could not be read | Make the service principal Contributor |
The Render macros need PyMuPDF, Pillow and pytesseract |
The packages or Tesseract are missing where the script ran | Install them on the Data Agent and assign the data source to that agent's group |
Page '...' not found or Visual '...' not found |
The name differs from the one the report uses | The error lists the names that exist; Report: Pages and Report: Visuals list them too |
Note: Model structure comes from the
INFO.VIEWDAX functions through Execute Queries. Microsoft's Execute Queries reference lists INFO functions as unsupported. TheINFO.VIEWfamily runs there today and the rest of the INFO family does not. If Microsoft blocks it, the model-structure macros fail with Power BI's message while DAX against your own tables keeps working.