Learn how your company can work directly with me or one of my peers.
The benefit is not just having an experienced engineer helping you manage the health of your environment. Through Premier Field Engineering, your company will have access to a wealth of knowledge from all of PFE and a channel into the product group to answer the most complex questions.
/*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
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.