Power BI

Power BI RLS and Fabric Data Agent considerations

Posted by Jens Vestergaard on in Community 1 Comment

In the last post I walked through auditing a semantic model before connecting it to an AI tool like Fabric Data Agent. Descriptions, naming, explicit measures, star schema: the things that decide whether a Fabric data agent generates an accurate query or a confident wrong one. I left one thing out on purpose, because it deserved its own post and because I got it wrong the first time I thought about it.

Security.

Here is the setup. I inherited a model. It had row-level security on it. Sales people saw their own region, the finance role saw everything, and the reports had been running like that for two years without a complaint. So when the request came in to connect a data agent to the workspace, I ticked the security box in my head and moved on. RLS was handled.

It was handled for people browsing reports. That is not the same thing as handled for an agent answering questions over the model, and the gap between those two sentences is where the trouble lives.

What I got wrong first

My first assumption was the dramatic one: the agent runs as some service identity, ignores the report layer, and reads everything. That is wrong, and the reason it is wrong is the useful part.

A Fabric data agent honors the security on the underlying data. Microsoft is explicit: the agent honors all user permissions to the data, including row-level security and column-level security, and for a Power BI semantic model the querying user only needs Read permission on the model, not workspace access, as documented in Fabric data agent sharing and permission management. The data agent concepts page repeats it in the limitations section: access through a data agent is governed by Read permission, and RLS and CLS still apply. So the agent is not a backdoor. It queries as the person asking the question, and that person’s RLS role filters the rows.

Good. So where is the problem? The problem is everything that decides what “the person’s RLS role” actually evaluates to. RLS is only as strong as the identity model around it, and that is where I had two real holes.

Hole one: RLS only binds Viewers

This is the one that catches people, and it caught me.

Row-level security only restricts users with the Viewer role in the workspace. It does not apply to Admins, Members, or Contributors. Those three roles carry edit permission on the semantic model, and a user who can edit the model can read all of it. Microsoft says it plainly in Row-level security (RLS) with Power BI: if you want RLS to apply to someone in a workspace, the only role you can give them is Viewer. Even a Viewer with Build permission still gets filtered. A Contributor does not.

Now connect that to the agent. The agent filters by the asking user’s identity. If the people allowed to ask the agent questions sit in the workspace as Contributors, because that is how the workspace was set up back when it was three developers and no governance, then RLS evaluates to “see everything” for every one of them. The agent is behaving correctly. It is honoring RLS. RLS just happens to be off for those identities.

The same rule applies to object-level security. OLS only binds Viewers too, as the object-level security documentation states in its limitations. The Admin, Member, and Contributor roles see the secured tables and columns regardless.

So the report side and the model side diverge exactly here. A Contributor browsing a report might still look safe, because the report only contains the visuals someone built. Point that same Contributor’s identity at the model through an agent that can write arbitrary DAX, and there is no report layer left to hide behind.

Hole two: service principals sit outside RLS entirely

The second hole is worse, because it fails silently in both directions.

Service principals cannot be added to an RLS role. The consequence, in Microsoft’s own words, is that RLS is not applied for apps using a service principal as the final effective identity. That is point three in the RLS considerations and limitations. The XMLA endpoint documentation says the same thing from the other side: service principals do not work with RLS and OLS and cannot be added as model role members.

This matters the moment any part of the chain runs as a service principal rather than a signed-in user. Automation, an embedded app, a fixed-identity Direct Lake model, a pipeline that calls the model. If the effective identity is a service principal, static RLS is simply not applied, and dynamic RLS is worse than not applied: USERPRINCIPALNAME() and USERNAME() return the application ID or an empty string for a service principal, so a filter written as [Region] = USERPRINCIPALNAME() matches nothing, or matches the wrong thing, depending on how the model was built. You either leak everything or you hand back silently incorrect slices, and neither failure announces itself.

So before connecting anything, I now need to know exactly what identity the thing on the other end runs as. “It uses RLS” is not an answer. “It queries as the signed-in user, who is a Viewer, whose role filters to their region” is an answer.

Hiding a column is not securing it

This one is subtle and I want to be careful with it.

