A Better Reporting Rebuild UI

If you are running Sitecore's xDB, at some point you will likely need to rebuild the reporting database. The reporting rebuild process reads all of your raw web analytics data out of MongoDB, and aggregates the metrics into relational tables in SQL Server, where you can then pull some beautiful Experience Analytics reports.

There are a bunch of reasons you may need to rebuild your reporting database:

  • if your database gets out-of-sync with the MongoDB for some reason
  • if you've customized the reporting database or the code that runs it
  • if you've performed certain Sitecore upgrades
  • if you need to enrich historical xDB data

Sitecore's reporting rebuild process is a bit clunky. It's complicated to run properly and requires a lot of manual steps in Sitecore and in SQL Server. In particular, the tool Sitecore provides out-of-the-box for running the rebuild is kind of plain and non-informative. If you have many millions of interactions to process, it can take days for the whole rebuild process to run. Imagine staring at this screen for 3 straight days:



Since I need to run this tool pretty frequently, I decided to create a new user interface for the reporting rebuild tool which would help me monitor the rebuild progress. Now, I have some valuable information that actually makes sure everything is ready, tells me if the process is working as expected, and approximately how long it will take to complete:



Much nicer, right? Here is how you can upgrade your own reporting rebuild UI:

Create a new aspx web form in your <processing website>/Sitecore/Admin folder. Name it something like CustomReportingRebuild.aspx.

Here is the markup for the page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomReportingRebuild.aspx.cs" Inherits="Integryx.Web.admin.Integryx.ReportingRebuild.ReportingRebuild" %>

<!DOCTYPE html>
"
<html>
<head runat="server">
    <title>Integryx Rebuild Reporting Database</title>
    <link rel="shortcut icon" href="/sitecore/images/favicon.ico" />
    <link rel="Stylesheet" type="text/css" href="/sitecore/shell/themes/standard/default/WebFramework.css" />
    <style type="text/css">
        .wf-container
        {
            min-width: 950px;
            display: inline-block;
            width: auto;
        }

        .wf-content
        {
            padding: 2em 2em;
        }

        #wf-dropshadow-right
        {
            display: none;
        }
        
        table.main
        {
            border: 1px solid #ccc;
            border-collapse: collapse;
            font-family: Tahoma;
            font-size: 14pt;
            padding: 1em 1em;
        }
        
        table.main td
        {
            font-family: Tahoma;
            font-size: 14pt;
            border: 1px solid #ccc;
            padding: 5px;
        }

        table.main th
        {
            font-family: Tahoma;
            font-size: 14pt;
            text-align: center;
            border: 1px solid #ccc;
            font-weight: normal;
            padding: 5px;
        }

        .wf-configsection table th {
            background-color: #ccc;
        }
        
        td.datacell {
            text-align: right;
            white-space: nowrap;
        }

        table.main th.dataheader {
            text-align: center;
        }

        tr.groupheader {
            background-color: #bbb;
        }

        .top1 {
            background-image: url(/sitecore/shell/themes/Standard/Images/PipelineProfiling/font_char49_red_16.png);
            background-repeat: no-repeat;
            background-position: 5px 5px;
        }
        .top2 {
            background-image: url(/sitecore/shell/themes/Standard/Images/PipelineProfiling/font_char50_orange_16.png);
            background-repeat: no-repeat;
            background-position: 5px 5px;
        }
        .top3 {
            background-image: url(/sitecore/shell/themes/Standard/Images/PipelineProfiling/font_char51_yellow_16.png);
            background-repeat: no-repeat;
            background-position: 5px 5px;
        }

        table.main td.processor {
            padding-left: 30px;
        }
    </style>
