Schedule SSAS Administrative Tasks with SQL Server Agent

Article
12/09/2022

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Using the SQL Server Agent service, you can schedule SQL Server Analysis Services administrative tasks to run in the order and times that you need. Scheduled tasks help you automate processes that run on regular or predictable cycles. You can schedule administrative tasks, such as cube processing, to run during times of slow business activity. You can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. For example, you can process a cube and then perform a backup of the cube.

Job steps give you control over flow of execution. If one job fails, you can configure SQL Server Agent to continue to run the remaining tasks or to stop execution. You can also configure SQL Server Agent to send notifications about the success or failure of job execution.

This topic is a walkthrough that shows two ways of using SQL Server Agent to run XMLA script. The first example demonstrates how to schedule processing of a single dimension. Example two shows how to combine processing tasks into a single script that runs on a schedule. To complete this walkthrough, you will need to meet the following prerequisites.

Prerequisites

SQL Server Agent service must be installed.

By default, jobs run under the service account. The default account for SQL Server Agent is NT Service\SQLAgent$<instancename>. To perform a backup or processing task, this account must be a system administrator on the Analysis Services instance. For more information, see Grant server admin rights to an Analysis Services instance.

You should also have a test database to work with. You can deploy the AdventureWorks multidimensional sample database or a project from the Analysis Services multidimensional tutorial to use in this walkthrough. For more information, see Install Sample Data and Projects for the Analysis Services Multidimensional Modeling Tutorial.

Example 1: Processing a dimension in a scheduled task

This example demonstrates how to create and schedule a job that processes a dimension.

An SQL Server Analysis Services scheduled task is an XMLA script that is embedded into a SQL Server Agent job. This job is scheduled to run at desired times and frequency. Because the SQL Server Agent is part of SQL Server, you work with both the Database Engine and SQL Server Analysis Services to create and schedule an administrative task.

Create a script for processing a dimension in a SQL Server Agent job

In SQL Server Management Studio, connect to SQL Server Analysis Services. Open a database folder and find a dimension. Right-click the dimension and select Process.
In the Process Dimension dialog box, in the Process Options column under Object list, verify that the option for this column is Process Full. If it is not, under Process Options, click the option, and then select Process Full from the drop-down list.
Click Script.

This step opens an XML Query window that contains the XMLA script that processes the dimension.
In the Process Dimension dialog box, click Cancel to close the dialog box.

In the XMLA Query window, highlight the XMLA script, right-click the highlighted script, and select Copy.

This step copies the XMLA script to the Windows Clipboard. You can leave the XMLA script in the Clipboard or paste it into Notepad or another text editor. The following is an example of the XMLA script.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
 <Parallel>  
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
    <Object>  
      <DatabaseID>Adventure Works DW Multidimensional</DatabaseID>  
      <DimensionID>Dim Account</DimensionID>  
    </Object>  
    <Type>ProcessFull</Type>  
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
  </Process>  
 </Parallel>  
</Batch>

Create and schedule the dimension processing job

Connect to an instance of the Database Engine and then open Object Explorer.
Expand SQL Server Agent.
Right-click Jobs and select New Job.
In the New Job dialog box, enter a job name in Name.
Under Select a page, select Steps, and then click New.
In the New Job Step dialog box, enter a step name in Step Name.
In Server, type localhost for a default instance of SQL Server Analysis Services and localhost\<instance name> for a named instance.

If you will be running the job from a remote computer, use the server name and instance name where the job will run. Use the format <server name> for a default instance, and <server name>\<instance name> for a named instance.
In Type, select SQL Server Analysis Services Command.
In Command, right-click and select Paste. The XMLA script that you generated in the previous step should appear in the command window.
Click OK.
Under Select a page, click Schedules, and then click New.
In the New Job Schedule dialog box, enter a schedule name in Name, and then click OK.

This step creates a schedule for Sunday at 12:00 AM. The next step shows you how to manually execute the job. You can also specify a schedule that executes the job when you are monitoring it.
In the New Job dialog box, click OK.
In Object Explorer, expand Jobs, right-click the job you created, and then select Start Job at Step.

