Walkthrough: Rebuilding the reporting database

Abstract

Follow the steps in this walkthrough to rebuild the reporting database.

The Sitecore Experience Database (xDB) uses two database systems (SQL Server and MongoDB) that need to be kept in sync to provide accurate data for Sitecore reporting applications:

  • MongoDB collection database - stores all experience data

  • SQL Server reporting database - stores aggregated reporting data

Sitecore automatically keeps these databases in sync but there are certain circumstances when you may need to perform a complete rebuild of the reporting database. For example, after you have run the Sitecore Analytics Conversion Tool and have additional historical data that you want to include in your reporting applications. There are also several other reasons for rebuilding the reporting database.

When you rebuild the reporting database, to avoid disruption during the rebuild process, you connect a secondary reporting database to store all aggregated data as it is reprocessed.

This walkthrough describes how to:

Note

To rebuild the reporting database in xDB Cloud, see REST API reference for xDB Cloud service.

Check prerequisite databases

Before you start the rebuild process, first check that you have the MongoDB analytics database and the SQL Server reporting database connected. These databases are part of a standard Sitecore installation. You only need to create and configure a secondary reporting database if you intend perform the rebuild process.

Each database has a separate connection string that you can configure in the App_Config/ConnectionStrings.config file:

  • Collection database (MongoDB) – can contain historical data from a converted Sitecore DMS SQL Server database and new data collected from your website (live data).

    MongoDB collection database connection string:

    <add name="analytics" connectionString="mongodb://localhost/analytics"/>

  • Primary reporting database (SQL Server) – contains all the live data collected on your website after upgrading to a newer version of Sitecore.

    Primary reporting database connection string:

    <add name="reporting" connectionString="user 
id=_sql_server_user_;password=_user_password_;Data 
Source=_sqlserver_;Database=Sitecore_Reporting" />

  • Secondary reporting database (SQL Server) stores historical data during the rebuilding of the reporting database. See the next section for detailed instructions on how to create and connect a secondary reporting database.

    Secondary reporting database connection string:

    <add name="reporting.secondary" connectionString="user 
id=_sql_server_user_;password=_user_password_;Data 
Source=_sqlserver_;Database= Sitecore_Reporting_Secondary" />

Create and connect a second reporting database

The first step in the rebuild process is to create and then connect a secondary reporting database.

To connect and configure a secondary reporting database:

  1. Take a clean copy of the DACPAC file for the Sitecore_Analytics database from your Sitecore distribution to use as your secondary reporting database. For best results, always use a clean copy.

  2. Create an empty database to be used for the Analytics secondary. If you are using Azure SQL then you will need to create a new SQL Azure database in the Azure Portal. Make sure to update the Firewall settings on the associated Azure SQL Server to have your Client IP Address, you should remove this IP address from the Firewall when the rebuild process is complete.

  3. In SQL Server Management Studio, connect to the SQL Server or Azure SQL database instance and deploy the Sitecore_Analytics DACPAC. Use the name reporting.secondary in the connection string.

    Note

    The reporting and secondary reporting databases can take up significant disk space so you may need to plan for extra storage requirements.

  4. If you are running any Sitecore modules, such as WFFM, run the SQL script that adds the Fact tables for those modules against the secondary reporting database.

  5. Add or edit the reporting.secondary connection string to point to the newly created database. For example:

    <add name="reporting.secondary" connectionString="user 
id=_sql_server_user_;password=_user_password_;Data 
Source=_sqlserver_;Database=Sitecore_Reporting_Secondary" />

    Note

    If you have installed the Sitecore upgrade package, you may have already added a secondary reporting database, and if so, you do not need to perform this step twice.

  6. If you are configuring a dedicated server, check that you have enabled the history processing pool in the appropriate configuration file.

    For example, to enable history processing on a dedicated content delivery server, navigate to the following processing configuration file:

    App_Config/Include/Sitecore.Analytics.Processing.Aggregation.ProcessingPools.config

    In this config file, make sure the <Enabled> parameter is set to true: /configuration/sitecore/aggregationProcessing/processingPools/history/Enabled

    <history type="Sitecore.Analytics.Data.MongoDb.ProcessingPool.MongoDbProcessingPool, 
  Sitecore.Analytics.MongoDb" singleInstance="true" >
  <param desc="connectionStringName">tracking.history</param>
  <Name>history</Name>
  <Enabled>true</Enabled>
</history>

Change the time to clear storage setting

In the latest version of Sitecore xDB, the primary SQL Server reporting database contains some additional marketing definition tables that you need to copy to the secondary reporting database. When you run the rebuild reporting database page ensure that you allow more time for the clear storage process while these tables are being copied.

The default time to clear storage setting is 1 minute. Change this setting to a time interval appropriate for your Sitecore solution, for example, 10 minutes.

To change the TimeToClearStorage setting:

  1. Open the Sitecore.Analytics.Processing.Aggregation.config file.

  2. Change the TimeToClearStorage setting to an appropriate time. For example 10 minutes.

    <reportingStorageManager type="Sitecore.Analytics.Aggregation.History.ReportingStorageManager" singleInstance="true">
  <TimeToClearStorage>0.00:01:00</TimeToClearStorage>

Run the Rebuild Reporting Database page

To rebuild the reporting database:

  1. In a web browser window, open the rebuild reporting database history processing page using the following path:

    <sitename>/sitecore/admin/RebuildReportingDB.aspx

    Note

    You can only run the RebuildReportingDB.aspx and RedeployMarketingData.aspx pages from a content management server.

  2. Choose the necessary rebuild targets and click Start to begin rebuilding the reporting database (synchronization processing). Sitecore supports two rebuilding targets: Rebuild DB and Rebuild Index. By default, the rebuild process rebuilds both targets.

    Sitecore 8.2 Update 6 and later supports Time Slice Aggregation. This makes it possible to rebuild only part of the data in the collection database. You can, for example, run the rebuild process for the last three months only. To do this, you enter the SaveDateTime value of the oldest interaction you want:

    75C0BECD09E2491CBC2E4C1E0868B5F7.png

    After the rebuild, the rebuilding target only contains data from the Minimum SaveDateTime until the present time.

  3. In the Rebuild Reporting Database page, when you see Waiting to receive to data status, copy the following marketing definition tables from the primary to the secondary reporting database:

    • CampaignActivityDefinitions

    • GoalDefinitions

    • OutcomeDefinitions

    • MarketingAssetDefinitions

    • Taxonomy_TaxonEntity

    • Taxonomy_TaxonEntityFieldDefinition

    • Taxonomy_TaxonEntityFieldValue

    Important

    It is important that you add these additional tables to the secondary reporting database while the Waiting to receive to data status is displayed in Rebuild Reporting Database page. Ensure that you have also changed the TimeToClearStorage setting in the Sitecore.Analytics.Processing.Aggregation.config file to allow sufficient time for these tables to be added.

    After you have added these tables the Rebuild Reporting Database page resumes the rebuilding process.

  4. The rebuild reporting database tool provides feedback while it is processing until the rebuild process is completed:

    2D054E10980F4BCABAE810EB50E71E71.png

    In Sitecore 8.2, Update 6 and later, the feedback looks like this:

    Walkthrough_Rebuilding_the_reporting_database-Picture_3-rId14-1840614501.png

    Note

    When you run the rebuild process (synchronization), this erases all information in the Sitecore_Reporting_Secondary database. Therefore, you should always use a clean copy of the database when you rebuild the reporting database because this speeds up the rebuild process and uses fewer resources.

Reconfigure reporting connections strings

After the rebuild process has finished, you need to reconfigure the reporting and reporting secondary connection strings:

  1. Navigate to the Website/App_Config folder, and change the names in the ConnectionStrings.config file Database= attribute, so that the reporting connection string points to the Sitecore_Reporting_Secondary database (this has replaced the Sitecore_Reporting database).

  2. Comment out the reporting.secondary connection string.

    The following samples show the connection strings after swapping the database names and commenting out the reporting.secondary connection string:

    <add name="reporting" connectionString="user 
id=_sql_server_user_;password=_user_password_;Data 
Source=_sqlserver_;Database=Sitecore_Reporting_Secondary" />

    <!--
<add name="reporting.secondary" connectionString="user 
id=_sql_server_user_;password=_user_password_;Data 
Source=_sqlserver_;Database=Sitecore_Reporting2" />
-->

    The xDB now uses the Sitecore_Reporting_Secondary database as its primary reporting database to collect new, live data from your website. The Sitecore_Reporting2 database is disabled until you need to enable it again the next time you rebuild the reporting database.

    Note

    Sitecore_Reporting2 is an example name that you can use for the clean reporting database next time you rebuild the reporting database.

  3. To verify that the rebuild has been successful, open the Experience Analytics reporting application and view reports that show the most recent visits to your website. The visits shown in these reports should correspond to the historical data that came from the MongoDB collection database.

    Note

    If you encounter problems, you can easily swap back the connection strings to restore the previous version of the reporting database.