Have you ever wondered which objects (tables, columns, measures etc.) are used within a Power BI report? How about which visual types are being used across a collection of Power BI reports? Or, if a report is using custom visuals? Well, you're probably not the only one looking for such information. However, it's generally quite a manual process to get this info as it requires monotonously navigating through each visual and filter on each page of the report (and of course for all reports). Doing this manually would take a very long time and probably would not even be accurate since it is such a long and tedious process. No longer!
The Method
Previously, I wrote a blog post on an automated method for exporting report-level measures and migrating them back to the data model. The solution for getting the report objects is quite similar - although a bit more involved. The parallel between these solutions is that they require 'hacking' into the Power BI Desktop (or Power BI Template) file and parsing through the 'Layout.json' file that is hidden beneath all Power BI reports. This 'Layout.json' file contains all the report-level metadata - including information on all the pages, visuals, filters, bookmarks, custom visuals, report-level measures and more.
The Solution
To streamline the process of collecting the report object metadata, I wrote a C# script which I have posted to GitHub. The script is to be executed in the Advanced Scripting window within Tabular Editor. It should be noted that in this capacity, Tabular Editor is functioning simply for the purpose of code-simplification and convenience (the model which is loaded into Tabular Editor before running this script does not matter).
Also, this script is able to collect the report objects for either a single Power BI report file or a collection of Power BI report files within a specified folder!
The Steps
Download the C# script from GitHub.
Copy and paste the C# script into the Advanced Scripting window in Tabular Editor.
*Open/connect to any model in Tabular Editor (or even click 'File->New Model' to create a new model).
Update the 'pbiFolderName' parameter to the folder which contains the Power BI report(s).
If you want the script to only scan a single Power BI report, specify the report's file name (and extension) in the 'pbiFile' parameter. If you want the script to scan all the Power BI reports within the aforementioned folder, ensure this parameter is set to be blank: @"";
If you want the report objects to be saved (to the same folder as specified in Step 4), set the 'saveToFile' parameter to 'true'. Otherwise, set this to 'false' (in this case the script will share the output in a series of pop-up windows).
Click play (or press F5).
* Tabular Editor only allows you to run a C# script in the Advanced Scripting window if it is connected to a model. In this case, the script is not referencing the model which is loaded so it does not matter which model is actually loaded or if a new (blank) model is created (and then discarded).
The Output
The following shows the format of the output files:
Report Filters
Report Name, Filter Name, Table Name, Object Name, Object Type, Filter Type
Pages
Report Name, Page Id, Page Name, Page Number, Page Width, Page Height, Page Hidden Flag, Visual Count
Page Filters
Report Name, Page Id, Page Name, Filter Name, Table Name, Object Name, Object Type, Filter Type
Visuals
Report Name, Page Name, Visual Id, Visual Name, Visual Type, Custom Visual Flag, Visual Hidden Flag, X Coordinate, Y Coordinate, Z Coordinate, Visual Width, Visual Height, Object Count
Visual Filters
Report Name, Page Name, Visual Id, Table Name, Object Name, Object Type, Filter Type
Visual Objects
Report Name, Page Name, Visual Id, Visual Type, Custom Visual Flag, Table Name, Object Name, Object Type, Source
Custom Visuals
Report Name, Custom Visual Name
Bookmarks
Report Name, Bookmark Name, Bookmark Id, Page Id
Connections
Report Name, Server Name, Database Name, Report Id, Connection Type
Visual Interactions
Report Name, Page Name, Source Visual ID, Target Visual ID, Type ID, Type
Note: 'Source' within Visual Objects shows as 'Standard' for objects used within rows/columns etc. of visual itself. Objects used for conditional formatting or to set titles, backgrounds etc. will show as such within the 'Source' column.
Next Steps
There are some cool things which you can do once you have this information. For example, with this information you have a generally better understanding of which parts of your model are used more often (and perhaps which are used less often or not at all). You could leverage this information to either trim down your model to only the elements needed within the report(s) or potentially use the Master Model technique to dynamically generate 'mini-models' tailored for specific reports (with only the objects necessary to generate those reports).
Conclusion
This post continues a series of enabling metadata to be exported - whether its exporting model metadata, report-level measures, or report objects (as in this post). Gathering this information used to be quite challenging but now it's easy! Just note that this 'hacking' method is not officially supported by Microsoft - but it works!