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>

SSAS: How to Create multiple partitions in a single XMLA script

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].&amp;[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].&amp;[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 :)

SSAS - DATAMEMBER: Parent Member should not be the sum of it's children, but has it's own value

A common problem that I've had to deal with is how to handle the sales of a parent member in a hierarchy. Under normal SSAS conditions the parent will be the sum of the children.  However this is often not the case.  A manager of a team may make sales in his own right and we may want to see his own sales, and not the sum of his team members' sales.

In SSAS every non-leaf member on a parent/child hierarchy has an extra system-generated child called a datamember.  Assuming the fact table contains values for a non-leaf member on a parent/child hierarchy, then these values will in fact be assigned to the member's datamember.

We can then use the MDX DATAMEMBER function.  This function causes the relevant measure to display the measure value associated datamember, and not aggregate the values of the children. 

 Using this within a SCOPE statment in the cube calculation script will define this functionality for the specified measure:

SCOPE([Measures].[Sales]);
THIS=[Employee].[Employees].CURRENTMEMBER.DATAMEMBER;
END SCOPE;
 
Now the Sales measure will always show the value assigned to the datamember, instead the aggregated value.

More detail, and a good example of this can be found here: http://www.packtpub.com/article/measures-and-measure-groups-microsoft-analysis-services-part1

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>