How To Execute XMLA Against SSAS In PowerShell

How To Execute XMLA Against SSAS In PowerShell

Having fun preparing that PowerShell deployment script and need to run some XMLA with it? Here’s how.

The function below will allow you to run one or more (if you pipe them) XMLA commands against an SSAS instance. To use it, if you first need to tuck it somewhere at the beginning of your script, so you can call it afterwards:

#################################################################################
# Execute-Xmla runs the given XMLA string against the target SSAS server
#################################################################################
function Execute-Xmla
{
    [CmdletBinding()]
    param
    (
        [Parameter(Position=0, Mandatory=$True)] [string] $TargetServerName,
        [Parameter(Position=1, Mandatory=$True, ValueFromPipeline=$True)] [string] $Xmla
    )
    begin
    {
        Add-Type -AssemblyName "Microsoft.AnalysisServices.AdomdClient, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
        $ConnectionectionString = "Data Source=$TargetServerName;Provider=MSOLAP.4;Integrated Security=SSPI;Impersonation Level=Impersonate;"
        $Connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection($ConnectionectionString)
        $Connection.Open();
    }
    process
    {
        $Comand = $Connection.CreateCommand();
        $Comand.CommandTimeout = 20000;
        $Comand.CommandType = [System.Data.CommandType]::Text;
        $Comand.CommandText = $Xmla;
        $Reader = $Comand.ExecuteXmlReader();
        if($Reader.Read())
        {
            $Return = $Reader.ReadOuterXml();
        }
        return $Return;
    }
    end
    {
        $Connection.Close();
        $Connection.Dispose();
    }
};

Now, because this is a properly piped and parametrized function, you have a number of ways you can integrate it in your main script.

You can use positional parameters to start with:

Execute-Xmla $SomeTargetServerName $SomeXmlaToExecute

Or you can use named parameters, if it feels more comfy:

Execute-Xmla -TargetServerName $SomeTargetServerName -Xmla $SomeXmlaToExecute

Or, you can pipe some content into it:

Get-Content some_file_with_xmla_in_it.xmla | Execute-Xmla -TargetServerName $SomeTargetServerName
Jorge Candeias's Picture

About Jorge Candeias

Jorge helps organizations build high-performing solutions on the Microsoft tech stack.

London, United Kingdom https://jorgecandeias.github.io