External Tools - Their Role in Documentation

Table of Contents

External Tools - Their Role in Documentation
  1. DAX Studio
          1. Guiding Informed Modeling Decisions
          2. Document Performance Evolution
          3. Verify Power BI Aggregations are being hit and document impact
  2. Tabular Editor
          1. BPA - Best Practice Analyzer
          2. Customizable Best Practice Rules
          3. C# Scripts code
                1. Format all Measures
                2. Hide columns on many side
                3. Summarization Off
                4. Data Quality checks
                5. Import VPAX Data into Tabular Editor annotations
                6. Remove Unnecessary Columns
                7. Copy DAX in field’s description
                8. Check last refresh time
                9. Check RI Violations
                10.Extended Date Table

Documentation provides rhythm and clarity to a project, setting expectations and showing the path to achieving goals without ambiguity in rules and processes.

When we finalize documentation, we document excellence.

Documentation should accompany development, helping identify improvements while ensuring our solution is optimized at every step. Like a Michelin chef checking and refining each plate before serving, we must optimize our Power BI reports by removing unused elements, ensuring proper formatting and naming, and perfecting every detail. The final documentation should reflect only a meticulously developed solution. This attention to detail throughout the process is what distinguishes top Power BI professionals.

This approach emphasizes that documentation serves three purposes:

• as a quality control tool during development
• as a record of excellence in the final product.
• as a starting point for evaluating and making any future changes

To achieve these goals effectively, complementary external tools are vital. In this week’s issue 8, we will provide an overview of two external tools that can help streamline documentation and accelerate development in a professional manner.

Without further ado, let’s jump right in:

1. DAX Studio - View Model

DAX Studio is a powerful, open-source and free tool specifically designed for querying and optimizing DAX. It’s a must have for Power BI professionals, particularly those who are performing optimization tasks.

DAX Studio has the ability to analyze your data model and report on the memory usage. This can be extremely helpful when performance tuning as the more memory your data model require, the slower it is to process and query.

While it is clear that DAX Studio is “inconturnable” (must have) for a pro developer, what specific advantages does this tool bring to documentation?

Key Advantages:

1. Guiding Informed Modeling Decisions

• DAX Studio’s model metrics provide detailed insights into memory usage, table sizes, and column details. This helps identify which elements of your model are consuming the most resources, enabling you to make informed decisions about what to remove or organize differently. This technical visibility creates a foundation for crucial conversations between developers and end users about trade-offs, as everything in a model has a cost. These discussions help establish that a semantic model should NOT attempt to answer all possible questions in a report, but rather focus on key business requirements. Documentation-wise, these insights are valuable not only to incorporate into your final documentation but also as a starting point during the initial exploration phase of the project.
• By documenting these metrics and the reasoning behind modeling choices, you create a valuable record that:

1. Shows the intentionality behind each inclusion or exclusion
2. Provides context for future maintainers about why certain trade-offs were made
3. Establishes a clear rationale for the model’s scope limitations
4. Creates transparency around performance considerations

This approach to documentation transforms what could be seen as technical limitations into evidence of thoughtful design decisions, which is particularly valuable when justifying model constraints to stakeholders who might otherwise request ever-expanding capabilities without understanding the associated costs.

2. Document Performance Evolution

• DAX Studio is great at monitoring performance changes over time. Follow these steps to establish a reliable benchmarking process:

1. Create a standardized test protocol:
   • Document specific navigation steps in a consistent order
   • Include filters, page navigation, drill-through actions, and interactive elements
   • Ensure the protocol can be repeated precisely each time
2. Execute the performance test:
   • Ensure the testing environment has the data volume and variety that is reflective of production (else the performance tests describe the development process only, and cannot be used to evaluate production performance requirements).
   • Open a fresh instance of Power BI Desktop
   • Start from a blank page to avoid cached data
   • Activate Performance Analyzer
   • Systematically follow your documented test protocol
3. Capture and analyze results:
   • Export the Performance Analyzer results as JSON
   • Import this data into DAX Studio using “Load Perf Data”
   • Generate concrete performance metrics as evidence
   • Establish these results as your benchmark for future comparisons
