SSAS - ProcessAdd explained

Further to my previous post on creating multiple partitions and multiple processing commands in a single xmla script (here and here) I thought I share one more idea around SSAS Processing tasks.

When looking the different types of processing options available in SSAS you tend to get the same list over and over: 

ProcessFull,
ProcessDefault,
ProcessData,
ProcessIndexes,
ProcessUpdate,
ProcessIncremental,
ProcessClear,
ProcessAdd

I will not explain here the differences between each of these types as this info is readily available on plenty of other sites (such as http://technet.microsoft.com/en-us/library/ms174774(v=sql.105).aspx). 

However if you try to use each of these processing types from management studio all will go well until you try and process a dimension using ProcessAdd.  It may sound like a useful option (faster than ProcessUpdate, less impact than ProcessFull etc) but when you try and use it you will find it is not as straightforward as the others. If you right-click the dimension in SSMS and select Process you might think to just select it in the drop down.  But take a close look at that drop down - ProcessAdd is not there! 

But the documentation says it is definately an option. Script a different processing option to XMLA and change the process type to Add and it may work, but if you havent specified which records to add the results could be misleading. Confusing, right?

In order to use this processing option you have to actually stipulate quite a lot of details.  This can be done with an XMLA script, but the process of building this script is quite tricky.  Therefore i shall do my best to explain how to do it here :)  (Greg Galloway gives a good overview of this here
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=4, and the steps below were based on following his method)

1 - Use Management Studio (SSMS) to script out to XMLA a ProcessUpdate command.  (I recommend editting the error settings to ignore/report duplicates before scripting)

2 - In BIDS in the Solution Explorer right click on the dsv (the dsv file that the relevant dimension is build from, if you have more than one) and select View Code.  Copy the entire code and paste it into a new window in SSMS to allow us to edit it.

3 - We should now reduce this dsv copy down to only the relevant part. We only actually need the
<
xs:element name  for the particular query that is relevant to our dimension. Tip - quick way to find this element would be to search for a unique string within the query that might single it out from the rest! Keep all data and tags within this particular tag and delete everything outside this (tags at the same level), but keep all the parent tags.  SSMS should make it easy to expand and collapse the tags in order to determine was tags are above or below, and what ones are at the same level.
4 - Copy what remains and paste it into your original process script from step 1. The code should be copied immediately before the closing Process tag.

5 - Now modify the actual query to limit it to only new data.  For example add a where clause that limits the results to be after a certain date.

6 - Voila - you now have your ProcessAdd script.  It should look something like this the code below and ready to execute.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><!--
Error Cofig--><
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>
<!--
Processing details--><
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"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>MyDim</DimensionID></
Object><
Type>ProcessAdd</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation>
<!--
Copy from dsv goes here--><
DataSourceView 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:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="4be9af80-0ae0-4f8a-936d-2103b944155f" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ID>MyDSV</ID><
Name>MyDSV</Name><
DataSourceID>MyDS</DataSourceID><
Schema><
xs:schema id="MyDS" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"><
xs:element name="MyDS" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:design-time-name="2557b54c-2a08-4e49-ab45-fdd94dfb6dee"><
xs:complexType><
xs:choice minOccurs="0" maxOccurs="unbounded"><!--
Query to be modified is here--><
xs:element name="MY_DIM" msprop:QueryDefinition="SELECT 'HELLO' AS PHRASE FROM MY_TABLE&#xD;&#xA;WHERE DATE_KEY > 20130801"

msprop:DbTableName="MY_DIM" msprop:QueryBuilder="GenericQueryBuilder" msprop:IsLogical="True" msprop:FriendlyName="MY_DIM" msprop:design-time-name="c4b7ad24-101e-4d41-bc04-7ac2b605cd7b" msprop:TableType="View"><
xs:complexType><
xs:sequence><
xs:element name="MY_ATTRIBUTE" msprop:design-time-name="1e60f905-098a-45f2-b5be-c34364bdf328" msprop:DbColumnName="PHRASE" minOccurs="0"><
xs:simpleType><
xs:restriction base="xs:string"><
xs:maxLength value="100" /></
xs:restriction></
xs:simpleType></
xs:element></
xs:sequence></
xs:complexType></
xs:element></
xs:choice></
xs:complexType></
xs:element></
xs:schema><
diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" /></
Schema></
DataSourceView><!--
End of copy from dsv--></
Process></
Parallel>
</
Batch>