Teaser: SSAS Cache Warming with PowerShell

You may be thinking oh-no, not another one. Allan Mitchell recently posted an example of an SSIS package that would warm the SSAS cache which is an updated version with a slightly simpler SSIS data flow from something Chris Webb originally blogged. Being a PowerShell fan as I read Allan's post I realised that most of the tasks mapped to native PowerShell cmdlets and I already had PowerShell code to execute an MDX command, so all I was missing was someway of reading in the trace data.

What I ended up with was the following 3 line script which uses 2 native and 2 custom cmdlets to do the same thing as Allan's SSIS package.

add-PSSnapin powerSSAS
add-PSSnapin powerTrace

get-SqlTraceEvent "c:\data\cacheWarmTest.trc" `
    |Where-Object {$_.EventSubclass -eq 0 `
-and $_.EventClass -eq "Query End" `
-and $_.TextData.Trim().Length -gt 0 } ` |Sort-Object TextData -unique ` | % {Invoke-AsMdx localhost $_.TextData > null}

The first two lines are just plumbing code that load the snapins (dlls) that contain the get-SqlTrace and Invoke-AsMdx cmdlets. The following is an outline of how the script works:

  • The get-SqlTraceEvent cmdlet will read a .trc file and return a collection of custom objects that has a property for each of the columns that were capture in the trace.
  • The call to Where-Object does the same thing as the conditional split in the SSIS package and filters out any events that are not MDX queries. (you can also setup your trace to only capture MDX commands)
  • The Sort-Object call is the same as the sort task in the SSIS and extracts only unique queries (based on an exact string match).
  • Finally the invoke-AsMdx cmdlet executes the queries. I am redirecting the output to null as I am not really interested in actually doing anything with the results.

So, if this has all been done before why would I bother doing it again? - Because PowerShell scripts are so easy to work with. What happens if your trace file contains a lot of small, fast queries that you do not want to bother running? With the script you can easily edit it to add another condition to only process queries that took more than 100 milliseconds.

get-SqlTraceEvent "c:\data\cacheWarmTest.trc" `
|Where-Object {$_.EventSubclass -eq 0
              -and $_.EventClass -eq "Query End"
              -and $_.TextData.Trim().Length -gt 0
} `
              -and $_.Duration -gt 100 } `
    |Sort-Object TextData -unique `
 | % {Invoke-AsMdx localhost $_.TextData > null}

And that is just the tip of the proverbial iceberg, you could also do things like loop over a series of .trc files or you could possibly get more sophisticated in your analysis and possibly look for queries that actually had been hitting the cache.

You may be wondering why I have called this post a teaser, well that's because the bits for the key parts of it are only on my laptop at the moment. I currently have the tracing in a separate snapin because it has a dependency on the SMO library and I did not want to add that dependency to powerSSAS. Then there is the fact that this should work with .trc files from the relational engine too, so it does not seem to fit neatly with powerSSAS.

I also have a snapin for PerformancePoint Monitoring and some SSIS cmdlets, so another thought I had was to bundle all these projects and release some sort of BI PowerShell pack. I'm not sure which direction to take, I would be interested to hear if anyone has any opinions on this.


Technorati Tags: ,

Print | posted on Sunday, November 16, 2008 11:41 PM

Comments on this post

# re: Teaser: SSAS Cache Warming with PowerShell

Requesting Gravatar...
First of all, thanks for your job.

I think that the approach of have the tracing in a separate snapin is good, because there is not exclusive of AS.

I'm impatient to view the snapin for PerformancePoint Monitoring.

Can you add the posibility to read files from the perform monitor?
Left by Alvaro Mosquera on Nov 18, 2008 10:43 PM

# re: Teaser: SSAS Cache Warming with PowerShell

Requesting Gravatar...
I'm not sure what you mean by "add the possibility to read files". The provider lets you connect to a Monitoring server and view all the metadata such as the available scorecards, reports, indicators, kpis etc.
Left by Darren Gosbell on Nov 18, 2008 10:58 PM

# re: Teaser: SSAS Cache Warming with PowerShell

Requesting Gravatar...
I was thinking in read the logs created by performance monitor.
Left by Alvaro Mosquera on Nov 19, 2008 3:54 AM

# re: Teaser: SSAS Cache Warming with PowerShell

Requesting Gravatar...
Sorry, that is a different type of monitoring. However you might be able to look at relog.exe, I think it come standard with windows and lets you convert blg files to csv. From there it might not be too hard to read with Powershell. Typing "relog.exe -?" at the commandline will display the help.
Left by Darren Gosbell on Nov 19, 2008 7:24 AM

# re: Teaser: SSAS Cache Warming with PowerShell

Requesting Gravatar...
Here are other articles on Cache Warming: http://ssas-wiki.com/w/Articles#Cache_Warming
Left by Sam Kane on Feb 24, 2011 2:29 AM

Your comment:

 (will show your gravatar)