Saturday, August 10, 2013

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.