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>

Reporting Services Report – Changing Column Names, Changing Table Names and sql PIVOT

The other day I was creating some SSRS reports.  For each datafeed in an ETL process the rejected rows were being diverted into error tables – a single error table for each feed, eg error_feed1, error_feed2.  As each of the feeds were different, so too were the column names and metadata of the error tables.  To allow users to review and correct these records I needed to build Reporting Services reports on each of the tables.

Initially it looked like I would need a different report for each feed – when you bind a SSRS report object  to a dataset the column names of the dataset have to remain constant or the report will fail, hence one report, one error table.  As I was dealing with dozens of feeds, the prospect of dozens of very similar reports did not seem favourable.

All these reports would be almost identical, the only difference was the column names and the table names.  I was sure there must be an easier way.  I googled around and found several useful suggestions.  Generally they followed the idea of pivoting the columns into rows in the dataset and then using a SSRS matrix object.  EG:

error_feed1

Id	Col1	Col2	Col3	Col4
1	W	X	Y	Z

Would become:

ID	measure	value
1	Col1	W
1	Col2	X
1	Col3	Y
1	Col4	Z

Using a matrix you would put the ID column on the rows, the measure column on the cross tab section, and value in the data section of the matrix object.  Now it wouldn’t matter if the column names changed, if new columns were added or old ones removed from the source table.  The 3 columns outputted by the pivot query would remain and the matrix report will adapt accordingly. The pivot/unpivot command to do the above would look like this:

SELECT ID, measure, value
FROM( SELECT id, col1, col2, col3, col4, col5 FROM error_feed1) p
UNPIVOT( VALUE FOR measure IN (col1, col2, col3, col4, col5)) AS unpvt

The columns are now dynamic, which solves half the problem. But the FROM clause uses specific column and table names, meaning this metatdata needs to be known in advance and hardcoded into the SSRS dataset query.  Which brings us back to our original problem – we cannot hard code these values because they are constantly changing.

In order to get around this problem I decided to have the dataset be the result of a stored procedure.  I can then have greater flexibility in manipulating the data, so long as I return a result set to SSRS at the end, and always with the same column names returned.

The proc accepts one parameter  - the feed name, to be supplied by the user running the report using a standard SSRS drop down.

The proc itself makes use of the sysobjects and syscolumns system tables to get the full list of columns for any given table:

SELECT      c.name
FROM        sys.columns c
INNER JOIN  sys.objects o
      ON    c.object_id = o.object_id
WHERE       type = 'U'
      AND   o.name LIKE @TableNameORDER BY    c.column_id

name
------------
id
col1
col2
col3
col4

Once the table name has been supplied (as a parameter in the SSRS report), dynamic sql can be leveraged to query the system tables and use the results to build a string containing the required sql PIVOT command, with all the relevant column names for any given table.
 

Once the string variable is populated with the sql script it is then executed,  returning a result set of just 3 columns; the same 3 columns - ID, measure and value -  regardless of the table being queried.  The actual code code of the proc is below:

This result set is all that the SSRS queryset would ever see, and the column names would always be the same 3 named columns, regardless of the feed selected by the user.  Setting up the SSRS matrix object in the manner suggested above would then display the contents of the table as normal – effectively doing a PIVOT to counter the UNPIVOT done in the stored procedure.

We now only need  one single SSRS report to display data from any database table the user selects from the feed list in the parameter drop down -  much simpler than dozens of different reports, or dozens of datasets and playing with visibility settings etc.