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)

​