Author: Joyce Zhou
Introduction
The MODELER allows you to process application objects including databases, cubes, partitions, and dimensions through the UI when needed. If a process task needs to be scheduled, we can use a SQL Agent Job.
Example - Process Partition
Step 1. Open SQL Server Management Studio and connect to the Analysis Server. Find the object that you need to process. Here we’ll process the Actual partition within the Cap Asset-Reporting cube as an example. Right click on the partition and select Process.
Step 2. In the pop-up window, select from the Process Options drop-down as needed.
Step 3. Click Script and select Script Actions to Clipboard.
Step 4. Go to the SQL Agent Job you are working on, add a New Job Step, select SQL Server Analysis Services Command as the Type, enter the server name, paste the code we copied from last step into the Command field, and then click OK to save.
Example - Process Database
You can follow the same steps to process other objects through SQL Agent Jobs as well:
Here is the code. You just need to change [DATABASE_ID] to your application name.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ErrorConfiguration 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" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<KeyErrorLimit>-1</KeyErrorLimit>
<KeyDuplicate>ReportAndContinue</KeyDuplicate>
<NullKeyConvertedToUnknown>ReportAndContinue</NullKeyConvertedToUnknown>
</ErrorConfiguration>
<Parallel>
<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" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<Object>
<DatabaseID>[DATABASE_ID]</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Comments
3 comments
Thanks for the article. I have a doubt about could I use SQL job to process several partitions with process add... Namely, I want to make two jobs one with full processing in weekend time and all partitions to process with process Add during a working week. I found that cube must be processed with the process full option when called from SQL agent, but can't find what's about partitions. Greetings
Hi Jasmina,
Similarly to what is explained above, you will want to right-click the Partition of choice and select the Process option. In the pop-up window, select Process Add using the Process Options drop-down.
Select the Configure option and configure the Data source and the table name. Press OK. Once you have done that, follow the same steps as the Process Full scripting.
Hi Joyce, thanks a lot for the fast reply and detailed explanation. Jasmina
Please sign in to leave a comment.