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.

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

    Thanks,
    — 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.
    http://go4answers.webhost4life.com/Example/connect-local-powerpivot-workbook-oledb-15913.aspx

    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 …

    $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

    • Hugo de Groot

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