</head>
<body>
    <form id="mainForm" runat="server" class="wf-container">
        <div class="wf-content">        
        <h1>Integryx Rebuild Reporting Database</h1>
        <br />
        Full instructions are <a href="#" target="_blank">HERE</a>.
        <asp:ScriptManager runat="server"></asp:ScriptManager>
        <asp:Timer ID="ProgressTimer" Interval="3000" runat="server" OnTick="ProgressTimer_Tick"></asp:Timer>
        <asp:UpdatePanel runat="server">
            <ContentTemplate>
                <br />
                <asp:TextBox runat="server" ID="txtRefreshSeconds" Text="3" Width="30px"></asp:TextBox><asp:Button runat="server" ID="btnSetRefreshRate" Text="Set Page Refresh Rate (seconds)" OnClick="btnSetRefreshRate_Click" />
                <asp:Label runat="server" ID="lblError" Visible="false" ForeColor="Red" Width="950" />
                <br />
                <br />
                <h3>Prerequisites</h3>
                <br />
                <asp:Panel ID="pnlCheckPrerequisites" runat="server">
                    <asp:Label ID="lblCheckPrerequisites" runat="server" Text="Checking prerequisites..." ForeColor="Orange" /> 
                    <br />
                    <br />
                </asp:Panel>
                <asp:Image ID="imgXdbEnabled" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblXdbEnabled" runat="server" Text="xDB is enabled" />
                <br />
                <asp:Image ID="imgReportingManagerEnabled" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblReportingManagerEnabled" runat="server" Text="ReportingManager is enabled" />
                <br />
                <asp:Image ID="imgMongoDBCheck" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblMongoDBCheck" runat="server" Text="Successful MongoDB connection" />
                <br />
                <asp:Image ID="imgRptDBCheck" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblRptDBCheck" runat="server" Text="Successful Primary DB connection" />
                <br />
                <asp:Image ID="imgSecDBCheck" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblSecDBCheck" runat="server" Text="Successful Secondary DB connection" />
                <br />
                <br />
                <h3>Reporting Database Rebuild</h3>
                <br />
                <br />
                <asp:Button runat="server" ID="btnStartProcess" OnClick="btnStartProcess_Click" Text="Start Reporting Rebuild" Width="250px" Enabled="False" />
                <asp:Button runat="server" ID="btnCancelReportingRebuild" OnClick="btnCancelReportingRebuild_Click" Text="Abort Reporting Rebuild" Width="250px" Enabled="False" />
                <br />
                <br />
                <asp:Image ID="imgProcessing" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblProcessing" runat="server" Text="Run the aggregation job." />
                <asp:Panel ID="pnlRebuildStatus" runat="server" Visible="false">
                    <br />
                    <br />
                    <table>
                        <tr>
                            <td>
                                Started:
                            </td>
                            <td style="text-align:right">
                                <asp:Label runat="server" ID="lblStartedAt" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Last Updated:
                            </td>
                            <td style="text-align:right">
                                <asp:Label runat="server" ID="lblLastChanged" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Total Interactions: </td><td style="text-align:right">
                                <asp:Literal ID="litTotalInteractions" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Processed: </td><td style="text-align:right">
                                <asp:Literal ID="litTotalProcessed" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Remaining: </td><td style="text-align:right">
                                <asp:Literal ID="litTotalRemaining" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                % Completed: </td><td style="text-align:right">
                                <asp:Literal ID="litPercentCompletion" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Hours: </td><td style="text-align:right">
                                <asp:Literal ID="litProcessingHours" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Avg. Interactions / Hour: </td><td style="text-align:right">
                                <asp:Literal ID="litAvgProcessedPerHour" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td>
                                Est. Hours Remaining: </td><td style="text-align:right">
                                <asp:Literal ID="litHoursRemaining" runat="server" />
                            </td>
                        </tr>
                    </table>
                </asp:Panel>
                <br />
                <br />
                <asp:Image ID="imgFinished" runat="server" ImageUrl="/sitecore/shell/Themes/Standard/images/error.png" style="position:relative;top:7px;height:25px;width:25px;" />
                  <asp:Label ID="lblFinished" runat="server" Text="Reporting rebuild completed." />
                <br />
                <br />
                <asp:Panel ID="pnlHistory" runat="server" Visible="false">
                    <asp:Repeater ID="rptHistory" runat="server" OnItemDataBound="rptHistory_ItemDataBound">
                        <HeaderTemplate>
                            <table>
                                <tr>
                                    <td colspan="4">
                                        <b>Processing History</b></td></tr><tr>
                                <td style="text-align:center">
                                    <u>Date/Time</u></td><td style="text-align:center">
                                    <u>Total Processed</u></td></tr></HeaderTemplate><ItemTemplate>
                            <tr>
                                <td style="text-align:center">
                                    <asp:Literal ID="litDateTime" runat="server" />
                                </td>
                                <td style="text-align:right">
                                    <asp:Literal ID="litTotalProc" runat="server" />
                                </td>
                            </tr>
                        </ItemTemplate>
                        <AlternatingItemTemplate>
                            <tr>
                                <td style="text-align:center">
                                    <asp:Literal ID="litDateTime" runat="server" />
                                </td>
                                <td style="text-align:right">
                                    <asp:Literal ID="litTotalProc" runat="server" />
                                </td>
                            </tr>
                        </AlternatingItemTemplate>
                        <FooterTemplate>
                            </table>
                        </FooterTemplate>
                    </asp:Repeater>
                </asp:Panel>
            </ContentTemplate>
            <Triggers>
                <asp:AsyncPostBackTrigger ControlID="ProgressTimer" EventName="Tick" />
            </Triggers>
        </asp:UpdatePanel>
    </div>
    </form>
</body>
</html>

And here is the code-behind:

using MongoDB.Driver;
using Sitecore;
using Sitecore.Analytics.Aggregation.History;
using Sitecore.Analytics.Aggregation.History.Remoting;
using Sitecore.Configuration;
using Sitecore.Diagnostics;
using Sitecore.sitecore.admin;
using Sitecore.Xdb.Configuration;
using Sitecore.Xml;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Xml;
 
namespace Integryx.Web.admin.Integryx.ReportingRebuild
{
    public partial class ReportingRebuild : AdminPage
    {
        #region Class Variables
 
        private long _totalInteractions = -1;
        private RebuildStatus RebuildStatus { get { return ReportingManager.GetRebuildStatus(); }}
        private IReportingStorageManager ReportingManager { get; set; }
        private const string CHECK_IMAGE_URL = "/sitecore/shell/Themes/Standard/images/check.png";
        private const string ERROR_IMAGE_URL = "/sitecore/shell/Themes/Standard/images/error.png";
        private const string IN_PROGRESS_IMAGE_URL = "/sitecore/shell/Themes/Standard/images/information.png";
 
        #endregion
 
        #region Page Events
 
        protected override void OnInit(EventArgs e)
        {
            base.CheckSecurity();
            bool flag = false;
            XmlNode configNode = Factory.GetConfigNode("reporting/remote");
 
            if (configNode != null)
            {
                flag = string.Equals(XmlUtil.GetAttribute("enabled", configNode), "true", StringComparison.InvariantCultureIgnoreCase);
            }
 
            if (!flag)
            {
                ReportingManager = Factory.CreateObject("aggregation/reportingStorageManager", true) as ReportingStorageManager;
            }
            else
            {
                ReportingManager = new ReportingStorageManagerProxy();
            }
 
            if (Session["integryx reporting rebuild page refresh rate"] != null)
            {
                txtRefreshSeconds.Text = Session["integryx reporting rebuild page refresh rate"].ToString();
            }
            else
            {
                Session["integryx reporting rebuild page refresh rate"] = 3;
            }
 
            Session["primary db checked"] = string.Empty;
            Session["secondary db checked"] = string.Empty;
 
            base.OnInit(e);
        }
 
        #endregion
 
        #region Button Events
 
        protected void btnStartProcess_Click(object sender, EventArgs e)
        {
            ResetLabels();
            SetProperty(1);
            StartRebuildJob();
            lblError.Visible = false;
            lblError.Text = string.Empty;
        }
 
        protected void btnCancelReportingRebuild_Click(object sender, EventArgs e)
        {
            ResetLabels();
            ReportingManager.CancelRebuild();
            SetProperty(0);
        }
 
        protected void btnSetRefreshRate_Click(object sender, EventArgs e)
        {
            ProgressTimer.Interval = int.Parse(txtRefreshSeconds.Text) * 1000;
            Session["integryx reporting rebuild page refresh rate"] = txtRefreshSeconds.Text;
        }
 
        #endregion
 
        #region Timer Events
 
