Tag Archives: Business Intelligence

Power BI Fixer — One Line to Fix Them All

How a single Python call in a Fabric Notebook can assess, standardize, and fix your Power BI reports and semantic models at scale.

DISCLAIMER: RUN THE SCRIPT AT YOUR OWN RISK. NOT MICROSOFT AFFILIATED.

Try it out → Go here

1. Why This Matters

Every Power BI developer knows the pain. You inherit a report — or revisit one you built six months ago — and find pie charts everywhere, axis labels on bar charts that serve no purpose, the default 720×1280 page size from 2018, visual filters that confuse end users, no calendar table, no “Last Refresh” indicator, and implicit measures galore.

Fixing all of that manually means clicking through dozens of property panes, writing Tabular Editor scripts, exporting TMDL, or opening the JSON files in Power BI Desktop developer mode. And that is just for one report.

What if you could do it all — for any report in any workspace — in a single line?

pbi_fixer()

That is not a concept. That is a working function, built on top of Semantic Link Labs, running natively inside a Microsoft Fabric Notebook. No external tools. No downloads. No separate C# runtime. Just Python, the Fabric APIs, and one function call.

A big thank you to Michael Kovalsky, the creator and maintainer of Semantic Link Labs. His work laid the groundwork that makes the PBI Fixer possible. He also pointed me in the right direction when I was exploring whether an interactive UI was even feasible inside a Fabric Notebook. Without Semantic Link Labs, none of this would exist.

The Game Changer: A Native Fabric Solution

One Line. (Nothing Else.)

The PBI Fixer is a notebook-native interactive UI built with ipywidgets. When you call pbi_fixer(), it renders a complete control panel directly in your notebook cell output:

  • Workspace / Report / Page input fields to target exactly what you need
  • Mode selector — Fix, Scan, or Scan + Fix
  • 11 fixers split across two categories — Report Visuals and Semantic Model
  • Checkbox control — pick exactly the fixers you want
  • XMLA write confirmation — a safety gate for semantic model modifications
  • Progress log — live output showing every action, every finding, every fix

You get the same result whether you point it at a report in your personal workspace or at a production workspace with a service principal. It is the same API, the same tool, the same one line.

PBI Fixer UI in a Fabric Notebook

If You Are Not on Fabric, You Are Missing Out

Let me be direct: if you are still exclusively on Power BI Pro or Premium Per User without a Fabric capacity, you are missing out on an entire generation of tooling. The PBI Fixer leverages:

  • Fabric Notebooks — serverless Python compute with ipywidgets for interactive UIs
  • PBIR format — the enhanced report format that exposes every visual property as JSON, readable and writable through the Fabric REST API
  • XMLA endpoints — read/write access to the Tabular Object Model (TOM) for semantic model modifications
  • Semantic Link Labs — a community-driven Python library (pip install semantic-link-labs) that wraps all of this into clean, documented functions

None of this requires downloading anything to your machine. You open a notebook, run one cell, and you have an interactive report-fixing tool at your fingertips. This is what a modern BI platform looks like — and Fabric delivers with being able to run this community‑driven tool directly integrated. But also let’s be clear again: the Power BI Fixer is obviously not a Microsoft product.

2. What the PBI Fixer Does — In Detail

The tool is split into two categories, each addressing a different layer of your Power BI solution.

Report Fixers (5 Fixers at the time of writing)

These fixers operate on the report definition — the PBIR JSON files that describe every visual, page, and filter in your report. They use the connect_report() context manager to read (and optionally write) the report definition through the Fabric REST API. Some of the following fixes could more easily be applied by adding on single json design theme to the report, but this would not fix any chart where the default has been touched, therefore the following fixes are directly modifying the property of each visual / page.

1. Fix Pie Charts

Problem: Pie charts are one of the worst chart types for comparing values. Human perception of angles and areas is far less accurate than perception of length (bars). Yet pie charts remain one of the most used visuals in Power BI.

What it does: Replaces every pieChart visual in the report with a clusteredBarChart (configurable in the function itself). The data bindings, filters, and layout are preserved — only the visual type changes.

Scan mode: Reports exactly how many pie charts exist and on which pages, without modifying anything.

🔍 [1/5] Scanning Fix Pie Charts…
   🟡 definition/pages/abc123/visuals/def456/visual.json — pie chart found (would be replaced with clusteredBarChart)

2. Fix Bar Charts

Problem: Bar charts are great — but Power BI’s default formatting includes axis titles nobody reads, axis value labels that duplicate the data labels, and gridlines that add visual clutter.

What it does: Applies five best-practice formatting rules to every barChart and clusteredBarChart:

Setting Target Why
X axis title Off The axis title (“Count of Sales”) adds no value — the data labels speak for themselves
X axis values Off Redundant when data labels are shown directly on the bars
Y axis title Off Category names are self-explanatory
Data labels On The single most important formatting choice — show the actual numbers
Vertical gridlines Off Reduces visual noise

Scan mode: Reports which specific properties deviate from the target for each visual, so you know exactly what would change.

🟡 definition/pages/…/visual.json — needs fixing: X axis title, X axis values, Data labels

3. Fix Column Charts

Problem: Same issues as bar charts, but with axes flipped. Power BI’s defaults for column charts include unnecessary axis titles, missing data labels, and gridlines.

What it does: Applies the equivalent five formatting rules to every columnChart and clusteredColumnChart, with the axis semantics appropriately swapped.

4. Fix Page Size

Problem: The default Power BI page size is 720×1280 pixels — a legacy from when Full HD was not universal. Reports created with this size appear small and waste screen real estate on modern displays.

What it does: Upgrades pages from the default 720×1280 to 1080×1920 (Full HD). Pages with custom sizes are left untouched — only the exact default match triggers a fix.

Scan mode: Reports which pages use the default size and which have custom dimensions.

🟡 “Sales Overview” (definition/pages/abc123/page.json) — default page size (720×1280), would be upgraded to 1080×1920
🟢 “Executive Summary” (definition/pages/def456/page.json) — custom size (800×1200), no change needed

5. Hide Visual Filters

Problem: Power BI automatically creates visual-level filters for every field used in a visual. These filters appear in the Filters pane and confuse end users who do not understand the distinction between visual, page, and report filters. Most report developers would rather hide them entirely.

What it does: Sets isHiddenInViewMode = True on every visual-level filter. For visuals that have query fields but no filterConfig, one is constructed automatically — the tool walks the visual’s queryState, classifies each field as Categorical or Advanced (for measures), and creates the hidden filter entries from scratch.

Scan mode: Reports how many visuals have visible filters and how many filters would be hidden.

Semantic Model Fixers (6 Fixers at the time of writing)

These fixers operate on the semantic model (the dataset) behind the report. They use the XMLA endpoint and the Tabular Object Model (TOM) through connect_semantic_model(). Because XMLA write operations are irreversible (the model can no longer be downloaded as a .pbix with embedded data after modification), the UI requires an explicit confirmation checkbox before running these fixers.

1. Discourage Implicit Measures

Problem: By default, Power BI allows end users to drag columns into the Values bucket and get automatic SUM/COUNT/etc. aggregations. This creates “implicit measures” that cannot be formatted, documented, or governed. It is also a prerequisite for calculation groups — they simply do not work correctly with implicit measures enabled. Actually if a calculation group is added this setting is automatically set to TRUE

What it does: Sets DiscourageImplicitMeasures = True on the model. One property, one toggle, impact the whole model.

2. Add Calendar Table (CalcCalendar)

Problem: Almost every analytical model needs a calendar (date) table. Yet many models either lack one entirely or use a poorly structured one. Without a proper calendar table marked with DataCategory = Time, time intelligence functions do not work and auto date/time creates hidden tables for every date column.

What it does: Checks if any table in the model has DataCategory = “Time”. If none exists, creates a comprehensive CalcCalendar calculated table with:

  • 20 columns — Date, Year, Quarter, Month (number and abbreviation), Day, Fiscal Year (October start), End of Month, Week of Year, Weekday, and 9 boolean/flag columns (Is Current Month, Is Previous Month, Is Current/Previous Calendar Year, Is Current/Previous Fiscal Year, Is Before This Month, Is Current or Past Month, Month Key, Relative Month)
  • 3 hierarchies — Date Hierarchy (Year → Quarter → Month → Day), Fiscal Date Hierarchy (Fiscal Year → Quarter → Month → Day), Calendar Hierarchy (Year → Month MMM → Week of Year → Weekday)
  • Display folders — Organized into Favorites, Calendar Date, Fiscal Date, and Flags
  • Sort-by-column — Month abbreviation sorted by month number
  • Marked as date table with the Date column as key

The DAX expression uses CALENDARAUTO() and derives all columns dynamically. The fiscal year start month is configurable (default: October).

3. Add Last Refresh Table

Problem: Users and administrators need to know when a dataset was last refreshed. This is especially critical for business-critical reports where stale data leads to wrong decisions.

What it does: Creates a hidden “Last Refresh” table with:

  • M partition (Power Query) that captures DateTime.LocalNow() on every refresh
  • Data column storing the refresh timestamp
  • Measure “Last Refresh Measure” displaying the formatted timestamp
  • Smart placement — If a “Measure” table exists, the measure is placed there instead of in the Last Refresh table, keeping things organized

4. Add Measure Table

Problem: Without a dedicated measure table, measures are scattered across fact and dimension tables. This makes them hard to find, hard to maintain, and easy to duplicate.

What it does: Creates an empty calculated table named “Measure” using the minimal expression {0} (a single-row, single-column placeholder). The auto-generated column is hidden, so only measures placed in this table are visible to the end user.

5. Add Units Calculation Group (Thousand / Million)

Problem: Displaying large numbers like “1,234,567” clutters visuals and makes comparison harder. Users often want to see “1,235K” or “1.2M”. Creating individual measures for every combination is not scalable.

What it does: Creates a “Units” calculation group with two items:

  • Thousand — divides by 1,000
  • Million — divides by 1,000,000

Both items include smart skip logic: measures whose name contains % or ratio are passed through unchanged — dividing a percentage by 1,000 would produce nonsense. The DAX uses DIVIDE() for safe division and checks ISNUMBER(SELECTEDMEASURE()) before applying.

Note: Calculation groups affect all visuals that use the slicer. The fixer includes a performance warning in its output.

6. Add Time Intelligence Calculation Group (21 Items)

Problem: Time intelligence is the most common analytical requirement — comparing this year to last year, computing year-to-date, calculating variances. Building these for every measure individually is tedious and error-prone. A calculation group solves this once for all measures.

What it does: Creates a “Time Intelligence” calculation group with 21 calculation items:

Category Items
Base periods AC (Actual), Y-1 (Prior Year), Y-2 (Two Years Ago), Y-3 (Three Years Ago)
Year-to-date YTD, YTD-1 (Prior Year YTD), YTD-2 (Two Years Ago YTD)
Absolute variances abs. AC vs Y-1, abs. AC vs Y-2, abs. AC vs YTD-1, abs. AC vs YTD-2
Relative variances AC vs Y-1 (%), AC vs Y-2 (%), AC vs YTD-1 (%), AC vs YTD-2 (%)
Achievement achiev. AC vs Y-1, achiev. AC vs Y-2, achiev. AC vs YTD-1, achiev. AC vs YTD-2

The DAX is generated dynamically based on the actual calendar table and date column names in the model. It uses SAMEPERIODLASTYEAR(), DATEADD(), DATESYTD(), and TOTALYTD() — all parameterized so they work regardless of how your calendar table is named.

Prerequisite: Requires a calendar table with DataCategory = “Time”. The fixer checks for this and prints a helpful message suggesting to run the Calendar fixer first if none exists.

3. Scan vs. Fix — Assess Before You Act

Every fixer in the PBI Fixer supports two modes:

Scan Mode

Scan mode is read-only. It opens the report or semantic model with readonly=True, checks every condition, and reports what would change — without touching anything. This is your assessment tool.

Use it to:

  • Audit a report before handing it to a client
  • Baseline the current state before applying fixes
  • Review across multiple reports to prioritize which ones need the most work

The output is a detailed log with green dots (✅ already correct), yellow dots (⚠️ would be fixed), and clear descriptions of each finding.

Fix Mode

Fix mode opens the report or semantic model with readonly=False and applies all selected fixes. The report definition changes are saved back to the Fabric service through the REST API. Semantic model changes are saved through the XMLA endpoint via TOM.

Scan + Fix

The third mode runs both phases sequentially: first a complete scan to document the current state, then a full fix pass. This gives you a before-and-after view in a single run.

4. How It Compares to Other Solutions

Tabular Editor C# Scripts

Let me be clear: I am a huge fan of Tabular Editor. For example TE’s C# scripting engine is powerful, mature, and battle-tested. Being able to save reusable C# scripts as one-click actions in your toolbar makes repetitive model tasks fast and consistent. If you work with semantic models professionally, Tabular Editor should absolutely be in your toolkit.

That said, there are scenarios where the PBI Fixer complements what Tabular Editor offers:

  • Local install required — Tabular Editor must be downloaded and installed on a Windows machine (or run as a portable version). The PBI Fixer runs entirely in a Fabric Notebook — no installs, no local machine dependency
  • C# vs. Python — Tabular Editor’s scripting language is C#. If your team lives in notebooks and Python, switching to C# for model automation is a context switch. The PBI Fixer keeps everything in one language and one environment
  • Semantic model only — Tabular Editor (understandably) focuses on the semantic model layer. It does not touch the report definition — it cannot fix pie charts, page sizes, or visual filters. The PBI Fixer covers both layers in a single tool
  • Interactive notebook UI — Tabular Editor scripts are run from a script editor, command line, or via Macros in the TE UI. The PBI Fixer provides an interactive widget directly inside a notebook, which fits naturally into Fabric-native workflows
  • License cost — TE3 (with the full scripting engine and Macros) requires a paid license, mho worth it for professional use, but it is a consideration for teams already invested in Fabric

Use Tabular Editor and its Macros for deep semantic model work at your desk. Use the PBI Fixer when you need report-layer fixes, notebook-native automation, or a single entry point that spans both layers without leaving Fabric.

TMDL Scripts / Git-Based Workflows

TMDL (Tabular Model Definition Language) is Microsoft’s text-based serialization format for semantic models. You can version-control your models in Git and apply changes through text manipulation.

  • Great for version control — TMDL excels at tracking changes over time
  • Manual text editing — adding a 20-column calendar table means writing 200+ lines of TMDL by hand
  • No report layer — TMDL is semantic-model-only, same as Tabular Editor
  • No assessment — you cannot “scan” a TMDL folder to find what is missing; you need to read and interpret the files yourself
  • Deployment pipeline required — changes in TMDL need to be deployed back to the service through Git integration or a deployment tool

The PBI Fixer’s scan mode provides the assessment that TMDL workflows lack, and its fix mode applies changes directly without requiring a full deployment pipeline.

Manual Modifications in Power BI Desktop

The most common approach: open the report, click through property panes, change settings, save, publish.

  • Time-consuming — fixing five formatting properties on ten bar charts means 50+ individual clicks
  • Error-prone — easy to miss a visual or forget a setting
  • Not scalable — doing this for 20 reports across five workspaces is a full day of work
  • No audit trail — no record of what was changed or what the previous state was
  • Not repeatable — the next report you create has the same default problems

The PBI Fixer is repeatable, consistent, and logs everything all by design.

Power BI REST API / Fabric SDK (Custom Python)

You could build this yourself using the raw Fabric REST API and the semantic-link SDK.

  • Maximum flexibility — you can do anything the API supports
  • Significant development effort — understanding the PBIR JSON schema, handling authentication, managing error cases, building a UI
  • No community — your custom scripts are yours to maintain

The PBI Fixer is built on top of Semantic Link Labs, which handles the heavy lifting. It uses connect_report() for the PBIR layer and connect_semantic_model() for TOM — both are well-tested, community-maintained abstractions over the raw APIs.

5. Getting Started

Prerequisites

  • A Microsoft Fabric capacity (F2 or higher, or a trial capacity)
  • A Fabric Notebook in your workspace
  • Semantic Link Labs installed – see script below
  • Reports in PBIR format (required for report fixers — check your Preview Features in Power BI Desktop, enable PBIR, save, and re-publish if your report is still in PBIRLegacy). The Fixer will also tell you in case it is not.
  • XMLA read/write enabled at the tenant level (Admin Portal → Tenant settings → Integration settings → “Allow XMLA endpoints and Analyze in Excel with on-premises datasets”). By default this is enabled. Required for semantic model fixers.
  • Large semantic model storage format enabled (Workspace Settings → Power BI → Large dataset storage format → ON). This applies to all datasets in the workspace and sets DefaultPowerBIDataSourceVersion to V3, which is required for any XMLA write operation. Without it, semantic model fixers will fail.

Usage

Two cells — that is all it takes. Install and import takes approx 1 min, the Fixer itself will run within seconds.

# Cell 1: Install and import
%pip install git+https://github.com/KornAlexander/semantic-link-labs.git
from sempy_labs.report import pbi_fixer

# Cell 2: Run
pbi_fixer()

