top of page

Measure Killer

ein externes Tool für Microsoft Power BI Desktop.

logo.png
Version1.0.png

Aufräumen des Power BI Tenants

Measure Killer kann einen Power BI Tenant bzw. die Datasets aufräumen und optimieren. Dies kann die Refreshdauer reduzieren und so zu weniger Last auf der Premium Kapazität führen.

Measure Killer liest nur die Metadaten eines Dataset, nie die eigentlichen Daten. Da wir uns über XMLA verbinden, muss das Dataset nicht downgeloadet werden.

Analyse des Power BI Tenants (under development)

Der 5. Modus von Measure Killer "Tenant analysis" wird einen Überblick über den gesamten Power BI Tenant geben können. Als Beispiel können wir zeigen welche Reports Custom Visuals nutzen oder report-level Measures haben. Außerdem wird es möglich seine eine Gesamtübersicht von Reports, Anzahl Seiten, Anzahl Visuals usw. tenant-wide zu bekommen.

Löschen unbenutzter Measures

Measure Killer kann selbständig unbenutzte Measures löschen. Alternativ kann ein C# Skript generiert werden, mit dem man die Measures in Tabular Editor manuell entfernen kann.

Weniger Spalten in Modell verbessern auch die Performance weil weniger RAM benötigt wird.

Löschen unbenutzter Spalten

Measure Killer generiert einen voll funktionsfähigen M-Code, der dann in den "Erweiterten Editor" der bereits bestehenden Query eingefügt werden kann.

Analyse eines Berichts

Für jeden Bericht wird ein detailliertes Excel File erstellt, hier kann man einsehen, wo ein Artefakt verwendet wird. Dies kann in: Berechnungen, Visuals, Filter, Conditional Formatting, Joins in Power Query oder anderen Teilen eines Power BI Berichts wie z.B. Relationships sein.

Außerdem, gibt Measure Killer auf Wunsch auch eine Analyse in Form eines Charts aus.

Neu: Measure Killer Analytics

Analyse_Icon_edited_edited.png
Columns_Icon_edited_edited.png
Measures_Icon_edited_edited.png

Was wird vom Measure Killer erkannt?

  • Visuals - inklusive Filter welche auf das Visual, die Seite oder den Bericht angewendet werden

  • Artefakte die nur in Custom Visuals benutzt werden (siehe compatibility matrix weiter unten)

  • Jegliche Art von Measures oder Beziehungen

  • Spalten die ausschließlich in Power Query z.B. joins, appends, references genutzt werden

  • Calculated columns

  • Calculated tables

  • Conditional Formatting (bedingte Formatierungen)

Was geht nicht?

  • Wenn man ganze Berichte kopiert und dann nur diverse Änderungen macht, kann dies zu falschen Ergebnissen führen.

  • Wenn man in Power Query neue Queries erstellt die auf bereits existierende Queries referenzieren, kann es sein, dass Measure Killer Spalten in der neuen Query referenziert, die in den alten Queries bereits entfernt wurden.

  • Wenn eine Query B (child) auf Query A (parent) referenziert (join, append, reference) und diese dieselben Spaltennamen haben, und wir nun einen Eintrag in M mit einer dieser Spalten haben, wird dies fälschlicherweise als benutzt in Query A geflagged.

  • DAX Ausdrücke die nur auf Tabellen verweisen und keinerlei Referenzen auf Measures oder Spalten haben, werden nicht beachtet. (zB COUNTROWS)

  • Es können mehrere Durchläufe notwendig sein, um alle unbenutzten Measures und Spalten zu löschen. (Das können zB welche sein, die lediglich in unbenutzten Measures und Spalten referenziert werden, aber nicht im Report selbst verwendet werden.)

  • Standardtitel und -untertitel (von Power BI vorgeschlagen/erstellt) werden nicht erkannt. Sobald der User diesen Titel abändert wird er erkannt.

  • Restriktive "sensitivity labels" können Fehler produzieren.

  • Spalten und Measures die nur im "neuen Mobile View" verwendet werden, sind aktuell nicht unterstützt - wir arbeiten daran.

Measure Killer Compatibility

legend

fully compatible

 ?   limited compatibility

 ✗  currently not working

General:

 .pbix (Desktop and Desktop RS)

 .pbix (Uploaded to PBI Service)

 .pbip (paid versions only)

 .pbir (paid versions only)

 .pbit (paid versions only)

 Thin files

 DirectQuery

 Composite models

Paginated reports (paid versions only)

Analyze in Excel (paid versions only)

All reports in the service can now be analyzed (paid versions only)