        protected void ProgressTimer_Tick(object sender, EventArgs e)
        {
            SetPresentation();
 
            // Allow the UI to refresh between steps.
            if (Session["TimerCount"] == null || Session["TimerCount"].ToString() == "2")
            {
                Session["TimerCount"] = 0;
            }
            else
            {
                var tc = int.Parse(Session["TimerCount"].ToString());
                Session["TimerCount"] = tc + 1;
                return;
            }
 
            var currentStatus = GetProperty();
            switch (currentStatus)
            {
                case -1:
                case 0:
                    break;
                case 1:
                    if (RebuildStatus.Step.ToString() == "Completed")
                    {
                        SetProperty(2);
                    }
                    else if (RebuildStatus.Step.ToString() == "Failed")
                    {
                        HandleError("Rebuild FAILED", new Exception(RebuildStatus.Error));
                    }
                    break;
                case 2:
                    // Finished.
                    break;
            }
 
            SetPresentation();
        }
 
        #endregion
 
        #region Repeater Events
 
        protected void rptHistory_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                ((Literal)e.Item.FindControl("litDateTime")).Text = ((History)e.Item.DataItem).Month.ToString() + "/"
                    + ((History)e.Item.DataItem).Day.ToString() + " "
                    + ((History)e.Item.DataItem).Hour.ToString() + ":00";
                ((Literal)e.Item.FindControl("litTotalProc")).Text = ((History)e.Item.DataItem).Processed.ToString("###,###,###");
            }
        }
 
        #endregion
 
        #region Presentation
 
        private void SetPresentation()
        {
            ResetPresentation();
 
            // Check the prerequisites.
            var prerequisites = true;
 
            if (ReportingManager != null)
            {
                imgReportingManagerEnabled.ImageUrl = CHECK_IMAGE_URL;
                lblReportingManagerEnabled.ForeColor = System.Drawing.Color.Green;
            }
            else
            {
                prerequisites = false;
                imgReportingManagerEnabled.ImageUrl = ERROR_IMAGE_URL;
                lblReportingManagerEnabled.ForeColor = System.Drawing.Color.Red;
                return;
            }
 
            if (XdbSettings.Enabled)
            {
                imgXdbEnabled.ImageUrl = CHECK_IMAGE_URL;
                lblXdbEnabled.ForeColor = System.Drawing.Color.Green;
            }
            else
            {
                prerequisites = false;
                imgXdbEnabled.ImageUrl = ERROR_IMAGE_URL;
                lblXdbEnabled.ForeColor = System.Drawing.Color.Red;
                return;
            }
 
            var totInteractions = GetTotalInteractions();
            if (totInteractions > 0)
            {
                imgMongoDBCheck.ImageUrl = CHECK_IMAGE_URL;
                lblMongoDBCheck.ForeColor = System.Drawing.Color.Green;
                lblMongoDBCheck.Text = "Successful MongoDB connection (" + totInteractions.ToString("###,###,###") + ")";
            }
            else
            {
                prerequisites = false;
                imgMongoDBCheck.ImageUrl = ERROR_IMAGE_URL;
                lblMongoDBCheck.ForeColor = System.Drawing.Color.Red;
                lblMongoDBCheck.Text = "Successful MongoDB connection";
                return;
            }
 
            if (Session["primary db checked"].ToString() == "1" ||
                (ConfigurationManager.ConnectionStrings["reporting"] != null &&
                ConfigurationManager.ConnectionStrings["reporting"].ConnectionString != null &&
                CheckDatabaseConnection(ConfigurationManager.ConnectionStrings["reporting"].ConnectionString)))
            {
                Session["primary db checked"] = "1";
                imgRptDBCheck.ImageUrl = CHECK_IMAGE_URL;
                lblRptDBCheck.ForeColor = System.Drawing.Color.Green;
            }
            else
            {
                prerequisites = false;
                imgRptDBCheck.ImageUrl = ERROR_IMAGE_URL;
                lblRptDBCheck.ForeColor = System.Drawing.Color.Red;
                return;
            }
 
            if (Session["secondary db checked"].ToString() == "1" ||
                (ConfigurationManager.ConnectionStrings["reporting.secondary"] != null &&
                ConfigurationManager.ConnectionStrings["reporting.secondary"].ConnectionString != null &&
                CheckDatabaseConnection(ConfigurationManager.ConnectionStrings["reporting.secondary"].ConnectionString)))
            {
                Session["secondary db checked"] = "1";
                imgSecDBCheck.ImageUrl = CHECK_IMAGE_URL;
                lblSecDBCheck.ForeColor = System.Drawing.Color.Green;
            }
            else
            {
                prerequisites = false;
                imgSecDBCheck.ImageUrl = ERROR_IMAGE_URL;
                lblSecDBCheck.ForeColor = System.Drawing.Color.Red;
                return;
            }
 
            if (!prerequisites)
            {
                return;
            }
 
            var currentStatus = GetProperty();
            if (currentStatus == -1)
            {
                // Error
                btnStartProcess.Enabled = true;
            }
            if (currentStatus == 0)
            {
                // Error
                btnStartProcess.Enabled = true;
            }
            if (currentStatus > 0)
            {
                // Started
                btnCancelReportingRebuild.Enabled = true;
 
                switch (RebuildStatus.Step.ToString())
                {
                    case "HistoryProcessing":
                        imgProcessing.ImageUrl = IN_PROGRESS_IMAGE_URL;
                        lblProcessing.Text = "Run the aggregation job. (" + RebuildStatus.Step.ToString() + ")";
                        lblProcessing.ForeColor = System.Drawing.Color.Orange;
                        break;
                    case "Completed":
                        imgProcessing.ImageUrl = CHECK_IMAGE_URL;
                        lblProcessing.Text = "Run the aggregation job. (Completed)";
                        lblProcessing.ForeColor = System.Drawing.Color.Green;
                        break;
                    case "Failed":
                        imgProcessing.ImageUrl = ERROR_IMAGE_URL;
                        lblProcessing.Text = "Run the aggregation job. (Failed: " + RebuildStatus.Error + ")";
                        lblProcessing.ForeColor = System.Drawing.Color.Red;
                        lblError.Text = RebuildStatus.Error;
                        lblError.Visible = true;
                        break;
                    default:
                        imgProcessing.ImageUrl = IN_PROGRESS_IMAGE_URL;
                        lblProcessing.Text = "Run the aggregation job. (" + RebuildStatus.Step.ToString() + ")";
                        lblProcessing.ForeColor = System.Drawing.Color.Orange;
                        break;
                }
 
                pnlRebuildStatus.Visible = true;
                pnlHistory.Visible = true;
 
                UpdateStats();
            }
            if (currentStatus > 1)
            {
                btnStartProcess.Enabled = true;
                imgFinished.ImageUrl = CHECK_IMAGE_URL;
                lblFinished.Text = "Reporting rebuild completed!";
                lblFinished.ForeColor = System.Drawing.Color.Green;
            }
 
        }
 
        private void ResetPresentation()
        {
            // Buttons
            btnStartProcess.Enabled = false;
            btnCancelReportingRebuild.Enabled = false;
 
            // Labels
            lblProcessing.ForeColor = System.Drawing.Color.Gray;
            lblFinished.ForeColor = System.Drawing.Color.Gray;
 
            // Images
            imgProcessing.ImageUrl = ERROR_IMAGE_URL;
            imgFinished.ImageUrl = ERROR_IMAGE_URL;
 
            // Panels
            pnlRebuildStatus.Visible = false;
            pnlHistory.Visible = false;
            pnlCheckPrerequisites.Visible = false;
        }
 
        private void ResetLabels()
        {
            lblError.Text = string.Empty;
            lblProcessing.Text = "Run the aggregation job.";
            lblFinished.Text = "Reporting rebuild completed.";
        }
 
        private void UpdateStats()
        {
            var rebuildStatus = RebuildStatus;
            var isActive = rebuildStatus.IsActive;
            lblStartedAt.Text = ResolveTimeString(rebuildStatus.Started);
            lblLastChanged.Text = ResolveTimeString(rebuildStatus.LastChanged);
 
            try
            {
                List<History> history = new List<History>();
 
                using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["reporting.secondary"].ConnectionString))
                using (SqlCommand dbCommand = new SqlCommand("[dbo].[Integryx_Get_ReportingRebuildStats]", dbConnection))
                {
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    dbCommand.Parameters.AddWithValue("@TotalInteractions", GetTotalInteractions());
                    dbCommand.CommandTimeout = 5;
                    dbConnection.Open();
 
                    SqlDataReader reader = dbCommand.ExecuteReader();
 
                    while (reader.Read())
                    {
                        history.Add(
                            new History()
                            {
                                Month = (int)reader[0],
                                Day = (int)reader[1],
                                Hour = (int)reader[2],
                                Processed = (double)reader[3]
                            });
                    }
 
                    rptHistory.DataSource = history;
                    rptHistory.DataBind();
 
                    reader.NextResult();
 
                    while (reader.Read())
                    {
                        litTotalInteractions.Text = GetTotalInteractions().ToString("###,###,###");
                        litTotalProcessed.Text = ((double)reader[0]).ToString("###,###,###");
                        litTotalRemaining.Text = (Math.Round((double)reader[1], 2)).ToString("###,###,###");
                        litPercentCompletion.Text = (Math.Round((double)reader[2], 2)).ToString() + "%";
                        litProcessingHours.Text = ((double)reader[3]).ToString("###,###,###");
                        litAvgProcessedPerHour.Text = ((double)reader[4]).ToString("###,###,###");
                        litHoursRemaining.Text = (Math.Round((double)reader[5], 2)).ToString("###,###,###");
                    }
 
                }
            }
            catch { }
        }
 
        #endregion
 
        #region Utils
 
        private void StartRebuildJob()
        {
            if (!RebuildStatus.IsActive)
            {
                ReportingManager.Rebuild();
            }
            SetProperty(1);
        }
 
        private int GetProperty()
        {
            var result = -1;
 
            try
            {
                using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["core"].ConnectionString))
                using (SqlCommand dbCommand = new SqlCommand(string.Empty, dbConnection))
                {
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = "SELECT TOP 1 [Value] FROM [core].[dbo].[Properties] WHERE [Key] = 'Integryx_ReportingRebuild'";
                    dbCommand.CommandTimeout = 5;
                    dbConnection.Open();
                    result = System.Convert.ToInt32(dbCommand.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                HandleError("Error getting property", ex);
 
            }
 
            return result;
        }
 
        private void SetProperty(int value)
        {
            try
            {
                using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["core"].ConnectionString))
                using (SqlCommand dbCommand = new SqlCommand(string.Empty, dbConnection))
                {
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = "DELETE FROM [core].[dbo].[Properties] WHERE [Key]='Integryx_ReportingRebuild';" +
                                            "INSERT INTO [core].[dbo].[Properties] ([ID], [Key], [Value]) VALUES(NEWID(), 'Integryx_ReportingRebuild', '" + value + "')";
                    dbCommand.CommandTimeout = 5;
                    dbConnection.Open();
                    dbCommand.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                HandleError("Error setting property", ex);
            }
        }
 
        private string ResolveTimeString(DateTime utcDateTime)
        {
            return DateUtil.ToServerTime(utcDateTime).ToString("yyyy-MM-dd HH:mm:ss \"GMT\"zzz");
        }
 
        private long GetTotalInteractions()
        {
            try
            {
                if (_totalInteractions < 1)
                {
                    var connectionString = ConfigurationManager.ConnectionStrings["analytics"].ConnectionString;
                    var server = MongoServer.Create(connectionString);
                    var database = server.GetDatabase("analytics");
                    var collection = database.GetCollection("Interactions");
 
                    _totalInteractions = collection.Count();
                }
 
                return _totalInteractions;
            }
            catch (Exception ex)
            {
                HandleError("Error connecting to MongoDB", ex);
            }
 
            return 0;
        }
 
        private void HandleError(string errMsg, Exception ex)
        {
            Log.Error(errMsg, ex, this);
            lblError.Visible = true;
            lblError.Text = errMsg + ": " + ex.Message;
 
            var state = GetProperty();
 
            // Don't halt the rebuild process on errors. Just log it and move on.
            if (RebuildStatus.Step.ToString() != "HistoryProcessing" || state == 5)
            {
                SetProperty(-1);
            }
        }
 
        private bool CheckDatabaseConnection(string connStr)
        {
            using (var l_oConnection = new SqlConnection(connStr))
            {
                try
                {
                    l_oConnection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
        }
 
        #endregion
 
        #region Models
 
        public class History
        {
            public int Month;
            public int Day;
            public int Hour;
            public double Processed;
        }
 
        #endregion
 
    }
}

To make it work, you'll also need to add a few stored procedures to your primary and secondary reporting databases. Note, you need to update the scripts with your database names before they will execute.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David Ruckman/Integryx
-- Description:	Used by CustomReportingRebuild.aspx - gets the rebuild statistics.
-- =============================================
CREATE PROCEDURE [dbo].[Integryx_Get_ReportingRebuildStats] 
	@TotalInteractions FLOAT
AS
BEGIN
	SET NOCOUNT ON;

    SELECT 
		CAST(DATEPART(MONTH, Processed) AS int) AS [Month], 
		CAST(DATEPART(DAY, Processed) AS int) AS [Day], 
		CAST(DATEPART(HOUR, Processed) AS int) AS [Hour], 
		CAST(COUNT(*) AS float) AS [Processed Interactions]
	FROM 
		<secondary reporting db name>.dbo.Trail_Interactions
	GROUP BY 
		DATEPART(MONTH, Processed), 
		DATEPART(DAY,Processed), 
		DATEPART(HOUR,Processed)
	ORDER BY 
		DATEPART(MONTH, Processed), 
		DATEPART(DAY,Processed), 
		DATEPART(HOUR,Processed)

	SELECT
		CAST(COUNT(*) AS float) AS [Total Processed],
		CAST(@TotalInteractions - COUNT(*) AS float) AS [Remaining],
		CAST(((COUNT(*) * 100) / @TotalInteractions) as float) AS [Percentage Processed],
		CAST(DATEDIFF(hh, MIN(Processed), MAX(Processed)) + 1 AS float) AS [Current Processing Time (Hours)], 
		CAST(COUNT(*) / (DATEDIFF(hh, min(Processed), MAX(Processed)) + 1) AS float) AS [Average Processed Per Hour],
		CAST((@TotalInteractions - COUNT(*)) / (COUNT(*) / (DATEDIFF(hh, MIN(Processed), MAX(Processed)) + 1)) AS float) AS [Estimated Hours Remaining]
	FROM 
		<secondary reporting db name>.dbo.Trail_Interactions

