Power BI Cleaner Gen2 now covers Analysis Services Models as well

In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.

Just a warning that the live connections to Analysis Service or shared Power BI dataset might be slow, as Power Query will retrieve the data from all datasets that are available for your from the service. I might include a feature to pre-select datasets in a future release if there is interest. So please add comments if you want to see this feature.

If you are new to the tool: It shows you which DAX expressions are used where in your reports and also indicates which elements can be deleted or hidden. It covers most use cases from visual fields, titles or format properties over filters on all levels, calculation groups, roles, incremental refresh policies…

As before, there are 2 versions: A basic Power BI version and an Excel-version, that adds some very convenient additional features: The option to analyze thin reports (connected to Analysis Services or a shared Power BI dataset) and to generate scripts that delete unused measures and hides unused columns automatically.

Basic Power BI version

Fill in parameters

First you have to open the Power BI file whose data model you want to analyse. Then open the Cleaner Power BI template file and fill in the local port number and file path of the file to analyze into the 2 mandatory parameters.

Enter parameters for the new Power BI Cleaner file

I’m always using DAX Studio to grab the port number, but there are other methods described in this article as well. Please use only the numeric part (not “localhost”):

Retrieve port number from DAX Studio

When you try to refresh the data, you will be prompted with a dialogue about how to connect to your local Power BI model:

Connect to data model that runs on a local Analysis Services instance

Just accept the default dialogue (“Use my current credentials”).

Next comes a prompt for Privacy Levels. Click on “Ignore Privacy Levels” in there:

Ignore Privacy levels in this prompt

If this prompt is not appearing and privacy levels are still enabled, you might get an error message (ending: “…. Please rebuild this data combination”). You then have to ignore them via the menu: File -> Options and settings -> Options -> Privacy (under: CURRENT FILE) -> Ignore the Privacy Levels..:

Ignore the Privacy Levels through the menu

 

Main report

Overview page

On the Overview page, you see a matrix with table and column names in the row section and usage information in the columns. Upon readers requests I’m distinguishing between fields that are not used at all in the report (1) and those who are used in DAX expressions, but the fields it has been used in, have not been used in the report at the end (2). So fields in here can also be deleted from the model. But depending on when you delete them, you might break some measures or calculated column temporarily. But those are the ones that can also be deleted at the end. The decision to delete a column in the source or hide it in the report might depend on the column size, which is also given at the end of the matrix (5).

In the next column you see where the used fields are actually used (3). So these fields should stay in the model. The next column is just a summary, indicating if the field can be deleted or not.

To the left there are some filters that you might find useful. For example, you can select all fields that can be deleted by choosing the first box in the “Can be deleted”-filter (6).

If you expand the fields in the row section, you will first see the DAX expression of the field. After that, you see where this field has been used throughout the report.

DAX Expressions page

The last page “DAX Expressions” simply holds a table with all DAX expressions in the model as a reference of to quickly check items. You can also use the search box to find strings in DAX expressions.

Delete or hide unused items automatically: Excel version

The Excel-version can also generate scripts for you that can delete unused measures or hide unused columns automatically. It also allows to analyze thin reports: Meaning Power BI reports that don’t have an own data model but instead use a live connection to a central data model in the service or use a live connection to an Analysis Services Tabular model (on prem or Azure).

I strongly recommend to close all other Excel files when working with this solution, especially when you’re analyzing thin reports (Analysis Services or PBI Live Connection). It will occupy resources and Excel might also become unresponsive for some time.

For these queries to run, you have to disable the privacy settings on file-level:

Disable privacy levels in Excel

Then you have to select the modus:

Single Power BI Desktop File

If you choose “PBI_Desktop_pbix” the following fields will show up:

In cell B8 you have to enter the port number just like in the PBI version. In B37 you must fill in the path to the pbix like in the Power BI version or the path to a pbit version. Both will work.

Click on “Refresh main result” will refresh the data and move you over to the “Result” page. This looks just the same than in the Power BI version.

The next page “Measures_Delete” holds a table to the left with one row for each measure that is not used. Based on it, the table to the right will generate a code that can be used to delete all the measures using the Tabular Editor tool.

Delete unused Measues

Before using this functionality, I strongly recommend to make a copy of your Power BI report, as the following actions are not officially supported by Microsoft. So if they break your report, they will not help you out on it.

The left table contains a list of detected measures that are not used anywhere in the report. If you want to keep some of the unused measures from there, simply delete the “Delete”-entry in column “Action” and it will be excluded from the code. Once you’re happy with the selection, click the button “Create and copy code”. This will refresh the table to the right that  contains the code that would automatically delete all the listed measures if used in Tabular Editor.

Use Tabular Editor to delete or hide unused items

