AdomdClientExportToCsv.ps1

### Author: Darren Gosbell (https://darren.gosbell.com)
### Date  : 22 Jan 2021 
### Usage : To use this script just update the following 4 variables for you environment


$server = "localhost\tab17"
$database = "adventure works"
$query = “evaluate 'Date'”
$filename = “c:\temp\tofile.csv”

# this line assumes you have installed the ADOMD Analysis Services client library on your machine
# you will have this if you have installed SSMS otherwise it can be downloaded from 
# https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") > $null

$connection = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdConnection

$connection.ConnectionString = “Data Source=$server;Initial Catalog=$database;”
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
$rowsOutput = $adapter.Fill($dataset)

$dataset.Tables[0] | export-csv $filename -notypeinformation
  
$connection.Close()

The invalid connection string error might mean that you need to encode the ampersand in your workspace url using %26 since ampersands in urls are special characters. And if you are doing that it would not hurt to also encode the spaces as %20 just in case.

eg

$server = "powerbi://api.powerbi.com/v1.0/myorg/D%26A%20Dashboard%20Sandbox"

$server = "powerbi://api.powerbi.com/v1.0/myorg/D%26A%20Dashboard%20Sandbox"
$database = "DSG-DART_Pulse_v1%20with%20AttachmentRates"

Exception calling "Fill" with "1" argument(s): "The connection string is not valid."
Do I have to login first?

You do have to login, but ADOMD client should prompt you for that.

It could be the version of adomdclient in the GAC (Global Assembly Cache) on your machine. It looks like its v14 and we are now upto v19. Which might explain why it works on my machine and not on yours.

Instead of the line doing LoadWithPartialName. If you have DAX Studio installed you could try loading this dll from there using something like:

[System.Reflection.Assembly]::LoadFile("c:\program files\DAX Studio\bin\Microsoft.AnalysisServices.AdomdClient.dll") 

Or you could get the installer for the latest version of Adomdclient from here https://learn.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions (note: you need to close an re-open Powershell to load a different version of a dll, you cannot just re-run the command since there is no "unload" of a dll)

thank you so much this helped !