Wednesday, January 2, 2013

SSAS - Cube Processing...

In SSAS there are different types of options available for Cube Processing. Each one of the Processing types have different uses. It would be good to understand the different types of processing for cubes.

Process Types Available in SQL 2005 / SQL 2008 / SQL 2008R2 Analysis Services:

ProcessFull : applies to all objects which means it discards the storage contents of the object and rebuilds them. It applies to descendants objects as well.

ProcessAdd: Applies to Dimensions and Partitions, it didn't existing in SSAS 2000. It essentially optimizes ProcessUpdate in scenerios where you are adding only new records to Dimension and facts.

ProcessClear: Discards the storage of the object, it applies to all objects and descendants objects
The ProcessUpdate option is a tricky option: It applies only to Dimension. It is the equivalent of incremental dimension processing in Analysis Services 2000. It sends SQL queries to read the entire dimension table and applies the changes—member updates, additions, deletions.

Since ProcessUpdate reads the entire dimension table, "How is it different from ProcessFull?" The difference is that ProcessUpdate does not discard the dimension storage contents. It applies the changes in a "smart" manner that preserves the fact data in dependent partitions. ProcessFull, on the other hand, does an implicit ProcessClear on all dependent partitions. ProcessUpdate is inherently slower than ProcessFull since it is doing additional work to apply the changes.
Depending on the nature of the changes in the dimension table, ProcessUpdate can affect dependent partitions. If only new members were added, then the partitions are not affected. But if members were deleted or if member relationships changed (e.g., a Customer moved from Redmond to Seattle), then some of the aggregation data and bitmap indexes on the partitions are dropped. The cube is still available for queries, albeit with lower performance.(with ProcessUpdate Flexible aggregations and indexes on related partitions will be dropped)

Thanks to Karan Gulati (From Microsoft SSAS/BI Group)

No comments:

Post a Comment