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

Index: PowerShell HowTo’s


Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s