✗ Thin files only (in mode 1 not possible, in other modes yes) for mode 1 switch to DQ to make it work!

Dashboards

Any type of live connection (SSAS, AAS, Datamarts) - create a local mode (DQ) to make it work!

.bim files / models stored in a folder etc.

Metrics (Goals)

Feature compatibility:

 Row-level security

 Calculation groups

 Field parameters

✗ KPIs (created in the tabular model)

✗ Object-level security (When an artifact is only used in OLS, Measure Killer will not detect it)

Compatibility of visuals:

 Standard visuals (all, unless listed below)

 Icon Map

 Zebra BI visuals

 HTML VizCreator Cert

 HTML VizCreator Flex

 Balance Sheet Visual

?  Other custom visuals (We have not tested Measure Killer for all custom visuals)

Q&A visual

✗ Paginated report visual

✗ Metrics (Goals) visual

visuals.png
Measure Killer pricing
All offline features are free to use personally and commercially.
Go to the Download section to get started


The online capabilities of Measure Killer (running in the Power BI Service) like Shared/Golden dataset online, Tenant Admin Mode or Tenant Analysis are paid upgrades.

To unlock these modules/features or to get priority support from the Measure Killer team, please purchase a license below.

Wichtiges und Versionierung​

Es gibt eine Version die Adminrechte benötigt, dann wird der Measure Killer auch zu den externen Tools von Power BI hinzugefügt. Bei der "portable" Version, die keine Adminrechte benötigt, wird dies nicht gemacht.​

Hier kann man Bugs melden.

Enterprise Kunden können Support Anfragen direkt an uns stellen.

Measure Killer erstellt für jeden analysierten Report einen versteckten "temp_" Ordner im selben Verzeichnis wie das Power BI File. Dieser Ordner ist nichts anderes als das entpackte .pbix File des Reports.​

Das copy+pasten von Visuals zwischen live/thin Reports kann dazu führen, dass Artefakte fälschlicherweise als benutzt identifiziert werden, obwohl sie das evt. nicht sind.

Anleitung zur Verwendung von "Measure Killer"

Single report / dataset mode

Shared datasets on local machine mode

Shared datasets online (in the Power BI Service)

Measure Killer - Download & Changelog

Aktuelle Version 1.2.1

Auch im Microsoft Store verfügbar (Admin Version)

Release Notes for 10/10/2023

Version 1.2.1

[BUG FIXES]

 -Fixed connection error in offline modes

 

Older versions

Release Notes for 10/05/2023

Version 1.2.0

[FEATURES]

-Removing measures and calculated columns in the online mode is now possible
-Added 'Used in' option to search artifacts
-Added clear search button on all text search fields
-Minor UI improvements

[BUG FIXES]

-Visual filters with aggregations false negative is fixed
-Power Query references bug fixed
-Fixed report size bugs when all artifacts were used or unused
-Changed authentication workflow to avoid MK getting stuck if authentication failed
-MK now asks to run again after artifacts have been restored
-Fixed a bug where MK would crash if the backup folder didn't exist

Release Notes for 08/14/2023

Version 1.1.0

[FEATURES]
-All Power BI and Paginated reports in the Service can now be analyzed
-New dark mode theme
-Release notes displayed in MK
-Users can now copy the rows, details, and the entire table directly from the main window.
-Analytics section with expanded artifact hierarchy in calculations.

[BUG FIXES]
-Small fixes in shared dataset on local machine mode
-Empty layout issue for older reports
-Minor UI bug fixes

 

Release Notes for 07/19/2023

Version 1.0.0

[FEATURES]

Admin Mode: For tenant admins, Measure Killer can now search your entire tenant for reports connected to a respective datasets. It temporarily grants you permission and then removes them again.


Excel File Analysis: Measure Killer now extends its analysis capabilities to Excel files linked to your dataset (you need to add them manually in all modes)
   

Paginated Reports: These reports can be analyzed just like Power BI reports now.

 

Measure Killer Analytics: An advanced analytics approach, providing unique information about your reports and datasets.

-Putting a cost on measures (in terms of storage they consume via columns they reference)

-Showing total storage used by an individual report, page in a report or visual.

-Giving you the distinct size of an artifact consumed (meaning this is what you can save if you removed it)
 

