Rebuild the reporting database

Important

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

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. Rebuild command issued from a Content Management server
  3. Rebuild performed by one or more Processing servers depending on your architecture
  4. Manually update all reporting connection strings to point to the newly re-built database

Create and configure 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_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.
  4. On your Processing and Reporting servers, add the following connection string, substituting your own server details and the name you have chosen for your secondary database:
<add name="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).

Verify that the rebuild was successful

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

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_Analytics_Secondary and the original Sitecore_Analytics database will be commented out:

  Before Rebuild During Rebuild After Rebuild
reporting Sitecore_Analytics Sitecore_Analytics Sitecore_Analytics_Secondary
reporting.secondary <!– Commented Out –> Sitecore_Analytics_Secondary <!– Commented Out –>

Important

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