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:
-
Create and configure a secondary reporting database.
-
Rebuild the reporting database.
-
Reconfigure reporting database connection strings.
-
Verify that the rebuild was successful.
Rebuilding the reporting database does not rebuild the search index - you must do this separately.
Create and configure a secondary reporting database
Before you can rebuild the reporting database, you must manually add a secondary reporting connection string to the processing server or servers.
The reporting and secondary reporting databases can take up significant disk space so you might want to plan for extra storage requirements.
To create and configure a secondary reporting database:
-
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.
-
Create an empty database for the Reporting secondary database. If you are using Azure SQL then you must create a new SQL Azure database in the Azure Portal. You must update the Firewall settings on the associated Azure SQL Server with your Client IP Address (and you must remove this IP address from the Firewall when the rebuild process is complete).
-
In SQL Server Management Studio, connect to the SQL Server or Azure SQL database instance and deploy the Sitecore_Reporting DACPAC.
NoteIf 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.
-
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"/>
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
The rebuild process is semi-automated but also requires an administrator to attach, detach, or replace databases in SQL Server, as well as modify some configuration files.
To rebuild the reporting database:
-
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:
RequestResponse<sitename>/sitecore/admin/RebuildReportingDB.aspx
-
To begin rebuilding the reporting database (synchronization processing), click Start .
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 is the Sitecore_Reporting_Secondary database and the original Sitecore_Reporting database is commented out.
To reconfigure reporting database connection strings:
-
Swap the reporting and reporting secondary databases.
-
Comment out or remove the reporting.secondary connection string.
The following table shows the connection string for each database before, during, and after the rebuild process.
You must swap reporting connection strings on all roles that reference the xDB Reporting database.
Database |
Before rebuild |
During rebuild |
After rebuild |
---|---|---|---|
reporting |
Sitecore_Reporting |
Sitecore_Reporting |
Sitecore_Reporting_Secondary |
reporting.secondary |
NA |
Sitecore_Reporting_Secondary |
<– Commented Out –> |
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 and check that your graphs and tables are populated with data.
The rebuild process for Path Analyzer historical data is triggered when the connection strings are reconfigured and swapped. The schedule for building the map data depends on the PathAnalyzer.Aggregation.MapDeployDateOffsetPadding
setting in the Sitecore.PathAnalyzer.config
file.