SSAS Performance Logger is a tool that allows you to navigate through the metadata for a tabular model and select from measures and table attributes to build a simple query which is executed and timed. The query can be executed many times to get an accurate sampling of test variations and to test performance while various loads are placed on the server.
My initial objective was to choose two different measures that were alternate methods of calculating the same value, run them side-by-side and then see which performed best. Then it occurred to me that we can run any number of measure performance tests in iterative loops and compare the results by playing back the captured log results. Since the results are captured in a log database, the test results for each measure or query can easily be compared and analyzed using tools like Excel and Reporting Services.
One of my main objectives for a future version of the tool is to add logging for server performance counters like memory usage, thread counts and CPU capacity; while these queries are running.
What you will need:
I’ve developed SSAS Performance Logger for use with SSAS Tabular in SQL Server 2012. To manage the logging database, it uses the OLEDB data provider for SQL Server 2012 which may be backward compatible as far back as SQL Server 2008. It also uses the ADOOMD data provider version for Analysis Services. When MDX support is added to a future version, it should support SSAS sources as far back as SSAS 2008.
System requirements:
- Microsoft .NET Framework 4.5
- ADOMD 6.0 (can be installed with the SQL Server 2012 SP1 Feature Pack)
- An instance of SQL Server 2008 or better with administrative/create database rights for the logging database
With these components installed, you should be able to copy the executable to a folder and run it.
To install:
- Download the zip file and extract it to a folder on your system
- Verify that the required dependent components are installed
- Run SSAS Perf Tester.exe
The first time it runs, the application will check for the logging database and prompt you to create it
- On the SSAS Query page, enter the Analysis Services server or instance name and press Enter
- Select a model or perspective, measure and table/attribute from the drop-down list boxes to build the query
- Click the Start button to run the query once and see how long it took to run
- On the Options page, type or select the number of times to repeat the query
- Click the Start button the run the query in a loop. The results are displayed in a line chart showing the duration in milliseconds for each execution
Every query execution is logged for later analysis. Here is a view of the logItem table in the SSASPerformanceTest database:
Features I hope to add soon:
- Generate MDX queries in a addition to DAX (added in version 1.1)
- Handle multiple measures in one testing batch
- Log server performance counters
Download:
Version 1.2 June, 2014
Version 1.1 June, 2014
hi Paul, it appears that neither 1.15 nor 1.2 do not work scope out cube metadata well with non-instance named server (we have an SSAS Tabular clustered server that has no instance name). Looks like 1.0 is fine. Also the SQL Db, for some reason, cannot be created with these two later versions. If it might be of interest that we provide a testbed for the setup via webex, we could do so…. or provide the errors, etc… thx a bunch, Cos.
I don’t see the create database button. If I click on start, then I get a unhandled exception.
Mike, I’ve shored up exception handling and made a correction to the logging database create code. Please download version 1.2 and let me now if this fixes the issue.
I see the button now but when I click on create database button I get the following error:
—————————
Bad things happened
—————————
GetLogStats exception
System.Data.SqlClient.SqlException (0x80131904): Invalid object name ‘LogItem’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at SSAS_Perf_Tester.frmPerformanceTester.GetLogStats()
ClientConnectionId:630f8b98-17d3-4af3-a5de-74ddb32c670e
—————————
OK
—————————