SSAS Batch Processing with XMLA
Par jb le lundi 9 novembre 2009, 12:10 - Décisionnel - Lien permanent
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.