Dump the results of a DAX query to CSV using PowerShell

I recently got an interesting question: someone inside Microsoft wanted to get a data dump from a big Tubular model that is used internally. They wanted a special table using columns and calculated fields from several tables. So we want to use a DAX query. The result would count more that 1.5 million rows so using Excel was out of the question. 

I decided to write a small PowerShell script to query the database and write the results to a CSV. Me being a fake programmer I stole most of the code from this Technet article: http://technet.microsoft.com/en-us/magazine/hh855069.aspx

Here is the script that I used, pretty simple right. I’ve got to admit that I’m loving PowerShell more and more Smile.

$connectionString = “Provider=MSOLAP;Data Source=.;Initial Catalog=Mydatabase;”
$query = “evaluate TABLE”

$filename = “tofile.csv”
 
$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] | export-csv $filename -notypeinformation
  
$connection.Close()

 

By the way, I think you should be able to do a similar thing using VBA macro’s in Excel…but I haven’t tried it.

9 Replies to “Dump the results of a DAX query to CSV using PowerShell

  1. Hi Kasper,

    I tried to modify your script to dump data from XLSX Data Model (PowerPivot) to CSV. But method $adapter.Fill($dataset) failed.

    Could you help to specify ConnectionString to connect correctly to XLSX Data Model 2013?

    Thanks,
    — Pavel

  2. Hi Kasper! This looks exactly what I need. But I get errors when I run the script. Two questions …

    $connectionString
    How do I construct the connection string for my own workbook?

    $query
    I assume I should replace TABLE with the name of the table in my datamodel that I want to export, right?

    Hope you can clarify these two points.
    Thanks!
    —Hugo

  3. Kasper,

    Is there any way to automate this same process from a local Power BI Desktop Query/Dataset? I think the challenge is the dynamic nature of the local SSAS Workspace FolderName and the dynamic/random port name two child folders below.

    I know how to find these manually, but I just don’t know how to do it in an automated fashion. Perhaps outputting the folder name of a WMI query of the parent directory of local SSAS Workspace FolderName? Then, querying the msmdsrv.port and saving both as variables?
    At that point, I could probably proceed with the process in this article….trouble is, I am new a powershell 🙁

    1. That is probably possible but not supported so I can’t help you. But there are posts online that tell you how to connect to the engine in PBI desktop.

  4. I realize this is an older blog post, however I am having issues as MDAC is what the $adaper.fill command uses and is unavailable with in Azure runbooks (and according to Microsoft being phased out).

    Was wondering if you have found any other ways to use powershell to query a SSAS model?

Leave a Reply

Your email address will not be published. Required fields are marked *

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