none
Automate Processing of Cubes

    Question

  • Hi!

    I am looking for ways to automate the processing of my dimensions/cubes. After some research on the web, it seems to me that I have the following possibilities:

    a) use SSIS

    b) use an SQL Server Agent job

    c) use the ascmd command line utility

    d) use AMO in a custom application

    However, to my surprise, it seems there is no way to accomplish this through a stored procedure, either by calling some system SP, or by issueing an XMLA statement.

    My questions are:

    - Are there any other means of automatically processing SSAS objects than the ones mentioned before?

    - Is it really not possible to simply launch an XMLA statement from an SP?

    - Given that SSIS is way too much complexity for my needs, and ascmd and AMO force me to leave the database environment and install files/applications (deployment, updates!), do I have another chance than using an SQL Server Agent job?

    Many thanks,

    iurtz.

    Wednesday, June 23, 2010 9:13 AM

Answers

All replies

  • You could use a stored procedure to invoke the sql agent job which sends the XMLA command.  In that way you won't have to create the SSIS package or develop custom AMO code;

    sp_start_job  will trigger the execution of the sql agent job


    Javier Guillen
    • Proposed as answer by Milli_22 Wednesday, June 23, 2010 10:38 AM
    • Marked as answer by Jerry NeeModerator Friday, July 9, 2010 1:29 AM
    Wednesday, June 23, 2010 10:20 AM
  • Javier,

    thanks for the hint, but once I have a Server Agent Job I can just as well schedule it to run without using an SP. My point in trying to launch cube processing via a stored procedure is to eliminate the need for a Server Agent Job that contains complex logic.

    I would like to use as few different techniques to accomplish my needs as possible in order to keep the product simple to overlook/configure/deploy. There are lots of SPs already, so adding another one does not add the least bit of complexity. A Server Agent Job (not to mention SSIS) does.

    Any more ideas on that topic anyone?

    iurtz.

    Tuesday, June 29, 2010 11:45 AM
  • Iurtz,  an approach that would perhaps include less moving parts is creating a managed stored procedure (in sql server) that would send the XMLA request. Charles Wang recently posted about it:  http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/782d5b7d-a4e2-4e7e-b6dd-cb6233a720f1

     


    Javier Guillen
    Tuesday, June 29, 2010 12:12 PM