SSAS Batch Processing with XMLA

Posted on Monday 09 November 2009, 12:10 - Décisionnel - Permalink

Article
|
Comments(0)
|
Attachments(0)

When you want to schedule the processing of SSAS database (using Sql Server jobs), you have two options :

create ans SSIS package containing Analysis services tasks & schedule it.
Build ans XMLA script & schedule it.

Here we will see how you can build an XML script describing the processing tasks you want to execute.

1- Buiding & testing the script

In Sql Server management studio, connect to yous SSAS database.

Open a new XMLA Query window & paste the following script in it.

<Batch ProcessAffectedObjects="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<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">
    <Object>
      <DatabaseID>ASdatabase</DatabaseID>
      <DimensionID>Dimension ID(not name)</DimensionID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Process>
    <Object>
      <DatabaseID>ASdatabase</DatabaseID>
    <DimensionID>Dimension 2 ID</DimensionID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Process>
<Object>
    <DatabaseID>ASdatabase</DatabaseID>
    <CubeID>Cube ID</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

Of course, you just have to set your own database and dimensions/cubes IDs. You can add as many <process></process> blocks you need.
Now just run your Script and see what happens.

2- Scheduling

When you are ready, create or open ans Sql job under SQL Server Agent.

add a step to your job, choose "SQL Server Analysis Services" as Type & paste your script.

Of course this is just a Quickstart. For more informations about XMLA structure & options, just refer to the MSDN Help.

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Blog Pro de Jean-Baptiste HEREN