Use PowerShell to get all the Measures from a 2016 Tabular Cube

With Analysis services 2016 and Analysis Services Management Object (AMO) we now have some powerful tooling to automate our cube with, for example, PowerShell. In fact, it’s now even (relatively) easy to retrieve all the measures from a cube in just a few seconds. That’s the example that we will be demonstrating in this blog. In the second part of the blog, we’ll try to do the same with an open Power BI file.

SSAS Tabular Cube 2016

Two important prerequisites:

• The cube/DB must at least be running in Compatibility mode 1200 (coming with SQL server 2016). When you restore a 2012 cube into SSAS 2016 it’s not sufficient as the compatibility mode needs to be set to 1200 (or higher).
  
• You need to register some dll’s. This is done automatically when you’ve installed Analysis Services 2016. You then load the dll in your PowerShell script by using the LoadWithPartialName function (see first line in the script below).

So, here’s the PowerShell script that will get the measures from a cube (change the first three variables to fit your environment):

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular");

$tab = "YourSSASserver";
$dbId = "ID_or_DB";
$saveas = "C:\YourFolder\{0}.dax" -f $tab.Replace('\', '_');


$as = New-Object Microsoft.AnalysisServices.Tabular.Server;
$as.Connect($tab);
$db = $as.Databases[$dbId];
# in case you want to search by the name of the cube/db:
# $as.Databases.GetByName("DB Name");

$out = "";

foreach($t in $db.Model.Tables) {
  foreach($M in $t.Measures) {
    $out += "// Measure defined in table [" + $t.Name + "] //" + "`n";
    $out += $M.Name + ":=" + $M.Expression + "`n";
  }
}

$as.Disconnect();
$out = $out.Replace("`t","  "); # I prefer spaces over tabs :-)
$out.TrimEnd() | Out-File $saveas; 

As you can see, it’s not such a long or complicated script. We connect to the SSAS instance and try to find the database. When this is done, we loop trough the tables and for each table we’re getting the measures that are defined in that table.

Just be warned about the separators (`;` vs `,`) as the settings on the SSAS server might be different than the ones on your local machine.

Also, check out the Analysis Services Cmdlets.

Power BI Desktop

We can now develop a sort like script to get the measures from an open Power BI file (*.pbix files). When you’re working with a Power BI file you’re actually running a local instance of SSAS (hint: look for the process named `msmdsrv.exe`). And you can even connect to it with SQL Server Management Studio (SSMS). The port being used will most likely differ when you restart Power BI.

A colleague of mine pointed out that we’re able to find the port(s) that Power BI is actively using, which is stored in a file named `msmdsrv.port.txt`. The location of the file is where your local app data is stored. In that folder, you will find all open workspaces that Power BI created. More specifically, it’s in this path:

<LocalAppData>\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\<WorkSpace>\Data

After you connect with SSMS, you’ll be able to browse it like you’re used to, but the name of the database does not reveal the name of the Power BI file:

Now that we have an approach for hacking ourselves into the Power BI model, we can extend the existing PowerShell script and make it work for Power BI as well. This is the link to the full script.

Enjoy it! :-)

Side note: not all the features that are present in the SSAS API will work with Power BI files. For example, processing the cube does not seem to be working. In case you find a workaround for that, I’m happy to hear it.