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.

Click ok. It will create the job

                               x.     It will create the job named “OLAP_Cube_Automation_Processing”. Now right click on the job and click on Start Job at step….

                             xi.    It will start the Job processing:

                           xii.  Once Job processing done. Go to the SQL Analysis instance and right click on your Cube and browse it.

3.     Conclusion

          By using above steps, we can automate the cube processing using SQL Agent Job.