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

$dataset.Tables[0] | export-csv $filename -notypeinformation


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.

  • Kasper – any idea how to do this with Powershell but referencing a desktop Excel file?

  • 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?

    — Pavel

  • Mr T

    Mijnheer de Jonge,

    Is it possible to connect to a local tabular model hosted in Excel (VertiPaq/xVelocity/Power Pivot)?

    It seems that was not possible in PowerPivot v 1.

    But what about Power Pivot v 2?

    Mr T

  • Hugo de Groot

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

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

    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.

    • Hugo de Groot

      Bummer. I found out this won’t work to access PowerPivot in Execl.

  • Steven Neumersky


    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 🙁

    • Kasper

      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.