Other features:
-Advanced Console: A robust command set for printing and displaying metadata, results, and more has been added.
-Automated Removal of Calculated Columns: Measure Killer can now automatically detect and remove calculated columns.
-Hiding Unused Columns: Functionality to hide unused columns has been added to Measure Killer.
-Support for .pbip and .pbit file formats (Preview feature): Measure Killer can now read the new .pbip file extension as well as template extensions.
-Bulk Restoration of Deleted Measures: Users can now restore multiple previously deleted measures simultaneously from Measure Killer backups.
-Improved Shared Dataset on Local Machine Mode: We've made this mode more user-friendly.
-Drag and Drop feature: You can now use drag and drop for adding themes, analyzing in Excel, and other file extensions.
-Usage Count in Results: To enhance result visualization, we've included a 'number of uses' metric in the output.

   

[Bug Fixes]
   
-Shared Dataset on Local Machine Mode: Fixed the issue of window minimizing when selecting the port. Now, the window stays open.
-Duplicate Entries: Fixed the issue of duplicate entries in row by row result documents.
-Theme Selection Bug: Fixed a bug where, in some instances, the theme selection wouldn't change as expected.

Release Notes for 05/19/2023

Version 0.9.10

[BUG FIXES]

-Fixed an issue that prevented all reports from being removed from the report list.
-Implemented a "Stop" button to end the process running in a separate thread, preventing it from continuing if the window is closed -after "Run".
-Improved error handling for instances where ".pbix" file paths were incorrect (moved or deleted), which caused a "Could not retrieve filters data" bug.
-Resolved a bug with page-level filters.
-Stopped automatic report downloads after closing Measure Killer in online mode.
-Fixed an issue causing the page for page-level filters in the main window to be blank.
-Corrected a false negative issue with the TopN Filter of visuals.
-Improved workspace sorting (first dropdown in the process/UI) for accuracy.
-Corrected a false negative when referencing tables with specials characters in thin/live reports.
-Enforced a requirement for at least one valid report to run Measure Killer in online mode.

[FEATURES]

-Auto-check for new versions of Measure Killer.
-Tooltips for buttons, providing explanations for their functionalities.
-Reverse sorting (Z-A) for lists and dropdowns in the online mode.
-Removed "Show Results" button for a streamlined experience.
-Implemented a progress label, providing real-time count of artifacts analyzed.
-Enhanced excel export by including visual title and subtitle for visuals.
-Included multiple notebooks in the main window: Information, Execution, and Killing logs for better organization and tracking.
-Added auto-copying of C# script for deleting measures to the clipboard.
-Displayed the dataset name in the main window for online mode.

Release Notes for 04/24/2023

Version 0.9.9

[BUG FIXES]
Resolved "Measure" key issue in live/thin reports with broken measures.

 

[FEATURES]
-Enhanced Shared Datasets Online Mode UI (Basic/Enterprise).
-Use [DEL] & [ENTER] to include/exclude reports in Shared Dataset Online Mode.
-Workspace search & sort options added.
-Report names now included in layout errors.

-More sorting options in online mode.

Release Notes for 04/07/2023

Version 0.9.8

[BUG FIXES]

-Resolved an issue where measures or columns in the rule option for conditional formatting were not being detected.

-Fixed the "Delete selected" button bug in the "Edit reports" section for live connections on local machines.

-Addressed the Group ("Single visual") bug when grouping visuals.

-Prevented the Settings window from opening twice.

-Removed the "MK_" prefix from the beginning of the saved results Excel file name.

-Corrected a bug where some columns were omitted from the report.

[FEATURES]

-Added the ability to backup and restore measures deleted by Measure Killer.

-Implemented a new and more secure authentication (license) method.

-Introduced an "Expand All" option in the main window.

Release Notes for 03/20/2023

Version 0.9.7

[Features]

-Added the ability to configure proxy options for API calls and Azure identity verifier.

[UI]

-Online mode now takes users back to the Workspace and Dataset window, rather than resetting the Measure Killer tool to its initial state.

-Dataset and Workspace selection window is now resizable, giving users more control over the size and layout of the window.

Release Notes for 03/15/2023

Version 0.9.6

[Bugs]

-Improved error handling

-Smart narrative visual fixed

Release Notes for 03/08/2023

Version 0.9.5

[Features]

-Added Shared/golden Dataset online mode.

Users can now select a dataset and Measure Killer will detect all connected reports in workspaces that the user has access to. For this mode, no local files are needed anymore.
-Enhanced detection of artifacts in visuals.

-Significantly reduced number of false positives

-Strongly improved Power Query M logic

[UI]
-The user interface has been improved with rearranged buttons, reduced windows, and a menu bar has been added.

-Users can choose to either "Remove other columns" or "Remove columns" to "kill" columns.

-Added a menu for Measure Killer's main window (less buttons).
-Users now have the option to choose one of 11 color themes for the entire Measure Killer application.


[Bugs]

-False positives for substrings in visuals have been fixed.

