SSAS Cube processing logs

Question

Where are SSAS cube processing (not error, not flight recorder) logs stored?

We have a SQL Agent Job running a SQL Server Analysis Services command. In there is some DMX which processes each dimension then processes the cube database (containing two cubes)

I want to know how long each of the various queries are taking. There is one query per dimension and one query per measure group

The cube was taking 20 minutes now it's taking 2 hours.

We are using SSAS 2008 R2

I have searched long and hard and as far as I can tell there is no such log.

questions which are not duplicates of this:

Error Log records in SSAS

get output of last Process on SSAS cube

I don't want to use Profiler. I want to see how long each query took in the last cube build at least. I can see all this info if I run interactively. How do I make it log this info when run from a job?

Answer 1

There are a couple of options. You could just continue processing the cube just the way you are but start logging all processing events. Other than the Profiler GUI, there are three main ways to do that:

1. A server side trace writes a .trc file to disk on the SSAS server with little overhead: http://blogs.msdn.com/b/karang/archive/2009/11/02/sql-2005-sql-2008-analysis-services-server-side-tracing.aspx Then you can load it into SQL server to analyze later via PowerShell: http://www.bp-msbi.com/2012/02/counting-number-of-queries-executed-in-ssas/

2. Install a community maintained service called ASTrace which uses the Profiler APIs (without the GUI) and writes the Profiler events you choose directly to SQL Server real-time. https://github.com/Microsoft/Analysis-Services/tree/master/AsTrace

3. Log XEvents and analyze them later: http://blog.crossjoin.co.uk/2012/05/05/using-xevents-in-ssas-2012/ Or: https://francescodechirico.wordpress.com/2012/08/03/identify-storage-engine-and-formula-engine-bottlenecks-with-new-ssas-xevents-5/

All of those options will log all processing transactions. You get to choose just which events you want to log (processing events not queries, for example).

But another alternative is to use a session trace. You could stop using the "SQL Server Analysis Services Command" step type in SQL Agent and start using a PowerShell step type and do something like the following. This example is running an SSAS backup from PowerShell with a SessionTrace to watch all the "profiler" events just for that one session: https://gallery.technet.microsoft.com/scriptcenter/Backup-Ssas-Databases-with-da62b084