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 dknight@pragmaticworks.com.