-Fixed duplicates in where artifact are used.
-The main window no longer becomes "not responding" in bigger reports.
-Fixed multiple steps with the same name, "Columns removed by Measurekiller"
-Fixed calendar artifacts usage bug.

Release Notes for 01/05/2023

Version 0.9.2

[MISC]

-Fixed a bug with "filter on this page" and "filter on all pages"

​Release Notes for 12/07/2022
Version 0.9.1

[MISC]
- Updated the installer

- Fixed offline bug where Measure Killer was not loading

​Release Notes for 12/05/2022
Version 0.9
 

[ FEATURES ]

- Added connection to shared datasets and live/thin reports (offline and via XMLA endpoint for premium workspaces)
- Improved results table

[UI]
- rearranged buttons and added some functional buttons e.g "back button"

- changed formatting of results table

Release Notes for 10/17/2022
Version 0.8

 

[ FEATURES ]

- Added option to kill measures directly in Measure Killer without using Tabular Editor
- Added feature to sort columns 
- Added detection of columns used only by incremental refresh
- Added (DAX) expression column for Excel output
- Added new report option (row by row) when saving the results file
- Self-update after killing measures (the user doesn’t have to click on run again)


[MISC]

- Added error handling for blank file selection
- var. smaller bug fixes

Release Notes for 10/06/2022
Version 0.7


[UI]
-You can now see how costly (in terms of storage space) your unused columns are
-Table overview of unused artifacts
[MISC]
- var. bugfixes and performance improvements

Release Notes for 9/16/2022
Version 0.5


[UI]
- Rearranged Buttons.
[MISC]
- Fixed an error that caused false positives if measure names would contain " and '.

Release Notes for 9/7/2022
Version 0.4


[ FEATURES ]
- Added M code to automatically remove columns.
[UI]
- Improved UI and added colored buttons.
 

Release Notes for 8/27/2022
Version 0.3


[ FEATURES ]
- Added C# script to remove measures.
- Added option to plot results.
[ MISC ]
- Removed info page on startup.

​Security information and documentation

This section will shed some light on what Measure Killer does in a technical way.

It can run completely offline, if you are offline the free version will be triggered automatically (Measure Killer local modes 1 and 2)

When you launch Measure Killer and you have an active internet connection, the following will happen:

Measure Killer will do one API call to google and one to our servers to check if you have access to the paid version or not (via a file on your machine which stores the license key - in the %APPDATA% folder).

With this second API call, we get your license information (if you bought a paid version) and we will get your IP address.

These two API calls mentioned above are the only times Measure Killer is doing something outside a Power BI Rest API call (to Microsoft) or something local on your machine.

After that, everything you do in the software (getting a list of workspaces, connecting to the metadata of a dataset via XMLA etc.) is documented below and all of these calls are between the client and Microsoft.

Power BI REST APIs  

This Python code uses the Power BI REST APIs to interact with Power BI data. The code provides several methods for accessing information, including accessible workspaces, datasets in a particular workspace, reports in a particular workspace, and the layout of a particular report. 

API calls made 

  • authenticate(): Uses the Azure identity package to authenticate with the Power BI API and retrieve an access token. 

  • workspaces(): Gets all accessible workspaces using a GET request to https://api.powerbi.com/v1.0/myorg/groups. 

The API calls are made using the requests package in Python, which sends HTTP requests to the Power BI API. The requests.get() method is used to make GET requests to the API endpoints. The access token is passed in the headers of the request, and the response is returned as a JSON object.

 

URLs used 

 

Note: {wk_id} and {re_id} are placeholders for the workspace ID and report ID, respectively. 

API calls specific to the Admin Mode of Measure Killer:

​Documentation

Installation

There are two different versions. An "admin" version and a "non-admin" or portable version. In order to be able to install the admin version, the user must have administrator rights in the system. But basically the two versions work exactly the same. The only disadvantage of the "non-admin" version is that Measure Killer will not be automatically added to the "External Tools" in Power BI Desktop. Measure Killer in the Microsoft store is the "admin" version.

 

For the basic and enterprise version the MSOLAP package needs to be installed to ensure XMLA connection. You might need to download and install it from Microsoft.

Used python libraries

sys, pyadomd, pandas, numpy, tkinter, os, json, shutil, re, subprocess, psutil, pyperclip, webbrowser, matplotlib
 

Instructions

Measure Killer can be easily opened in the "External Tools" in Power BI. If the "Non-Admin" version is used, the tool must either be run via the .exe file or added manually to the "External Tools". The port will be automatically added from the report in which the Measure Killer was started.  