4. Integrate into your workflow:
   • Make performance testing a mandatory requirement for all development work
   • Compare new versions against the established benchmark
   • Validate impact with clients before deploying to production

By implementing this systematic approach to performance testing, you transform subjective perceptions into objective measurements. When clients request complex features, you’ll have concrete data to challenge them and demonstrate performance implications. This positions you as a trusted advisor who balances functionality with performance rather than a “report builder” who simply does what s/he is told. This evidence-based approach empowers you as a developer to make informed decisions and propose truly optimized solutions to clients.

[!TIP] Make sure to rename first each element on the page for more clarity.

[!TIP] Incorporate this performance data into a dedicated Power BI report to track test dates and model versions/changes and maintain visibility on performance trends. Consider developing standardized templates for tracking performance evolution across all your reports.

---

3. Verify Power BI Aggregations are being hit and document impact

DAX Studio is essential for confirming that your Power BI aggregations are actually being used and delivering performance benefits. Without this verification, the effort put into creating aggregations could be wasted.

Using DAX Studio, you can:

• Confirm which queries are using which aggregation tables versus detail tables
• Create before/after performance comparisons showing tangible improvements
• Document exactly which measures and filter combinations benefit from aggregations
• Track which specific user scenarios are optimized

This documentation provides concrete evidence that your aggregation strategy is working as designed and delivers the expected performance improvements. It transforms a technical implementation into measurable, documented proof of optimization that stakeholders can understand.

2. Tabular Editor

Tabular Editor is widely considered the most essential external tool among Power BI professionals. While its benefits are numerous enough to build an entire series, this article focuses specifically on how, in my opinion, Tabular Editor enhances documentation processes.

Key Advantages:

1. BPA : Best Practice Analyzer

BPA scans your model against common best practice rules, quickly identifying any deviations. It enables you to:

• Assess model quality in seconds
• Generate detailed quality reports
• Document improvement areas
• Track compliance over time

You can also export these rules to create an accompanying file that shows your development aligns with best practices. This helps non-expert clients gain confidence in your work.

Using the C# Script below you can export the data to an Excel file and document the information.

using TabularEditor.BestPracticeAnalyzer;
  
var bpa = new Analyzer();
bpa.SetModel(Model);
  
var sb = new System.Text.StringBuilder();
string newline = Environment.NewLine;
  
sb.Append("RuleCategory" + '\t' + "RuleName" + '\t' + "ObjectName" + '\t' + "ObjectType" + '\t' + "RuleSeverity" + '\t' + "HasFixExpression" + '\t' + "RuleDescription" + newline);
  
foreach (var a in bpa.AnalyzeAll().ToList())
{
    sb.Append(a.Rule.Category + '\t' + a.RuleName + '\t' + a.ObjectName + '\t' + a.ObjectType + '\t' + a.Rule.Severity + '\t' + a.CanFix + '\t' + a.Rule.Description + newline);
}
  
sb.Output();

To load Tabular Editor with these rules (so they can be applied to your reports), follow these steps:

Loading the rules

1. Download and install Tabular Editor.
2. Open Tabular Editor and run the following code in the Advanced Scripting window. *
3. Copy/Paste the following code and click Play

System.Net.WebClient w = new System.Net.WebClient(); string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.LocalApplicationData); string url = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json"; string downloadLoc = path+@"\TabularEditor\BPARules.json"; w.DownloadFile(url, downloadLoc);

4. Close and reopen Tabular Editor

Source: Best practice rules to improve your model’s performance | Microsoft Power BI Blog | Microsoft Power BI

2. Customizable Best Practice Rules

BPA stands out with its flexibility. You can:

• Create custom rules tailored to your organization
• Document unique implementation requirements
• Leverage community resources if C# scripting isn’t your thing
• Import community-created rules to boost documentation

The Power BI community actively contributes to these resources. Experts like Matt Allington, Kurt Buhler, Melissa de Korte, Brian Julius, Michael Kovalsky, Reid Havens and others have shared valuable snippets. John Kerski, for example, published custom Power Query-specific rules on his GitHub based on LinkedIn feedback. This not only documents your report but also ensures adherence to best practices and helps accelerates development. Linked post

