Complete Semantic Model Lineage in the Power BI Service

In the Power BI Service the built-in lineage view and impact analysis features have limitations. Even as an admin, you might not see all artifacts connected to a semantic model if you lack access to certain workspaces or reports.

For example the model named "imdb_dataset" shown in the image below. In the lineage view, you might notice missing reports, specifically, one in a personal workspace and another in a Pro workspace called "Adventure Works".

Impact analysis in the Power BI Service - Missing 2 artifacts connected to the model

In this post I will discuss two methods to retrieve complete lineage information for a semantic model:

Option 1) Using Power BI REST APIs (hard but no cost)

Option 2) With Measure Killer's Tenant Analysis Mode (easy but paid) -> scroll down

Method 1: Using Power BI REST APIs

Using Power BI REST APIs allows you to build a complete lineage, but it requires administrative rights and programming knowledge.

Step 1: Workspace IDs

First, use the GetGroupsAsAdmin API (More info on this API call) call to obtain a list of workspace IDs in your tenant:

GET https://api.powerbi.com/v1.0/myorg/admin/groups?$top=5000

Notes:

• The $top parameter is mandatory, with a maximum value of 5000.

• If you have more than 5000 workspaces, you'll need to split the requests using the $skip parameter

Step 2: Request Workspace Information with Lineage Details

Next, use the PostWorkspaceInfo API (More info) call to get detailed workspace information,

POST https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?lineage=True&datasourceDetails=True

Request Body:

{ "workspaces": [ "ccfe6072-aec6-461a-b2fd-b232500e4277", "251c27f7-be83-44c2-8460-31857bef6d04", "f3b476b1-63e3-48c5-885e-e0059b41bbd1", "c20551fa-c4f0-4ac9-927f-e82f17d1e95d" ] }

Important:

• The request body must include a list of workspace IDs.

• There's a limit of 100 workspaces per request. If you have more than 100 workspaces, split your list into batches of 100.

  
  

Step 3: Retrieve the Scan Results

Upon sending the POST request, you'll receive a 202 response containing a scan ID similar to this:

{ "id": "ace475c0-e266-4e61-8cd6-f82f202e053b", "createdDateTime": "2024-10-31T14:13:40.6449264Z", "status": "NotStarted" }

Use this scan ID with the GetScanResult API (More info on this API call) to obtain the lineage data:

GET https://api.powerbi.com/v1.0/myorg/admin/workspaces/scanResult/ace475c0-e266-4e61-8cd6-f82f202e053b

This call returns a JSON file containing all the information needed to build the lineage. For example, you might now see that the "imdb_dataset" model is connected to the "Test PPU" report in the "Adventure Works" workspace and another report in a personal workspace, by matching the 'datasetId' field of each report with the ID of your semantic model, you can map all connected artifacts. The images below show sections of the JSON returned by the GetScanResult API call and the relevant fields for building the lineage.

"imdb_dataset" metadata from the JSON response of the GetScanResult API call

Missing reports metadata from the JSON response of the GetScanResult API call

Advantages

• No additional licensing costs.

Disadvantages

• Requires Admin Rights

• Involves multiple API calls and handling JSON responses.

• Requires knowledge of scripting and API interactions.

• Must manage workspace batching due to the 100-workspace limit per request.

Note: Due to the complexity and scope, the full code logic is not included in this post. If you're interested in the Python script I've developed, please leave a comment below.

Method 2: Using Measure Killer

Measure Killer offers an out-of-the-box solution for Power BI admins to obtain complete lineage information without extensive coding or API management.

Using the Tenant Analysis mode you can analyze all artifacts, including semantic models, reports, dataflows, and datasources. No programming are skills required.

Using our "imdb_dataset" example, Measure Killer displays the previously missing "Test PPU" and "personal_imdb" reports. It also shows the full upstream lineage, tracing through connected dataflows up to the datasource level.

Measure Killers Tenant Analysis lineage tab displaying the full lineage of the imdb_dataset

Advantages

• Fast and Reliable

• Simple interface without the need for coding.

• Provides end-to-end lineage from datasources to reports.

Disadvantages

• Requires a Measure Killer license.

• Requires Admin Rights

Conclusion

Power BI REST APIs are ideal for those comfortable with programming and looking for a free solution. Be prepared to handle API limitations and invest time in scripting and data parsing. Measure Killer on the other hand, offers a user-friendly experience at an additional cost. It's suitable for admins who prefer an out-of-the-box solution without complexity.

In summary, if you have the technical expertise and resources, using the Power BI REST APIs is a viable option. However, if you value speed and simplicity, Measure Killer provides a robust solution for lineage analysis.