Running Measure Killer 

  • There are four choices when starting Measure Killer: 

    • Start

      • Single report and dataset: This is the most basic way to use Measure Killer. Only one report/dataset can be analyzed per run (same file).

      • Shared/golden dataset on local machine: We can analyze multiple reports having a live connection to this dataset. We need to have all files locally however.

      • Shared/golden dataset online via XMLA: All files can remain in the Power BI Service, we can analyze a dataset and x number of connected reports which Measure Killer will find automatically (if the user has access to the various workspaces).

      • Tenant admin mode: If you are an admin, we can analyze a dataset and x number of connected reports which Measure Killer will find automatically, even if you don’t currently have access to the workspaces.

Single report and dataset

  • Analysis of a single report

This will take you directly to the main page of Measure Killer. If you launch MK from within Power BI External Tools, the port will already be pre-selected (drop down field on the top left). If you start MK separately, you will need to select it yourself, however. You further need to select the report and correct path, you will automatically see the last opened Power BI file on top of the list. Click run to start the analysis.

Measure Killer is now doing its magic. To show the user the progress and what is currently being done, a progress bar is displayed in the left window. Once this process has finished, it will automatically show the results.

The main table displays the results of the analysis. You will be able to see which artifacts are used, the size of the columns and other information like on which page of the report a measure is used, the type of visual it is used in and the number of uses. It is possible to expand each of the used columns and measures, by clicking on the arrow icon to the left of the column/measure name. This will show you, where exactly the artifact is used in the report. To expand all used objects with one click, use the expand button. 

Additionally, these results can be displayed in more detail by selecting save results on the top left. You can choose between two different Excel reports: save row by row report and save clustered report. In the row by row report, each use of an artifacts is displayed in one row. For example, if you the [Sales] column is used 8 times (e.g. in measures, calc. columns, visuals etc.), you will get 8 rows in the output. The clustered report, on the contrary, contains only one row for each artifact. 

To only see the unused measures and columns, click on the Filter unused button . Additionally, for a quick overview, you can bring up a bar chart under plot results in the menu on the top, showing the number of used and unused measures and columns. 

You can delete unused measures directly in Measure Killer, by clicking on kill measures and columns -> kill measures and selecting if you want to kill all unused measures, or if you want to select measures to kill. Alternatively, you can also generate a C# script. 

To delete unused calculated columns, you have to click on kill measures and columns -> kill columns. Then you can choose between kill all unused calculated columns and choose calculated columns to kill. If you select choose calculated columns to kill you get a pop-up menu. There you can select the calculated columns you want to kill and then press kill selected

Should you happen to delete an unused measure or calculated column and later realize that you actually still need it, you can now restore measures and calculated columns by clicking on restore measures and columns. You get a list of your previously deleted measures and calculated columns. Just select all the backups you want to look at and press add to the table. Then you can restore all items or only restore the item you selected. This feature is currently still in preview so make sure to keep backups in case something goes wrong. It is important to understand that all deleted measures will be shown here (also from other reports).

For deleting unused columns, you have to select the option kill measures and columns -> kill columns -> new M code for unused columns. A popup window appears, where you can choose whether you want to select the columns you want to delete (“Remove columns” syntax in PowerQuery), or whether you want to only keep the selected columns (“Remove other columns” syntax in PowerQuery). First you have to select a table and then select the columns, click on apply and copy the M code. Now open the Advanced Editor of the respective table in Power Query and paste the copied M Code, replacing the existing code. You can see that now there is a new step in your transformation protocol.

 

You now also have the option to hide all unused columns. For this you have to click on Kill measures and columns -> hide all unused columns. MK will automatically hide all you unused columns.

The functionality of each button is explained in more detail further down in this document.

Shared/golden dataset on local machine

  • Offline analysis of multiple live/thin reports, that are connected to one dataset.

First you need to make sure to have the dataset to be analyzed and all live/thin reports locally on your computer. We recommend putting all thin report files in one separate folder, as you can then use the select folder option. The dataset has to be open in Power BI, only then will you find its port in the dropdown. Click on refresh if you have just opened the file so MK can search for it again. Once you connect to a port and you want to use a different port, you have to reset MK via the Reset Measure Killer button on the top left. 

Once you did that, you need to add the dataset by either choosing it in the dropdown menu or by clicking on the file button on the left and choosing the .pbix file of your dataset. After that you can select the live/thin reports. Click on Add connected live/thin files and then Add files. Here you can either select individual files with Add Single File or a whole folder full of .pbix files via Add files from a folder. Once all files you want to analyze have been selected, double check if they are in the list, then click apply. You will then be taken to the main window. Click run to start the analysis.

Measure Killer is now doing its magic. To show the user the progress and what is currently being done, a progress in percentages is displayed in the left window.

