This piece was originally posted last week, 25th June, prior to the post on multiple processing scripts in an xmla command: http://sqlbanana.blogspot.co.uk/2013/07/ssas-how-to-run-multiple-xmla-scripts_6877.html
For some reason the post was not published and lost, so i am reposting it here.
------------------
I was recently hit with a fairly simple problem, but it took me a while to figure out how to do it, so I thought I'd share it to save others some time.
I wanted create a series of partitions on the measure groups of my SSAS 2008R2 database. I needed a partition for each month, for 5 years. For each measure group this corresponds to 60 partitions, and I did not want to have to run them one at a time or in multiple windows.
The solution is to create a single xmla command, that will create all the required partitions in a single batch. Here's how...
First create your Batch tags. We want all our partitions to be created in a single batch. Having multiple batch tags in a single session will throw an error, but multiple Create scripts inside a single batch is perfectly fine.
Batch tags:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
.
.
.
</Batch>
Then put all your create scripts for each partition inside these start and end tags.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
.
.
</Create>
Thus the entire script to create the partitions all in a single command will look something like this:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<
Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ParentObject><
DatabaseID>MyDB</DatabaseID><
CubeID>MyCube</CubeID><
MeasureGroupID>MyFACT</MeasureGroupID></
ParentObject><
ObjectDefinition><
Partition 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"><
ID>FACT_20130301</ID><
Name>FACT_20130301</Name><
Source xsi:type="QueryBinding"><
DataSourceID>MyDS</DataSourceID><
QueryDefinition>SELECT COL1 FROM FACT
WHERE (DATE_KEY = 20130301)
</QueryDefinition></
Source><
StorageMode>Molap</StorageMode><
ProcessingMode>Regular</ProcessingMode><
Slice>[Dates].[Date].&[20130301]</Slice><
ProactiveCaching><
SilenceInterval>-PT1S</SilenceInterval><
Latency>-PT1S</Latency><
SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><
ForceRebuildInterval>-PT1S</ForceRebuildInterval><
Source xsi:type="ProactiveCachingInheritedBinding" /></
ProactiveCaching></
Partition></
ObjectDefinition></
Create>
<
Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ParentObject><
DatabaseID>MyDB</DatabaseID><
CubeID>MyCube</CubeID><
MeasureGroupID>MyFACT</MeasureGroupID></
ParentObject><
ObjectDefinition><
Partition 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"><
ID>FACT_20130302</ID><
Name>FACT_20130302</Name><
Source xsi:type="QueryBinding"><
DataSourceID>MyDS</DataSourceID><
QueryDefinition>SELECT COL1 FROM FACT
WHERE (DATE_KEY = 20130302)
</QueryDefinition></
Source><
StorageMode>Molap</StorageMode><
ProcessingMode>Regular</ProcessingMode><
Slice>[Dates].[Date].&[20130302]</Slice><
ProactiveCaching><
SilenceInterval>-PT1S</SilenceInterval><
Latency>-PT1S</Latency><
SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><
ForceRebuildInterval>-PT1S</ForceRebuildInterval><
Source xsi:type="ProactiveCachingInheritedBinding" /></
ProactiveCaching></
Partition></
ObjectDefinition></
Create>
</
Batch>
We can extend this now to include all 60 partitions in a single file/Batch. If you ever need to rerun, or modify the scripts, you now have them all in a single place, instead of 60 different places.
Hope this helps :)
Showing posts with label Batch. Show all posts
Showing posts with label Batch. Show all posts
Friday, 2 August 2013
Thursday, 25 July 2013
SSAS: How to run multiple XMLA scripts in a single batch - Processing
This post is a follow-on from this article:
http://sqlbanana.blogspot.co.uk/2013/08/ssas-how-to-create-multiple-partitions.html
-----------
Further to my last post, I thought it would be helpful to extend multiple script to commands to processing tasks. As we explained, we can perform multiple commands inside a single Batch, and therefore run them at once in a single session.
In this short example we will see how we can easily process multiple partitions or dimensions in serial, parallel or any combination.
Similar to previous, we open the Batch tag and include everything inside. The other tag to pay attention to is the Parallel tag. Anything within a single parallel tag runs, surprisingly, in parallel. Note that you still need a parallel set of tags for the items not run in parallel, but they will appear within their own individual start and end Parallel tags.
In the script below we are processing DIM1 and DIM2 in parallel and then afterwards DIM3 will be processed. This can easily be extended to refer to measure groups, partitions etc.
Note that if an item in the batch fails, the entire batch is rolled back, not just the failed item.
<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"><
KeyErrorLimit>-1</KeyErrorLimit></
ErrorConfiguration><
Parallel>
<!--
Process DIM1--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM1</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process><!--
Process DIM2--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM2</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel><
Parallel><!--
Process DIM3--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM3</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel></
Batch>
http://sqlbanana.blogspot.co.uk/2013/08/ssas-how-to-create-multiple-partitions.html
-----------
Further to my last post, I thought it would be helpful to extend multiple script to commands to processing tasks. As we explained, we can perform multiple commands inside a single Batch, and therefore run them at once in a single session.
In this short example we will see how we can easily process multiple partitions or dimensions in serial, parallel or any combination.
Similar to previous, we open the Batch tag and include everything inside. The other tag to pay attention to is the Parallel tag. Anything within a single parallel tag runs, surprisingly, in parallel. Note that you still need a parallel set of tags for the items not run in parallel, but they will appear within their own individual start and end Parallel tags.
In the script below we are processing DIM1 and DIM2 in parallel and then afterwards DIM3 will be processed. This can easily be extended to refer to measure groups, partitions etc.
Note that if an item in the batch fails, the entire batch is rolled back, not just the failed item.
<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"><
KeyErrorLimit>-1</KeyErrorLimit></
ErrorConfiguration><
Parallel>
<!--
Process DIM1--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM1</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process><!--
Process DIM2--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM2</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel><
Parallel><!--
Process DIM3--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>DIM3</DimensionID></
Object><
Type>ProcessUpdate</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation></
Process></
Parallel></
Batch>
Subscribe to:
Posts (Atom)