使用 SQL Server 代理来计划 SSAS 管理任务Schedule SSAS Administrative Tasks with SQL Server Agent

适用于: 是SQL Server Analysis Services没有Azure Analysis ServicesAPPLIES TO: yesSQL Server Analysis Services noAzure Analysis Services

使用 SQL Server 代理服务,你可以根据所需顺序和时间来计划要运行的 Analysis ServicesAnalysis Services 管理任务。Using the SQL Server Agent service, you can schedule Analysis ServicesAnalysis 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. 还可以通过在 SQL Server 代理作业中创建作业步骤来确定任务的执行顺序。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. 如果一个作业失败,则可以配置 SQL Server 代理以继续运行剩下的任务或停止执行。If one job fails, you can configure SQL Server Agent to continue to run the remaining tasks or to stop execution. 也可以配置 SQL Server 代理以发送有关作业执行成功或失败的通知。You can also configure SQL Server Agent to send notifications about the success or failure of job execution.

本主题是一个演练,演示了如何使用 SQL Server 代理通过两种方式运行 XMLA 脚本。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 代理服务。SQL Server Agent service must be installed.

默认情况下,作业在服务帐户下运行。By default, jobs run under the service account. SQL Server 代理的默认帐户是 NT Service\SQLAgent$<实例名 >。The default account for SQL Server Agent is NT Service\SQLAgent$<instancename>. 若要执行备份或处理任务,此帐户必须是 Analysis Services 实例的系统管理员。To perform a backup or processing task, this account must be a system administrator on the Analysis Services instance. 有关详细信息,请参阅 向 Analysis Services 实例授予服务器管理员权限For more information, see Grant server admin rights to an Analysis Services instance.

您还应拥有要使用的测试数据库。You should also have a test database to work with. 可以部署 AdventureWorks 多维示例数据库或 Analysis Services 多维教程中的项目以在本演练中使用。You can deploy the AdventureWorks multidimensional sample database or a project from the Analysis Services multidimensional tutorial to use in this walkthrough. 有关详细信息,请参阅 安装 Analysis Services 多维建模教程的示例数据和项目For more information, see Install Sample Data and Projects for the Analysis Services Multidimensional Modeling Tutorial.

示例 1:处理计划任务中的维度Example 1: Processing a dimension in a scheduled task

此示例演示如何创建和计划处理维度的作业。This example demonstrates how to create and schedule a job that processes a dimension.

Analysis ServicesAnalysis Services 计划任务是嵌入在 SQL Server 代理作业中的 XMLA 脚本。An Analysis ServicesAnalysis 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. 由于 SQL Server 代理是 SQL ServerSQL Server的一部分,因此要同时使用数据库引擎和 Analysis ServicesAnalysis Services 来创建和计划管理任务。Because the SQL Server Agent is part of SQL ServerSQL Server, you work with both the Database Engine and Analysis ServicesAnalysis Services to create and schedule an administrative task.

创建用于处理 SQL Server 代理作业中的维度的脚本Create a script for processing a dimension in a SQL Server Agent job

  1. SQL Server Management StudioSQL Server Management Studio中,连接到 Analysis ServicesAnalysis ServicesIn SQL Server Management StudioSQL Server Management Studio, connect to Analysis ServicesAnalysis Services. 打开数据库文件夹并查找维度。Open a database folder and find a dimension. 右键单击维度,然后选择“处理”。Right-click the dimension 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.

    此步骤将打开包含处理维度的 XMLA 脚本的 “XML 查询” 窗口。This step opens an XML Query window that contains the XMLA script that processes the dimension.

  4. “处理维度” 对话框中,单击 “取消” 关闭该对话框。In the Process Dimension dialog box, click Cancel to close the dialog box.

  5. 在“XMLA 查询”窗口中,突出显示 XMLA 脚本,右键单击突出显示的脚本,然后选择“复制”。In the XMLA Query window, highlight the XMLA script, right-click the highlighted script, and select Copy.

    此步骤会将 XMLA 脚本复制到 Windows 剪贴板中。This step copies the XMLA script to the Windows Clipboard. 可以将 XMLA 脚本保留在剪贴板中,也可以将其粘贴到记事本或其他文本编辑器中。You can leave the XMLA script in the Clipboard or paste it into Notepad or another text editor. 以下是 XMLA 脚本的一个示例。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

  1. 连接到数据库引擎实例,然后打开对象资源管理器。Connect to an instance of the Database Engine and then open Object Explorer.

  2. 展开 “SQL Server 代理”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. 在“服务器”中,为 Analysis ServicesAnalysis Services 的默认实例键入“localhost”,并为命名实例键入“localhost”\<实例名称>。In Server, type localhost for a default instance of Analysis ServicesAnalysis 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.

  8. “类型” 中,选择 “SQL Server Analysis Services 命令”In Type, select SQL Server Analysis Services Command.

  9. 在“命令”中,右键单击并选择“粘贴”。In Command, right-click and select Paste. 上一步中生成的 XMLA 脚本应显示在命令窗口中。The XMLA script that you generated in the previous step should appear in the command window.

  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.

    此步骤将创建一个于星期日 12:00 AM 启动的计划。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.

  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 at which the job should start.

  15. 在作业完成后,请单击 “关闭”When the job finishes, click Close.

示例 2:批处理计划任务中的维度和分区Example 2: Batch processing a dimension and a partition in a scheduled task

