PowerShell – Accessing Power BI Desktop Data Cube and more…

Recently I got interested in getting data out of a Power BI Data Model to push into an SQL database.

On my way I discovered a lot of interesting techniques and Tools for Power BI Desktop.

1. First of all you need to know that Power BI Desktop actually is a small Web Server running an SSAS Mini Cube.

2. Next you need to prepare PowerShell to access the PBI Cube.

Solution :

In order to start you first need to start PBI Desktop and open a PBIX file you created before.

Once started you need to find out on which Random Port is was started.

There are 4 way to get the job done.

1. Install Dax Studio which you need to have, no question about this !

    Even if you are not using it for getting the Random Port…

Look in the bottom right corner :

image

2. Command line tool NETSTAT

   Run NETSTAT –b command and look for Power BI

image

In this case it was Port 60520

3. Look for the msmdsrv.port.txt file.

Once PBI is started it will create some folders and files.

You can find those here :

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\

image

If you have multiple PBIX files open you will see more Random created subfolders.

Go to the random created folder and next to the Data Subfolder.

Open the msmdsrv.port.txt file to get the Port number.

image

4. Using the Tabular Editor

But it needs some dependent MS AS AMO Libraries

image

image

Now that you know how to get access to the Web Server Port.

PowerShell 32 Bit

You can now test PowerShell connection to the Power BI Desktop you have open.

But you first need a Connection String to be used in PowerShell

FIRST USE the 32 Bit PowerShell if you are using a 32 Bit Office installation.

Later in this post I will explain how to get it running in a 64Bit PS even if you have a 32 Bit Office installed.

I suggest that you use EXCEL for this to copy and paste it Winking smile

Go to Data and choose From Analysis Services.

image

Follow these steps :

image

image

image

image

Copy it from here :

image

You can also get the Catalog GUID from the folder name where you could find the Port number.

image

Run this PowerShell Script, it uses a DMV Query to get hold of the PBI Cube Properties

CLS

$Port = "60520"
$Catalog = "cff5b9a8-2f87-416c-97fd-8eccd5962d51"

$Query = 'Select * from $SYSTEM.DISCOVER_PROPERTIES'

$connectionString = "Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:$Port;Initial Catalog=$Catalog"
 
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection

$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset)

$dataset.Tables[0] | Format-Table # export-csv $filename -notypeinformation
   
$connection.Close() 

image

To list data from a Table use the EVALUATE command

$Query = 'EVALUATE Tickets'

image


PowerShell 64 Bit

If you are running a 32 Bit Office, you need to install the OLAP Drivers for 64 bit

(will be done when you install DAX Studio).

clip_image002

Or manually register the MSOLAP.DLL from the Power BI install folder.

image

Tools :

Power Pivot :

You can connect to a Power BI Desktop model using Power Pivot for example.

image

image

Dax Studio :

You can run commands directly against your Power BI Desktop.

Use DAX Commands

image

Or use a DMV Query to select the data from the PBI Meta Data or the Data Set.

// Select data using DMV queries

SELECT * FROM $System.DISCOVER_PROPERTIES

// Or select all DATA from the PBI Data Set using this command

EVALUATE Tickets

image

For more info on DMV Queries see here

Power BI Helper :

image

More Tools you can find here

Enjoy !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: