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

Hi Darren, and many thanks for this script, it works. I did not test yet with windows scheduler and such, but (at least launching the script manually) the system asks for my windows credentials. This would prevent process automation. Do you have suggestion on how to enhance the script or is there something I can do with the scheduler settings and get rid of this sign-on?

Are you sure it's windows credentials that you are getting prompted for? Normally windows credentials are passed through without any prompting and if the current logged in user does not have access then you get a permission denied error of some sort.

What sort of data source are you connected to? Is it a local SSAS server using <instance name> or is it AzureAS, Power BI XMLA or a local HTTP(S) connection

Hello and greetings from Winnipeg. I am trying to use this script to extract all measure formulas for specific Power Bi datasets programmatically (Powershell). I tried to use this example but no luck so far.
How would you modify the script in a way that the query is "SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES"
I tried this, but did not work.
$server = "powerbi://api.powerbi.com/v1.0/myorg/D&A Dashboard Sandbox"
$database = "DSG-DART_Pulse_v1 with AttachmentRates"
$query = “SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES”

Any help will be much appreciated!

One problem with calling a DMV from powershell is that powershell prefixes variables with the $ symbol so it will assume that you have a variable called $system that you want to inject into the query. There are 2 ways around this

You can either escape the $ with a backtick

$query = “SELECT * FROM `$SYSTEM.MDSCHEMA_MEASURES”

Or you can use single quotes for the string so that the variable substitution does not take place.

$query = 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES'

But if you have a different issue, if you can let me know more details of what you mean by it "did not work" then we should be able to get this working (as long as you have write access to the dataset, users with only view & build cannot view measure expressions)

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 !