END

SET ANSI_NULLS ON

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David Ruckman/Integryx
-- Description:	Sets a property.
-- =============================================
CREATE PROCEDURE [dbo].[Integryx_GetProperty] 
AS
BEGIN
	DECLARE @RecordCount int
	SET @RecordCount = (SELECT COUNT(*) FROM Properties WHERE [Key]='Integryx_ReportingRebuild')
	IF (@RecordCount = 0)
		BEGIN
			INSERT INTO Properties ([Key], [Value]) VALUES('Integryx_ReportingRebuild', '0')
		END
	SELECT [Value] FROM Properties WHERE [Key]='Integryx_ReportingRebuild'
END

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David Ruckman
-- Description:	Sets a property.
-- =============================================
CREATE PROCEDURE [dbo].[Integryx_SetProperty] 
	@Value varchar(12)
AS
BEGIN
	DECLARE @RecordCount int
	SET @RecordCount = (SELECT COUNT(*) FROM Properties WHERE [Key]='Integryx_ReportingRebuild')
	IF (@RecordCount = 0)
		BEGIN
			INSERT INTO Properties ([PropertyId], [Key], [Value]) VALUES(NEWID(), 'Integryx_ReportingRebuild', '0')
		END
	ELSE
		BEGIN
			UPDATE Properties SET [Value]=@Value WHERE [Key]='Integryx_ReportingRebuild'
		END
END

GO

After these steps, compile and deploy your solution, and the tool is ready to run.

As always, there are many ways to accomplish these things, and this is just my way of doing it. Please share your ideas, and thoughts on how I might improve this tool.

Happy rebuilding!

~David

Add comment

Loading