The main table displays the results of the analysis. You will be able to see which artifacts are used, the size of the columns and other information like on which page of the report a measure is used or the type of visual it is used in. It is possible to expand each of the used columns and measures, by clicking on the arrow icon to the left of the column/measure name. This will show you, where exactly the artifact is used in the report. To expand all used objects with one click, use the expand button on the top. 

In this mode you can see that we do not only have artifacts from our dataset, but maybe also from our thin files since we could have report-level measures there. These are measures that are built within in the thin report and only exist there. This cannot be deleted directly but has to be done manually. 

Additionally, these results can be displayed in more detail by selecting save results on the top left. You can choose between two different Excel reports: save row by row report and save clustered report". In the row by row report, each use of an artifacts is displayed in one row. For example, if the [Sales] column is used 8 times (e.g. in measures, calc. columns, visuals etc.), you will get 8 rows in the output. The clustered report, on the contrary, contains only one row for each artifact. 

To only see the unused measures and columns, click on the Filter unused button. Additionally, for a quick overview, you can bring up a bar chart under plot results in the menu on the top, showing the number of used and unused columns and measures. 

You can delete unused measures directly in Measure Killer, by clicking on kill measures and columns
-> kill measures and selecting if you want to kill all unused measures, or if you want to select measures to kill. Alternatively, you can also generate a C# script.

To delete unused calculated columns, you have to click on kill measures and columns -> kill columns. Then you can choose between kill all unused calculated columns and choose calculated columns to kill. If you select choose calculated columns to kill you get a pop-up menu. There you can select the calculated columns you want to kill and then press kill selected

Should you happen to delete an unused measure or calculated column and later realize that you actually still need it, you can now restore measures and calculated columns by clicking on restore measures and columns. You get a list of your previously deleted measures and calculated columns. This feature is currently still in preview so make sure to keep backups in case something goes wrong. It is important to understand that all deleted measures and calculated columns will be shown here (also from other reports). 

For deleting unused columns, you have to select the option kill measures and columns -> kill columns -> new M code for unused columns. A popup window appears, where you can choose whether you want to select the columns you want to delete (“Remove columns” syntax in PowerQuery), or whether you want to only keep the selected columns (“Remove other columns” syntax in PowerQuery). First you have to select a table and then select the columns, click on apply and copy the M code. Now open the Advanced Editor of the respective table in Power Query and paste the copied M Code, replacing the existing code. You can see that now there is a new step in your transformation protocol.

 

You now also have the option to hide all unused columns. For this you have to click on Kill measures and columns -> hide all unused columns. MK will automatically hide all you unused columns.

The functionality of each button is explained in more detail further down in this document.

Shared dataset via XMLA

  • Analysis of multiple live/thin reports which are connected to a dataset (via a XMLA endpoint). This option is useful if the dataset e.g. can not be downloaded

Vorraussetzungen:​

  • Basic or Enterprise version of Measure Killer 

  • Premium Workspace (for Basic only PPU, Enterprise any Premium workspace is possible)

  • XMLA Endpoint activated with at least read permissions

  • XMLA Endpoint allowed in the Power BI Admin Portal 

  • MSOLAP package installed

  • Access to all workspaces where connected reports should be searched for (you need at least contributor permissions in the workspace)

First, Measure Killer will trigger you to log in to your Microsoft Account to check if it can find a workspace in your native Power BI tenant. Then, select the workspace where the dataset to be analyzed is located in via the drop down above the top left window. Alternatively, you can also search for a workspace in the search bar left of the dropdown. Now, click on datasets next to the drop down menu. In the upper right window, select the correct dataset and click on connect. It will ask you to connect with your account again. Be aware that your dataset has to reside in a Premium capacity. We need to authenticate twice since now we are doing an XMLA connection which is different from the Power BI Rest API call we did initially.

Now we need to select all the workspaces where Measure Killer should search for connected reports. Please be aware of the number of Rest API calls (1x per workspace) in order to avoid hitting any tenant wide limits (e.g. if we do thousands of calls this might happen). 

Click on reports in the lower left window. All reports are automatically selected for the analysis, which is visible in the last column called “Include this report”. In order to exclude one or more reports, select the one(s) to be excluded and press the “DEL” button on your keyboard. Now, in the last column it will show you that this report will not be included. Hit “Enter” to include a report that was previously excluded. You also have the option to add files to the analysis that you only have on your local machine. To do that click on add files -> add files and choose the reports that you want to include in the analysis. The reports will show up in the list and if you have all the reports you want to use in the list you can click on apply. Once you are done, click on run. A pop-up will show you all the reports you’ve selected and ask you if you wish to continue. 

