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.