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
$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
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
No this is not possible today.
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
Bummer. I found out this won’t work to access PowerPivot in Execl.
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 🙁
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.
I realize this is an older blog post, however I am having issues as MDAC is what the $adaper.fill command uses and is unavailable with in Azure runbooks (and according to Microsoft being phased out).
Was wondering if you have found any other ways to use powershell to query a SSAS model?
Hello,
how do I refresh data before dumping?
Probably using DAX studio would be better these days to try and export data: https://www.biinsight.com/exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-dax-studio-methods/