Dfyjkt

Apparently the databases/cubes in an analysis server contain "script connection as > alter" option like so:

I want to use a powershell script like this:

 $hashtable = @
Import-Csv "CSV_file" | ForEach-Object 
 $hashtable += @$($_.Server) = ($_.Cube -split 's*,s*') 


Import-Module SqlServer

foreach($server in $hashtable.Keys) 

 $Analysis_Server = New-Object Microsoft.AnalysisServices.Server 
 $Analysis_Server.connect("$server") 

 foreach($CUBE in $hashtable[$server]) 

 ####### Setting connection property for $Cube #######
" 
 <Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>$CUBE</DatabaseID>
 </Object>
 <ObjectDefinition>
 <DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xsi:type="RelationalDataSource">
 <ConnectionString>Connection Timeout=60;User Id=someID;Password=pass;Data Source=td.domain.com;Persist Security Info=True;Session Character Set=UTF8</ConnectionString>
 </DataSource>
 </ObjectDefinition>
 </Alter>
 "

 

that takes input a csv file

Server,Cube

server1.domain.com,Database1

and loops through the databases/cubes in the server to alter their DataSource <ConnectionString>

However i am sure that there is something missing in that script, but the thing is if i run that XML in SSMS, it complain about the <Name> element for <DataSource> is required. i understand that the full XML is needed, but it wont work from a script perspective that i am trying to accomplish because i cant have the same DataSource for other databases i have in the server. they are all possibly different, so the ID, Name, etc...have to be removed from the XML query in powershell script.

i receive this error

The object definition supplied for the ALTER statement is of a different type that the object reference to be altered.

Note: The reason i am not using analysis server properties instead is because the analysis properties method i was trying here ConnectionString property not printing connection string

did not work, and has been escalated to Microsoft as broken property,

We learned together that SQL Server 2016 or higher works with at compatibility level of 1200 or higher. Whenever you work on level 1200 or higher you are not able to use the classic AMO objects to work the the different properties.

Microsoft actually states this very clearly: Programming with Analysis Management Objects (AMO)

If you are programming for tabular models at 1200 or higher compatibility level, use the Tabular Object Model (TOM). TOM is an extension of the Analysis Services Management Object (AMO) client library.

Learning about TOM is just like learning about AMO back in the days. You have to start somewhere like this: Tabular Object Model (TOM)

We found that this works

Import-Module SqlServer

$newConnectionString = "Connection Timeout=60;User Id=SOME_NEW_ID;Data Source=10.10.19.10;Persist Security Info=True;Session Character Set=UTF8"

$svr = new-Object Microsoft.AnalysisServices.Tabular.Server
$svr.Connect("server1.domain.com")

$svr.databases[1].model.datasources[0].ConnectionString = $newConnectionString
$svr.Databases[1].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)

So - based on the findings from @thom schumacher from your other question (located here)

$Analysis_Server = New-Object Microsoft.AnalysisServices.Server
$Analysis_Server.connect("AX2012R2A")

#Getting current state
PS C:UsersAdministrator> $Analysis_Server.Databases | ForEach-Object ForEach-Object $_.ConnectionSt
ring
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_4
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_2
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_1
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_6
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_3
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_5

#Setting the new connection string in variable
$connectionString = "Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_40"

#Assigning the variable to one of the databases
$Analysis_Server.Databases[0].datasources[0].ConnectionString = $connectionString

#Remember to update the new configuration back to the database
$Analysis_Server.Databases[0].datasources[0].Update()

#Getting the new configuration
PS C:UsersAdministrator> $Analysis_Server.Databases | ForEach-Object ForEach-Object $_.ConnectionSt
ring
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_40
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_2
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_1
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_6
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_3
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_5

Please pay attention to the first entry in each list. It went from DatabaseName_4 to DatabaseName_40

That is how you only change the details of the connection string, using PowerShell and SMO.