Powershell For Loop for multiple servers - to get SSAS connection string details

Question

I am very new to powershell script. i am trying to get SSAS Tabular model connection string details for multiple servers. i have code which will return only for single server. How to modify the code to pass multiple servers?

$servername = "servername1"  
# Connect SSAS Server
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($servername)
$DSTable = @();
foreach ( $db in $server.databases) 
{
    $dbname = $db.Name
    $Srver =  $db.ParentServer   
    foreach ( $ds in $db.Model.DataSources)
    {
        $hash = @
        {
            "Server" = $Srver;
            "Model_Name" =  $dbname ;
            "Datasource_Name" =  $ds.Name ;
            "ConnectionString" =  $ds.ConnectionString ;  
            "ImpersonationMode" = $ds.ImpersonationMode;
             "Impersonation_Account" = $ds.Account;
        }
        $row = New-Object psobject -Property $hash
        $DSTable += $row
    }
}

Answer 1

As commented, you can surround the code you have in another foreach loop.
Using array concatenation with += is a bad idea, because on each addition, the entire array needs to be recreated in memory, so that is both time and memory consuming.

Best thing is to let PowerShell do the heavy lifting of collecting the data:

$allServers = 'server01','server02','server03'  # etc. an array of servernames
# loop through the servers array and collect the utput in variable $result
$result = foreach($servername in $allServers) { 
    # Connect SSAS Server
    $server = New-Object Microsoft.AnalysisServices.Server
    $server.Connect($servername)
    foreach ( $db in $server.databases) {
        foreach ( $ds in $db.Model.DataSources) {
            # output an object with the desired properties
            [PsCustomObject]@{
                Server                = $db.ParentServer
                Model_Name            = $db.Name
                Datasource_Name       = $ds.Name
                ConnectionString      = $ds.ConnectionString
                ImpersonationMode     = $ds.ImpersonationMode
                Impersonation_Account = $ds.Account
            }
        }
    }
}

# output on screen
$result | Out-GridView -Title 'SSAS connection string details'

# output to a CSV file  (change the path and filename here of course..)
$result | Export-Csv -Path 'D:\Test\MySSAS_Connections.csv' -UseCulture -NoTypeInformation

^{The above uses parameter -UseCulture because then the delimiter used for the CSV file is the same as your machine expects when double-clicking and opening in Excel. Without that, the default comma is used}