I'm rebuilding the reporting database after completing upgrade from 7.5 to 8.1 update 1 according to the following documentation:


After a while of clicking on "Start" button on sitecore/admin/RebuildReportingDB.aspx the rebuild completed,

My issue that I'm using custom aggregator processors to pull data from MongoDB and add these data to custom SQL tables in the reporting database and when checking these tables they have data less than the data in the primary reporting database.

I checked the log files and there are no errors related, Is there any setting that should be added, Is there a time interval that should be set other than the time interval for clearing secondary database tables?

Following are more details:

I'm tracing page events like click on a banner rendering, this is saved to MongoDB, then I'm using the following to get these data into SQL:

public class PageEventProcessor : AggregationProcessor
    protected override void OnProcess(AggregationPipelineArgs args)
        Assert.ArgumentNotNull(args, "args");
            var fact = args.GetFact<PageEvent>();
            foreach (var page in args.Context.Visit.Pages)
                foreach (var pEvent in page.PageEvents)
                    var eventkey = new PageEventKey
                        EventId = pEvent.PageEventDefinitionId,
                        Date = args.DateTimeStrategy.Translate(pEvent.DateTime),
                        ItemId = pEvent.ItemId,
                        PageId = page.Item.Id
                    var eventValue = new PageEventValue
                        Count = 1
                    fact.Emit(eventkey, eventValue);
        catch (Exception ex)
            Log.Error("Error in PageEventProcessor.OnProcess", ex, this);
        Log.Info("PageEventProcessor.OnProcess - end", this);

Following are the table schema in SQL:

CREATE TABLE [dbo].[Fact_PageEvent](
[Date] [smalldatetime] NOT NULL,
[EventId] [uniqueidentifier] NOT NULL,
[PageId] [uniqueidentifier] NOT NULL,
[ItemId] [uniqueidentifier] NOT NULL,
[Count] [int] NOT NULL)

In addition in the secondary database table I have 181 record while in the old database I have more thank 16000 record!

  • For us to be able to help you, you'll need to add more detail about the specifics of your custom aggregation and about your problem in general. Can you add the code and relevant configuration bits of your custom processors? What sort of data are you aggregating? How much data do you have in your custom tables in the primary database and how much in the secondary? What data is missing, can you spot a pattern? Have you removed any data from MongoDB since your 7.5 solution went live? Dec 15 '16 at 10:04
  • @DmytroShevchenko I added more details to the question above. Dec 15 '16 at 10:24
  • What about the standard Fact_* tables? Do they contain the same amount of data in both Reporting databases? Dec 15 '16 at 10:49
  • I believe I figure it out. Dec 15 '16 at 13:16
  • Then please share the solution with us :) Dec 15 '16 at 13:18

Following what I found and based on my problem has been solved:

After double check the error log, I found the following error

ManagedPoolThread #19 2016:12:15 02:14:48 ERROR Cannot parse the time interval specified in the 'IntervalTokeep' option to the desired format 'hh:mm:ss' for the CleanupEventQueue agent. The value specified in the DaysToKeep option will be used instead – the specified value is '1' days. The specified value in the IntervalToKeep option is ''.

Which is according to the following, is a Sitecore bug: https://community.sitecore.net/developers/f/8/t/1636

Also, I updated my configuration a little as mentioned in the following: https://community.sitecore.net/developers/f/9/t/373

Now I have more than 34000 record in the mentioned table.


You may need to check the [DeployDate] from [Segments] table as for the default processors to pull MongoDB to SQL server you need to try the following if you have missing data in SQL server.

Open the reporting database using the SQL Management Studio and run the following query: SELECT * FROM [Segments]

1) Comment the reporting.secondary connection string.

2) Open the sitecore/admin/redeploymarketingdata.aspx page and click the Redeploy Segments button.

3) Open the reporting database using SQL Management studio and run the following query: UPDATE [dbo].[Segments] SET [DeployDate] = '2001-08-05 08:44:45.500'

Segments are used while aggregating the data for the Experience Analytics application. If the interaction.SaveDateTime <= segment.DeployDate, the interaction won't be aggregated (That's why we need to change the deploy date manually).

4) Uncomment the reporting.secondary connection string.

5) Rebuild the reporting database.

  • Checked, it is already updated. Dec 15 '16 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.