此示例中的过程演示如何创建和计划一个作业,该作业将批处理 Analysis ServicesAnalysis Services 数据库维度并处理依赖于聚合维度的多维数据集分区。The procedures in this example demonstrate how to create and schedule a job that batch processes an Analysis ServicesAnalysis Services database dimension, and at the same time to process a cube partition that depends on the dimension for aggregation. 有关批处理 Analysis ServicesAnalysis Services 对象的详细信息,请参阅批处理 (Analysis Services)For more information about batch processing of Analysis ServicesAnalysis Services objects, see Batch Processing (Analysis Services).

创建用于批处理 SQL Server 代理作业中的维度和分区的脚本Create a script for batch processing a dimension and partition in a SQL Server Agent job

  1. 通过使用相同的数据库,展开“维度”,右键单击“客户”维度,然后选择“处理”。Using the same database, expand Dimensions, right-click the Customer dimension, and select Process.

  2. “处理维度” 对话框的 “对象列表” 下的 “处理选项” 列中,验证此列的选项是否为 “处理全部”In the Process Dimension dialog box, in Process Options column under Object list, verify that the option for this column is Process Full.

  3. 单击 “脚本”Click Script.

    此步骤将打开包含处理维度的 XMLA 脚本的 “XML 查询” 窗口。This step opens an XML Query window that contains the XMLA script that processes the dimension.

  4. “处理维度” 对话框中,单击 “取消” 关闭该对话框。In the Process Dimension dialog box, click Cancel to close the dialog box.

  5. 依次展开“多维数据集”、“Adventure Works”、“度量值组”、“Internet 销售”和“分区”,右键单击列表中的最后一个分区,然后选择“处理”。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.

  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.

  7. 单击 “脚本”Click Script.

    此步骤将打开另一个包含处理分区的 XMLA 脚本的 “XML 查询” 窗口。This step opens a second XML Query window that contains the XMLA script that processes the partition.

  8. “处理分区” 对话框中,单击 “取消” 关闭该编辑器。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.

    警告

    如果先处理分区,则后续维度处理将导致分区变为未处理状态。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.

  9. 在包含处理分区的 XMLA 脚本的“XMLA 查询”窗口中,突出显示 BatchParallel 标记中的代码,右键单击突出显示的脚本,然后选择“复制”。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>  
    
  10. 打开包含处理维度的 XMLA 脚本的 “XMLA 查询” 窗口。Open the XMLA Query window that contains the XMLA script that processes the dimension. </Process> 标记左侧的脚本中右键单击,然后选择“粘贴”。Right-click within the script to the left of the </Process> tag and select Paste.

    下面的示例显示了修改后的 XMLA 脚本。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>  
    
  11. 突出显示修改后的 XMLA 脚本,右键单击突出显示的脚本,然后选择“复制”。Highlight the revised XMLA script, right-click the highlighted script, and select Copy.

  12. 此步骤会将 XMLA 脚本复制到 Windows 剪贴板中。This step copies the XMLA script to the Windows Clipboard. 可以将 XMLA 脚本保留在剪贴板中、将其保存到文件中或将其粘贴到记事本或其他文本编辑器中。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

  1. 连接到 SQL ServerSQL Server实例,然后打开对象资源管理器。Connect to an instance of SQL ServerSQL Server, and then open Object Explorer.

  2. 展开 “SQL Server 代理”Expand SQL Server Agent. 如果该服务尚未运行,请启动该服务。Start the service if is not running.

  3. 右键单击“作业”,然后选择“新建作业”。Right-click Jobs and select New Job.

  4. “新建作业” 对话框的 “名称” 中,输入作业名称。In the New Job dialog box, enter a job name in Name.

  5. “步骤” 中,单击 “新建”In Steps, click New.

  6. “新建作业步骤” 对话框的 “步骤名称” 中,输入步骤名称。In the New Job Step dialog box, enter a step name in Step Name.

  7. “类型” 中,选择 “SQL Server Analysis Services 命令”In Type, select SQL Server Analysis Services Command.

  8. “运行身份” 中,选择 “SQL Server 代理服务帐户”In Run as, select the SQL Server Agent Service Account. 回想“先决条件”部分中所述的内容,此帐户必须对 Analysis Services 具有管理权限。Recall from the Prerequisites section that this account must have administrative permissions on Analysis Services.

  9. “服务器” 中,指定 Analysis Services 实例的服务器名称。In Server, specify the server name of the Analysis Services instance.

  10. 在“命令”中,右键单击并选择“粘贴”。In Command, right-click and select Paste.

  11. 单击 “确定”Click OK.

  12. “计划” 页上,单击 “新建”In the Schedules page, click New.

  13. “新建作业计划” 对话框的 “名称” 中,输入计划名称,然后单击 “确定”In the New Job Schedule dialog box, enter a schedule name in Name, and then click OK.

    此步骤将创建一个于星期日 12:00 AM 启动的计划。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.

  14. 单击 “确定” 关闭对话框。Click OK to close the dialog box.

  15. 在“对象资源管理器”中,展开“作业”,右键单击创建的作业,然后选择“作业开始步骤”。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.

  16. 在作业完成后,请单击 “关闭”When the job finishes, click Close.

另请参阅See Also

处理选项和设置(Analysis Services)Processing Options and Settings (Analysis Services)