Speed up Rebuilding Reporting Database

Question

Analytics data since September 1 is missing for a Sitecore customer. We are trying to rebuild the reporting database in Production (Sitecore 10.2). There are approximately 60+ Lacs of data. The process is going really slow (It shows 255 done after running it for more than 2 days).

There is a 'Minimum StartDateTime' field in the RebuildReportingDB.aspx which I have tried to use and provided a value of September 1. I'm trying to run this in a lower environment and even though this is going a little faster, I suspect that the data before September 1 will be lost.

So I would like to know if there are any alternatives or something can be done to expedite the process?

Answer 1

I beleive that before starting the rebuild you have successfully configured the secondary Reporting database and updated the connection strings accordingly as this is a must-to-have step, see more here.

The process of rebuilding the reporting database can take a very long time, this is a known fact on the big volumes of data. You can monitor its status on the RebuildReportingDB.aspx page by looking at the details in the Processed column, which shows how many entries have been processed. The EstimatedTotal column displays the total number of entries to be processed.

To improve the overall performance of the rebuild I suggest to revisit your configuration of the processing agents as you may need to increase the number of the aggregation agents/threads you run:

• Open the Sitecore.Analytics.Processing.Aggregation.Services.config file;
• Edit the maxAgents setting for the History Worker to specify the maximum number of threads that you want to run on the server.

Note, that the History Worker is the key agent involved in the historical data rebuild. To speed up the rebuild process you have to increase its maximum number of threads and then set it back to 1 again when the rebuild finishes. Read more here about processing agents.

Answer 2

Apart from the points mentioned by Kate, there was another issue that slowed the rebuilding of reporting database. I had to create a Sitecore ticket for this. There was a warning in the logs:

13164 17:20:50 WARN [Sitecore.CES.DeviceDetection.Providers.FiftyOneDegrees.DeviceInformationProvider51Degree] Could not initialize 51Degrees provider for database 'C:\intepub\wwwroot\sc_instance\App_Data\DeviceDetection\DeviceDetectionDB-221013051449-9F66307A483EDA1C5AD70B13546A8A71.db'
Exception: System.IO.EndOfStreamException
Message: Unable to read beyond the end of the stream.
Source: mscorlib
at System.IO.__Error.EndOfFile()
at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
at System.IO.BinaryReader.ReadInt32()
at FiftyOne.Foundation.Mobile.Detection.Factories.RootNodeFactory.Create(DataSet dataSet, Int32 index, Reader reader)
at FiftyOne.Foundation.Mobile.Detection.Entities.Memory.MemoryFixedList`2.Read(Reader reader)
at FiftyOne.Foundation.Mobile.Detection.DataSetBuilder.LoadForStreaming(IndirectDataSet dataSet, Dictionary`2 cacheConfiguration)
at FiftyOne.Foundation.Mobile.Detection.DataSetBuilder.BuildFromFile.Build(String filename)
at FiftyOne.Foundation.Mobile.Detection.Factories.StreamFactory.Create(String filePath, DateTime lastModified, Boolean isTempFile)
at Sitecore.CES.DeviceDetection.Providers.FiftyOneDegrees.ProviderFactory.CreateFromDatabase(String databasePath)
at Sitecore.CES.DeviceDetection.Providers.FiftyOneDegrees.DeviceInformationProvider51Degree.InitializeDatabase(String databaseFilePath)

Solution: Clear the contents of the App_Data\DeviceDetection folder. Sitecore will download the DeviceDetection database again.