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