Then you can simply copy the code, open Tabular Editor (LinkToProVersion, LinkToFreeVersion) and paste the code into the “Advanced Scripting” window. Click run, save the file and the cleanup is done:

Open Tabular Editor

Connect to PBIX model

Select default settings

Paste code from Excel and run script

Save modifications to the model

In your PBIX-model, the measures should be gone now.

The same principle goes for the columns, that can be hidden with the tables from sheet “Columns_Hide”.

Analyze thin reports

Based on files

Also in the Excel-version, there is a table where you can add all the files to analyze that are using the currently opened dataset. Yes: You can analyze “thin reports” with it that don’t have an own data model, but are connected to a centrally published model instead. Simply add the path to the pbix or pbit-versions into table “ReportFile (s)” from cell B37 onwards on the “Instructions”-sheet. The model that holds the central dataset must be open in Power BI Desktop and its port number entered into B11 of the “Instructions” page.

In the pivot table on the “Result”-sheet, the last column level field is “Report File(s)” and there you can see in which (thin) report each element is used.

Directly from the service

And to make it even more convenient, you can also analyze your reports from the Power BI service directly. This requires an app registration that provides the access to this data. This article provides a guide on how to register such an app. The app needs to have “Workspace.ReadAll” and “Report.ReadAll” permissions. To authenticate with this service from Excel, you can create and grab a token from one of the API endpoint-pages, like: Datasets – Get Datasets – REST API (Power BI Power BI REST APIs) | Microsoft Docs. There you must click on “Try it”:

Connect to endpoint to grab token

Next the authentication will start and you have to login to the account that has access to the app. On success, you’ll see a window like so that displays the token. Click on “Copy”:

Copy token from webpage

On the “Instructions” page of the xlsx choose modus “PBI_Live_Connection”. Paste the copied authentication figures into row 30/31:

Also fill in the port number into B25 and the dataset ID into B26. You find the dataset ID in the URL if you edit the dataset details in the service like so for example:

Grab shared dataset ID from URL

2 different connection modes

If you have admin rights in Power BI, the queries can fetch details from all workspaces in your tenant. Therefore you have to switch the value in cell B27 to “As Admin”. Otherwise, keep the default entry “As User”. In that case, only the details of those reports will be displayed, where you have access to.

Please note, that even measures that have been created in the thin reports (report level measures) will be detected as well. Just have in mind, that they will not be covered by the automatic

As long as there is an entry for the DatasetID in cell B26, Excel will try to use the dataset and the corresponding token. After a while, the token will become invalid. To generate a new one, simply refresh the page where you’ve grabbed it from. If you haven’t already, now it’s time to vote to enable the usage of custom connectors in Excel so this cumbersome process can be omitted: Add support for custom data connectors (Power Query M extensions) – Welcome to Excel’s Suggestion Box! (uservoice.com)

Reports connected to an Analysis Services model

If you select modus “Analysis_Server” the following dialogue will appear:

Enter the path to the SQL server into B15, or “localhost” if it’s running you your local machine. The database name goes into B16 and the credential in B19/B20 follow the same mechanics than described above under “Directly from the service”.

Optionally, you can add links to local files if they should be analyzed as well.

Just have in mind, that these methods can take quite a while, because in order to identify all reports in the Power BI service that use the specified datasets, Power Query has to retrieve metadata from all datasets and all data sources that live in your tenant (and you have access to).

Areas covered

The following areas of field usage are covered in these versions:

  • DAX expressions (measures, columns, hierarchies, tables, report level measures, roles, bins, calculation items and title expressions)
  • Incremental refresh policies
  • Relationships
  • Filters (visual-, page- and report level)
  • Visuals (incl. conditional formatting)

Limitations

  • When upgrading your workspace, your reports might be rebound to the dataset. In that case, the API will not return any data.
  • Automatic retrieval by the API will also not work for reports in the old workspace format.
  • Fields used in connected Excel reports are not covered by this solution.
  • Fields in custom visuals might not be properly detected. If you find problems there, please report.

Warning

I cannot guarantee that this all works 100% correct, so you’re using it on your own risk. Don’t forget to make a copy of your file before starting to throw out your measures and columns. If you find any bugs, please mention them in the comments.

Feature requests

Are there additional functionalities that you’d like to see in this tool?
Post it in the comments, please.

Potential Problems

Run-time error ‘1004’

This error message might indicate the your token isn’t valid any more and you have to refresh it:

… please rebuild the data combination – error

If you’re getting a warning that data sources cannot be combined, you must disable privacy settings like so:

Disable privacy levels in Excel

 

If you experience further problem, please post them in the comments or send me an email.

Download files

PBIT: Power BI Version Cleaner Gen2

Excel version with macros: Cleaner Gen2 Excel-Version with Macros

