Semantic model column lineage

[jmoedata]

Is there a way to tell what columns from a semantic model are being used in reports? We would like to scale back some columns from the semamtic model, but before doing so, would like to know which ones are used in reports.

[ribisht17 · Accepted solution]

hi   @jmoedata 

Yes, there is a way to identify which columns from a semantic model are being used in Power BI reports. You can use the Power BI Helper Tool or DAX Studio to analyze and document the usage of columns in your reports. Here’s how you can do it:

Using Power BI Helper Tool

1. Download and Install: Get the Power BI Helper Tool from Microsoft AppSource.

2. Analyze Column Usage: Open the tool, select your shared dataset, and run the "Column Usage" feature. This will show you which columns are not used in any reports1.

3. Remove Unused Columns: Based on the analysis, you can safely remove unused columns from your dataset.

   Using DAX Studio

   1. Download and Install: Get DAX Studio from its official website.

   2. Connect to Dataset: Open DAX Studio and connect it to your shared dataset.

   3. Run Column Usage: Use the "Column Usage" feature in DAX Studio to identify unused columns.

   4. Remove Unused Columns: Based on the analysis, you can remove unused columns from your dataset.

      Using Power BI's Built-in Features

      1. Usage Metrics Report: In Power BI Service, navigate to the workspace containing your reports.

      2. Generate Usage Metrics: Select "More options" (...) for the report and choose "View usage metrics report".

      3. Analyze Usage: The usage metrics report will show you how the reports are being used, and you can infer which columns are likely being used.

         By using these tools and features, you can effectively identify and scale back unused columns from your semantic model, ensuring that your dataset remains optimized and relevant.

         Power BI Helper Version 12 Jan 2021 Feature Summary

         Solved: Identify and delete unused columns from all report... - Microsoft Fabric Community

Regards,

Ritesh

"✅ Please mark the answer if it was helpful so that it can help others as well. 🙌"

[ribisht17]

Good to see the thumbs-up

Please mark the answer if it was helpful so that it can help others as well. 

"

[jmoedata]

Thanks, but I am looking for table and column specific lineage, which I have not been able to find.  I understand that I can do this manually by going through each and every report, but was hoping that there is a tool to show where tables and columns are used, not just models.

[Brunner_BI]

Check out Measure Killer, it will give you everything you asked for.

[saud968]

Yes, there are ways to determine which columns from a semantic model are being used in reports. Here are a few methods you can consider:

1. Usage Metrics in BI Tools: Many business intelligence tools, like Power BI or Tableau, offer usage metrics and audit logs. These can help you track which columns are being queried in reports.

2. Query Logs: If your semantic model is connected to a database, you can analyze the query logs to see which columns are being accessed. This might require some SQL skills to parse and analyze the logs effectively.

3. Report Analysis: Manually reviewing the reports can also help identify which columns are in use. This can be time-consuming but is very thorough.
   
   Best Regards
   Saud Ansari
   If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
   
   

[jmoedata]

Thanks.  In regards to option 1, "Usage Metrics in BI Tools", that is essentially my question.  Does PowerBI natively offer a tool to do this?

[saud968]

Yes, Power BI does offer native tools to monitor usage metrics for your reports and dashboards. You can use the Usage Metrics feature to see how your content is being used. Here’s how you can access it:

1. Open the Workspace: Go to the workspace that contains the report or dashboard you want to analyze.
2. View Usage Metrics: Select the report or dashboard, click on the More options (...) menu, and choose View usage metrics report.
3. Usage Metrics Report: Power BI will generate a pre-built report with usage metrics for the last 90 days. This report includes details like the number of views, unique viewers, and how users are accessing the content (web or mobile)
   
   Best Regards
   Saud Ansari
   If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
   
   

[saud968]

@jmoedata Does the above explanation helped