I beforehand defined in a weblog submit what skinny studies are and why we must always care about them. I additionally defined Report Stage Measures in one other weblog submit. On this submit, I attempt to increase some real-world challenges we face when growing skinny studies. I additionally present an answer to these challenges.
Report Stage Measure Associated Challenges
Creating and utilizing Report Stage Measures is comparatively simple, however there are some challenges that we face infrequently, resembling:
- Distinguishing Report Stage Measures from Dataset Stage Measures
- Report Stage Measure dependencies
Figuring out Report Stage Measures from Dataset Stage Measures
One of many challenges that Energy BI Builders face is creating many report stage measures. Sadly, Energy BI Desktop at the moment makes use of the identical iconography for each varieties of measures, making it laborious 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 current skinny report. We’ve got 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 until we use third-party instruments, which I clarify on this submit, we should undergo the handbook course of.
Report Stage 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 influence 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 the moment have any choices supporting that, so we’ve to click on each measure and browse by means of the DAX expressions to establish the dependencies or use the third-party instruments to save lots of growth time.
Dataset and Skinny Reviews Dependency Challenges
The opposite challenges are much more troublesome to beat relate to interdependencies between datasets and skinny studies. Energy BI Service supplies a lineage view that exhibits the dependencies between a dataset and its linked skinny studies. However the challenges can get extra complicated to beat manually. The next are some real-world examples of extra complicated conditions:
- What if we have to analyse the influence of adjustments in a dataset measure on all report stage measures of the linked skinny studies?
- How will we analyse the influence of adjustments on a dataset measure on all linked skinny studies, together with the visuals, filters, and many others…?
- What if we have to tune the efficiency and we wish to discover a record of all unused tables or unused fields?
As you may see, the scenario can get fairly complicated, so handbook operations are just about inconceivable.
However there’s a third social gathering instrument we are able to use which supplies heaps of capabilities with a few clicks.
Introducing A Third Social gathering Instrument That Can Assist
Fortuitously, there’s a third social gathering instrument that may assist to resolve all of the above challenges. The Information Vizioner workforce, myself included, labored laborious to implement an add-on for Energy BI Documenter that helps skinny studies. Let’s get to it and see the way it works.
Getting a Checklist of Report Stage Measures and Their DAX Expressions utilizing Energy BI Documenter
We will at the moment use the out-of-box function to get all report stage measures and their DAX expressions within the Energy BI Documenter with out activating any add-ons. All you should do is create an account should you haven’t already accomplished so. As it’s possible you’ll know, Energy BI Documenter at the moment accepts Energy BI Template recordsdata (PBIT); so you should open the skinny report in Energy BI Desktop and export it to PBIT, then comply with these steps:
- Login to Energy BI Documenter
- Click on the Add PBIT button
- Click on Browse and choose the PBIT file to add
- The Documenter detects the report sort is a skinny report
- Click on the skinny report and navigate to the Mannequin tab
- Increase the Report Stage Measures part
- Click on the Obtain as CSV file button
As proven within the previous picture, you may see the report stage measures, their DAX expressions, and the visuals utilizing them.
However wait, what concerning the different challenges we simply mentioned, the dataset to all skinny studies dependencies, used and unused fields, and many others?
Allow us to see how Energy BI Documenter will help with these.
Skinny Report Add-on for Energy BI Documenter
As talked about, we labored laborious at Information 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 Recordsdata web page.
Allow us to add a number of skinny studies and their associated dataset recordsdata (PBIT) within the Documenter and see how simple it’s to get all of the dependencies in a few clicks:
- Click on the Add PBIT file button
- Click on Browse
- Choose all required PBIT recordsdata, together with the PBIT containing the dataset and all associated skinny studies
- Click on Open
After the recordsdata are uploaded into the documented, the documented robotically detects the file sort as beneath:
Now, allow us to choose the dataset and all associated skinny studies:
- Click on the ellipsis button on the specified file
- Click on the Choose associated studies from the context menu
- Now that each one associated studies and their dataset are chosen, click on the Analyse button
- Choose the specified possibility from the menu, the Documenter at the moment helps the next 4 choices:
- Unused tables: downloads a CSV file containing an inventory of the tables from the dataset that none of their fields is used wherever throughout the dataset itself and all chosen skinny studies
- Unused fields: downloads a CSV file containing an inventory of all unused fields together with columns, calculated columns, measures, and report stage measures
- Used tables: downloads a CSV file containing an inventory of the tables that at the least certainly one of their fields is used someplace throughout the dataset itself or any of the chosen skinny studies
- Used fields: downloads a CSV file containing an inventory of the fields which can be used someplace both throughout the dataset or any of the chosen skinny studies or their report stage measures
There you go! You’ve it. Within the subsequent part, we clarify what the CSV recordsdata give us.
The Definition of Used and Unused
Because the previous picture exhibits, we analyse the info into the next 4 classes:
- Unused tables
- Unused fields
- Used tables
- Used fields
To grasp these classes we’ve to have a definition for used objects the place the objects are Tabular mannequin objects. We at the moment 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 newest definition.
The Definition of Used Fields
A discipline, from a Tabular object mannequin perspective, contains columns, calculated columns, and measures. A used discipline is a discipline that seems in any of the next throughout the dataset and all skinny studies 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 Stage Safety (RLS)
- Object Stage 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
- Filters
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 record of used fields.
The Definition of Used and Unused Tables
A used desk is a desk with at the least one discipline showing within the record of used fields. Conversely, an unused desk is a desk with no fields showing within the used fields’ record.
Understanding the CSV Output
As you’ll have already famous, figuring out the dependencies between dataset objects and all linked skinny studies is a posh course of. So the dimensions of generated CSV file varies relying on the dataset dimension, its complexity, the variety of linked skinny studies, and their complexity. We’re additionally conscious that CSV shouldn’t be the simplest format to grasp and interpret the data, so we goal to organize a user-friendly UI sooner or later. However for now, let’s choose one possibility and see what we get within the CSV file and tips on how to interpret the info.
In my pattern, I chosen a dataset and 11 skinny studies. The next picture exhibits the leads to the downloaded CSV file for Used Fields appears just like the beneath when opened in Excel:
We will filter the title to reply many questions resembling the next:
What report stage measures do we’ve in all skinny studies?
To reply this query we simply have to filter the CSV when the Kind 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 studies?
To reply this query we have to filter the CSV when each the Desk and Kind columns’ worth is Date. The next picture exhibits the outcomes:
What’s the influence 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 Discipline Identify column to Transport Value
- Filter the Kind column to Measure
- Filter the Dependent Report column and exclude Blanks
- Filter the Dependent Discipline Expression column and exclude Blanks
The next picture exhibits the outcomes:
These are only some examples of questions we are able to reply utilizing the CSV output of the Skinny Report add-on within the Energy BI Documenter as you may 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 function implies it’s an add-on that you may allow in your Energy BI Documenter account. We at the moment allow this add-on solely by way of 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 price us some huge cash. So we can not allow it for 1000’s of customers. You don’t wish to see us bankrupted, do you? So I encourage you to specific your curiosity by filling out the next kind and we get again to you as quickly as we course of your request:
As at all times, I might love to listen to your ideas. So please depart your message within the feedback part beneath.
