Walkthrough: Scheduling Administrative Tasks

  • Article

New: 17 July 2006

Using the SQL Server Agent component of Microsoft SQL Server, you can schedule SQL Server 2005 Analysis Services (SSAS) administrative tasks to run in the order and times that you need.

Tasks illustrated in this walkthrough include the following:

  1. Creating an XMLA script from a task in an Analysis Services instance.
  2. Creating a job in an instance of SQL Server and embedding the XMLA script inside a job step.
  3. Scheduling the job to run.

Prerequisites

To complete this walkthrough, you will need the following:

  • SQL Server 2005
  • SQL Server 2005 Analysis Services (SSAS)
  • Analysis Services Tutorial project samples.

The Analysis Services Tutorial project is a part of the SQL Server samples that are available from the Microsoft download site. For more information, see Obtaining Updated Samples in Installing Samples.

As creating a scheduled task for Analysis Services requires some steps that occur in the SQL Server Management Studio, you will need the Analysis Services Tutorial project as it exists at the end of Lesson 3. The following procedure prepares the Analysis Services Tutorial project for this walkthrough. For more information about the Analysis Services Tutorial, see SQL Server 2005 Analysis Services Tutorial.

Note

The Analysis Services Tutorial project should be deployed and processed on a test server, not on a production system.

To open, deploy, and process the Analysis Services Tutorial project at Lesson 3

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.

    The Microsoft Visual Studio 2005 development environment opens.

  2. Close the Start Page tab. On the File menu of Visual Studio, point to Open, and click Project/Solution.

  3. Open C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson3 Complete, and double-click the Analysis Services Tutorial.sln.

    In Solution Explorer, you will see a cube and three dimensions.

  4. In Solution Explorer, right-click the top node named Analysis Services Tutorial, and click Deploy.

    This step deploys the project to an Analysis Services instance as an Analysis Services database, and then processes the database. By default, the deployment process targets the default Analysis Services instance. If the computer contains only named instances of Analysis Services, the deployment fails unless the deployment property for the project is changed. For more information, see Deploying an Analysis Services Project.

    The next step opens SQL Server Management Studio, which exposes the Analysis Services database created by this step.

  5. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  6. In the Connect to Server dialog box, select Analysis Services as the Server type, the instance to which the project was deployed as the Server name, and then click Connect.

  7. In Object Explorer, expand Databases.

    You will see the Analysis Services Tutorial database. From this point, you are ready to start the procedures in the following section.

Examples

Most Analysis Services administrative tasks can be automated using SQL Server Agent. The following procedures show the steps for processing a dimension, and batch processing a dimension and a partition. To execute these procedures in your test environment, first complete the steps in "Prerequisites", earlier in this topic.

A. Processing a dimension in a scheduled task

You use the following procedures to create and schedule a job that processes an Analysis Services database dimension.

To create the script that will be used in the job

  1. Using the Analysis Services Tutorial database, expand Dimensions, right-click Time, and select Process.

  2. 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.

  3. Click Script.

    This step opens an XML Query window that contains the dimension processing XMLA script.

  4. Click Cancel.

  5. 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 other text editor. The following is an example of the XMLA script.

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Object>
      <DatabaseID>Analysis Services Tutorial</DatabaseID>
      <DimensionID>Time</DimensionID>
    </Object>
    <Type>ProcessUpdate</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
</Batch>

To create and schedule the process job

  1. Connect to an instance of SQL Server 2005, and then connect to Object Explorer.

  2. Expand SQL Server Agent.

  3. Right-click Jobs and select New Job.

  4. In the New Job dialog box, enter a job name in Name.

  5. Under Select a page, select Steps, and then click New.

  6. In the New Job Step dialog box, enter a step name in Step Name.

  7. In Server, type localhost for a default instance of Analysis Services and localhost\<instance name> for a named instance.

    If you are running from a remote computer, use the server name and instance name where the job runs. Use the format <server name> for a default instance, and <server name>\<instance name> for a named instance.

  8. In Type, select SQL Server Analysis Services Command.

  9. In Command, right-click and select Paste.

  10. Click OK.

  11. Under Select a page, click Schedules, and then click New.

  12. 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 or you can select a schedule that executes the job when you are monitoring it.

  13. In the New Job dialog box, click OK.

  14. 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 where the job starts.

  15. When the job finishes, click Close.

B. Batch processing a dimension and a partition in a scheduled task

You use the following procedures to create and schedule a job that batch processes an Analysis Services database dimension together with a cube partition that depends on the dimension for aggregation. For more information about batch processing of Analysis Services objects, see Batch Processing in Analysis Services.

To create the script that will be used in the job

  1. Using the Analysis Services Tutorial database, expand Dimensions, right-click Time, and select Process.

  2. 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 select Process Full from the drop-down list.

  3. Click Script.

    This step opens an XML Query window that contains the dimension processing XMLA script.

  4. Click Cancel.

  5. Expand Cubes, expand Analysis Services Tutorial, expand Measure Groups, expand Internet Sales, expand Partitions, right-click Internet Sales, and then select Process.

  6. In the Process Partition 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.

  7. Click Script.

    This step opens a second XML Query window that contains the partition processing XMLA script.

  8. Click Cancel.

    At this point you must merge the two scripts with the dimension processing occurring first. If the partition is processed first, the subsequent dimension processing causes the partition to become unprocessed. The partition requires a second processing to reach a processed state.

  9. In the XMLA Query window that contains the partition processing XMLA script, highlight the code inside the Batch 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>Analysis Services Tutorial</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Internet Sales</MeasureGroupID>
      <PartitionID>Internet Sales</PartitionID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
  1. Open the XMLA Query window that contains the dimension processing XMLA script. Right-click to the left of the </Batch> tag and select Paste.
    The following is an example that displays the revised XMLA script.
<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Object>
      <DatabaseID>Analysis Services Tutorial</DatabaseID>
      <DimensionID>Time</DimensionID>
    </Object>
    <Type>ProcessUpdate</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>Analysis Services Tutorial</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Internet Sales</MeasureGroupID>
      <PartitionID>Internet Sales</PartitionID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
</Batch>
  1. 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 or paste it into Notepad or other text editor.

To create and schedule the process job

  1. Connect to an instance of SQL Server 2005, and then connect to Object Explorer.

  2. Expand SQL Server Agent.

  3. Right-click Jobs and select New Job.

  4. In the New Job dialog box, enter a job name in Name.

  5. Under Select a page, select Steps, and then click New.

  6. In the New Job Step dialog box, enter a step name in Step Name.

  7. In Server, type localhost for a default instance of Analysis Services and localhost\<instance name> for a named instance.

    If you are running from a remote computer, use the server name and instance name where the job runs. Use the format <server name> for a default instance, and <server name>\<instance name> for a named instance.

  8. In Type, select SQL Server Analysis Services Command.

  9. In Command, right-click and select Paste.

  10. Click OK.

  11. Under Select a page, click Schedules, and then click New.

  12. 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 or you can select a schedule that executes the job when you are monitoring it.

  13. In the New Job dialog box, click OK.

  14. 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 where the job starts.

  15. When the job finishes, click Close.

See Also

Concepts

Automating Analysis Services Administrative Tasks
Scheduling Administrative Tasks Using SQL Server Agent

Help and Information

Getting SQL Server 2005 Assistance