ProcessUpdate on Dimension dropping aggregations on unrelated partitions

Question

I was testing ProcessUpdate as part of a incrementally loading cube.  I am noticing that when dimension members change, related aggregations are changed, but are getting invalidated on ALL partitions of the measure group, not just the one(s) that it affects.  I was able to replicate this using the Adventure Works 2008 Cube.

I installed the sample databases and deployed and fully processed the Adventure Works 2008 DW 2008 analysis services db.  I looked at the aggregations using the DMVs for each of the Reseller Sales partitions using the following:

SELECT


 * FROM


 SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_PARTITION_STAT
, DATABASE_NAME = 'Adventure Works DW 2008'


, CUBE_NAME = 'Adventure Works'


, MEASURE_GROUP_NAME = 'Reseller Sales'


, PARTITION_NAME = 'Reseller_Sales_2004'


);

All aggregations seemed ok.  I ran the following SQL statement against the Promotion Dimension

UPDATE


 DimPromotion SET


 EnglishPromotionType = 'Seasonal Discount'


,SpanishPromotionType = 'Descuento de temporada'


,FrenchPromotionType = 'Remise saisonnière'


WHERE


 PromotionKey = 7

To double check, I ran the following query to confirm that this promotion was only in the fact table/measure group for one day.  The OrderDateKey is what the measure group is being partitioned on in the cube.

SELECT


 DISTINCT


 OrderDateKey
FROM


 [dbo].[FactResellerSales]
WHERE


 PromotionKey = 7

This resulted in one day in 2002 which is in the Resellar_sales_2002 partition.  I ran a ProcessUpdate on the dimension and when investigating the Aggregations using the DMV from above, certain aggregations were dropped across ALL 4 partitions.  This is not what i expected.  I was under the impression that only aggregations were dropped on related partitions.  I would expect that only the aggregations on the 2002 partition would be dropped.

I am baffled and can't seem to figure out why this is happening.  Has anyone seen this before or have any ideas?

Thanks,
Abe

Answer 1

I tried this with another dimension hoping that this was purely coincidence.  Unfortunately the same thing occured and aggregations were lost on other partitions.

Abe

Answer 2

Hi Abe,

Could you refer to the following document:

Analysis Services 2005 Performance Guide - Microsoft Corporation (written for Analysis Services 2005 but still relevant for Analysis Services 2008)

Flexible aggregations

An aggregation is flexible when one or more of the attributes participating in the aggregation have flexible direct or indirect relationships to the key attribute.

If you perform a ProcessUpdate on a dimension participating in flexible aggregations, whenever deletions or updates are detected for a given attribute, the aggregations for that attribute as well as any related attributes in the attribute chain are automatically dropped. The aggregations are not automatically recreated unless you perform one of the following tasks:

 

Regards,

Raymond

Answer 3

Thanks Raymond.  I think I might have been unclear about what my issue was.  I know that aggregations were going to be dropped.  In all the documentation (BOL, whitepapers, etc...) it indicates that only the aggregations AND the partitions affected will be affected.  I updated only 1 dim member which affted only 1 partition, but all 4 partitions had aggregations dropped.  That is why I am confused.

Thanks,
Abe

Answer 4

I would recommend filing this as a suggestion on https://connect.microsoft.com/SQLServer/feedback and suggest MS considers doing the extra work (or letting you set a flag asking it to do the extra work) to determine which partitions contain the members that changed (not just which measure groups are connected to that dimension). That check of partitions may be more efficient than dropping and recreating all, or it may be more expensive. I can't say.

Good find. Post back the link to the Connect request if you create it.

---

http://artisconsulting.com/Blog/GregGalloway

Answer 5

  It maybe not the most intuitive behavior, but due to the implementation and nature of aggregations, they will be most likely dropped when a process update on dimension will be called.

Aggregations hold precalculated measures data, for example sales of a certain category in given month. When you make a Process Update, the subcategories, can change the category to which they are assigned. As the result, the value of precalculated sales of subcategories have also changed. That is the reason why the aggregations are dropped.

However adding new members to dimension and calling Process Update, should not drop any aggregations. If it does remove aggregations, that can mean that you have some cube design problems (for example no time dimension is selected). Adding member should drop only aggregations from the partition that contains last values of the time dimension. 

Hope it will help,

Adam

Answer 6

Thanks.  I am going to file it as a suggestion.  For reason, I thought that functionality already existed.  It was probably my loose interpretation of the whitepapers out there.

Here is the link on Connect.  https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=509271

Thanks,
Abe