SSAS Cache Warming with an SSIS package

, 2010-07-01

There are several techniques that can be used for performance tuning Analysis Services.  You may already be familiar with the typical methods used like Partitions and Aggregations.  However, an often forgotten method for tuning query performance is cache warming.  This is the very basic idea of running your frequently used queries ahead of time so that after reprocessing your cube your users will not be querying from a “cold cache”.  The queries that your users will run throughout the day will already have been run once.

There are several blogs post available that walk you through the steps of creating an SSIS package to “warm cache”.   Chris Webb wrote an excellent blog several years ago describing his technique for building a cache warming SSIS package.  The reason I like his method is it is basic and easy to understand.

To replicate this solution you will first want to create a new database that will store the results of a profiler trace that watches you user queries that they are running.  They likely don’t even realize it but every time they use the cube even in Excel the MDX queries that are running behind the scenes will be captured by the profiler trace.  You will want to run a profiler trace while telling your users to run typical queries they would use daily.  Take the results of this profiler trace and save it to a table in the new database you just created.  Your user habits are likely to change so ensure to do this again at least once a quarter but more frequently if you know their habits have changed sooner.

Chris’ blog explains recreating the package well but if you have trouble duplicating it yourself you can download my recreation of it here.  The package looks more complicated than it really is but here’s what each step does.

Foreach Loop  (Loop through each table in Cache Warmer DB) – Uses an SMO enumerator to loop through the cache warming database and return back each table name individually.

Script Task (Generate SELECT statement) – This generates a select statement and places it in a variable.  The variable is used in the next task.

Execute SQL Task (Get list of MDX Queries) – Uses the variable created from the previous task and runs it as an SQL statement.  The SQL statement generates a list of all the MDX statements that need to be run and places it in an object variable called MDXQueryObject.

Foreach Loop (Run individual MDX queries) – Gathers each MDX query from the object variable and separates them into individual queries to run.

Script Task (Parse MDX as String) - Parses each MDX query as string.

Execute SQL Task (Run MDX against server) – Actually runs MDX against SSAS database

**Optional** Script Task (Msgbox MDX) – Used for testing purposes.  Is disable by default.  This task prints a message box with the MDX statement when connected and enabled.

If you have any issues with this yourself feel free to email me at





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads