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.

8 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.

Leave a Reply

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