July 27, 2011 at 7:52 am
Hi all,
Firstly, I'm not sure if this is the correct forum for this post, if not, accept my apologies and could you please suggest a more appropriate forum?
If it is -
I’m being asked to write a report in T-SQL which will return information on our nightly ETL and cube process routines.
For the SQL portions of the job (data fetches/ETL procedures) I can get to the information that I need easily (using the logs in our BI framework), however I do not know how to get data on Analysis Services actions, such as (and most importantly) the process of a cube, etc.
I need users to be able to see when a cube was last processed and what the status of that process was (success, any errors, etc).
So to succinctly summarise my question -
“How, using only T-SQL, can I determine when a given Cube was last processed, and what the outcome of that process was?”
I’ve done a bit of research on this, and have discovered the following:
1.Information on Analysis Services logging is pretty sparse!
2.Some text from the webpage on the A.S. server log properties (URL below) states of the “File” property - “..A string property that identifies the name of the server log file. This property only applies when a disk file is used for logging, as opposed to a database table (the default behavior). The default value for this property is msmdsrv.log."
Source - (http://msdn.microsoft.com/en-us/library/ms174827.aspx)
This sounds very hopefully as it says that the default behaviour of A.S. logging is a database table, but doesn’t give it’s name, nor can I find any other reference to this! Does anyone have information on this database table which A.S. logs to? If so, this could be the holy grail of what I’m after (A.S. log info. easily accessible via T-SQL).
3.There are a series of A.S. DMV views which can be accessed via SQL (similar to SQL’s DMV views, good article here: http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/), however these don’t appear to contain information on when a cube was last processed, mainly they deal with cube metadata and metrics which could aid performance and usage analysis. If I am wrong on there not being admin activity information in these, please correct me.
4.Finally it is possible to configure the logging of queries given to A.S. by users to a SQL table, this enables statistics to be gained on how users are using cubes. However I am not interested in cube usage, only when cube admin tasks (i.e. when a cube was processed) occurred. So this is of no interest to me.
Any help on this greatly appreciated.
Cheers,
Ian
Environment:
SQL Server 2008 R2 Enterprise
Windows server 2008 R2 Enterprise
August 1, 2011 at 3:17 am
MDX queries will help you on this.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply