Home > Analysis Services, SQL Agent, SQL Server, Tips > SQL Agent and SSAS Commands

SQL Agent and SSAS Commands

SQL Agent can be used to execute SSAS commands such as processing of a dimension and i can see that it is useful when you want to quickly schedule an adhoc processing task but there is a major drawback you need to be aware of.

You are not able to specify the database to connect to and whilst you may think that it does not matter because the database is specified in the connection but it really does matter. Below are extracts of a processing task from profiler which was executed using a SQLAgent task.

image

So, what is happening here?

    1. The processing command starts and a processing schedule is built but no database name is specified because its missing from the connection string.
    2. The XMLA command specified the ResourceDB was to be processed so SSAS now switches to this database and starts the processing steps.
    3. This is the real gotcha. At point of commit because no database was specified SSAS switches to the default database. If there is processing occurring on the default database processing on the ResourceDB is blocked and you might also find yourself suffering at the hands of a server lock. Depending on your ForceCommit Timeout and Commit Timeout settings this processing could terminate the other processing, rollback or worse if your really unlucky.

So, what is the SSAS default database? There are 2 ways to look at it. It is either the first database created on the instance or the oldest database on the instance (should you have deleted the first). Some of you may have noticed the commit subclass and thought you had not seen it before. It was first introduced in SSAS 2008 R2 and prior to R2 you had to a) Know there was such a thing as commit and b) Calculate it by taking the difference between the batch completed event and the one immediately before it.

In my opinion SQL Agent Analysis Services command is not a viable option for scheduling processing whilst this issue exists and i have raised a connect item for it.

In the mean time i have put together a very simple SSIS package which you can schedule via SQL Agent and you can specify the database name & commit timeout (so you can avoid impacting other processing). You also specify the path to the xmla file you want to run. The SSIS package and T-SQL to create the agent job is here, all you have to do is update the set values.

There are many other ways to run adhoc processing including ascmd and i hope you find this method quick & effective.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: