Sitecore Custom Reduction Troubles

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.
Exception: System.Data.SqlClient.SqlException
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:

Contact Us Today!

About Author

Naim Al
Naim holds a Bachelor Degree in Information Technology and an MBA in Finance. He started his career in programming in 2001 building desktop financial applications. In 2004 he shifted his focus to develop websites. In 2016 Naim joined as a Senior Sitecore Developer. In 2020 Naim won the 1st place in Sitecore Hackathon 2020 competition with American Eagle XTeam Winner, over 82 teams from more than 23 countries were participating. When he is not coding, he likes to spend time with his wife and two kids, watching TV, or relax in his backyard.

Featured Posts