As I continue working on an Analysis Services implementation I am learning more and more about the product, some of which is well worth sharing.
Today I was attempting to optimise the processing of my cube. I issue processing commands from a parameterized XMLA script that I built myself. The XMLA script is executed using the ever brilliant ascmd.exe. The XMLA script is basically this:
<
Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<!--Dimensions-->
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>CoreMarketReturn</DatabaseID>
<DimensionID>Return Currency</DimensionID> </Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<!--Then do a ProcessUpdate on the rest of my dimensions
...
...
-->
</Parallel>
<!--Measure groups-->
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>$(DATABASEID)</DatabaseID>
<CubeID>$(CUBEID)</CubeID>
<MeasureGroupID>Capacity</MeasureGroupID>
<PartitionID>Capacity_$(PREVIOUSYEAR)</PartitionID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<!--Then do a ProcessFull on the rest of my mesaure groups
...
...
-->
</Parallel>
</Batch>
As you can see, I do a ProcessUpdate on all of my dimensions and then a ProcessFull on a small subset (that's important) of my partitions. My cube contains 888 partitions in total (don't ask!).
I was monitoring activity on the Analysis Server during execution of this script and was seeing some strange things going on. All of my dimensions were getting updated (and I could see the SQL statements being fired at the underlying data source) but there were also 518 records in my Profiler trace with the message:
Finished processing the '<partition name>' partition
none of which resulted in a query against the underlying source. This made no sense to me. I couldn't understand why:
a) Partitions that I did not request to be processed were getting processed and
b) The processing didn't result in a query against the source data.
Thankfully Edward Melomed of the Analysis Services team in Redmond had the answer. It is simply because doing a ProcessUpdate on dimensions causes Analysis Services to drop partitions aggregations and indexes. Its a shame that the message one gets in Profiler doesn't explain this very well but I'll let that one lie. At least I can now easily explain away what previously was looking like really odd behaviour.
All useful stuff to know methinks!!
-Jamie