I beforehand defined in a weblog put up what skinny reviews are and why we should always care about them. I additionally defined Report Degree Measures in one other weblog put up. On this put up, I attempt to elevate some real-world challenges we face when growing skinny reviews. I additionally present an answer to these challenges.

Report Degree Measure Associated Challenges

Creating and utilizing Report Degree Measures is comparatively straightforward, however there are some challenges that we face every now and then, akin to:

• Distinguishing Report Degree Measures from Dataset Degree Measures
• Report Degree Measure dependencies

Figuring out Report Degree Measures from Dataset Degree Measures

One of many challenges that Energy BI Builders face is creating many report stage measures. Sadly, Energy BI Desktop at present makes use of the identical iconography for each kinds of measures, making it arduous to tell apart the precise measures created throughout the dataset from the report stage measures. It will get much more difficult if we have to write technical documentation for an present skinny report. We’ve to open the PBIX file of the skinny report within the Energy BI Desktop and click on each single measure. If the expression bar seems, the chosen measure is a report stage measure; in any other case, it’s a dataset stage measure.

So except we use third-party instruments, which I clarify on this put up, we should undergo the handbook course of.

Report Degree Measure dependencies

One other ache level associated to the earlier problem is discovering the dependencies between the report stage measures. It’s essential to concentrate on the interdependencies when doing affect evaluation. We have to perceive how a change in a report stage measure impacts different report stage measures. Once more, Energy BI Desktop doesn’t at present have any choices supporting that, so we’ve to click on each measure and skim by the DAX expressions to establish the dependencies or use the third-party instruments to avoid wasting growth time.

Dataset and Skinny Stories Dependency Challenges

The opposite challenges are much more tough to beat relate to interdependencies between datasets and skinny reviews. Energy BI Service supplies a lineage view that exhibits the dependencies between a dataset and its related skinny reviews. However the challenges can get extra advanced to beat manually. The next are some real-world examples of extra advanced conditions:

• What if we have to analyse the affect of modifications in a dataset measure on all report stage measures of the related skinny reviews?

• How will we analyse the affect of modifications on a dataset measure on all related skinny reviews, together with the visuals, filters, and so on…?

• What if we have to tune the efficiency and we need to discover a checklist of all unused tables or unused fields?

As you’ll be able to see, the scenario can get fairly advanced, so handbook operations are nearly not possible.

However there’s a third social gathering software we will use which supplies heaps of capabilities with a few clicks.

Introducing A Third Get together Software That Can Assist

Happily, there’s a third social gathering software that may assist to resolve all of the above challenges. The Knowledge Vizioner group, myself included, labored arduous to implement an add-on for Energy BI Documenter that helps skinny reviews. Let’s get to it and see the way it works.

Getting a Checklist of Report Degree Measures and Their DAX Expressions utilizing Energy BI Documenter

We will at present use the out-of-box characteristic to get all report stage measures and their DAX expressions within the Energy BI Documenter with out activating any add-ons. All you might want to do is create an account when you haven’t already carried out so. As you could know, Energy BI Documenter at present accepts Energy BI Template information (PBIT); so you might want to open the skinny report in Energy BI Desktop and export it to PBIT, then comply with these steps:

1. Login to Energy BI Documenter

2. Click on the Add PBIT button
3. Click on Browse and choose the PBIT file to add

4. The Documenter detects the report sort is a skinny report

5. Click on the skinny report and navigate to the Mannequin tab
6. Broaden the Report Degree Measures part
7. Click on the Obtain as CSV file button

As proven within the previous picture, you’ll be able to see the report stage measures, their DAX expressions, and the visuals utilizing them.

However wait, what in regards to the different challenges we simply mentioned, the dataset to all skinny reviews dependencies, used and unused fields, and so on?

Allow us to see how Energy BI Documenter can assist with these.

Skinny Report Add-on for Energy BI Documenter

As talked about, we labored arduous at Knowledge Vizioner to organize an add-on for Energy BI Documenter. After activating the add-on in your Energy BI Documenter account, a brand new Analyse button seems on the highest proper of the Information web page.

Allow us to add a number of skinny reviews and their associated dataset information (PBIT) within the Documenter and see how straightforward it’s to get all of the dependencies in a few clicks:

1. Click on the Add PBIT file button
2. Click on Browse
3. Choose all required PBIT information, together with the PBIT containing the dataset and all associated skinny reviews
4. Click on Open

After the information are uploaded into the documented, the documented mechanically detects the file sort as under:

Now, allow us to choose the dataset and all associated skinny reviews:

5. Click on the ellipsis button on the specified file
6. Click on the Choose associated reviews from the context menu