Because the job has only one step, the job executes immediately. If the job contains more than one step, you can select the step at which the job should start.
When the job finishes, click Close.

Example 2: Batch processing a dimension and a partition in a scheduled task

The procedures in this example demonstrate how to create and schedule a job that batch processes an SQL Server Analysis Services database dimension, and at the same time to process a cube partition that depends on the dimension for aggregation. For more information about batch processing of SQL Server Analysis Services objects, see Batch Processing (Analysis Services).

Create a script for batch processing a dimension and partition in a SQL Server Agent job

Using the same database, expand Dimensions, right-click the Customer dimension, and select Process.
In the Process Dimension dialog box, in Process Options column under Object list, verify that the option for this column is Process Full.
Click Script.

This step opens an XML Query window that contains the XMLA script that processes the dimension.
In the Process Dimension dialog box, click Cancel to close the dialog box.
Expand Cubes, expand Adventure Works, expand Measure Groups, expand Internet Sales, expand Partitions, right-click the last partition in the list, and then select Process.
In the Process Partition dialog box, in the Process Options column under Object list, verify that the option for this column is Process Full.
Click Script.

This step opens a second XML Query window that contains the XMLA script that processes the partition.
In the Process Partition dialog box, click Cancel to close the editor.

At this point you must merge the two scripts, and ensure that the dimension is processed first.

Warning

If the partition is processed first, the subsequent dimension processing causes the partition to become unprocessed. The partition would then require a second processing to reach a processed state.

In the XMLA Query window that contains the XMLA script that processes the partition, highlight the code inside the Batch and Parallel tags, right-click the highlighted script, and select Copy.

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
    <Object>  
      <DatabaseID> Adventure Works DW Multidimensional</DatabaseID>  
      <CubeID>Adventure Works</CubeID>  
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>  
      <PartitionID> Internet_Sales_2004</PartitionID>  
    </Object>  
    <Type>ProcessFull</Type>  
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
  </Process>

Open the XMLA Query window that contains the XMLA script that processes the dimension. Right-click within the script to the left of the </Process> tag and select Paste.

The following example shows the revised XMLA script.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
 <Parallel>  
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
    <Object>  
      <DatabaseID>Adventure Works DW Multidimensional</DatabaseID>  
      <DimensionID>Dim Customer</DimensionID>  
    </Object>  
    <Type>ProcessFull</Type>  
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
  </Process>  
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
    <Object>  
      <DatabaseID>Adventure Works DW Multidimensional</DatabaseID>  
      <CubeID>Adventure Works</CubeID>  
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>  
      <PartitionID>Internet_Sales_2004</PartitionID>  
    </Object>  
    <Type>ProcessFull</Type>  
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
  </Process>  
 </Parallel>  
</Batch>

Highlight the revised XMLA script, right-click the highlighted script, and select Copy.
This step copies the XMLA script to the Windows Clipboard. You can leave the XMLA script in the Clipboard, save it to a file, or paste it into Notepad or another text editor.

Create and schedule the batch processing job

Connect to an instance of SQL Server, and then open Object Explorer.
Expand SQL Server Agent. Start the service if is not running.
Right-click Jobs and select New Job.
In the New Job dialog box, enter a job name in Name.
In Steps, click New.
In the New Job Step dialog box, enter a step name in Step Name.
In Type, select SQL Server Analysis Services Command.
In Run as, select the SQL Server Agent Service Account. Recall from the Prerequisites section that this account must have administrative permissions on Analysis Services.
In Server, specify the server name of the Analysis Services instance.
In Command, right-click and select Paste.
Click OK.
In the Schedules page, click New.
In the New Job Schedule dialog box, enter a schedule name in Name, and then click OK.

This step creates a schedule for Sunday at 12:00 AM. The next step shows you how to manually execute the job. You can also select a schedule which will execute the job when you are monitoring it.
Click OK to close the dialog box.
In Object Explorer, expand Jobs, right-click the job you created, and select Start Job at Step.

Because the job has only one step, the job executes immediately. If the job contains more than one step, you can select the step at which the job should start.
When the job finishes, click Close.