When you set a column to hidden in the model, you remove it from the field list. You have not secured it. A hidden column is still queryable. Microsoft makes the equivalent point about perspectives, which are also just a curated view: a user can still query a table or column even when it is not visible to them, so a perspective is a convenience, not a security feature. That note lives in the report consumer security planning guidance. The hidden flag is the same kind of convenience. The agent generating DAX is not limited to your field list. It reads the model metadata, and hidden objects are still part of the model.

The actual controls are object-level security and column-level security. OLS removes the table or column for a role so completely that, to that role, it does not exist, and unlike RLS it also hides the object name and metadata, so the field cannot even be discovered. Column-level security does the same at the column grain by setting the metadata permission to none, as shown in the Analysis Services object-level security reference. You cannot author OLS in Power BI Desktop today. You set it in Tabular Editor or another external tool by setting the table or column permission to None for the role. And, to close the loop, OLS binds Viewers only, same as RLS.

So if there is a salary column, or a cost column, or anything that should not reach the agent, hiding it does nothing. Securing it with OLS or CLS does, for Viewer identities, and for no one else.

Report-level safe is not model-level safe

If there is one sentence to take from this, it is that one. Reports hide visuals. The model still answers DAX. An agent talks to the model, not to the report, so every assumption that lived in the report layer needs to be re-checked at the model layer before the agent goes live.

The check I run now

Before I connect a semantic model to a data agent, I work through this. It is short on purpose.

  1. List every RLS role and write down, in one line each, exactly what it filters. If you cannot describe a role’s filter in a sentence, you do not yet understand what the agent will return for its members.
  2. List the members of each role, and then list everyone with Admin, Member, or Contributor on the workspace. That second list is the bypass list. For those identities RLS and OLS do not apply at all. Decide whether every name on it should genuinely see all rows.
  3. Identify the agent’s effective identity. If it is a service principal or a fixed identity, RLS is not being applied, and you cannot fix that by adding the principal to a role, because you cannot add it to a role. Reach for a different pattern, such as a model whose fixed identity is itself scoped to safe data, or per-user identity passthrough.
  4. Check sensitive columns for OLS or CLS, not for the hidden flag. The hidden flag is decoration. Confirm the salary-shaped columns are actually secured, and remember that the security only binds Viewers.
  5. Test as the agent’s identity, not as yourself. The Test as role feature evaluates dynamic RLS using your own UPN, so it cannot show you what a service principal or a guest would see. Where it matters, sign in as the real identity, or run the same DAX the agent would run, as that identity, and look at the rows that come back.
  6. Default everyone who only needs to ask questions to Read on the model, nothing more. The agent needs Read, not Build, not a workspace role. Least privilege here is not a nicety. It is the only configuration where RLS actually does its job.

Where I am still unsure

I have not fully mapped how a Fabric data agent resolves identity across every sharing mode it offers. The sharing documentation is clear that the agent honors RLS and CLS and that consumers need Read on the model, and that lines up with the per-user story. What I want to test next is the boundary cases: an agent shared with a consumer who has no workspace role at all, a model on a fixed identity, and an agent invoked from automation rather than a chat window. I expect the identity that reaches the model to differ across those, and the difference is exactly what decides whether RLS holds. When I have run it, that is the next post.

Until then, the rule I am keeping is the unglamorous one. Before the agent, list the roles, list who bypasses them, find out what identity actually hits the model, and test as that identity. Not as me.

Sources

Power BI prep for AI routine

Posted by Jens Vestergaard on in Community 0 Comment

In the Fabric Ontology post from a couple of months ago, the third item on the pre-agent checklist was “build the semantic foundation first.” I moved on from that line pretty quickly. It deserved more than a sentence.

Most Power BI developers who have been in the field for a few years have inherited at least one model they did not design. It has measures with names like Amt_Adj_Net2 and FlagActCust, columns that were added for a specific report in 2021 and never cleaned up, and business logic that someone once explained verbally in a meeting. No descriptions. No documentation. The people who built it have either moved on or are too busy to explain it.

Now you are being asked to connect that model to a Fabric data agent. Or Power BI Copilot. And suddenly the question “what do these fields actually mean” has a hard deadline.

This post is a checklist. It is what I would do before connecting any semantic model to an AI tool, and it works backward from how those tools actually read your model.

How AI agents read your semantic model

When a Fabric data agent gets a question, it does not look at your data. It reads your metadata. The DAX generation tool builds queries from the model schema, descriptions, synonyms, relationships, data types, and the configuration you set in Prep data for AI, then validates and executes the query against the model. Microsoft documents this query flow in the semantic model best practices for data agent guide.