7. Now that each one associated reviews and their dataset are chosen, click on the Analyse button
8. Choose the specified choice from the menu, the Documenter at present helps the next 4 choices:
   • Unused tables: downloads a CSV file containing a listing of the tables from the dataset that none of their fields is used anyplace throughout the dataset itself and all chosen skinny reviews
   • Unused fields: downloads a CSV file containing a listing of all unused fields together with columns, calculated columns, measures, and report stage measures
   • Used tables: downloads a CSV file containing a listing of the tables that at the least one in every of their fields is used someplace throughout the dataset itself or any of the chosen skinny reviews
   • Used fields: downloads a CSV file containing a listing of the fields which are used someplace both throughout the dataset or any of the chosen skinny reviews or their report stage measures

There you go! You will have it. Within the subsequent part, we clarify what the CSV information give us.

The Definition of Used and Unused

Because the previous picture exhibits, we analyse the information into the next 4 classes:

• Unused tables
• Unused fields
• Used tables
• Used fields

To know these classes we’ve to have a definition for used objects the place the objects are Tabular mannequin objects. We at present do not issue the Energy Question objects and their interdependencies within the evaluation. So, whereas we’ve confidence within the output, it is crucial for the customers to grasp that they should sense test earlier than deleting the unused objects from their mannequin.

The Definition of Used Fields’ definition will change as we add further features, so at all times test for the most recent definition.

The Definition of Used Fields

A area, from a Tabular object mannequin perspective, contains columns, calculated columns, and measures. A used area is a area that seems in any of the next throughout the dataset and all skinny reviews chosen by the person:

• Dataset stage dependencies
  • Relationships
  • Tabular object dependencies in DAX
    • Calculated column expressions
    • Measure expressions
    • Calculated desk expressions
  • Calculation teams
  • Safety
    • Row Degree Safety (RLS)
    • Object Degree Safety (OLS)
  • Kind by column
• Report stage dependencies
  • Filters
    • Report filters
    • Web page filters
    • Visible filters
  • Wherever on Visuals together with however not restricted to
    • Axis or values
    • Conditional formatting
    • Dynamic conditional formatting
    • Tooltips
  • Report stage measures
  • Report stage measure’s dependencies
    • Dependency on different report stage measures
    • Dependency on dataset fields

The Definition of Unused Fields

By having the definition of the used fields readily available, the unused ones are these fields that don’t seem within the checklist of used fields.

The Definition of Used and Unused Tables

A used desk is a desk with at the least one area showing within the checklist of used fields. Conversely, an unused desk is a desk with no fields showing within the used fields’ checklist.

Understanding the CSV Output

As you’ll have already famous, figuring out the dependencies between dataset objects and all related skinny reviews is a fancy course of. So the dimensions of generated CSV file varies relying on the dataset dimension, its complexity, the variety of related skinny reviews, and their complexity. We’re additionally conscious that CSV shouldn’t be the simplest format to grasp and interpret the knowledge, so we goal to organize a user-friendly UI sooner or later. However for now, let’s choose one choice and see what we get within the CSV file and the right way to interpret the information.

In my pattern, I chosen a dataset and 11 skinny reviews. The next picture exhibits the ends in the downloaded CSV file for Used Fields seems just like the under when opened in Excel:

We will filter the title to reply many questions akin to the next:

What report stage measures do we’ve in all skinny reviews?

To reply this query we simply have to filter the CSV when the Sort column is REPORT_MEASURE. The next picture exhibits the outcomes:

The place the Date column from the Date desk is used throughout the dataset and skinny reviews?

To reply this query we have to filter the CSV when each the Desk and Sort columns’ worth is Date. The next picture exhibits the outcomes:

What’s the affect of fixing the Transport Value, a dataset measure, on report stage measures?

To reply this query we simply have to filter the CSV as follows:

• Filter the Subject Title column to Transport Value
• Filter the Sort column to Measure
• Filter the Dependent Report column and exclude Blanks
• Filter the Dependent Subject Expression column and exclude Blanks

The next picture exhibits the outcomes:

These are only some examples of questions we will reply utilizing the CSV output of the Skinny Report add-on within the Energy BI Documenter as you’ll be able to think about. For extra details about how the Skinny Report add-on works watch the next quick video:

Do you want what you see? In case your reply is sure, proceed studying.

Enabling Skinny Report Add-on in Energy BI Documenter

Because the identify of this characteristic implies it’s an add-on that you would be able to allow in your Energy BI Documenter account. We at present allow this add-on solely through request. I hear you ask Why? As talked about earlier, the method of figuring out all interdependencies between the dataset objects and all skinny report objects is fairly resource-intensive that may value us some huge cash. So we can’t allow it for 1000’s of customers. You don’t need to see us bankrupted, do you? So I encourage you to specific your curiosity by filling out the next type and we get again to you as quickly as we course of your request:

As at all times, I’d love to listen to your ideas. So please go away your message within the feedback part under.