article 22162

Documenting Power BI Semantic Models With Fabric Data Agents

AI is meant to help us automate boring tasks, and what could be more boring than creating documentation for your Power BI semantic models? It’s such a tedious task that most people don’t bother; there’s also an ecosystem of third party tools that do this job for you, and you can also build your own solution for this using DAX DMVs or the new-ish INFO functions (see here for a good example). That got me wondering: can you use Fabric Data Agents to generate documentation for you? And what’s more, why even generate documentation when you can just ask a Data Agent the questions that you’d need to generate documentation to answer?

To test this idea out, I dug up a semantic model I’ve used for blog posts in the past with a simple star schema and a few measures:

Here are the definitions of the measures:

Units = SUM(Sales[SalesUnits])
Value = SUM(Sales[SalesValue])
Apples Value = 
CALCULATE([Value], KEEPFILTERS('Product'[ProductName]="Apples"), USERELATIONSHIP('Product'[ProductID], 'Sales'[ProductID]))
Apples Value Forecast = [Apples Value] * 1.1 

After publishing this model I created a Data Agent directly connected to it and found I couldn’t access the metadata I needed from it (I didn’t try too hard, but I don’t think it was possible) so I then created a second Import-mode semantic model, used the first as a data source and created a single table bound to the following DAX query using the INFO.CALCDEPENDENCY function:

EVALUATE INFO.CALCDEPENDENCY()

This second semantic model therefore contained a list of all the DAX calculations, their definitions and the dependencies between them, from the first semantic model. Here’s that table:

I then published this second semantic model, built a Data Agent on it, and used it for all my tests. I also added some instructions to the Data Agent – I freely admit I have no idea whatsoever about prompt engineering but after a bit of trial and error the following instructions did the trick:

You are a Power BI semantic model developer.
The CalcDependencies table contains the output of the DAX INFO.CALCDEPENDENCY() function for another Power BI semantic model. It contains information about the dependencies between the columns, tables, measures, calculated columns, calculated tables and relationships in that model. 
Each row in the CalcDependencies table represents a dependency between two objects. 
The combination of values in the OBJECT_TYPE, TABLE and OBJECT column uniquely identifies the object which is the source of the dependency.
The combination of values in the REFERENCED_OBJECT_TYPE, REFERENCED_TABLE and _REFERENCED_OBJECT columns uniquely identifies the object which is the target of the identity.
You can join the table to itself multiple times to find chains of dependencies between objects.
When I ask about dependencies, please include direct dependencies and also indirect dependencies, for example where one object has a dependency on another object which in turn has a dependency on the object I am asking about.
The EXPRESSION column contains the DAX definition of an object.
A value of CALC_COLUMN in the OBJECT_TYPE table indicates that the object is a calculated column.

First test: create some documentation. The following prompt:

Generate some documentation for the measures listed in the CalcDependencies table. Describe what each calculation does and how it works.

…generated the following output:

Here is the documentation for the measures listed in the CalcDependencies table, describing what each calculation does and how it works:

Measures

1. Units

• Definition: SUM(Sales[SalesUnits])
• Description: The Units measure calculates the total number of sales units. It does this by summing the values in the SalesUnits column in the Sales table. This measure is useful for tracking the total quantity of items sold.

2. Value

• Definition: SUM(Sales[SalesValue])
• Description: The Value measure calculates the total sales value. It achieves this by summing up the values in the SalesValue column in the Sales table. This measure is essential for understanding the total revenue generated from sales.

3. Apples Value

• Definition: CALCULATE([Value], KEEPFILTERS('Product'[ProductName]="Apples"), USERELATIONSHIP('Product'[ProductID], 'Sales'[ProductID]))
• Description: The Apples Value measure calculates the total sales value specifically for the product “Apples.” It works by applying a filter to include only rows where the ProductName in the Product table is “Apples.” Additionally, it uses a specific relationship between the Product and Sales tables defined by the ProductID columns. This measure helps in analyzing the revenue generated from the sale of apples.

4. Apples Value Forecast

• Definition: [Apples Value] * 1.1
• Description: The Apples Value Forecast measure estimates the future sales value for the product “Apples” based on a forecasted increase. It multiplies the current Apples Value by 1.1 (indicating a 10% increase). This measure is useful for predicting future revenue from apple sales.

These descriptions provide a clear understanding of what each measure does and how it works, helping anyone who looks at the documentation to comprehend the purpose and functionality of each calculation.

The verdict? More than good enough. To be honest, I was expecting the Data Agent to do a good job here though.

Second test: understand the dependencies between calculations. This is an interesting challenge because you can have chained relationships (ie measures that depend on other measures that depend on other measures etc) which makes the DAX harder to write. Also, you can’t easily understand dependencies by just looking at the output of INFO.CALCDEPENDENCIES – most of the third party tools mentioned above solve this by visualising the data with some kind of network diagram.

The following prompt:

Which measures would be affected if I deleted the SalesValue column on the Sales table?

…generated the following output:

If you delete the SalesValue column from the Sales table, the following measures will be affected:

1. Measure Name: Value
   • Definition: SUM(Sales[SalesValue])

Since the Value measure directly depends on the SalesValue column, deleting the SalesValue column will cause the Value measure to break. Additionally, any measures that depend on the Value measure, such as Apples Value and Apples Value Forecast, will also be affected indirectly:

2. Measure Name: Apples Value
   • Definition: CALCULATE([Value], KEEPFILTERS('Product'[ProductName]="Apples"), USERELATIONSHIP('Product'[ProductID], 'Sales'[ProductID]))
3. Measure Name: Apples Value Forecast
   • Definition: [Apples Value] * 1.1

Therefore, deleting the SalesValue column will break the Value, Apples Value, and Apples Value Forecast measures.

The verdict? I’m impressed, this is useful.

Final test: can we generate a visualisation of the dependencies? A few months ago one of my colleagues showed me the Mermaid language for defining diagrams like this and the rather cool Markdown & Mermaid custom visual that allows you to display Mermaid diagrams in Power BI. The following prompt:

Generate an expression in the Mermaid diagramming language that shows all the measures in the model and the dependencies between them

…generated the following response:

Here is an expression in the Mermaid diagramming language that shows all the measures in the model and the dependencies between them:

graph TD
  Units --> Units
  Value --> Value
  Apples_Value --> Value
  Apples_Value_Forecast --> Apples_Value

  Units["Units"]
  Value["Value"]
  Apples_Value["Apples Value"]
  Apples_Value_Forecast["Apples Value Forecast"]

Explanation:

• Units is a measure with no dependencies on other measures.
• Value is a base measure.
• Apples Value depends on the Value measure.
• Apples Value Forecast depends on the Apples Value measure, which in turn depends on the Value measure.

This Mermaid diagram visually represents all the measures in the model and the dependencies between them using arrows.

Pasting the expression in the response into the Markdown & Mermaid custom visual gave me the following:

I wasn’t particularly happy with those messy lines linking the Value and Units measures to themselves, but overall it does the job pretty well.

This blog post is already long enough and while these are simple tests, I think I’ve proved my point that Fabric Data Agents can be very useful when it comes to understanding the structure of a semantic model and the calculations in it. Bringing in data from other DAX INFO functions and tuning the AI Instructions would allow you to answer even more complex questions. I can also imagine a solution using Semantic Link Labs that not only dumps the data from DAX INFO functions for all your semantic models into a table in OneLake for consumption by a Data Agent, but also then uses the Data Agent SDK to automatically generate markdown documentation for those models, complete with diagrams. The possibilities are endless!