Copilot in Power BI works the same way. The principle generalises: a natural-language-to-query tool sitting on top of a model reads the metadata, not the data.

If your metadata is clear, the AI generates accurate queries. If it is ambiguous or missing, the AI guesses. And when an AI guesses about business logic, the answers look correct but frequently are not.

The audit checklist

1. Explicit measures over implicit aggregation

An implicit measure is what you get when a report visual or Q&A aggregates a numeric column on the fly. The number is calculated by the consuming layer, not defined as a calculation in the model. An explicit measure is a DAX expression you write and store in the model.

Microsoft’s own guidance for the Fabric data agent is direct on this: relying on implicit measures leads to unpredictable results, so you should create explicit DAX measures for the calculations you want users to query, and set the correct default summarization on numeric columns to prevent unintended aggregations. The optimize your semantic model for Copilot page makes the same point: include a set of predefined measures that users are most likely to request.

Go through every numeric column and ask: is there an explicit DAX measure for the business calculation it feeds? If not, and it matters for the questions the AI will receive, create one. This is the highest-priority item on the list. A model that leans on implicit aggregation will produce inconsistent answers from any AI tool regardless of how well everything else is documented.

2. Names that mean something

Go through every table name, column name, and measure name. For each one, ask: could a new developer understand what this is from the name alone, with no other context?

SalesAmount is fine. Amt is not. IsActiveCustomer is fine. Flag1 is not. Date on a calendar table is fine. D is not.

Microsoft lists non-descriptive naming as a common pitfall for the data agent, using exactly these kinds of examples: object names like TR_AMT, F_SLS, or DIM_GEO_01 give the DAX generation tool no context. Use clear, business-friendly names such as Total Revenue, Sales, or Customer Geography.

Rename anything that requires decoding. Yes, this will break existing reports if you rename columns, so do it carefully and test. If you genuinely cannot rename an object, make sure its description and synonyms carry the context instead. But an AI that can’t interpret TrxTypCd will give you wrong answers, and changing the name now is cheaper than debugging wrong outputs later.

3. Descriptions on measures

The description field on a measure is passed directly to the AI as part of its schema context. A measure without a description forces the agent to infer meaning from the DAX expression alone.

For a measure that calculates CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR)), the DAX is interpretable. For a measure that calls four other measures and has a name like Adj_Rev_Cy, it is not.

Use the TMDL triple-slash syntax if you are working in PBIP format:

/// Net revenue adjusted for returns and currency differences, current year
measure 'Adj Rev CY' = ...

If you are working in Power BI Desktop, the description field is in the properties pane.

Keep the description tight. In the DAX query view grounding path, Microsoft documents that measure descriptions are truncated after the first 200 characters when passed to Copilot as context. Front-load the meaning in the first sentence rather than burying it under a paragraph of caveats.

For a model with fifty or more measures and no descriptions, this is a significant amount of work. Power BI has a built-in Copilot button to generate measure descriptions from the DAX formula, which is a reasonable shortcut for a first pass. In a follow-up post I will cover that workflow and its limitations in more detail.

4. Hide what should not be queryable

Surrogate keys, flag columns, internal technical fields. If a column has no business meaning and is not needed for navigation, hide it. Hidden columns are invisible to AI tools. Fewer columns in the schema means less noise for the AI and fewer opportunities for a wrong-turn query.

A model with 180 visible columns where 120 of them are source system artefacts is harder for an AI to navigate than a model with 60 columns where everything is intentionally exposed.

5. Verify the star schema structure

Fact tables should contain measurements and foreign keys. Dimension tables should describe entities. Microsoft is explicit that DAX is optimised for star schema with clear fact and dimension tables, and lists flat or denormalised structures as a pitfall for the data agent. If you have many-to-many relationships, bidirectional filter behaviour, circular references, or deeply nested snowflake structures, the AI will not interpret them the way you intend.

This does not mean every unusual pattern is wrong. It means you need to know where they are, understand what they do, and either clean them up or document them in the AI instructions (see item 6 below).

Role-playing dimensions deserve specific attention. If your Date table participates in three relationships (order date, ship date, due date) and only one is active, the AI will use the active one by default for every date-related question unless you tell it otherwise. Microsoft’s guidance for ambiguous date fields is to use verified answers and AI instructions to specify which date field to use for each type of question.

