THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Automate MDX Query Testing

In one my recent MDX classes, a student asked about automation of MDX queries for the purposes of testing query performance, so today I’ll answer that question and draw your attention to a few resources available.

Get the ASCMD Utility

If you’re running SQL Server 2008 Analysis Services or SQL Server 2008 R2 Analysis Services, you can download  the ASCMD_StressTestingScripts from the Analysis Services Samples page on Codeplex to get the main prize, the compiled ASCMD executable. (This version should also work with SQL Server 2005 Analysis Services, but I haven’t tested it.)

This utility allows you to execute MDX or DMX queries or XMLA commands from the command line. This capability means that you can build out some complex batch files to automate your MDX query testing, along with other repetitive tasks such as database processing. You can view the 2005 version of the ReadMe file to see the syntax as well as various ways that you might you use this utility (if you scroll all the way to the bottom of the page).

The old ReadMe file doesn’t include all of the arguments that are in the latest version of ASCMD. You can download a more current ReadMe document that’s buried deep in the bowels of Codeplex to get more information.

Try a Simple Command

In theory, the stress testing scripts let you execute ASCMD as a single client with single query or multiple queries, or as multiple clients with single query, or as multiple clients with multiple queries.  I wasn’t able to get that piece working as a file seems to be missing from the download, but you can create your own batch files to do similar things. You can get the basic idea by reviewing the RunASCMDParallelStressTest.cmd and RunASCMDSerialStressTest.cmd files that come in the download.

If you don’t want to wade through all that batch file stuff to figure out how to make ASCMD work, let me cut to the chase. You can execute asmcd /? to see the parameters available. For running an MDX query, assuming that you want to capture trace information to see the query duration and key trace events like QuerySubcubeVerbose and GetDataFromCache, you use the following syntax:

 ascmd –S <server\instance> -d <database name> -I <mdx file> -o Output.xml -T <CSV file>

So, for example, to run on my local instance, using a query file found in the Queries subfolder of the download, I would run the following command:

ascmd -S localhost -d "Adventure Works DW 2008R2" -i Queries\Query1-3.mdx -o Output.xml -T Trace.csv

The Output.xml will contain the query results and a lot of metadata about the cube that you can probably ignore if your goal is to get performance testing data. The Trace file is a pipe-delimited CSV file that contains the same type of trace data that you get when you run a SQL Server Profiler trace for Analysis Services. The trace file gets overwritten on each execution, except as noted below.

You can adjust the level of detail in your trace file by adding the –Tl argument with one of the following values

  • High (default) – captures everything.
  • Medium – captures everything except ProgressReportCurrent and Notification events.
  • Low – captures only events with “End” or “Error”.
  • Duration – captures only execution duration and writes one line in the trace file with current time, duration, execution text, database, and server name. If you use this argument, and execute ASCMD multiple times, each execution appends new data to the file.
  • Duration-result – captures the same results as when you use “duration” but includes an addition column to store the result of the execution. Each execution appends new data to the file.

Batch It

The ASCMD utility can run one file at a time, but you can put multiple queries into the file using a GO command between the queries. Or you can set up a batch process with a loop to call ASCMD multiple times.

You can leave out the –o argument but you’ll get the output stream on your screen, unless of course you put this into a batch file and use echo off and echo on after executing the ASCMD.

For performance testing, you should also include some additional steps before you run the MDX query to get true baseline performance measurements. Put these steps into an MDX or XMLA script file and use ASCMD to execute them in your batch file before running the MDX query that you’re testing:

  • Clear the Analysis Services cache
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ClearCache>
    <Object>
      <DatabaseID>Adventure Works DW 2008R2</DatabaseID>
    </Object>
  </ClearCache>
 </Batch>
SELECT {} ON 0 FROM [Adventure Works]

Learn More About Query Performance Analysis

Now that you’ve captured trace event information, what do you do with it? Here are some resources to help you determine what it means:

Published Wednesday, February 22, 2012 9:25 PM by smisner

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Boyan Penev said:

Nice post, Stacia. You can also make the cache clearing easier by using ASSP - with the ClearAllCaches() method - it clears the filesystem and standby caches together with all SSAS caches.

February 23, 2012 12:04 AM
 

smisner said:

Thanks, Boyan - the astute reader will notice that point when they check out the link for clearing the file system cache. I just have old habits that die hard, and will admit I don't always clear the file system cache depending on what I'm up to, so I didn't think to point it out. Anyway, you're right - if someone is going to register an ASSP assembly, they can do the first two steps in one call to ClearAllCaches().

February 23, 2012 12:41 AM
 

Davide Mauri said:

As additional tool for testing, I'm using BI.Quality http://biquality.codeplex.com/, that allows  also to verify that MDX results are the one we expect to have. Nice tool.

February 23, 2012 2:28 AM
 

SharePoint Experts London said:

your post is very interesting. I like it  very much. It is very helpful and useful for me. Thanks for share this valuable post.

February 23, 2012 4:25 AM
 

Jason Thomas said:

Great post. I like the AS Performance Workbench also (http://asperfwb.codeplex.com/) when load testing my mdx queries.

February 23, 2012 7:19 AM
 

smisner said:

Thanks, Davide and Jason - I was hoping to spark a conversation about other tools, and I succeeded! I appreciate the time you took to read this post ad your suggestions for other tools.

SharePoint Experts London - I'm glad you found the post useful.

February 23, 2012 1:23 PM
 

Suhas said:

Nice Post Good informations on ASCMD Utility.

February 23, 2012 11:32 PM
 

smisner said:

Thanks,  Suhas!

February 23, 2012 11:48 PM
 

Thirumurugan said:

How to test Saiku output (OLAP Data) with Source Database for Data validation

October 12, 2012 7:47 AM
 

smisner said:

Normally I run SQL queries against the source and validate against the corresponding results in the OLAP data.

October 28, 2012 8:48 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement