Rich Tips - How to Rebuild your Sitecore Analytics Table

Posted on May 17, 2016 in Technical  | No comments


Author: Richard Hauer Date: 18/05/2016

If your Sitecore environment is tracking and recording analytics, but the Processing isn’t populating the fact tables, it might be because the Segments have not been properly deployed.


I’m not sure how it happened, but one day all the Segments disappeared from the Analytics (Reporting) DB, and this killed all the processing in my Sitecore instance.

The Segments are re-coded in Sitecore and “deployed” to the Analytics DB using Workflow tasks. There’s probably a dozen reasons why this didn’t work, but here is how you can repopulate it.

Making it Happen

The code below was created by pulling apart the Workflow Task and re-constituting it into a “run-once” ASPX that I dropped into the Sitecore instance and ran. 

Once this is run we need to reset the DeployDate on each row so that the processing server goes back for the historical data. You need to have a SQL session open and ready to go to minimise the chance that processing happens between the execution of the 2 steps.

Begin by deleting all the rows in the Segments table – we’re going to re-create them anyway. I would have deleted all the data from the Fact tables as well, if I’d had any.

Open a browser to the Sitecore Cache Reset page cause we’ll need that in a sec.
Copy the following into a “.aspx” and copy the URL into the Windows copy buffer so you can just paste it into the browser.  Remember we’re trying to do all 3 steps as quickly as possible.


<% @ Page Language="C#" 															%>
<% @ Import Namespace="Sitecore" 													%>
<% @ Import Namespace="Sitecore.Analytics.DataAccess" 								%>
<% @ Import Namespace="Sitecore.Configuration" 										%>
<% @ Import Namespace="Sitecore.Data" 												%>
<% @ Import Namespace="Sitecore.Data.Items" 										%>
<% @ Import Namespace="Sitecore.Diagnostics" 										%>
<% @ Import Namespace="Sitecore.ExperienceAnalytics.Api" 							%>
<% @ Import Namespace="Sitecore.ExperienceAnalytics.Client" 						%>
<% @ Import Namespace="Sitecore.ExperienceAnalytics.Core.Diagnostics" 				%>
<% @ Import Namespace="Sitecore.ExperienceAnalytics.Core.Repositories.Contracts" 	%>
<% @ Import Namespace="Sitecore.ExperienceAnalytics.Core.Repositories.Model" 		%>
<% @ Import Namespace="Sitecore.Globalization" 										%>
<% @ Import Namespace="Sitecore.Pipelines" 											%>
<% @ Import Namespace="Sitecore.StringExtensions" 									%>
<% @ Import Namespace="Sitecore.Web.UI.Sheer" 										%>
<% @ Import Namespace="Sitecore.Workflows" 											%>
<% @ Import Namespace="Sitecore.Workflows.Simple" 									%>
<% @ Import Namespace="Sitecore.Sites" 												%>

	ISegmentRepository segmentRepository = ClientContainer.Repositories.GetSegmentRepository();
	var allSegments = segmentRepository.GetAll();
	var i=1;
	foreach ( var segment in allSegments )
		Response.Write( (i++) + "/" + allSegments.Count() + "  Processing : " + segment.Title  + ": " );
		Item item = Config.MasterDatabase.GetItem(new ID(segment.Id));
		if ( item.TemplateID != Sitecore.ExperienceAnalytics.Client.Globals.Templates.Segment )
			Response.Write( "Not a segment. Skipping.<br />" );
		Response.Write( "Re-deployed<br />" );

You probably don’t need all those Imports, but I haven’t the time to figure out which ones you don’t need (and it’s free to put them in!).

In the SQL Server connection open a new query window and paste in the following:

UPDATE [Segments] SET DeployDate = '2000-01-01 00:00:00'

The goal there is to set the date before your first data.

Once you’re ready to go, execute as quick as you can in the following order:

  1. Load the web page that re-deploys the Segments
  2. Execute the SQL
  3. Clear the Sitecore cache

If it’s possible to stop your Sitecore processing server while you do all of the above, then more power to you; you’ll have the luxury of extra clicking time.

That’s it.  (really wasn't that hard was it?)

You should notice the processing server picking up segments to process after a short while.