Waiting for PBI desktop "working on it"

If you have ever created a Power BI (PBI) report, you may have noticed that every change in PBI data model is accompanied by some waiting time and message "Working on it". This is because PBI desktop needs to save the changes in the model and perform what it needs to perform.

Now, if you need to do more than one change on PBI data model (e.g. create measures, hide columns, switch relationship cross-filtering direction to "both ways", change measure formatting to "thousand separator no decimals"), be prepared for more waiting.

No more waiting for PBI desktop

While I was working on my recent PBI project, I managed to save quite a bit of time on mass operations on PBI desktop data model. I did that by automating many kinds of routine operations in PBI Desktop via PowerShell.

As a side benefit, I also managed to programmatically create the data model documentation in Excel and JSON for review and source control (perhaps this deserves a separate post).

The script may not be ideal, but it did the job for me.

How this is possible

When you open a pbix file (PBI report), the process PBIDesktop.exe spawns an SSAS Tabular server msmdsrv.exe. The SSAS Tabular DB contains the entire data model of the opened pbix file. From the PBIDesktop.exe command line, it is possible to get the path of the opened pbix file. And from the msmdsrv.exe process, it is possible to get the port where SSAS Tabular is running.

When you know the host and port of SSAS Tabular instance, you just need to connect to it - and your entire PBI model (i.e. tables, relationships, measures, load scripts) becomes available for scripting via .NET API and Microsoft.AnalysisServices.Tabular namespace. When a model change is made and saved, PBI desktop updates the view automatically. You do still have to wait for this update, but only once per a set of changes - not after each model change.

There are tools

Yes, there are tools to work with the Tabular model. I prefer to do this in PowerShell because then I am in full control and I can create the logic exactly as I need.

What you need to know to work with the script

• PowerShell
• Power BI
• SSAS Tabular

What you can do

• Mass create tables, measures, relationships
• Mass rename tables, columns, measures by find-replace
• Mass delete tables, columns, measures
• Mass change properties of tables, columns, measures, relationships, e.g. hide columns, change measure formatting, or set cross-filtering of relationships to "both directions"
• All that you can do using regular expressions
• As a side benefit from the access to the PBI model, you can automatically create data model documentation and save it as Excel or model.json - e.g. for review or source control

What you can break

• If you rename a measure, all visuals using the old measure will break because visuals are not part of SSAS Tabular Model.

  At the moment I cannot do anything about that. Well, not until Microsoft exposes the PBI desktop API (maybe it already has, but I am not aware).

  Workarounds:

  • If you know what you rename, it will be straightforward to drag and drop the renamed measure in the impacted visuals in place of the old measure(s).
  • Alternatively, you can create wrapper measures that will only be used in visuals. The DAX formula for those measures will just refer to the underlying measure, i.e.:

    Visual measure = [Underlying measure]​

    Then you can rename [Underlying measure] with the script. The change will be cascaded to the DAX formula of [Visual measure]. And because the name of [Visual measure] remains intact, the visuals will not break.

• If you rename a column using the current version of the script (as of 20221108), the column name change is not (yet) cascaded to the measures/calculated columns/tables that use the old column name. The model objects using this column will break. Implementing cascade renaming of columns is in the works.
• There is no undo in the current version of the script (as of 20221108). To revert to the previous version of the model after making changes, you will need to close the pbix without saving and open it again.

How to get started

The ps1 files with functions and examples are on github.

1. Download the directory
2. Install the PS dependencies (PS modules)
3. Open your pbix file in PBI desktop
4. Try out the examples

Example: mass hide columns that begin with _ or $

# The functions are here
. "$PSScriptRoot\psPbiDesktop.ps1"
# if you have a single pbix open, this will work
# otherwise specify here the name of opened pbix file
$pbixFilePathRegex = '.*' 
# Get local PBI desktop instance
$localPBIDesktopInstance = Get-LocalPBIDesktopInstances -pbixFilePathRegex $pbixFilePathRegex
# Get the SSAS Tabular host and port
$port = $localPBIDesktopInstance.LocalPort
$SSASTabularHostPort = "localhost:$port"
# Get the SSAS Tabular DB behind the PBI desktop - there is a single DB
$db = Get-SSASTabularDBs -SSASTabularHostPort $SSASTabularHostPort
# look for tables with this name pattern - keep .* for all tables
$tableNameRegex = '.*'
# look for cols with this name pattern - in this case, columns starting with _ or $
$columnNameRegex = '^_|^\$'
# set these properties - refer to psPbiDesktop.ps1 for possible properties and their names
$columnProperties = @{
    IsHidden = $true
}
Set-SSASColumnProperties -db $db -tableNameRegex $tableNameRegex -columnNameRegex $columnNameRegex -columnProperties $columnProperties