How to process OLAP Cube using SQL Server Agent Job

1.     Background

The purpose of this document is to describe a way of processing OLAP cube using SQL Server Agent job. This method uses SQL Server Analysis command with XMLA script to process the Cube. It is a way where we can automate the Cube processing without SSIS package development & deployment.

2.     Steps to create a Job for processing Cube:

                                i.     Connect to SQL Server Analysis Service instance where your cube resides that need to be processed.

                              ii.    Right click on the Cube that need to process then Process:

                            iii.   It will open the Process Cube Window:

                             iv.    Click on Script down arrow button and select “Script Action to Clipboard”:

                               v.    Once scripting done successfully, Below will be the progress status:

Click Cancel

                             vi.    Connect SQL Server Database Engine instance. Go to SQL Server Agent then Job folder.

 

                           vii.  Right Click on the Jobs folder and click New Job:

                         viii.  It will open wizard to create job. Give the name of the Job:

 

                             ix. Click on steps then New. Provide the below details for the Job steps:

·         Step Name      : Cube Processing

·         Type                : SQL Server Analysis Services Command

·         Run as             : SQL Agent Service Account or proxy account

·         Server              : Name of the Server where Cube resides

·         Command       : Paste the code from Clipboard using paste button.