Splitting this into two cells is intentional. The install only needs to run once per session — after that you can skip Cell 1 entirely and just re-run Cell 2. It also keeps things clean: use “Clear Output” on Cell 1 to remove the verbose pip installation log, so only the interactive PBI Fixer UI remains visible in your notebook.

The interactive UI renders in the cell output. Enter your workspace and report name, select the fixers you want, choose your mode, and click Run.

Note: If your report was uploaded as a .pbix file, it may be in PBIRLegacy format — the visual fixers won’t find any charts until you convert it. Open the report in Power BI Desktop, save it, and re-publish to convert to PBIR. Also make sure Large semantic model storage format is enabled in the workspace settings (see prerequisites above) — without it, all semantic model fixers will fail with an XMLA write error.

> Note: The PBI Fixer currently lives in a fork. Once it is part of the official Semantic Link Labs package, this becomes a true one-liner — just %pip install semantic-link-labs followed by pbi_fixer(). I am working toward getting it merged into the main repository, but given the scope of the fixers and the UI layer, this may take some time and may not happen at all.

For automation or scripting without the UI, call any fixer function directly:

from sempy_labs.report import fix_piecharts, fix_barcharts

# Scan only — see what would change
fix_piecharts(report="Sales Dashboard", workspace="Production", scan_only=True)

# Fix — apply the changes
fix_barcharts(report="Sales Dashboard", workspace="Production")

6. The Road Ahead

The PBI Fixer is designed as a framework, not a fixed set of rules. Each fixer is an independent Python function with a consistent interface: report, workspace, scan only. Adding a new fixer is as simple as writing one function and wiring it into the UI.

Currently available fixers:

Report Fixers

  • fix_piecharts — replaces all pie charts with Clustered Bar Charts
  • fix_barcharts — removes axis titles/values, adds data labels, removes gridlines
  • fix_columncharts — removes axis titles/values, adds data labels, removes gridlines
  • fix_page_size — changes default 720×1280 pages to 1080×1920 (Full HD)
  • fix_hide_visual_filters — sets isHiddenInViewMode on all visual-level filters

Semantic Model Fixers

  • fix_discourage_implicit_measures — sets DiscourageImplicitMeasures to True (recommended & required for calc groups)
  • add_calculated_calendar — adds a calculated calendar table if no table has been marked as a date table
  • add_measure_table — adds an empty “Measure” calculated table to centralise measures
  • add_last_refresh_table — adds a “Last Refresh” table with M partition & measure showing refresh timestamp
  • add_calc_group_units — Thousand & Million items, skips % / ratio measures
  • add_calc_group_time_intelligence — AC, Y-1/Y-2/Y-3, YTD, abs/rel/achiev. variances (requires calendar table)

What’s especially exciting is that this is hopefully just the beginning—because the whole fixer is built for easy extension, anyone can contribute new fixers or improvements. As more people get involved, the checklists and best practices incorporated by the PBI Fixer will continue to grow and evolve. This open and extensible approach means the tool can adapt to new challenges and community needs over time.

Here is what I find most exciting about the extensibility:

IBCS Implementation

The International Business Communication Standards (IBCS) define a comprehensive set of rules for business charts: uniform scaling, standardized notation, consistent color coding, and strict chart type selection. Some of the PBI Fixer’s concepts already align with IBCS principles:

  • Replacing pie charts — IBCS explicitly discourages pie charts in favor of bar charts
  • Removing axis clutter — IBCS emphasizes clean, uncluttered visuals with data labels instead of gridlines
  • Standardizing page sizes — consistent canvas dimensions are a prerequisite for standardized layouts

These are early steps, but the architecture makes it straightforward to explore further IBCS-aligned fixers over time — and to potentially integrate with IBCS-focused custom visuals like TRUECHART for scenarios where native chart types reach their limits.

Simplifying the Defaults

Power BI’s default settings are optimized for getting started quickly — not always for production-quality reports. The idea is that the Fixer will potentially be extended by further best practice checks and fixes:

  • VertipaqAnalyzer integration — Semantic Link Labs already includes vertipaq_analyzer(). A future fixer could scan the model for high-cardinality columns, unused columns, and oversized string columns, then recommend or apply optimizations automatically
  • Report BPA integration — Semantic Link Labs already ships run_report_bpa() with 9 built-in rules that analyze the report definition for issues like oversized visuals, missing alt text, and excessive filters. Integrating this directly into the PBI Fixer as a scan-capable fixer would give you a full report-level health check alongside the visual fixes — and for rules with deterministic resolutions, auto-fix them in the same pass
  • Model BPA integration — The library’s comprehensive Model BPA covers semantic model best practices. Future fixers could take BPA findings and apply fixes for every rule that has a deterministic resolution — turning “assessment” into “assessment + auto-fix” in one step

What You Can Build Today

Even without waiting for future releases, the framework is open. You can write your own fixer function today:

def fix_my_custom_rule(report, page_name=None, workspace=None, scan_only=False):
    with connect_report(report=report, workspace=workspace, readonly=scan_only) as rw:
        # Your logic here — iterate visuals, check properties, apply fixes
        pass

Wire it into the report_fixers list in the UI, and it appears as a checkbox alongside the built-in fixers. Same for semantic model fixers — write a function that uses connect_semantic_model() and add it to sm_fixers.

The barrier to entry is intentionally low. If you can write a Python function that reads and modifies a dictionary, you can write a fixer.

Final Thoughts

The PBI Fixer is not just a tool — it is a shift in how we think about report quality. Instead of manually checking and fixing every visual property, every model setting, every best practice after the fact, we can now codify our standards and apply them at scale with a single function call.

Fabric’s notebook environment, combined with the PBIR format and the XMLA endpoint, has created something that was simply not possible two years ago: a unified, programmatic, interactive interface to both the report layer and the semantic model layer of Power BI — running entirely in the browser, with zero local tooling.

If you are building Power BI solutions professionally, this is the direction things are moving. One line. Everything assessed. Everything fixed.

pbi_fixer()

My Power BI Toolbox: 80+ Tabular Editor Macros to Automate Data Model Development

When it comes to streamlining your Power BI data modeling, the value of automation cannot be overstated. One of the most powerful tools available to achieve this automation is Tabular Editor, where you can write C# scripts to optimize and automate repetitive tasks. In this blog post, I’ll introduce you to my Power BI Toolbox — an extension of my original Pimp script — and share how it helps bring efficiency and best practices to Power BI data modeling.

Tabular Editor Scripts: The Key to Automation

Tabular Editor Scripts, also referred to as macros, C# scripts, or custom actions, are essential tools for automating actions within Tabular Editor. Tabular Editor, an invaluable tool for Power BI professionals, offers the ability to write custom scripts to save time and ensure consistency in data models. With C# scripting capabilities, you can automate a huge collection of tasks like creating measures, setting properties, and sooo much more.

Over the years, I’ve developed and wide range of these scripts. My Power BI Toolbox is after my PIMP script the next evolution in this journey, containing a suite of over 80 Tabular Editor scripts, each designed to simplify various aspects of Power BI data modeling. If you want to know more about why this stuff is important for Tabular models check out the following video with Reid Havens. https://www.youtube.com/watch?v=-9YaxArn3TM

What’s in the Toolbox?

My Power BI Toolbox is a collection that includes:

  • Many of the Official Tabular Editor repository macros (excluding those exclusive to TE3)
  • Bernat’s macros, You will definitely find hidden gems there.
  • My own macros, which build upon the lessons learned from my earlier PIMP script

More Details on My Favorite Macros

