​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. In addition, the last opened .pbix  file is pre-selected as the file path.

​

Running Measure Killer

• There are three 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).

​

Single report and dataset

 

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 in percentages is displayed in the left window. Once this process has finished, click on show 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 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 plus 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 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 artifacts.

​

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 only specific measures to kill. Alternatively you can also generate a C# script.

​

Should you happen to delete an unused measure and later realize that you actually still need it, you can now restore measures by clicking on restore measures. You get a list of your previously deleted measures. 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 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). Now 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.

​

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

​

Shared/golden dataset on local machine​

​

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 report files in one separate folder. The dataset has to be open in Power BI, only then you will find its port in the dropdown. Click on refresh if you have just opened the file so MK can search for it again. 

​

Now you need to add the dataset by clicking on edit report list and then add a new .pbix file. Select your dataset here, this is very important. After that you can select the live/thin reports. Either individually with add a new .pbix file or a whole folder full of .pbix files via live/thin reports from a folder. Once all files you want to analyze have been selected, double check if the dataset and thin files have been identified correctly, then click submit. 

​

Measure Killer will double check to make sure we have identified the correct dataset.

​

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. Once this process has finished, click on show 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 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 plus 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. 

​

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. This cannot be deleted directly, since we only have the dataset open currently.

​

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 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 artifacts.

​

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 only specific measures to kill. Alternatively you can also generate a C# script.

​

Should you happen to delete an unused measure and later realize that you actually still need it, you can now restore measures by clicking on restore measures. You get a list of your previously deleted measures. 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 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). Now 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.

​

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

​

Shared/golden dataset online via XMLA

​​

Requirements:

• 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) 

• Reports that cannot be downloaded from the Power BI service cannot be analyzed by Measure Killer either (see Details -> Measure Killer compatibility -> ✗ Thin files for more information)

​

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. 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 right 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. Once your list is complete, click on run Measure Killer. 

​

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. Once this process has finished, click on show 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 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 plus 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. 

​

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. This cannot be deleted directly, since we only have the dataset open currently.

​

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 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 artifacts.

​

You cannot delete unused measures directly in Measure Killer since we only read XMLA information. You can however generate a C# script.

​

Should you happen to delete an unused measure and later realize that you actually still need it, you can now restore measures by clicking on restore measures. You get a list of your previously deleted measures. 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 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). We cannot do this directly so please use the new M code in your preferred tool.

​

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

​

Various buttons in the user interface explained

• Back (icon - top left): 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. 

• Copy text: Copies the text from the text box on the left. 

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

• Execute: Starts the analysis. 

• 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: 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)

​