Command Shell Examples
Useful SQL Queries
Return all dirty aggregations in last X days - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Return all dirty aggregations in last X days

Return all dirty aggregations in last X days

  • Comments 2
  • Likes
/*Return all dirty aggregations in last X days*/
DECLARE @Days AS int
SET @Days = 1 --number of days to go back
SELECT DS.DatasetDefaultName,
    CASE SDAH.AggregationTypeId
        WHEN 0 THEN 'Raw' WHEN 20 THEN 'Hourly' WHEN 30 THEN 'Daily' ELSE 'Unknown'
    END AS Type,
    SDAH.AggregationDateTime,
    DATEADD(HH, (datediff(HH, getutcdate(), getdate())), SDAH.AggregationDateTime) AS [LocalTime],
    SDAH.DirtyInd,
    SDAH.AggregationCount    
FROM Dataset AS DS INNER JOIN
    StandardDatasetAggregationHistory AS SDAH ON SDAH.DatasetId = DS.DatasetId
WHERE SDAH.DirtyInd <> 0 AND SDAH.AggregationDateTime > DATEADD(DD, -@Days, getdate())
ORDER BY SDAH.AggregationDateTime DESC, DS.DatasetDefaultName ASC, [Type] ASC

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • What does it mean and what should we do to avoid these dirty aggregations?

  • Having dirty aggregations usually indicates a performance problem, but sometimes can be poorly written procedures.  If you see one or two dirty aggregations in a row, and then a bunch of successful aggregations, do not be concerned.  If you see 10 or more dirty aggregations in a row, and there is no recovery, then you've got issues.  Main point is, the DW should always recover from dirty aggregations.