ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube

mdxssas

I have Account and Customer dimensions in the cube that are connected to the same measure groups (there are about 15 – 20 measure groups in the cube).

When I run XMLA command to process update these two dimensions, like this:

<Batch>
    <Parallel>
        <Process>
            <Object>
                <DatabaseID>My Database</DatabaseID>
                <DimensionID>Dim Customer</DimensionID>
            </Object>
            <Type>ProcessUpdate</Type>
            <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
        </Process>
    </Parallel>
</Batch>

in the case of Account dimension it finishes in a couple of minutes because it doesn't trigger processing of all partitions of all measure groups. But in the case of Customer dimension it triggers processing of all partitions of all measure groups, so process update of this dimension lasts longer then full processing of entire cube.

I am not sure what can be the reason from which the dimension will trigger all this processing in the case of one dimension and not in the case of the other. For both dimensions Process affected objects is set to Do not process. Where should I look, what to check, can I somehow prevent this reprocessing happen?

Thanks!

Best Answer

I'm somewhat surprised that your partitions are processed without you specifying "process affected objects" anywhere.
Maybe you are confusing the output "partition processing operations" with the actual processing of the partitions.

The logic to process partitions when updating dimensions is this.

  • if you process update a dimension and only new members were added, the partition is not affected
  • if you process update a dimension and changes were made (deletes, changes in dimension relations such as a customer changed customer group/zip code whatever) some of the aggregation data and bitmap indexes on the partitions are dropped.

The drop of the aggregation data and bitmap index shows up in profiler as "partition processing operations".

This however will not render the partition inaccessible, so the measures are still available for querying, albeit slower.

If ProcessAffectedObjects were set to true, the partitions for which the aggregations/indexes were dropped would have their aggregations and indexes rebuilt (but not the entire partition reprocessed).

So I think you are confusing the message "partition processing operations" with the actual reprocessing of the partition, and the customer dimension just takes longer to process than the account partition (possibly because it has more members/hierarchies/relations/...).

For reference you could visit this link which offers you a script on the $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT dmv to validate this is what happens: Different Kinds of SSAS Processing in simple words.

The "impact analysis" button only uses the cube definition to see which partitions "may" be affected, it isn't until after actual processing of the dimension that SSAS knows which partitions "are" affected.

Related Question