Sitecore Experience Platform

Rebuild the reporting database

Rebuilding the reporting database reprocesses the interactions that have already been aggregated into the reporting database. The rebuild process can be summarized as follows:

  1. Manually add a secondary reporting connection string to the processing server or servers.

  2. Issue a rebuild command from a Content Management server.

  3. Perform rebuild on one or more Processing servers depending on your architecture.

  4. Manually update all reporting connection strings to point to the newly re-built database.

Note

Rebuilding the reporting database does not rebuild the search index - this must be done separately.

Create and configure a secondary reporting database

Before you can rebuild the reporting database, you must manually attach and configure a secondary reporting database:

  1. Take a clean copy of the DACPAC file for the Sitecore_Reporting 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_Reporting DACPAC.

    Note

    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.

  4. On your Processing and Content Management servers, add the following connection string, substituting your own server details and the name you have chosen for your secondary database:

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

Note

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

Rebuilding with multiple processing servers

If you have multiple processing servers, all active processing servers must have access to the secondary reporting database, even if you have a dedicated server for history processing. This is because live data is being aggregated into the secondary reporting database, which means that agents responsible for interaction aggregation and contact processing need access.

Rebuild the reporting database

  1. In a web browser window, open the rebuild reporting database history processing page using the following path where <sitename> is the URL of your Content Management server:

              <sitename>/sitecore/admin/RebuildReportingDB.aspx
            
  2. Click Start to begin rebuilding the reporting database (synchronization processing).

Reconfigure reporting database connection strings

When the rebuild process is complete, you must manually swap your reporting and reporting.secondary databases. This means that the ‘live’ database will now be Sitecore_Reporting_Secondary and the original Sitecore_Reporting database will be commented out. The following table shows

Note

You must swap reporting connection strings on all roles that reference the xDB Reporting database.

Before Rebuild

During Rebuild

After Rebuild

reporting

Sitecore_Reporting

Sitecore_Reporting

Sitecore_Reporting_Secondary

reporting.secondary

<!– Commented Out –>

Sitecore_Reporting_Secondary

<!– Commented Out –>

Important

If you do not comment out or remove the reporting.secondary connection string, data is continually written to both databases.

Verify that the rebuild was successful

To verify that the reporting database was successfully rebuilt, open the Experience Analytics UI. Your graphs and tables should be populated with data.