6. Add AI instructions to the model

Power BI has a feature called AI instructions. It is free-text guidance that Copilot interprets, authored through the Prep data for AI experience in Power BI Desktop or in the semantic model settings in the Power BI service. The model needs Power BI Q&A turned on before the Prep data for AI tabs become available. Use AI instructions to:

  • Define domain-specific acronyms and abbreviations that appear in your field names
  • Direct the AI toward the correct measure for each type of question
  • Note any calculation quirks around fiscal year, currency conversion, or time intelligence
  • Explain role-playing dimension behaviour

From a product perspective this is metadata you configure, not a file you hand-edit. PBIP and TMDL projects may surface it as a project artifact on disk, but Microsoft documents the feature as AI instructions and the supported authoring path is the Prep data for AI surface. One caveat worth knowing for the data agent specifically: the DAX generation tool only reads the AI instructions you set in Prep data for AI, not instructions added at the data agent level, so keep model-specific guidance in Prep data for AI.

7. Scope the AI data schema

Alongside AI instructions, the Prep data for AI experience has a Simplify data schema tab where you define a focused subset of fields for Copilot to prioritise. If your model has 200 fields but only 50 are relevant for the business questions the AI will receive, scope the schema to those 50. When you include a measure, include its dependent objects too, or the generated DAX can fail.

Synonyms are a separate mechanism. If your users ask about “revenue” but the measure is called “Net Sales Amount”, you map those terms through Q&A linguistic modelling, not through the AI data schema. Both feed the same goal of reducing ambiguity, but they are configured in different places.

8. Mark the model as Approved for Copilot

Once the above steps are complete, mark the semantic model as Approved for Copilot in the Power BI service settings. The setting was previously called “prepped for AI”. Marking it removes the friction warnings that standalone Copilot shows on answers from models that have not been approved, and reports built on that model inherit the approval.

It is also a signal to your organisation that this model has been intentionally prepared for AI use.

What to do with the findings

This audit will produce a list. Some items are quick (renaming a column, hiding a key). Some are not (creating 40 explicit measures for a model that has been running on implicit ones for three years without anyone noticing).

Prioritise by impact. Start with the tables and measures the AI will actually touch. You do not need to fix the entire model before connecting anything to an AI tool. You need to fix the parts that will be queried.

The second thing this audit tends to reveal: there is no one who knows what most of these measures actually calculate. The descriptions you need to write require conversations with people who may no longer be at the company. That is a different problem to solve. But knowing you have it is better than finding out after the AI agent gives the board a wrong number.

When you work out how to describe a measure you did not write, and no one who wrote it is available, the answer is in the next post.

Sources

Semantic Model Diff

Posted by Jens Vestergaard on in Community 1 Comment

…a change tracker for Power BI semantic models

Semantic models do not have version control. Not really. You can store .pbip files in Git, and Tabular Editor gives you a .bim file you can diff, but neither of those workflows answers the simplest question a team asks after an update cycle: what changed?

I do not mean “which file was touched.” I mean: which measures were modified, which columns were added, which relationships were removed, and what exactly is different in the DAX expression that someone edited last Tuesday. That is the question I kept running into, and the one I built this notebook to answer.

This is my third submission to the Fabric Semantic Link Developer Experience Challenge. The first two (a DAX unit test harness and a lakehouse validation notebook) test whether the model produces correct results. This one tests whether the model itself has changed, and tells you precisely how.

The problem

Every Power BI developer has been in this situation. A semantic model gets promoted from dev to production. Something breaks. A report shows unexpected numbers, or a relationship is missing, or a column that used to exist is gone. The first question is always: “What changed since last time?”

Without a structured answer, the debugging starts. Someone opens Tabular Editor and clicks through tables. Someone else opens the old .bim file in a text editor and does a manual comparison. If the model has 40 tables and 200 measures, that process takes long enough that you start questioning your career choices.

I wanted something I could run in a Fabric notebook that would snapshot the full model metadata, store it as JSON, and then produce a colour-coded diff between any two snapshots. No manual clicking. No text-editor heroics.

How it works

The notebook has two engines: a snapshot engine and a diff engine.

