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()

Hello Darren, thank you so much in advance for your help. Very much appreciated.

I realized about the $ sign and escaping it.
I already accessed the data through DAX Studio, so credentials should not be the issue.
What I am trying to accomplish is to get the list of all measures within a given dataset. Just the list, not modifying anything.
Once I am able to get the results, I will expand the script so I can iterate through multiple workspaces and datasets (I envision a big document file in the end with all measures from multiple and different workspaces and datasets)

Here is a breakdown of your code and the partial outputs I was able to capture.
It is failing here: $rowsOutput = $adapter.Fill($dataset)
The error is the following: Exception calling "Fill" with "1" argument(s): "The connection string is not valid."

Here are all previous variable captures for you to see:

$AA = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
$AA
CodeBase : file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Microsoft.AnalysisServices.AdomdClient/v4.0_14.0.0.0__89845dcd8080cc91/Microsoft.AnalysisSe
rvices.AdomdClient.dll
FullName : Microsoft.AnalysisServices.AdomdClient, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
EntryPoint :
DefinedTypes : {AssemblyVersionInfo, Microsoft.AnalysisServices.AdomdClient.LocalExcelVar,
Microsoft.AnalysisServices.AdomdClient.Internal.SPClient.Interfaces.ASSPClientProxyFactory,
Microsoft.AnalysisServices.AdomdClient.Internal.SPClient.Interfaces.IASSPClientProxy...}
Evidence : {<System.Security.Policy.GacInstalled version="1"/>
, <System.Security.Policy.Hash version="2">



</System.Security.Policy.Hash>

There is more output for $AA, but I think this is good enough. Moving on now...

$connection = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$connection
SessionID :
ShowHiddenObjects : False
Cubes :
MiningModels :
MiningStructures :
MiningServices :
ConnectionString :
ConnectionTimeout : 60
Database :
State : Closed
ProviderVersion :
ServerVersion :
ClientVersion : 14.0.6.443 ((BI_ASEngine_SQL17_GDR).190312-2013)
Properties : {}
Site :
Container :

$connection.ConnectionString = “Data Source=$server;Initial Catalog=$database”
$connection

SessionID :
ShowHiddenObjects : False
Cubes :
MiningModels :
MiningStructures :
MiningServices :
ConnectionString : Data Source=powerbi://api.powerbi.com/v1.0/myorg/D&A Dashboard Sandbox;Initial Catalog=DSG-DART_Pulse_v1 with AttachmentRates
ConnectionTimeout : 60
Database : DSG-DART_Pulse_v1 with AttachmentRates
State : Closed
ProviderVersion :
ServerVersion :
ClientVersion : 14.0.6.443 ((BI_ASEngine_SQL17_GDR).190312-2013)
Properties : {Data Source, Catalog, LocaleIdentifier}
Site :
Container :

$command = $connection.CreateCommand()
$command

CommandStream :
CommandText :
ActivityID : 00000000-0000-0000-0000-000000000000
RequestPriority : Normal
CommandTimeout : 0
CommandType : Text
Connection : Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Parameters : {}
Properties : {}
UpdatedRowSource :
Site :
Container :
Transaction :

$command.CommandText = $query
$command

CommandStream :
CommandText : SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES
ActivityID : 00000000-0000-0000-0000-000000000000
RequestPriority : Normal
CommandTimeout : 0
CommandType : Text
Connection : Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Parameters : {}
Properties : {}
UpdatedRowSource :
Site :
Container :
Transaction :

$adapter = New-Object -TypeName Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter $command
$adapter

SelectCommand : Microsoft.AnalysisServices.AdomdClient.AdomdCommand
DeleteCommand :
InsertCommand :
UpdateBatchSize : 1
UpdateCommand :
AcceptChangesDuringFill : True
AcceptChangesDuringUpdate : True
ContinueUpdateOnError : False
FillLoadOption : OverwriteChanges
MissingMappingAction : Passthrough
MissingSchemaAction : Add
ReturnProviderSpecificTypes : False
TableMappings : {}
Site :
Container :

$dataset = New-Object -TypeName System.Data.DataSet
$dataset

RemotingFormat : Xml
SchemaSerializationMode : IncludeSchema
CaseSensitive : False
DefaultViewManager : {System.Data.DataViewManagerListItemTypeDescriptor}
EnforceConstraints : True
DataSetName : NewDataSet
Namespace :
Prefix :
ExtendedProperties : {}
HasErrors : False
IsInitialized : True
Locale : en-US
Site :
Relations : {}
Tables : {}
Container :
DesignMode : False
ContainsListCollection : True

Finally:
$rowsOutput = $adapter.Fill($dataset)

Exception calling "Fill" with "1" argument(s): "The connection string is not valid."

• $rowsOutput = $adapter.Fill($dataset)

•   + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : AdomdConnectionException
  
  
  
  
  
  
  
  
  

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 !