kohera-logo-regular.svg

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

Vector_BG.png

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

Vector_BG.png

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.

Photo of successful woman coder hacker web creator sitting armchair comfortable workspace workstation indoors.
The hurdles and pitfalls of moving or migrating a System-versioned temporal table cross database
Maybe you already have your own way of doing this and are wondering about alternative methods, or maybe you are...
Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...