Enjoy and stay queryious 😉

Comments (19) Write a comment

  1. Hi Imke, I gave a try this new version today but I’m getting back an error. I went back to the previous version with the same pbix file and everything worked well. The error message is:

    DistinctFields
    OLE DB or ODBC error: [Espression.Error] The column ‘Level’ of the table wasn’t found..

    On a different test, I tried to use the Excel version to connect to a local pbix thin file which dataset is in the service, but I received a 1400 error message. In this scenario I was not able to connect with the previous version of Excel tool either. Maybe I’m missing something here, please advise

    Cheers,

    Reply

    • Hi Mau,
      sorry for the inconvenience, but I cannot reproduce unfortunately.
      But I can image where it could come from and tweaked the code a bit.
      Please try out the latest version and let me know if it works.
      Thanks a lot,
      Imke

      Reply

  2. Pingback: Power BI Cleaner Gen2 – Curated SQL

  3. Hi Imke, please don’t worry, both of your tools are fantastic, love them. I know it must not be easy to cover all the bases. You’ve already done a great service to the community with these tools. Please keep up with this great work. Regarding the latest version, I tested it with two different pbix files, both in import mode. With one of them the Excel tool and the PBI tool worked well. With the other, I received thes error messages:

    StorageSize
    Column’Key’ in table ‘StorageSize’ contains a duplicate value ‘ClubView.Club Name’ and this is not allowed for columns on the one side of a many-to-many relationship

    AllFields_DAX
    Query ‘AllFields_DAX’ (step ‘Removed Duplicates’) references other queries or steps, so it may not directly access a data source

    I don’t know if it is something in my file or something that could be fixed in the tools, but this info helps.

    Cheers!

    Reply

    • Hi Mau,
      sorry for the inconvenience.
      Will fix the duplicate error with the next release.
      With regards to the other error, you have to disable privacy settings (this is described in the blogpost).
      Cheers,
      Imke

      Reply

  4. I’ve tried the the powerbi & excel version , it works very fine and fast with a local pbi connection .
    I did not arrive to connect to a dataset in the service , but this is rather ‘a nice to have’ for me .
    Thanks for all this great work and sharing this with us .
    I will certainly use this tool

    Kind regards

    Alain

    Reply

    • Hi Alain,
      Thank you for the kind feedback.
      Over time I will add more documentation on how to use this tool which will hopefully help you to use the “nice to have”-features as well 🙂
      /Imke

      Reply

  5. Hi Imke,

    I’ve followed all steps correctly but keep getting this after trying to apply the changes post the local host step. What could be wrong here?

    AllFields_DAX
    Loading blocked by failures with other queries.

    AllFields_Used
    The specified distinct criteria is invalid.

    DistinctFields
    The specified distinct criteria is invalid.

    StorageSize
    Loading blocked by failures with other queries.

    /Mido

    Reply

    • Hi Mido,
      sorry for the inconvenience.
      Are you using the Excel-version or Power BI?
      And if it is the Excel-Version: Which modus?
      /Imke

      Reply

    • I am getting this refresh error as well. I even changed all the data sources so the queries point to my local SQL Server – everything is on the local machine. Followed instructions on disabling privacy levels in Options.

      Reply

      • When I go into the ‘AllFields_Used’ query in PQ, it fails on the 2nd step and says, “An error occurred in the ‘UsedFields1_Visuals’ query. Expression.Error: The specified distinct criteria is invalid. Details: [List]”. So when I go to ‘UsedFields1_Visuals’ query it fails on the 2nd step and says, “DataFormat.Error: We found extra characters at the end of JSON input. Details: Value=! Position=634528”. I have no idea how to fix that, but maybe that’ll help someone!

        Reply

        • Thanks a lot for this additional info.
          Will try to check this over the weekend.

          Reply

  6. Hello, I too am having the StorageSize issue when using the Power BI file. For me it says:

    “Column ‘Key’ in Table ‘StorageSize’ contains a duplicate value ‘Storage Location.STORAGE LOCATION CODE’ and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.”

    Thanks

    Reply

  7. Hi Imke:

    I am trying out the tool and am having a field tagged as ‘Delete’ when it is used directly in a visual, any steps/FAQ you can point me to?

    I have a screen shot of the output if that would help.

    Reply

  8. Its showing on the overview screen. One of the fields is used as an implicit measure so on the visual it appears as ‘First Audit Date’ instead of ‘Audit Date’. That one is used on multiple card visuals. I just realized the other one is used on a custom visual – paypal KPI Donut Chart.

    Reply

  9. Hi lmke
    unfortunately when I try to connect directly to a tabular model in SSAS I got these errors:
    We cannot convert the value null to type Text. And I’m using the newest version.
    Thanks in advance
    Best regards
    Brian

    Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz