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