Previously we have looked at what thin reports are and why we should care about them. In a seperate post we delved into report level measures. Finally, in this post, you'll learn about some real-world challenges we face when developing thin reports (and a solution to those challenges!)
Creating and using report level measures is relatively easy, but there are some challenges that we face from time to time, such as:
One of the challenges that Power BI developers face is creating many report level measures. Unfortunately, Power BI Desktop currently uses the same iconography for both types of measures, making it hard to distinguish the actual measures created within the dataset from the report level measures. It gets even more challenging if we need to write technical documentation for an existing thin report. We have to open the PBIX file of the thin report in the Power BI Desktop and click every single measure. If the expression bar appears, the selected measure is a report level measure; otherwise, it is a dataset level measure.
So unless we use third-party tools, which I explain in this post, we must go through the manual process.
Another pain point related to the previous challenge is finding the dependencies between the report level measures. It is crucial to be aware of the interdependencies when doing impact analysis. We need to understand how a change in a report level measure impacts other report level measures. Again, Power BI Desktop does not currently have any options supporting that, so we have to click every measure and read through the DAX expressions to identify the dependencies or use the third-party tools to save development time.
The other challenges are even more difficult to overcome relate to interdependencies between datasets and thin reports. Power BI Service provides a lineage view that shows the dependencies between a dataset and its connected thin reports. But the challenges can get more complex to overcome manually. The following are some real-world examples of more complex situations:
As you can see, the situation can get pretty complex, so manual operations are virtually impossible.
But there is a third party tool we can use which provides heaps of capabilities with a couple of clicks.
Fortunately, there is a third party tool that can help to resolve all the above challenges. The Data Vizioner team, myself included, worked hard to implement an add-on for Power BI Documenter that supports thin reports. Let’s get to it and see how it works.
We can currently use the out-of-box feature to get all report level measures and their DAX expressions in the Power BI Documenter without activating any add-ons. All you need to do is create an account if you haven’t already done so. As you may know, Power BI Documenter currently accepts Power BI Template files (PBIT); so you need to open the thin report in Power BI Desktop and export it to PBIT, then follow these steps:
As shown in the preceding image, you can see the report level measures, their DAX expressions, and the visuals using them.
But wait, what about the other challenges we just discussed, the dataset to all thin reports dependencies, used and unused fields, etc?
Let us see how Power BI Documenter can help with those.
As mentioned, we worked hard at Data Vizioner to prepare an add-on for Power BI Documenter. After activating the add-on on your Power BI Documenter account, a new Analyse button appears on the top right of the Files page.
Let us upload multiple thin reports and their related dataset files (PBIT) in the Documenter and see how easy it is to get all the dependencies in a couple of clicks:
After the files are uploaded into the documented, the documented automatically detects the file type as below:
Now, let us select the dataset and all related thin reports:
There you go! You have it. In the next section, we explain what the CSV files give us.
As the preceding image shows, we analyse the data into the following four categories:
To understand these categories we have to have a definition for used objects where the objects are Tabular model objects. We currently do not factor the Power Query objects and their interdependencies in the analysis. So, while we have confidence in the output, it is important for the users to understand that they need to sense check before deleting the unused objects from their model.
The Used Fields’ definition will change as we add additional functions, so always check for the latest definition.
A field, from a Tabular object model perspective, includes columns, calculated columns, and measures. A used field is a field that appears in any of the following across the dataset and all thin reports selected by the user:
By having the definition of the used fields on hand, the unused ones are those fields that do not appear in the list of used fields.
A used table is a table with at least one field appearing in the list of used fields. Conversely, an unused table is a table with no fields appearing in the used fields’ list.
As you may have already noted, identifying the dependencies between dataset objects and all connected thin reports is a complex process. So the size of generated CSV file varies depending on the dataset size, its complexity, the number of connected thin reports, and their complexity. We are also aware that CSV is not the easiest format to understand and interpret the information, so we aim to prepare a user-friendly UI in the future. But for now, let’s pick one option and see what we get in the CSV file and how to interpret the data.
In my sample, I selected a dataset and 11 thin reports. The following image shows the results in the downloaded CSV file for Used Fields looks like the below when opened in Excel:
We can filter the title to answer many questions such as the following:
To answer this question we just need to filter the CSV when the Type column is REPORT_MEASURE. The following image shows the results:
To answer this question we need to filter the CSV when both the Table and Type columns’ value is Date. The following image shows the results:
To answer this question we just need to filter the CSV as follows:
The following image shows the results:
These are only a few examples of questions we can answer using the CSV output of the Thin Report add-on in the Power BI Documenter as you can imagine. For more information about how the Thin Report add-on works watch the following short video:
Do you like what you see? If your answer is yes, continue reading.
As the name of this feature implies it is an add-on that you can enable on your Power BI Documenter account. We currently enable this add-on only via request. I hear you ask Why? As mentioned earlier, the process of identifying all interdependencies between the dataset objects and all thin report objects is pretty resource-intensive. If you're interested in the Thin Report add-on, please get in touch and we can setup a demo.