The snapshot engine connects to a semantic model via the Tabular Object Model (TOM), exposed through semantic-link-labs, and captures everything: tables, columns, measures, relationships, partitions, roles, and shared expressions. Each object gets recorded with all its properties. A table entry includes its name, description, hidden flag, data category, and type (regular table, calculated table, or calculation group). A measure entry includes the full DAX expression, format string, display folder, and whether it has a KPI attached.

The snapshot is saved as a plain JSON file in the attached Lakehouse’s Files area. File names encode the dataset name and UTC timestamp, so snapshots sort chronologically and you can keep as many as you need.

The diff engine takes any two snapshots and compares them object by object. Each object type has a natural key (for tables, the name; for columns, the table plus column name; for relationships, the relationship name). Objects are categorised as Added, Removed, or Modified. For modified objects, the engine reports exactly which properties changed and shows the old and new values side by side.

What gets captured

The snapshot covers seven object types:

  • Tables: name, description, hidden, data category, type
  • Columns: data type, format string, calculated column expression, display folder, sort-by column, summarize-by
  • Measures: DAX expression, format string, display folder, KPI presence
  • Relationships: from/to table and column, active flag, cross-filter direction, cardinality
  • Partitions: mode, source type, query or M expression
  • Roles: RLS row filter expressions per table
  • Shared expressions: M parameters and Power Query definitions

That last category matters more than you might expect. Shared expressions include connection strings and parameter values. If someone changed the data source path in a parameter, this diff catches it.

The diff output

The diff produces a summary table first: one row per object type, with counts of added, removed, and modified objects. In a clean promotion where nothing changed, all counts are zero and you get a green “No changes detected” message. That is the happy path.

When changes exist, the notebook renders a colour-coded DataFrame per object type. Green rows for additions, red for removals, amber for modifications. Modified rows show the specific property, old value, and new value on the same line. If a measure’s DAX expression changed, you see the full old expression and the full new expression right there.

The notebook also exports the full diff as a self-contained HTML file. I use that for sharing with team members who are not running notebooks. Drop it in a Teams channel or attach it to a pull request.

Using it

The workflow is four steps:

  1. Take a baseline snapshot before making changes
  2. Edit the model (in Power BI Desktop, Fabric Model View, Tabular Editor, wherever)
  3. Take a new snapshot
  4. Compare the two and review the diff

Configuration is two variables: DATASET (the name or GUID of the semantic model) and WORKSPACE (optional, defaults to the notebook’s workspace). Everything else runs from those values. Authentication is handled by Fabric, so there are no credentials to manage.

If you already have two snapshot files and just want to compare them, you can skip straight to step 4. The notebook auto-selects the two most recent snapshots if you do not specify file paths explicitly.

Why not just diff the .bim file?

A .bim file is JSON, so you can run a text diff on it. I have done that. The problem is that a .bim file is a single monolithic document. A text diff on a 15,000-line JSON file where someone reordered a few properties is not a useful diff. You get hundreds of lines of noise for a single meaningful change.

The structured diff in this notebook compares at the object level, not the text level. If a measure’s expression changed, you see that one measure and just the changed expression. Everything else is filtered out. The signal-to-noise ratio is massively better.

The .bim diff also does not work if you are comparing across environments. If you want to verify that a dev model matches production before promotion, you need to snapshot both and compare the metadata, not the files. This notebook handles that.

Where this goes next

Running this in a Fabric pipeline as a post-publish step is the obvious next move. Snapshot the model after every deployment, compare against the previous snapshot, and fail the pipeline (or post a Teams notification) if objects were unexpectedly removed.

Cross-environment comparison is another use case I am already running: snapshot Dev and Prod on the same day, diff them, and confirm that what was promoted is exactly what was intended.

A more ambitious extension would be inline DAX expression diffs using a line-diff algorithm instead of full expression replacement. The structure supports it. I have not built that part yet.

The notebook is submitted to the Fabric Notebook Gallery as part of the Semantic Link Developer Experience Challenge. If you have ever spent an afternoon trying to figure out what changed in a semantic model, this might save you the detective work.

Source code available here: vestergaardj/Semantic-Link-TestHarness: Semantic Model Test Harness: Unit & Regression Testing for DAX (using Semantic Link Labs)

Planning in Microsoft Fabric IQ for SQL Developers

Posted by Jens Vestergaard on in Programming 0 Comment