Here are some of my favorite macros that I’ve developed to improve efficiency and automation in Power BI data modeling:

  • Calendar Table as Calculation Group: This macro helps you create a calendar table as a calculation group and mark it as the calendar table in your data model. It’s a quick way to ensure that your time intelligence functions are referencing the correct table.
  • Time Intelligence Calculation Group: This script automatically detects the calendar table and creates a calculation group for common time intelligence measures like Year-to-Date, Quarter-to-Date, Month-to-Date, Previous Year, and more. This reduces manual work and helps ensure consistency in your model.If you have not marked your calendar table as calendar table it will ask you for it.
  • Units Calculation Group: Creates a calculation group for units without splitting text or ratio measures, making it easier to maintain a clean, efficient model structure.
  • Empty Measure Table Creation: This macro allows you to create an empty measure table, which is useful for organizing measures and maintaining a clean data model structure. The script needs to be run in two steps, first creation, than save, than hiding the column.
  • Explicit Measure Creation for Selected Columns: This macro lets you create explicit measures for selected columns and move them into a designated display folder. Additionally, it prompts you with the option to move these measures into a different table, such as an empty measure table, ensuring your data model is organized.
  • Previous Year, Delta PY, and Delta PY % Measure Creation: Automatically generates explicit measures for all selected measures with the variation previous year, delta previous year, and delta previous year percentage. The script is smart enough to recognize the marked calendar table and link these measures appropriately, saving you a lot of repetitive work.
  • Macros Containing Metadata Documentation This macro inspired by Martyn Booth is design to automatically document the model. The macro adds four new calculated tables need to be added, saved, and then hidden as part of the standard workflow. These tables can then be used to effectively document the data model, providing valuable metadata insights. The four calculated tables are: info.view.Tables, info.view.Columns, info.view.Measures, and info.view.Relationships. This approach essentially replaces the need for additional tools like VertiPaq Analyzer or Bravo for Power BI, to a considerable extent. One limitation is the absence of column size information, which would be a useful enhancement, but the benefit of automatic updates makes this a powerful feature for keeping documentation current without extra manual work. More info here: https://www.notjustaprettydashboard.com/auto-documentation-report-using-info-view-dax-functions/
  • Combine Time Intelligence with Value Parameter: This macro, inspired by Mark Endicott, extends my time intelligence template by adding a dynamic value parameter. The number of years is now made dynamic, allowing for more flexibility in time-based analysis. The next step is to make the period selection fully dynamic, enabling switching between different time frames such as year, quarter, month, and day.

These scripts are targeted at best practices in Power BI data modeling, enabling you to standardize and streamline your workflows in a consistent, efficient manner.

No Admin Rights? No Problem

No admin rights? No problem. Just make sure you’re running the latest version of Tabular Editor Portable. This version allows you to use all the powerful features of Tabular Editor without needing to install anything, making it perfect for environments where you lack administrative privileges.

How to Install the Power BI Toolbox for Tabular Editor

Installing the Power BI Toolbox is straightforward. You can use the Installation Script available here: Installation Script. Or as an alternative you can also just copy paste the MacroActions.json file into your AppData\Local\TabularEditor directories. Just paste into your file explorere “%AppData%” and you are already almost there. 

Here is a video of how to install it, if manual is not what you prefer: https://www.linkedin.com/posts/alexanderkorn_hicocando-actionablereporting-tabulareditor-activity-7250519843966181377-sfdd?utm_source=share&utm_medium=member_desktop

The installation script takes care of the following steps:

  1. Backup the MacroActions.json file if it exists in your AppData\Local\TabularEditor or TabularEditor3 directories.
  2. Add a fresh MacroActions.json file from my GitHub repository if one does not already exist.
  3. Append my DataModelToolbox.txt file to the current MacroActions.json file if one exists. Please note, if you already have some of my macros, this script does duplicate all of them.

The MacroActions.json file is where all the magic happens, containing the macros that Tabular Editor will use to automate your Power BI tasks. You can check it out here: MacroActions.json File.

How to Execute C# Scripts in Tabular Editor 2

To execute C# scripts in Tabular Editor 2, follow these simple steps:

  • Connect to Open Power BI Desktop: Start by connecting Tabular Editor to your open Power BI Desktop instance to gain access to the data model.
  • Right-Click Options: You can execute scripts by right-clicking on a table, column, data model, or measure within the model. Depending on the certain Macro it will be visible within the context menu where you can select and run the script.
  • Navigate to the C# Section: Alternatively, you can directly navigate to the C# scripting section within Tabular Editor, where you can write and execute your scripts.

Shortcuts for Macros

In Tabular Editor 3, you can even assign shortcuts to these actions, giving you more flexibility and making repetitive tasks even faster. This is especially useful when working with large and complex data models where efficiency matters most. 

Here is a video of this in action: https://www.linkedin.com/posts/alexanderkorn_do-you-also-have-a-button-to-implement-power-activity-7252416004079677443-0HHC?utm_source=share&utm_medium=member_desktop

You can either set the shortcuts for the macros under Tools > Preferences > Keyboard or alternatively copy and paste my UiPreferences.json file into your directory AppData\Local\TabularEditor3. You can find the UiPreferences.json file here: UiPreferences.json.

I am using the following shortcuts for my macros:

Macro ID Range Shortcut Combination
1 to 9 CTRL + SHIFT + Number (1 to 9)
10 to 19 CTRL + ALT + Number (1 to 9)
20 to 29 CTRL + ALT + SHIFT + Number (1 to 9)

I need to give for the keyboard shortcut and Stream Deck idea full credit to Bernat Agullo Rosello. He gave me the whole idea. I wasn’t aware of Stream Deck till he pointed me that way. While he had the idea, I went ahead and made it real. I am now using a Stream Deck with custom icons for these shortcuts, which allows me to apply data model changes with just the press of a button. This setup significantly enhances my productivity by making these powerful macros available at my fingertips. With the Stream Deck, I can visually organize the most frequently used macros, assign dedicated buttons to each, and execute them instantly. This workflow improvement not only saves time but also makes navigating complex models more intuitive, as I can easily trigger common actions without needing to remember numerous key combinations or navigate through menus.

Sorting the IDs

All Macros have an ID, those can be consequential but don’t need to be. The sorting in Tabular Editor 3 is by default exactly like the MacroActions.json file is sorted. That being said, if you move macros around in the json file, than the IDs do not get automatically adjusted. At the time of writing you will see that actually my file skips a few IDs and is not perfectly sorted. Andreas Nordgaard provided for us a free Python script which does the job.

Save the python script in the same location as the Macro file install python, potentially modify the python script to use the correct parent path and you are good to go.