Measure Killer is now doing its magic. To show the user the progress and what is currently being done, first you get a pop-up window that shows you which report layout is getting downloaded at the moment and the time elapsed on top. During the analysis you get the progress in percentages displayed in the left window. 

The main table displays the results of the analysis. You will be able to see which artifacts are used, the size of the columns and other information like on which page of the report a measure is used or the type of visual it is used in. It is possible to expand each of the used columns and measures, by clicking on the arrow icon to the left of the column/measure name. This will show you, where exactly the artifact is used in the report. To expand all used objects with one click, use the expand button on the top. 

In this mode you can see that we do not only have artifacts from our dataset, but maybe also from our thin files since we could have report-level measures there. These are measures that are built within in the thin report and only exist there. This cannot be deleted directly but has to be done manually. 

Additionally, these results can be displayed in more detail by selecting save results on the top left. You can choose between two different Excel reports: save row by row report and save clustered report". In the row by row report, each use of an artifacts is displayed in one row. For example, if the [Sales] column is used 8 times (e.g. in measures, calc. columns, visuals etc.), you will get 8 rows in the output. The clustered report, on the contrary, contains only one row for each artifact.

To only see the unused measures and columns, click on the Filter unused button. Additionally, for a quick overview, you can bring up a bar chart under plot results in the menu on the top, showing the number of used and unused columns and measures. 

You can delete unused measures directly in Measure Killer, by clicking on kill measures and columns
-> kill measures and selecting if you want to kill all unused measures, or if you want to select measures to kill. Alternatively, you can also generate a C# script.

To delete unused calculated columns, you have to click on kill measures and columns -> kill columns. Then you can choose between kill all unused calculated columns and choose calculated columns to kill. If you select choose calculated columns to kill you get a pop-up menu. There you can select the calculated columns you want to kill and then press kill selected

Should you happen to delete an unused measure or calculated column and later realize that you actually still need it, you can now restore measures and calculated columns by clicking on restore measures and columns. You get a list of your previously deleted measures and calculated columns. This feature is currently still in preview so make sure to keep backups in case something goes wrong. It is important to understand that all deleted measures and calculated columns will be shown here (also from other reports). 

For deleting unused columns, you have to select the option kill measures and columns -> kill columns -> new M code for unused columns. A popup window appears, where you can choose whether you want to select the columns you want to delete (“Remove columns” syntax in PowerQuery), or whether you want to only keep the selected columns (“Remove other columns” syntax in PowerQuery). First you have to select a table and then select the columns, click on apply and copy the M code. Now open the Advanced Editor of the respective table in Power Query and paste the copied M Code, replacing the existing code. You can see that now there is a new step in your transformation protocol.

 

You now also have the option to hide all unused columns. For this you have to click on Kill measures and columns -> hide all unused columns. MK will automatically hide all you unused columns.

The functionality of each button is explained in more detail further down in this document.

Tenant Admin Mode 

  • Analysis of multiple live/thin reports which are connected to a dataset (via a XMLA endpoint) even if you don't have permission for certain workspaces. This option is useful if the dataset e.g. can not be downloaded

Requirements:​

  • Basic or Enterprise version of Measure Killer 

  • Have admin rights for the whole Power BI tenant 

  • Premium Workspace (for Basic only PPU, Enterprise any Premium workspace is possible)

  • XMLA Endpoint activated with at least read permissions

  • XMLA Endpoint allowed in the Power BI Admin Portal 

  • MSOLAP package installed

First, Measure Killer will trigger you to log in to your Microsoft Account to check if it can find a workspace in your native Power BI tenant. Then, select the workspace where the dataset to be analyzed is located in via the drop down above the top left window. Alternatively, you can also search for a workspace in the search bar left of the dropdown. Now, click on datasets next to the drop down menu. In the upper right window, select the correct dataset and click on connect. It will ask you to connect with your account again. Be aware that your dataset has to reside in a Premium capacity. We need to authenticate twice since now we are doing an XMLA connection which is different from the Power BI Rest API call we did initially. 

Now we need to select all the workspaces where Measure Killer should search for connected reports. Please be aware of the number of Rest API calls (1x per workspace) in order to avoid hitting any tenant wide limits (e.g. if we do thousands of calls this might happen). 

