SSAS : Update a dimension won't drop aggregation process index won't rebuild aggregation (Network Steve Forum)

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

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, just read that 'DISTINCT_COUNT measure is not aggregatable', Is that the problem ?

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 ?

Edited by ewan7 4 hours 13 minutes ago

August 26th, 2015 3:41am

Hi ewan7,

In Analysis Services, Process Update, Process Full and Process Index will all drop the aggregation and recreate one. In this scenario, as you said, DISTINCT_COUNT measure is not aggregatable. Because distinct count should always work on corresponding grain directly. It can never aggregate the lower grain fact data to get the higher grain fact data. So even you have done Process options above, it still can't make changes on distinct count measure. You refer to the book: Microsoft SQL Server 2008 Analysis Services Unleashed.

Regards,

Free Windows Admin Tool Kit Click here and download it now

August 27th, 2015 2:47am

This topic is archived. No further replies will be accepted.