An adventure on SQL Server performance and features

Quick Tip: How to find the address of the PowerBI local tabular instance

Q

Super quick tip, I’ve seen this done via CMD or DaxStudio, but not via PowerShell (probably because I haven’t searched so much since I’ve done it myself), but here we are:

I’m sure you know that PowerBI tabular models are 100% the same as SSAS tabular models, hence you can use SSMS to connect to a PowerBI Tabular model as if it was SSAS (because it is), to do whatever you want, for example scripting out the objects that you’ve created in the PowerBi Desktop GUI or extract the model to deploy it in SSAS.

The only issue to do so is that PowerBI Desktop each time you open a report creates the local SSAS Tabular instance on a random port, so it’s not like you can save a connection string and that’s it 🤷‍♂️

But you can do the following:

$data = Get-Process -ProcessName msmdsrv

if ($null -eq $data) {
    Write-Host "A PowerBi model instance is not running"
}
else {
   $a = Get-NetTCPConnection -OwningProcess $data.Id
   $port = $a[0].LocalPort
   Write-Host "The PowerBi local SSAS instance is @ 127.0.0.1:$port"
}

PowerBi creates a different instance for each PowerBI Desktop client open, this code doesn’t consider that, because why you would connect to multiple local SSAS instance in SSMS? What are you doing there?

About the author

Emanuele Meazzo

1 comment

An adventure on SQL Server performance and features

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you