Load Testing SSAS with PowerShell
Over the years, there has been much confusion as to the best way to load test Analysis Services. I have seen solutions ranging from as simple as using ascmd to completely custom console apps in Visual Studio. One of the challenging aspects of Business Intelligence solutions has been unit testing in the past, and I believe that one of the major barriers to adoption has been the complexity required to set things up. Projects always have very tight deadlines, and who has the time, I hear a lot. Fortunately, with Powershell and the Invoke-ASCmd cmdlet this becomes a very direct exercise and removes any barriers. This solution can be used either against Multidimensional or Tabular databases, with either MDX, DAX, or DMX queries.
The Invoke-ASCMD cmdlet is a part of the sqlps module for Powershell. If you do not already have this imported into your environment, the first step would be to bring this in so you can properly run script against Analysis Services.
## Import the SQL Server Module.
Import-Module “sqlps” -DisableNameChecking
Once this is done, we can then start running queries by using the Invoke-ASCmd cmdlet within PowerShell. This cmdlet allows us execute MDX, XMLA, DAX, DMX, or (with tabular 2016 only) TMSL scripts against an Analysis Server remotely. A very straightforward run of the cmdlet would look something like the below:
#Issue a query command against SSAS and output result to disk
Invoke-ASCmd -Server localhost\sql2012tabular -Database "AdventureWorks Tabular Model SQL 2012" -Query "SELECT { [Measures].[Number of Orders], [Measures].[Sum of SalesAmount] } ON COLUMNS ,
NON EMPTY { [Date].[Calendar].[Year] } ON ROWS
FROM [Model]"
Where the -Server parameter denotes the server you want to run against, -Database is the database you want to hit, and -Query is the query you want to run. This is very handy but can be extended by changing the -Query parameter to an Input File parameter. This allows us to write and manage our queries separately. Additionally, instead of 1 query having them separated into a new InputFile gives the ability to run multiple queries via the use of the GO operator between queries. Looking at the same query above but with the InputFile parameter we get the below syntax:
Invoke-ASCmd -Server localhost\sql2012tabular -Database "AdventureWorks Tabular Model SQL 2012" -InputFile "C:\ASPowershellFolder\InputQueries\Query1.mdx"
And the queries that live in the Query1.mdx file are as follows:
SELECT NON EMPTY { [Measures].[Sum of SalesAmount], [Measures].[Number of Orders] } ON COLUMNS
, NON EMPTY { ([Customer].[Occupation].[Occupation].ALLMEMBERS ) } ON ROWS
FROM [Model]
GO
SELECT NON EMPTY { [Measures].[Sum of SalesAmount] } ON COLUMNS
, NON EMPTY { ([Date].[Calendar].[Month].ALLMEMBERS ) } ON ROWS
FROM [Model]
GO
Ok, we now have a simple elegant way to run either a single or multiple query(ies) against SSAS through PowerShell. We can now extend our solution via the use of PowerShell workflows. Workflows are a way in which PowerShell can do the same thing multiple times and retry upon failure, as well as creating a queue that stacks up. Without the use of the workflow, if we executed the above script with 1000 concurrent queries, all 1000 would fire off, some would be successful and the rest would fail after we hit our limit. Workflows take the 1000 queries and send them in via managed requests up to the max point the server can handle. I found the below code from Jamie Thomson over on sqlblog, which is very close to what we are looking for. By changing the function that he created to call a URI to our earlier Invoke-ASCmd, we can kick off a specified number of requests in parallel against our database! The code to create the workflow is below:
#used the workflow code from Jamie Thomson's parallel workflows in Powershell tip at https://sqlblog.com/blogs/jamie\_thomson/archive/2014/12/09/parallel-foreach-loops-one-reason-to-use-powershell-workflow-instead-of-ssis.aspx
#given an specified input file path and the database that it should be run under, runs a simulated load test against an SSAS server with the specified number of
#used the workflow code from Jamie Thomson's parallel workflows in Powershell tip at https://sqlblog.com/blogs/jamie\_thomson/archive/2014/12/09/parallel-foreach-loops-one-reason-to-use-powershell-workflow-instead-of-ssis.aspx
#given an specified input file path and the database that it should be run under, runs a simulated load test against an SSAS server with the specified number of
workflow asloadtest{
Param($NumberofConnections)
$array = 1..$NumberofConnections
function ASLoadTest($i){
Invoke-ASCmd -Server localhost\sql2012tabular -Database "AdventureWorks Tabular Model SQL 2012" -InputFile "C:\ASPowershellFolder\InputQueries\Query1.mdx"
;
}
foreach -parallel ($i in $array) {ASLoadTest $i}
}
cls
asloadtest 5
Now the only thing left for us to do is to call the workflow to execute, and pass in the number of connections that we want to execute in parallel. This is a very straightforward command:
asloadtest 5
Where asloadtest is the name of the workflow and the 5 is the number of connections parameter. That’s it! With 20 lines of PowerShell code we have a scalable SSAS load testing harness. What’s really cool is that we can easily change the Invoke-ASCmd to Invoke-SQLCmd and reuse this exact same approach for load testing the database engine as well. How cool is that!? Now that we have the hard part done the next step is to configure analysis services to capture the performance monitor metrics that we want to see. Over on his blog, Bill Anton has done an excellent job detailing what perfmon metrics you should be capturing for SSAS and why they are all important (as well as a super helpful summary list at the bottom).
The complete PowerShell scripts are available here on the TechNet gallery for download.