Just be careful if you change your IDs those will than potentially not match your Tabular Editor 3 shortcuts anymore.

    https://github.com/aols0228/Semantic-Models/blob/main/Macros/MacroActions.json/Update%20Id’s%20in%20MacroActionsJSON.py

    Stream Deck Profile

    In case you also have Stream Deck and want to overcome the hustle to set it up here is my Stream Deck profile, which should have all the icons. Just make sure your shortcuts in TE3 align with the profile. https://github.com/KornAlexander/PBI-Tools/blob/main/Data%20Model%20Toolbox/Tabular%20Editor.streamDeckProfile

    Why Automate?

    The Power BI Toolbox is more than just a collection of scripts; it’s about bringing best practices to every model you create. It’s about removing friction, increasing productivity, and reducing the chance of human error when performing repetitive tasks. Automation lets you focus on what matters most: making data-driven decisions that impact your business.

    Get Started Today

    Ready to give your Power BI data modeling a productivity boost? Install the Power BI Toolbox and start automating today! You can access the installation script here and transform how you model your data or alternativly just add it manually.

    Let me know if you have any questions or if you need help getting started. Maybe do you have a killer script I need to add. Let me know. Would be very happy to receive it. Happy modeling! 🙂

    Video Demo

    Spot Poor Power BI Reports: No4. No “EMPTY MEASURE TABLE” 

    When building Power BI reports, deciding where to place your measures—whether in a dedicated measure table or directly in the fact table—can impact both usability and maintainability. Here’s a look at why creating a separate measure table often works best and why it’s becoming a popular approach for Power BI developers and report consumers alike. I call the approach of a dedicated measure table as “EMPTY MEASURE TABLE” because it is just a container, no actual data is in there, except maybe the relationships but that’s a minor detail.

    Advantages of a Dedicated Measure Table

    1. Clear Structure for Reports Under Development: Fact tables are often “work in progress” —especially in development or test environments — where new measures or transformations may still be in the pipeline. A measure table provides a consistent location for measures even if the fact table is incomplete.
    2. Ease of Development: From a developer’s perspective, having all measures in a single, separate table makes it easy to find and work with them. It avoids digging through the fact table, especially when it’s not fully denormalized.
    3. Attributes in Fact Table are unavoidable: Normally attributes in fact tables like an invoice number should be ideally avoided. But as we all know business request are a priority and this can’t be always avoided. As a result the fact table will not move to the top with the calculator icon. And if that is the case Measure Table for sure it is. A separate invoice number dimension table is also not optimal.
    4. Easier Tracking of Incomplete Work: Developers can quickly see if a fact table still needs further adjustments without having to go through measure definitions, as all measures are in their own place.
    5. Non-Disruptive Maintenance: When tables are deleted, measures in a separate measure table remain untouched, avoiding accidental data model breakages due to table deletions.
    6. Improved User Experience: Users may not always understand the difference between fact and dimension tables, and a dedicated measure table avoids confusing them with technical table names. This is especially helpful when users need quick access to measures such as variance or trend measures across multiple tables. Users don’t have to dig through multiple tables to find what they need.
    7. Consistent Naming Conventions: A measure table supports streamlined naming conventions. While dimensions and facts are often tagged in table names, this isn’t necessary with a measure table, which can be kept hidden or labeled more intuitively, contributing to a cleaner, more navigable model.

    Encounter the disadvantage of Excel functionality:

    Is the data model used in excel a lot the empty measure table approach should very carefully considered and maybe thrown over board.

    1. Drillthrough Flexibility: Yes the big disadvantage of missing the drillthrough needs to be addressed with additional work with detailrowexpression. Maybe we could set it on the table level instead of on the measure level. For more details on this: https://www.sqlbi.com/articles/controlling-drillthrough-in-excel-pivottables-connected-to-power-bi-or-analysis-services/
    2. Missing Relationships: In Excel normally the relationships are automatically visible. That means no wrong relationship can be used. With “Empty measure tables” manually setting up fake relationships is needed. Obviously this can’t be done as exact, since different measures might have different relationships.

    Naming Convention

    Did you know that you can’t use “Measures” as name for your empty measure table? The best next alternative is “Measure” – Easy fix.

    Organization in Display folders

    Obviously organizing the measures in folders and subfolders is needed, but this is unrelated to the fact which approach should be chosen. Organizing that way can be done in both worlds.

    But how do I implement it?

    Here is a super easy two step process in Tabular Editor 2. Just paste it into C# Script and Execute the two scripts separately.

    // ATTENTION FOR TE2 Users: Script needs modification AND needs to be run in 3 steps
    // First Step: Add Table
        var table = Model.AddCalculatedTable("Measure", "{0}"); 
    // Second Step: JUST FOR TE2 Save Data Model Changes
    // Third Step: Hides the column, uncomment the next two lines and execute it separately to the previous creation
        var table = Model.Tables["Measure"]; 
        table.Columns[0].IsHidden = true;

    Final Thoughts

    Using a separate measure table provides a cleaner, more efficient Power BI model that’s easier for both developers and end-users to navigate. This approach aligns with best practices in data model design, creating a well-organized, scalable, and maintainable report structure.

    And once again if Excel usage is a priority, the approach should be carefully considered and maybe revert back to the fact table approach. But than push hard to have it fully denormalized / all columns hidden.

    Spot Poor Power BI Reports! No1: The Slicer Mess

    This article is part of a series on how to spot a poor Power BI report. There are many pitfalls, which are obvious like 3D Pie Charts, but then there are others which are less obvious. We all have done those mistakes, we actually all of us make some of those still every day, aware or unaware. I want to highlight a few of them in this upcoming series.

    I wrote already about one misconception in the past, so check it out: https://actionablereporting.com/2023/12/21/myths-about-red-green-deficiency-in-visualizations/

    I would like to start with one “Power BI Failure” which is definitely less obvious, heck maybe even up for discussion: Slicer Vs Filterpane

    If you’re familiar with Power BI, you know there are three primary ways to “filter” a report (page):

    1. Filter pane
    2. Visuals
    3. Slicers

    One sign of a poorly designed Power BI report is the excessive use of slicers. Here’s why using option 1 or 2 is in most cases better than option 3.

    1. Increase Information Density Without Losing Clarity

    • Visual Importance: If an element is significant enough to be on the report page as slicer, than it should be a visual like a bar chart. Visuals provide almost the identical functionality as slicers, with the added advantage that visuals convey information while slicers just don’t. The use of space can be the same or at least similar. In case you don’t know multi-select is possible with CTRL.
    • Filter Pane Utilization: In case you want to permanently apply the selection than you still can revert back to the filter pane. This approach not only streamlines the report but also reduces visual clutter.
    • Collapse and Expand Issues: Slicers cannot collapse or expand. Unless you design a slicer pane in your report. That’s definitely something I try to avoid at all costs, because that’s a set up for failure through a bookmark nightmare.

    2. Performance: Faster Reports

    • Impact on Loading Times: Each element on the page needs to be rendered, and visuals must wait for slicers to load as well. This can significantly slow down the report.
    • Optimization Tip: Set slicers to “All.” If nothing is selected. Supposedly that’s faster.

    3. Structure and Simplification

    • Consistency is key for user familiarity: Everyone who has used Power BI in the past at least once, can answer the following question: Where is the filter pane in Power BI? Easy: on the right side of the report. Does the same apply for slicers? Nope, sometimes those are top, left, right or maybe even scattered. No matter which report or company you are going to work with the filter pane is going to be on the right side of the report. Rarely if ever is the filter pane disabled. Did you know you can do that as well?
    • Single Source of Filtering: Totally ommiting is by the way often not an option, because you want to give the users often the possibility to filter more than just one or two attributes, therefore you absolutely need the filter pane. And if you are already using the filter pane than make the single source of filtering. Have you tried to put 20 filters as slicers on your page? Good luck with that. Not saying you should do it with the filter pane. But at least space usage is minimal and structure is super neat.
    • Three Levels of Granularity: The filter pane is super clear on the possibility to filter. There are three levels:
      • Visual
      • Page
      • report –> I try to reside just to this one. Skipping page and visual if possible

    In contrast have you tried to sync slicers across many pages? That can be a nightmare to maintain, which is visible on which page and which is syncing on which page?

    • Order and Size Consistency of filter: The order of filters in the filter pane is super consistent. I tend to number those filters in the filter pane. Each element is uniformly sized and has similar possibilities to filter. I tend to number the filters through with modification of their text. Note that numbering the filters in the filterpane shouldn’t be done if translations are in the game.
    • Search Functionality: Searching values in the filter pane is enabled by default. With slicers this also possible, you need to enable it through the three dots before.

    Exceptions: Where slicers have some small advantage:

    If you haven’t noticed I am not a big fan of slicers, but I want to acknowledge some arguments in favor of slicers.

    1. Hierarchies: When dealing with hierarchical data. Hierarchies in filter pane not possible, but in slicers it is. Edit 17. July 2024 But potential even a bigger performance impact.
    2. Logos: If you use your slicers with small pictures on your page such as logos, I am okay for that. Picture in the filter pane. Not possible.
    3. Unspottable Slicers: When a slicer with a field parameter overlays invisibly the new card visual, it can be the bomb. (e.g. you cards become clickable for different measures how cool is that)
    4. Title Integration: Once again if slicers are not noticeable I am okay too, such as part of a title or other text element you would have on your page either way.
    5. Publish to Web: okay this is definitely an argument for slicers. Filterpane is not available for reports published to the web (To be clear: I mean web not Power BI Service)

    In conclusion, while slicers can be a useful tool in Power BI, for me it is clear: The less slicers –> the better your Power BI. Yes I am also sometimes resorting back to slicers and yes sometimes the combination of all three options is the magic path.

    Increasing information density, having faster reports, and a better structure in my filters is key for me and I hope you take my advice and will think about the (over-)use of slicers in the future twice. 😉

    Edit: 17. July 2024

    Sharing this blog article I got great input from various people. My opinion still stands “it depends” or “both” is the right answer. With me clearly trying to lean towards the filter pane as much as possible. As long as you make an explicit for one or the other than you are good to go.

    Overall the majority was rather opposing my standpoint and the survey also supports this:

    Result of Survey

    Supporting / Supplemental arguments

    However some also supported my point of view such as Armand van Amersfoort performance argument.

    Or also Kerry Kolosko posted a great rule of thumb, which I support 100%

    Opposing Viewpoint

    If you are interested to read a completely opposite viewpoint than check out this great article from Johnny Winter:

    https://greyskullanalytics.com/should-you-hide-the-filter-pane-in-power-bi

    “IBCS Power BI Generator”: Automate your Power BI report development

    DISCLAIMER: You are running the tool at your own risk. The tool is currently deleting and replacing all current report pages, including all visuals. So make sure to have a backup. I continuing to develop the tool as we speak. This is hopefully just a start.

    Manual

    1. Enable PBIP+TMDL Preview Feature in Power BI Desktop
    2. Download and run the script
    3. Select your existing PBIP File (Just the data model needed)
    4. Select your logo, corporate color, value column to analyse, date column…
    5. Done

    Introduction

    In today’s data-driven business environment, the need for clear, comprehensive, and automated reporting is more crucial than ever. The IBCS Power BI Implementer is at the forefront of this revolution, offering a fully automated report creation process that is seamlessly integrated with HICO’s Best Practice Templates. This innovative solution not only streamlines the reporting process but also personalizes it to suit your business’s unique needs and branding requirements.

    What is IBCS Power BI Implementer?

    IBCS Power BI Implementer is an executable tool that enhances the capabilities of Power BI, Microsoft’s interactive data visualization software. This Implementer allows you to automate the entire report creation process, from data model configuration to the final design elements. It’s like having an expert assistant that takes over the tedious tasks, freeing you to focus on analyzing the data and gaining insights.

    Key Features of IBCS Power BI Implementer

    The IBCS Power BI Implementer comes with a wide array of features that cater to different aspects of report creation. Here’s a closer look at what each selection option entails:

    Option1: Date Dimension Calculated Table

    Automating the creation of a calculated table for date dimensions ensures that your reports can effectively handle time-based data, which is essential for trend analysis and time series forecasting.

    Option2: Date Dimension PQ Script

    Power Query (PQ) scripts for date dimensions are vital for processing and transforming date-related data, ensuring consistency across all your reports.

    Option3: Last Refresh Table and Measure

    This option automatically generates a table and measure that keep track of the last time your data was refreshed, providing transparency and ensuring data is up-to-date.

    Option4: Calculation Group – Time Intelligence

    Creating calculation groups for time intelligence simplifies the process of comparative analysis over different time periods, such as year-to-date calculations and period-over-period comparisons.

    Option5: Calculation Group – Units

    This allows for automated conversion and handling of various units within your data, making the reports versatile and adaptable to different measurement systems.

    Option6: Empty Measure Table

    An empty measure table is crucial for organizing custom calculations and measures that you may add to enhance your reports.

    Option7: Sales-Dashboard

    Leverage a template with the custom visual TRUECHART to get a jumpstart on visualizing sales metrics in a way that adheres to IBCS standards.

    Option8: Add Background with company colors including PPTX creation

    Customize the visual aspect of your reports by adding a background with your company’s colors, and even generate a PowerPoint presentation for easy sharing and presentation.

    Option9: Add Company Logo to Design

    Personalize your reports further by embedding your company logo, enhancing brand consistency across all corporate communications.

    Option10: Automatically close and reopen Power BI Report

    For updates that require a restart, this feature ensures the process is seamless, saving time and avoiding manual handling errors.

    Option11: Add Relationship between Fact and Date Table

    Establishing relationships between the fact table and the newly created date dimension table is automated, which is a cornerstone for accurate data modeling in Power BI.

    Option12: Select Column for AC value

    Choose the specific column that represents the Actual (AC) value in your reports. The explicit measures for the column AC, PY, Delta PY are automatically created.

    Integrating with Best Practice Templates

    Integration with templates means that your reports won’t just be automated—they’ll be crafted according to the International Business Communication Standards (IBCS). These standards ensure that your reports are not only efficient but also effective in communicating the right information to the right audience.

    Conclusion

    The IBCS Power BI Implementer exemplifies the next step in the evolution of business intelligence reporting. By automating the entire process and ensuring that each report is customized to your company’s design and data model, it significantly reduces the time and effort required to generate insights. Whether you’re a small business or a large corporation, the IBCS Power BI Implementer could be the tool that transforms your data reporting process into a competitive advantage.

    Here is the executable: https://github.com/KornAlexander/PBI-Tools/blob/main/IBCS%20Power%20BI%20Generator.exe

    Alternative Downloadlink: IBCS Power BI Generator.exe

    The Hammer for your Power BI Report Server Issues

    For many IT admins, troubleshooting a Power BI Report Server can be a daunting task. With so many potential sources of error and so much data to collect, it can be difficult to know where to start. Fortunately, the Power BI Report Server Troubleshooting Tool is here to help.

    Think of this tool as a giant hammer in your toolbox, ready to take on any nail that needs to be driven down. The hammer I provide is certainly not the right tool for all issues with Power BI Report Server but, I hope you will be able to address many of the nails / issues needing to be fixed. To be clear “the tool is not fixing anything, it is just collecting data”. This troubleshooting tool is designed to make your life easier with automating a majority of the collection of data you would do either way. This data is coming from various sources, therefore might also give you an idea about things to investigate which you have previously not considered.

    The best part? It’s incredibly easy to use. All you need to do is download the script and execute it on your report server machine. Assuming you have the “reportserver” database on your localhost, everything should work just fine. Even if that’s not the case you will either way get prompted for various variables you could modify or not.

    One important caution to keep in mind is that the script is designed to collect everything. Depending on the number of log files and rows in your executionlog, it may take some time to finish. However, the good news is that all of the scripts included in the tool are a great starting point for troubleshooting a wide range of scenarios.

    So, what exactly does this tool collect? Here’s a quick rundown of what you can expect to be collected:

    • Executionlog3 view: This view provides a wealth of information about the execution of your reports, including data about the report itself, the user who ran the report, and any errors or warnings that were encountered.
    • Event table: This table contains a detailed log of all the events that have occurred on your report server. Normally this table should be empty, if that’s not the case you have most likely an issue or at least a backlog most likely with refreshes/subscriptions.
    • ConfigurationInfo table: This table contains information about the configuration of your report server, including settings related to email delivery, security, and more.
    • Subscription and schedule refresh last status: This script collects data about the last status of your report subscriptions and schedule refreshes. This is incredibly helpful to identify if end users created a subscription potential harmful for your report server, either because of errors or because of potentially unnecessary frequent scheduling.
    • Subscription and Schedule Refresh History table: This table contains a log of all the subscription and schedule refreshes that have occurred on your report server, including information about when they occurred.
    • Rsreportserver.config file: This file contains configuration settings for your report server, including information about database connections, security settings, and more.
    • All “.Log” files, trace and dump files are excluded. Log files are capturing almost every traffic happening to your report server. In the biggest majority of the cases if you see an issue in Power BI Report Server you will find here more details about this. Since the log files are an unstructured log file, having a timestamp is essential.
    • Timestamp of error from user input: This allows you to focus on specific errors or issues that you have encountered and make sure you focus during your troubleshooting on the right issue.

    While this collection of data is certainly comprehensive, it’s important to note that the script could be elaborated to further scenarios, such as troubleshooting Kerberos, SSRS, or performance issues. This is just the start of this tool and I looking forward if someone takes it further with a different hammer or makes this hammer even bigger.

    In conclusion, the Power BI Report Server Troubleshooting Tool is a valuable addition to any IT professional or developer’s toolkit. It simplifies the process of collecting data and provides a great starting point for troubleshooting a wide range of scenarios. So why wait? Download the script and give it a try today.

    Power BI Report Server Monitoring Tool

    As a support engineer, we often receive requests for usage, audit, or monitoring reports for Power BI Report Server. Sometimes, there are support requests, users could easily self-resolve themselves if they would just have the right tool. For Power BI Service there are audit and monitoring templates available, but for Power BI Report Server there is not much or at least to my knowledge nothing publicly available.

    Hence, that’s why I hope the following work in progress pbix will be beneficial to you: https://github.com/KornAlexander/PBI-Tools/blob/main/PBIRS/PBIRS%20Monitoring.pbix

    The report consists of three pages (WIP)

    1. Report Inventory & Usage Stats
    2. Schedule Refresh and Subscription Overview
    3. General Config Information

    1. Report Inventory & Usage Stats

    The first page is the most comprehensive one, with the highest information density, providing a report inventory and display for usage statistics at the same time. You could definitely consider separating the usage statistic from the report inventory.

    Depending on the objective of analysis you have the option to switch the table with a bookmark navigation button to focus on users or reports.

    This report page is also great to investigate the report which have rarely never or not lately been used. To define which unused reports you want to display I recommend to use the bottom right slicer. In case you have reports, which have never been open with an “interactive request” once, there is a separate slicer for this as well.

    One main asset of the pbix is the following sql script for the purpose of building a catalog table. The sql script combines information from various tables into one catalog dimension for all of your items in your report and has the very most properties of each item.

    2. Schedule Refresh and Subscription Overview

    The second page is for subscription and schedule refresh already discussed in a previous article: Power BI Report Server Monitoring for Subscriptions and Schedule Refreshes. New here is on the bottom right corner the number of lines in the event table. The bigger the number the more likely you will be having an issue with a big backlog. Depending on the nodes you have this might take shorter or longer to catch up. In case you have not multiple nodes through scale-out deployment available, the server will be able to start 6 events (schedule refreshes or subscriptions) per minute.

    3. General Config Information

    The third page is nothing special, just some report server configurations, which you can easily see and adjust in the advanced properties of the report server.

    More Info about the PBIX and how to get started.

    All queries in the report use direct query mode, so there’s no need to refresh, and your monitoring is always up to date. To make the report work for you, replace the database parameter “ReportServer” with your database name. The report should be opened on your report server machine, since it is using server localhost. The report was created with Power BI Desktop for Report Server on Sep 22, so you should be able to use and publish it to your report server. Instead of a sql view in the database I chose to add the date dimension under utilizing the the system table “master..spt_values” starting from 2020 till the current date.

    References and sources for report

    1. I want to give a shout out to Nikola Ilic for providing input for this pbix file through his blog post: Check Power BI usage metrics on Report Server! – Data Mozart (data-mozart.com)
    2. The pbix includes the job overview also already published in one of my previous blog posts. Essentially it incorporates the report into this report, but focuses just on report server: Power BI Report Server Monitoring for Subscriptions and Schedule Refreshes
    3. Info for the catalog dimension in regards to the report server’s internal analysis services instance and its related information was taken from Pedro Salinas-Ruiz blog Power BI Report Server Query to List Power BI Reports – Business wIntelligence
    4. There are various documentations how to get an overview of subscription and schedule refreshes such as the following community discussions: info1, info2 , but Filipe Caetano made me aware of a script which is used for one of the two overview tables in the subscription/schedule refresh page

    Taking the report further

    I am sure the report still has a lot of potential. It could be enhanced further if the decision would be made to divert from direct query connection to import connection mode. This would enable to integrate data from various other sources, such as report server log files, the rsreportserver.config, powershell or WEB API calls.

    Furthermore there is quite some potential to optimize the sql queries even further. For example one could merge the subscription/schedule refresh dimension with the catalog dimension.

    For sure visualization and report design wise the report is definitely not where I would like to have it. for example this monitoring report is definitely not IBCS compliant, but I would be happy to see someone taking it in this direction further.

    Ask for a Power BI Report Server Assessment

    In case you want to know more about this topic, I highly recommend that you reach out to a Microsoft representative in your region, such as your Customer Success Account Manager, for assistance and ask for a Power BI Report Server Assessment or for a Power BI Service Assessment potentially available to you.

    Please send me Feedback

    If you found the pbix helpful or if you have any feedback I would be happy to hear about it. Feel free to ping me via the channel that suits youi.

    Also if you are able to develop the pbix further, I would be super happy if you send me your version back.

    Power BI Report Server Monitoring for Subscriptions and Schedule Refreshes

    As a Power BI Report Server Administrator, it is crucial to monitor the performance of your report server and keep track of your subscriptions and schedule refreshes. Inefficient utilization of report server capacity is a common issue, for instance, when there are specific periods of high server load and others with minimal activity. This can be a caused by schedule refreshes and subscriptions set up to be triggered all at the same time. This article explores how you can make use of a Power BI report, the job agent, and Power BI Report Server’s database table to visualize potential bottlenecks.

    Thanks to Frederik Bogaerts, making me initially aware of this Power BI report and thanks to a blog post from Mattias de Smet, I was able to modify this Power BI report for monitoring not only the SQL Server Agent but also on a separate tab the Power BI Report Server. The main addition to Mattias’ report is that the main objective of my report is instead of monitoring only the SQL Server Agent I wanted to monitor the Power BI Report Server’s schedule refreshes and subscriptions as well. Nevertheless the pbix file and detailed description provided by Mattias was particularly helpful in getting started.

    Inspiration for this report also came from Ganttchart’ paginated report blog post about “Visualizing Power BI Report Server Refresh Schedule”, which is particular good if the environment to monitor is a SQL Server Reporting Services and not a Power BI Report Server. This is due to the fact that Pedro’s WIP solution is a paginated report. Just like Pedro’s paginated report, also this solution is based on the dbo.SubscriptionHistory table.

    Theoretically the SQL Server Agent could also be used to monitor the report server, with the exception that the report server’s jobs in the SQL Agent have a GUID, which need to matched with the report server’s database tables. Furthermore in my repro, the SQL agent cleared the history when modifying or deleting the schedule refreshes and the duration in seconds of the jobs were not captured.

    This solution is work in progress and needs to be taken further for sure, not only by monitoring all jobs, PBIRS, SSAS, SSIS, but even more importantly with further analysis of the Power BI Report Server’s database tables, such as the ExecutionLog3 View. One additional requirement is that if you want to see jobs of all services in the report, you would need to run those on the same machine. If this is not the case, like in many environment, an ETL pipeline would need to be set up to consolidate the different services. I would be interested to see if someone can take this approach further.

    There are quite a few further checks and Power BI Report Server Monitoring which could be included in this report.

    Instructions How to Set Up the Report

    Step 1: Download and Open the Power BI Desktop file on your Power BI Report Server and authenticate against your SQL Server.

    Download and open the following .pbix file here: https://github.com/KornAlexander/PBI-Tools/blob/main/PBIRS/PBIRS%20Job%20Monitoring.pbix

    Make sure to open the report on the report server machine, since it is using localhost to connect to your database.

    Step 2: in Power Query –> Change “ReportServer” to match your database name

    Go into PowerQuery and modify the SQL Statments to match the name of your report server database name. In my case the name is “Report Server”, you might have a different name of your database. Once you have changed this you should be done and good to go.

    Optional: Step 3: Creating a SubscriptionsAndScheduleRefreshes VIEW Used in the following script and for PBIRS Monitoring

    The following steps are optional and only need to be applied, in case you want to see your Power BI Report Server jobs from the SQL agent and not from the database, as well as SSAS or SSIS jobs potentially on your SQL Server Agent.

    In this step, we will be creating a view called “SubscriptionsAndScheduleRefreshes” in the “ReportServer” database. This view will be used by the subsequent view “Job_Step_History_Analysis” and therefore needs to be created first.

    /*CREATE Or ALTER View SubscriptionsAndScheduleRefreshes
This view selects the ReportID, ScheduleID as JobName, and Path columns from the dbo.ReportSchedule, dbo.Schedule, and dbo.[Catalog] tables. 
The script uses the WITH (NOLOCK) hint which allows the query to read data from tables that are being used by other queries. 
The script joins the tables on the ScheduleID, and ReportID columns respectively.*/

