The Sitecore community has come across an issue with aggregation and reporting that is causing incorrect data in Experience Analytics. This can be very frustrating and tough to nail down. However, we've narrowed the distortion to reside in the Fact_SegmentMetricsReduced table.
In this example we review a simple case where one event occurred in a single day. The Fact_SegmentMetrics table accurately reflects this aggregation. However, we did not expect the Reduce table to have any values. It appears to have reduced the single dimensionkey metrics with itself!
This is a pattern we are seeing at large where records exist in the Reduced table that are 2X, 3X, even 12X the records in the other table.
Moreover, we noticed the same behavior in custom processor aggregations and we believe the two issues are related, the logs are full of failing ambiguous aggregation errors similar to:
ERROR [Sitecore.Analytics.Aggregation.SqlReportingStorageProvider] Exception when storing an aggregation result into the reporting database. Item will be postponed and retried later.
Message: Failed to insert or update rows in the [Fact_Payments] table.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
We reviewed the custom aggregation documentation, and everything was consistent and matches their approach.
I decompiled the "Sitecore.Analytics.SQL.dll" to understand the internal process better and to add more logs.
We found the vague error message was actually a violation of the custom table primary key, which was strange as all the data were valid (we was considering the possibility of corrupted data).
Sitecore support was able to figure it as the client used Mongo db version 3.6, which is not compatible with Sitecore 8.1 rev. 160302 (Update-2), the computability tables can be found at: https://kb.sitecore.net/articles/633863