Loading the rules

Click on John Kerski Github, go to PQ_Rules.json, click Raw and copy the link

Go to Tabular Editor (2 or 3) - Tools / Manage BPA rules and Include Rule File from URL

Copy the link you copied from the raw file.

Now you can see the rules

These BPA rules not only provide an excellent resource for documenting your reports and demonstrating adherence to best practices, but they also drive you to enhance your in-tool documentation.

3. C# Scripts code

Here are the top 10 C# scripts I’ve found particularly helpful when documenting Power BI reports. These are proven tools that significantly accelerate development and enhance documentation. These scripts provide immediate, tangible benefits to your work, so feel free to save, experiment with, and integrate them if you find them valuable.

1. Format all Measures

Model.AllMeasures.FormatDax();

You can find the C# script here

2. Hide columns on many side

/*
 * Title: Hide columns on the many side of a relationship  
 *
 * Author: Matt Allington, https://exceleratorbi.com.au  
 *
 * it is dangerous to use columns on the many side of a relationship as it can 
 * produce unexpected results, so it is a best practice to hide these columns
 * to discourage their use in reports.
 */

// Hide all columns on many side of a join
foreach (var r in Model.Relationships)
{ // hide all columns on the many side of a join
    var c = r.FromColumn.Name;
    var t = r.FromTable.Name;
    Model.Tables[t].Columns[c].IsHidden = true;
}

You can find the C# script here

3. Summarization Off

foreach(var c in Model.AllColumns)
{
    c.SummarizeBy = AggregateFunction.None;
}

You can find the C# script here

4. Data Quality checks

You can find the C# script here

5. Import VPAX Data into Tabular Editor annotations

Import VPAX Data into Tabular Editor

This code can be used to add annotations into Tabular Editor using information from a Vertipaq Analyzer (VPAX) file. And now, you can holistically check the integrity of your model via BPA rules which reference these annotations. You can find the C# script here

6. Remove Unnecessary Columns

You can find the C# script here

7. Copy DAX in fields’s description

/*
 * Title: Copy DAX Expression into the measure's description field.
 * 
 * Author: Reid Havens, https://www.havensconsulting.net/ 
 * 
 * This script, when executed, will loop through all the measures in the model and
 * copy the DAX epression into the field's description for documentation purposes.
 */

foreach (var m in Model.AllMeasures) {
  m.Description = m.Expression;
}

You can find the C# script here

8. Check last refresh time

//https://data-goblins.com/power-bi/te-dmv-scripts
/////////////////////////////////////////////////////////////////////////////////////////////
//
// Evaluates a DMV to determine the last time the model was processed (refreshed)
//
// Original method from Marco Russo https://www.sqlbi.com/articles/last-process-date-in-ssas-tabular/
//
/////////////////////////////////////////////////////////////////////////////////////////////

// Query to be evaluated
string _dmv = "SELECT TOP 1 [LAST_DATA_UPDATE] FROM $SYSTEM.MDSCHEMA_CUBES";

// Evaluate the query
using(var daxReader = ExecuteReader(_dmv))
{
    // Read the results
    while(daxReader.Read())
    
    {
        var rowValues = new object[daxReader.FieldCount];
        daxReader.GetValues(rowValues);
        var row = rowValues.Select(v => v == null ? "" : v);

        // Convert the scalar value to a string
        string _processdate = row.ElementAt(0).ToString();
        Info ( "The model was last processed at " + _processdate);
    }

    // Close the reader
    daxReader.Close();

}

You can find the C# script here

9. Check RI Violations

You can find the C# script here

10. Extended Date Table from Melissa de Korte

Extended Date table M function | Creates an ISO-8601 type calendar

💬 Let’s discuss:

• What’s your go-to tool for Power BI documentation?
• What’s the biggest challenge you’ve faced in keeping reports well-documented?

Drop your thoughts in the LinkedIn post: We’d love to keep the discussion going! LinkedIn post: #Issue 8 - External Tools - Their Role in Documentation