USE [ReportServer] --change this line to the report server database name
GO
Create OR ALTER View [SubscriptionsAndScheduleRefreshes]
AS
SELECT rs.ReportID
,REPLACE(REPLACE(s.ScheduleID , '{', ''), '}', '') COLLATE SQL_Latin1_General_CP1_CI_AS AS JobName
,cat.[Path]
FROM dbo.ReportSchedule rs WITH (NOLOCK)
INNER JOIN dbo.Schedule s WITH (NOLOCK) ON rs.ScheduleID = s.ScheduleID
INNER JOIN dbo.[Catalog] cat WITH (NOLOCK) ON rs.ReportID = cat.ItemID

    The “WITH (NOLOCK)” statement is used to prevent the view from being locked while it is being queried.

    Optional Step 4: Creating a View for “Job_Step_History_Analysis” for Analysis of SQL Agent

    In this step, we will be creating a view called “job_step_history_analysis” in the “msdb” database. The view is made up of data pulled from multiple system tables such as “sysjobhistory”, “sysjobs”, “syscategories” and joined with the synonym “SubscriptionsAndScheduleRefreshes” created in step 2. It uses a common table expression called “job_history” to select various columns. The view then Selects from the “job_history” CTE to produce the final output.

    USE [msdb]
GO

/****** Object:  View [dbo].[job_step_history_analysis]    Script Date: 1/20/2023 9:31:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*Create or ALTER VIEW job_step_history_analysis
This view selects various columns from the sysjobhistory, sysjobs, syscategories, and SubscriptionsAndScheduleRefreshes tables. The script uses the Common Table Expression (WITH) to define the job_history table. 
The script filters for enabled jobs and excludes steps with step_id = 0. 
The script also calculates various duration statistics and calculates the percentage increase of each duration from the average. The script also selects the error message if the job outcome is 'Failed'.*/