If you work with SQL in Fabric, you already know the pattern. Reporting data lands in Fabric SQL or Lakehouse tables, semantic models sit on top, and Power BI turns that into something business users understand. Planning has always been the odd one out, usually living in a separate tool that IT feeds with exports and integrations. Not anymore…

Announced today at FabCon today (March 18th 2026)
If you haven’t already, check out Arun Ulag’s hero blog “FabCon and SQLCon 2026: Unifying databases and Fabric on a single, complete platform” for a complete look at all of our FabCon and SQLCon announcements across both Fabric and our database offerings. 

With Planning in Microsoft Fabric IQ, that separation disappears. Planning now sits directly on top of the same Fabric SQL and semantic models that developers and data engineers already maintain.

Ref: Introducing Planning in Microsoft Fabric IQ: From historical data to forecasting the future | Microsoft Fabric Blog | Microsoft Fabric

For SQL professionals, this unlocks a much cleaner architecture, less integration work and more predictable data flows.

Planning Uses Your Existing Semantic Models

Planning in Fabric IQ reads business logic directly from Power BI semantic models. SQL developers no longer have to replicate definitions or maintain special “planning exports.”

If your measures, reference tables and dimensions are modeled correctly, Planning uses them as its foundation. This eliminates drift between planning logic and reporting logic, something that has always been painful in disconnected systems.

Writeback Lands in Fabric SQL

The biggest operational change for SQL developers is this. Planning writeback does not land in a proprietary planning database. It lands in Fabric SQL tables that you control and can query.

This means:

  • Forecasts and budgets are stored as regular tables
  • Versioning and governance policies work the same way as other data
  • You can join planning data with operational data without a round trip to another tool
  • Downstream BI reports update automatically because the data lives in the same environment

A typical structure might look like:

SQL

SELECT
    d.CustomerKey,
    f.ForecastAmount,
    f.Version,
    a.ActualSales
FROM dbo.FinanceForecast f
    JOIN dbo.DimCustomer d ON d.CustomerKey = f.CustomerKey
    JOIN dbo.SalesActuals a ON a.CustomerKey = f.CustomerKey
WHERE f.Version = '2026-Base'

No more external APIs, sync jobs or file drops.

OneLake Makes Planning Data Instantly Available

Shortcuts and mirroring remove a major pain point for SQL developers. Data used by planners does not need a dedicated pipeline or internal copy. If the data is already in OneLake, Planning can use it immediately.

This avoids:

  • Daily ETL loads
  • Redundant staging areas
  • Manual reconciliation work

For SQL developers, this is more predictable, more consistent and easier to operate.

A Single Environment From Actuals to Forecasts

Traditionally, SQL teams have had to maintain two parallel worlds.
One world holds actuals and historical performance.
Another world holds budgets and planning data from a separate system.

Planning in Fabric IQ merges these worlds:

  • Actuals remain in Lakehouse and Fabric SQL
  • Plans and scenarios write back into Fabric SQL
  • Semantic models unify both
  • Power BI reports read everything from the same data estate

This reduces the number of moving parts and makes lineage, governance and validation much easier.

Planning as a New Input to AI and Automation

Planning introduces a new type of data for SQL developers to work with: intent data.
Targets, constraints, scenario assumptions and expected outcomes become tables that intelligent agents can read.

This shifts planning from an isolated workflow to a central part of automated decision support. For SQL developers, this means new opportunities to model features, feed scoring pipelines and support decision logic with richer context than just historical data.

Why SQL Developers Should Care

Planning in Fabric IQ is worth paying attention to because it simplifies several long standing operational challenges:

  • No more pipeline maintenance to feed external planning tools
  • No more reconciliation work between planning and reporting
  • No more duplicated metrics in separate systems
  • No separate planning database that IT cannot fully control
  • Writeback is now a standard Fabric SQL operation
  • Planning logic aligns with semantic model logic

This is a major improvement for anyone who has had to support planning workflows while also maintaining clean, governed SQL environments.

Getting Started

Planning in Fabric IQ is available in preview. To experiment:

  1. Open a Fabric workspace
  2. Connect Planning to an existing semantic model
  3. Observe how writeback lands in Fabric SQL
  4. Integrate planning data with your existing T SQL workload

From there, it becomes clear that planning is no longer an external dependency. It is part of the platform, and SQL developers can finally treat it like any other governed dataset.

Reversing a semantic model w/ incremental refresh using Claude