Click on reports in the lower left window. All reports are automatically selected for the analysis, which is visible in the last column called “Include this report”. In order to exclude one or more reports, select the one(s) to be excluded and press the “DEL” button on your keyboard. Now, in the last column it will show you that this report will not be included. Hit “Enter” to include a report that was previously excluded. You also have the option to add files to the analysis that you only have on your local machine. To do that click on add files -> add files and choose the reports that you want to include in the analysis. The reports will show up in the list and if you have all the reports you want to use in the list you can click on apply. Once you are done, click on run. A pop-up will show you all the reports you’ve selected and ask you if you wish to continue. Then another pop-up will show up telling you how many workspaces you will get Contributor access temporarily. If you want to save time you can press the refresh user button. Be aware, the refresh user command can only be done once every hour.

Measure Killer is now doing its magic. To show the user the progress and what is currently being done, first you get a pop-up window that shows you which report layout is getting downloaded at the moment and the time elapsed on top. During the analysis you get the progress in percentages displayed in the left window.

The main table displays the results of the analysis. You will be able to see which artifacts are used, the size of the columns and other information like on which page of the report a measure is used or the type of visual it is used in. It is possible to expand each of the used columns and measures, by clicking on the arrow icon to the left of the column/measure name. This will show you, where exactly the artifact is used in the report. To expand all used objects with one click, use the expand button on the top.  

In this mode you can see that we do not only have artifacts from our dataset, but maybe also from our thin files since we could have report-level measures there. These are measures that are built within in the thin report and only exist there. This cannot be deleted directly but has to be done manually. 

Additionally, these results can be displayed in more detail by selecting save results on the top left. You can choose between two different Excel reports: save row by row report and save clustered report". In the row by row report, each use of an artifacts is displayed in one row. For example, if the [Sales] column is used 8 times (e.g. in measures, calc. columns, visuals etc.), you will get 8 rows in the output. The clustered report, on the contrary, contains only one row for each artifact.

To only see the unused measures and columns, click on the Filter unused button. Additionally, for a quick overview, you can bring up a bar chart under plot results in the menu on the top, showing the number of used and unused columns and measures. 

You can delete unused measures directly in Measure Killer, by clicking on kill measures and columns
-> kill measures and selecting if you want to kill all unused measures, or if you want to select measures to kill. Alternatively, you can also generate a C# script.

To delete unused calculated columns, you have to click on kill measures and columns -> kill columns. Then you can choose between kill all unused calculated columns and choose calculated columns to kill. If you select choose calculated columns to kill you get a pop-up menu. There you can select the calculated columns you want to kill and then press kill selected

Should you happen to delete an unused measure or calculated column and later realize that you actually still need it, you can now restore measures and calculated columns by clicking on restore measures and columns. You get a list of your previously deleted measures and calculated columns. This feature is currently still in preview so make sure to keep backups in case something goes wrong. It is important to understand that all deleted measures and calculated columns will be shown here (also from other reports). 

For deleting unused columns, you have to select the option kill measures and columns -> kill columns -> new M code for unused columns. A popup window appears, where you can choose whether you want to select the columns you want to delete (“Remove columns” syntax in PowerQuery), or whether you want to only keep the selected columns (“Remove other columns” syntax in PowerQuery). First you have to select a table and then select the columns, click on apply and copy the M code. Now open the Advanced Editor of the respective table in Power Query and paste the copied M Code, replacing the existing code. You can see that now there is a new step in your transformation protocol.

 

You now also have the option to hide all unused columns. For this you have to click on Kill measures and columns -> hide all unused columns. MK will automatically hide all you unused columns.

Various widgets in the user interface explained:

  • Back (icon - top left): Resets Measure Killer and takes you to the previous page to select the mode of Measure Killer.

  • Refresh (icon - top left): Gets the latest ports of all opened .pbix files.  

  • Help (videos): Sends you to our YouTube channel.  

  • Run: Starts the analysis.  

  • Give Feedback: Redirects you to a blog post on our website where we collect feedback for MK.  

  • Plot unused vs. used: Plots the results graphically (not a nice chart, we know, sorry!)  

  • Save results: Generates a detailed documentation as .xlsx file.  

  • Kill unused measures: Allows you to either delete selected or all unused measures (see Features below for more information on this).  

  • Kill unused columns: Opens a new window, where you can select all of the tables containing unused columns. These tables are sorted by the sum of storage space you can save in your model. It will generate an M code you can copy into the Advanced Editor of the respective table/query.  

  • Reset table: Resets the table to the default view.  

  • Filter (in main window): Here you can filter by the Name/Table/Report columns in conjunction with the text field.  

  • Clear table: Deletes the content of the table. 

  • Restore measures (preview): Shows you which measures have been deleted and gives you the possibility to restore deleted measures (be careful since we can restore measures deleted in other reports as well - you basically get a full backup of all the measures you ever deleted in MK) 

bottom of page