CREATE OR ALTER   VIEW [dbo].[job_step_history_analysis]
AS 

WITH job_history AS 
(SELECT SJ.[name] AS [Job],
        SJH.step_name [Step],
        SJH.step_id [Step Order],
        C.[name] AS [Category],
        msdb.dbo.agent_datetime(SJH.run_date, SJH.run_time) AS [Start Time],
        CASE
           WHEN SJH.run_status = 0 THEN 'Failed'
           WHEN SJH.run_status = 1 THEN 'Succeeded'
           WHEN SJH.run_status = 2 THEN 'Retry'
           WHEN SJH.run_status = 3 THEN 'Cancelled'
           ELSE 'Unknown'
        END [Job Outcome],
        SJH.run_duration % 100                           -- seconds
             +(SJH.run_duration / 100) % 100 * 60        -- minutes to seconds
             +(SJH.run_duration / 10000) % 100 * 60 * 60 -- hours to seconds
          AS [Duration In Seconds],
        IIF(SJH.run_status = 0, SJH.[message], '') AS [Error Message], --select error message if job failed
        S.[Path]
FROM [msdb].[dbo].[sysjobhistory] SJH
      JOIN [msdb].[dbo].[sysjobs] SJ ON SJH.job_id = SJ.job_id
      INNER JOIN [msdb].[dbo].[syscategories] C ON SJ.category_id = C.category_id
      JOIN [ReportServer].[dbo].[SubscriptionsAndScheduleRefreshes] S ON SJ.[name] = S.[JobName]
WHERE SJ.enabled = 1 --filter for enabled jobs
  AND step_id <> 0 --exclude steps with step_id = 0
 --AND SJH.run_duration > 0 --jobs with less than a second are not disabled
)

