SSAS : Update a dimension won't drop aggregation process index won't rebuild aggregation

0
votes

I have a 'Employee' dimension which will be changed (modified) everyday, I made monthly partitions in cube and only process full the current month partition. Lately found that the past month's aggregation will not be dropped. Tired to 'ProcessUpdate' on this dimension and 'ProcessIndex' on partition but remained same. Also tried the setting 'ProcessAffectObjects'and 'ProcessIndex' again, still the same, tried both on lazyprocessing true and false with no luck. So my question is how to drop the stale aggregation on past month and rebuild them explicit ? It is a distinct count measurement and no aggregation designed via wizard

Tried drop the index by using 'ProcessClearIndexes' in XMAL command, it worked fine and use 'ProcessIndexes' did rebuild the indexes and aggregation, saw them from the SSMS query execution message .

So might it only be related to the distinct count , just because it is a non-aggregation measurement ?

"Non-additive measures create the following problems on a typical OLAP system: Roll-ups are not possible. When pre-calculating results during cube processing, the system cannot deduce summaries from other summaries. All results must be calculated from the detail data. This situation places a heavy burden in processing time. All results must be pre-calculated. With non-additive measures, there is no way to deduce the result for a higher-level summary query from one pre-calculated aggregation. Failure to pre-calculate the results in advance means that the results are not available. It is impossible to perform and maintain incremental updates to the system. A single transaction added to the cube usually invalidates huge portions of previously pre-calculated results. In order to recover from this, a complete recalculation is needed."

"Aggregations As mentioned before, DISTINCT COUNTs are not additive (and this is the main reason why these measures are so problematic). Therefore, the aggregations, which are all derived from additive operators, are completely useless;"

someone answered my question on MSDN

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7302227f-11b8-4adc-98ff-72b6c395775b/ssas-update-a-dimension-wont-drop-aggregation-process-index-wont-rebuild-aggregation?forum=sqlanalysisservices

1
ssasaggregationdimension
Why do you thing the aggregations are stale? Are the totals you get in queries wrong? Or are you just saying the agg files on disk don't change with every ProcessUpdate? – GregGalloway
the distinct count result is wrong, I just process current month's partition every night. the past month's partition will not be changed when the employee changed his/her city. Then the distinct count number is wrong when using 'Employee' dimension. I have to process full on those partitions to get correct result. But fact data is fixed in our case, so no need to process data , just rebuilding the aggregation might work. But from some articles I copied above, those aggregation is useless for distinct count, so now I am working on the solution to process full on those partitions in night job. – ewan
If all you do is ProcessClearIndexes then ProcessIndexes and that fixes the inaccurate totals then that's a bug. Have you updated to the latest service pack? SSAS should never return the wrong numbers. – GregGalloway
sorry, I didn't make it clear. I just said the 'ProcessClearIndexes' did clear the aggregation, but when using 'processindexes' to rebuild aggregation, the result is same , not correct. – ewan
Oh. Then the issue has nothing to do with ProcessIndexes or aggregations. Some values must have changed in SQL in the fact table or dimensions. If ProcessFull on the whole database solves it that's your culprit. – GregGalloway

1 Answers

0
votes

If you use materialized reference dimensions ensure you do ProcessFull to reprocess the fact tables again fully. The reason is that the join to the intermediate dimension happens in the measure group partition processing query: http://sqlblog.com/blogs/alberto_ferrari/archive/2009/02/25/ssas-reference-materialized-dimension-might-produce-incorrect-results.aspx