Process OLAP Database with SQL Agent Job

Author: Joyce Zhou

Introduction

The MODELER allows you to process application objects including databases, cubes, partitions, and dimensions through the UI when needed. If a process task needs to be scheduled, we can use a SQL Agent Job.

Example - Process Partition

Step 1. Open SQL Server Management Studio and connect to the Analysis Server. Find the object that you need to process. Here we’ll process the Actual partition within the Cap Asset-Reporting cube as an example. Right click on the partition and select Process.

Step 2. In the pop-up window, select from the Process Options drop-down as needed.

Step 3. Click Script and select Script Actions to Clipboard.

Step 4. Go to the SQL Agent Job you are working on, add a New Job Step, select SQL Server Analysis Services Command as the Type, enter the server name, paste the code we copied from last step into the Command field, and then click OK to save.

Example - Process Database

You can follow the same steps to process other objects through SQL Agent Jobs as well:

Here is the code. You just need to change [DATABASE_ID] to your application name.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
    <KeyErrorLimit>-1</KeyErrorLimit>
    <KeyDuplicate>ReportAndContinue</KeyDuplicate>
    <NullKeyConvertedToUnknown>ReportAndContinue</NullKeyConvertedToUnknown>
  </ErrorConfiguration>
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <Object>
        <DatabaseID>[DATABASE_ID]</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>