SELECT [Category],
       [Job],
       [Step],
       [Step Order],
       [Start Time],
       [Job Outcome],
       [Duration In Seconds],
       MIN(job_history.[Duration In Seconds]) OVER (PARTITION BY Job, Step) AS [Min Duration In Seconds], 
       MAX(job_history.[Duration In Seconds]) OVER (PARTITION BY Job, Step) AS [Max Duration In Seconds], 
       AVG(job_history.[Duration In Seconds]) OVER (PARTITION BY Job, Step) AS [Average Duration In Seconds], 
       Case When job_history.[Duration In Seconds] = 0 THEN NULL
       Else ((1.0 * [Duration In Seconds] / (AVG(job_history.[Duration In Seconds]) OVER (PARTITION BY Job, Step))) - 1) END As [Pct Increase],
       [Error Message],
       [Path]
FROM job_history;
GO

    The following is only informational: The following select statement is already automatically included in the Power BI report, therefore it is not needed to actually execute this select statement. I found it helpful for troubleshooting and therefore am including it here. I also included a Where statement to filter out for the jobs of the Power BI Report Server, so in case you are looking for SQL agents jobs from Power BI Report Server you won’t find them, but you already should have them from the historysubscription table.

    This SELECT statement selects various columns from the “job_step_history_analysis” view created in step 3. Additionally, the script applies several IIF and CONVERT functions to the “Min Duration In Seconds”, “Max Duration In Seconds” and “Average Duration In Seconds” columns to convert their values from seconds to a more human-readable format (hours, minutes, seconds). These converted columns are named as “Min Duration”, “Max Duration” and “Average Duration” respectively.

    /* Documentation: This select statement selects various columns from the job_step_history_analysis view 
and formats the duration columns to be in Hours, Minutes, and Seconds. 
It also selects the Pct Increase, Error Message, and Path columns from the view.*/

SELECT
	[Category], 
	[Job], 
	[Step], 
	[Step Order], 
	[Start Time], 
	[Job Outcome], 
	[Duration In Seconds],
	[Min Duration In Seconds],
	[Max Duration In Seconds],
	[Average Duration In Seconds],
	IIF([Min Duration In Seconds]/60/60>0,	
		CONVERT(VARCHAR, [Min Duration In Seconds]/60/60) + ' Hours ','') + 
		IIF([Min Duration In Seconds]/60> 0,	
			CONVERT(VARCHAR, [Min Duration In Seconds]%3600/60) + ' Minutes ','') + 
			CONVERT(VARCHAR, [Min Duration In Seconds]%60) + ' Seconds' AS [Min Duration],
	IIF([Max Duration In Seconds]/60/60>0, 
		CONVERT(VARCHAR, [Max Duration In Seconds]/60/60) + ' Hours ','') + 
		IIF([Max Duration In Seconds]/60> 0, 
			CONVERT(VARCHAR, [Max Duration In Seconds]%3600/60) + ' Minutes ','') + 
			CONVERT(VARCHAR, [Max Duration In Seconds]%60) + ' Seconds' AS [Max Duration],
	IIF([Average Duration In Seconds]/60/60>0, 
		CONVERT(VARCHAR, [Average Duration In Seconds]/60/60) + ' Hours ','') + 
		IIF([Average Duration In Seconds]/60> 0, 
			CONVERT(VARCHAR, [Average Duration In Seconds]%3600/60) + ' Minutes ','') + 
			CONVERT(VARCHAR, [Average Duration In Seconds]%60) + ' Seconds' AS [Average Duration],
	[Pct Increase], 
	[Error Message], 
	[Path]
FROM job_step_history_analysis
Where [Category] <> 'Report Server'

    The Importance of Actionable Reporting in BI & Reporting Best Practice IBCS

    Business Intelligence (BI) is a powerful instrument that can help organizations make better decisions and improve the company’s performance. However, very often I encounter poor BI reports with little to no insight. The assumption in this article is that poor reporting does not stem from an intent to deceive or misinform the report’s target audience. Instead, it is usually due to a lack of understanding about how to create effective and actionable reports.

    Someone may argue that there are numerous ways to interpret and display the same data through visualizations, however I disagree. I believe that there is a single most valuable insight and one best way to visualize that insight. While a company’s objectives and strategies may impact the relative significance of certain key performance indicators or categories, it does not alter the effect that these KPIs or categories have on each other.

    This is why we need actionable reporting best practices that everyone follows. The current most complete best practice for this is IBCS, which stands for International Business Communication Standards. These standards incorporate the majority of previous work done in this area such as ideas from Stephen Few, Edward Tufte or Barbara Minto. Newer publications of standards in the area of information design for charts and tables are already included in the much more elaborate IBCS. E.g Dashboard Design Rules – OKVIZ, “The Data Visualization Society’s Ten Simple Rules for Better Figures” or “The Harvard Business Review’s Guide to Data Storytelling”

    IBCS are a set of guidelines for creating effective and actionable reports. It provides a consistent and standardized approach to creating and presenting data, making it easier to understand and use. By following these over 100 rules, you can create reports that are easy to understand and provide clear insights. This will help to ensure that your reports are being used to drive decision-making and improve the company’s performance, rather than just being ignored. IBCS has proven its importance in everyday business already several times but also in a study conducted by blueforte at the Technischen Universität München (TUM) resulting in 61% less mistakes and being 46% faster when consuming reports. Management Summary

    One of the key principles of IBCS is the use of a clear and consistent visual structure. This means that all elements of a report should be arranged in a logical and consistent manner, making it easy to understand and navigate. Additionally, IBCS emphasizes the use of clear and concise language, avoiding technical jargon and confusing terminology.

    Another important aspect of actionable reporting is the use of the right visualizations. Choosing the right visualization for the data you are presenting is crucial for providing clear insights. For example, a line chart is a good choice for showing trends over time, while a bar chart is better for comparing values. IBCS provides guidelines for choosing the right visualization for different types of data.

    To be even more precise the above mentioned rules are categorized into seven areas with the acronym SUCCESS to better memorize them. Putting the SUCCESS areas into a logical order:

    I: Conceptual rules are guidelines that help to effectively communicate a message by using an appropriate narrative structure. They include:

    1. Say: Convey the main message first and foremost.
    2. Structure: Structure the information in a logical and coherent manner.

    II: Perceptual rules are guidelines that help to effectively communicate a message through visual design. They include:

    1. Express: Expressing the message through the use of appropriate visual elements. In very short there are two chart types covering the very biggest majority of the needs: multi-tier bar chart (table) for categories, multi-tier column chart for time. The combination of both should be than a small multiple multi-tier column chart or less often a multi-tier bar chart (table) with multiple previous period comparisons.
    2. Simplify: Simplify the design to minimizing distractions and therefore avoid any unnecessary noise.
    3. Condense: Once there is more space, the report author has the possibility to increase information density with adding additional elements. Condensing therefore means to utilize the limited space as effective as possible.
    4. Check: Once more elements or charts are added, it is crucial to maintain visual integrity. In very short: “things which mean the same should look the same”. Among many other tasks, one is to check the scaling of the newly added visual elements.

    III: Semantic rules are guidelines that help to effectively communicate a message through the use of a uniform notation system. This includes:

    1. Unify: Unify all the above in a consistent manner and apply notation standards, such as through a notation manual or guide.

    The very first area “Say” I would like to highlight specifically. Brent Dykes has published two great articles stating that one should not simply focus on the visualization part and also put a focus on the equally important data (analysis) and narrative (message) part. Elephant In The Room: Data Storytelling Is More Than Just Data Visualization

    But in my opinion the focus on great visualization first is correct. Because like Brent correctly states in his “Insight Funnel” the dashboard/report is the frame for the analysis and consequent message which follows. Why Data Storytelling Requires a Mindset Shift

    Yes you can convey a good insight with a pie chart if your analysis through other means and the message is the bomb. However my argument would be that you are much more efficient and effective with analysis and storytelling and the related message creation process if your storyframing / dashboarding is superb.

    In conclusion, actionable reporting is essential for creating effective BI reports that provide clear and actionable insights. By following the guidelines provided by IBCS, you can ensure that your reports are easy to understand and provide the information that is needed to drive decision-making and improve performance.

    Business intelligence will only reach its full potential if the concept of actionable reporting is applied, and IBCS is perfectly suited to help you do just that.