Posted by Jens Vestergaard on in Programming 0 Comment

Ever faced the issue of having to download a semantic model from the Power BI service but getting stuck when incremental refresh has been implemented?

Usually it’s a rule of thumb to keep the original .pbix file to allow for any modifications in Power BI Desktop, but sometimes that file is lost, misplaced or somehow no longer available. While there are some work arounds blogged out there I though I’d give Claude a test on converting a .bim file to a .pbip project. A .bim file is obtainable using Tabular Editor, which you can find as a free version here, or paid version here (additional features). Connect to the live semantic model and select Save As (model.bim).

Once the model.bim is on you computer, you can release Claude on it to convert it into a .pbip project that you can open and manage through Power BI Desktop.

NB!
Incremental refresh is to be re-configured once deployed to the service and a full refresh is required.

Below is the complete prompt that I had Claude generate once we were through some hiccups.

Property ‘datasetReference’ has not been defined and schema does not allow additional properties. Path ‘datasetReference’, line 3, position 21

Error Message:
Cannot read ‘C:\<path removed>\model.bim’. Missing required artifact ‘model.bim’.

DatasetDefinition: Required artifact is missing in ‘C:\<path removed>\definition.pbism’.

Only text with UTF8 encoding without BOM (byte order marks) is supported. Detected BOM: ‘UTF-8’

Prompt begin: Convert a .bim file into a .pbip (Power BI Project) structure.

Analyze the model.bim file

Given the model.bim file (SSAS Tabular / Power BI semantic model JSON), create a valid .pbip project that opens in Power BI Desktop. Follow these exact rules:

Folder structure

<ProjectName>.pbip
<ProjectName>.SemanticModel/
    .platform
    definition.pbism
    model.bim
<ProjectName>.Report/
    .platform
    definition.pbir

File contents

<ProjectName>.pbip

{
  "version": "1.0",
  "artifacts": [
    {
      "report": {
        "path": "<ProjectName>.Report"
      }
    }
  ],
  "settings": {
    "enableAutoRecovery": true
  }
}

<ProjectName>.SemanticModel/definition.pbism

{
  "version": "1.0",
  "settings": {}
}

<ProjectName>.SemanticModel/.platform

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json",
  "metadata": {
    "type": "SemanticModel",
    "displayName": "<ProjectName>"
  },
  "config": {
    "version": "2.0",
    "logicalId": "<generate-a-new-guid>"
  }
}

<ProjectName>.Report/definition.pbir

{
  "version": "4.0",
  "datasetReference": {
    "byPath": {
      "path": "../<ProjectName>.SemanticModel"
    },
    "byConnection": null
  }
}

<ProjectName>.Report/.platform

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json",
  "metadata": {
    "type": "Report",
    "displayName": "<ProjectName>"
  },
  "config": {
    "version": "2.0",
    "logicalId": "<generate-a-different-guid>"
  }
}

<ProjectName>.SemanticModel/model.bim — the original .bim file, placed here unchanged.

Critical rules

  1. model.bim goes directly in the <ProjectName>.SemanticModel/ folder — NOT in a definition/ subfolder. The definition/ subfolder is only for TMDL format.
  2. All files must be UTF-8 without BOM (no byte order mark). Use new System.Text.UTF8Encoding(false) or equivalent.
  3. definition.pbism must NOT contain a datasetReference property — that property belongs only in .pbir files. The .pbism schema only allows version and settings.
  4. definition.pbir must reference the SemanticModel via relative path using "byPath": { "path": "../<ProjectName>.SemanticModel" }.
  5. The .pbip file is the entry point — users double-click this to open in Power BI Desktop.
  6. The .platform files contain Fabric Git integration metadata. The logicalId GUIDs are placeholders that get regenerated on deployment.
  7. Derive <ProjectName> from the "name" property at the root of the .bim JSON.

If the model uses incremental refresh

If the .bim contains a table with refreshPolicy and policyRange partitions, and the goal is to convert to standard import mode:

  • Remove the refreshPolicy object from the table
  • Replace all policyRange partitions with a single "type": "m" partition containing the M query from the refreshPolicy.sourceExpression, but with the RangeStart/RangeEnd filter removed
  • Remove the RangeStart and RangeEnd tables from model.tables

Let me know in the comments if you find this useful, not working at all, or any experiences in between 🙂