I have to
rebuild Sitecore reporting databases for one of the client. I take references
of different sites to complete this task as no sites provided end to end
details. So I clubbed all these steps that worked for me.
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 we may need sync them manually, this
operation is called rebuild of the
reporting database.
Following are the steps to do this operation:
Let us assume, name of website is WebsiteName,
1. First,
make a new instance of Sitecore and get its Analytics database from SQL Server.
Attach this database as new database to the WebsiteName SQL
Server with name as WebsiteName_Analytics_Secondary.
2. Make
sure that the owner of this database is the user of WebsiteName_Analytics database.
3. 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.
(I
skipped this step)
·
Get the WFFM post installation
SQL script (WFFM_Analytics.sql) and run it against the analytic database. If
your website has installed the WFFM this SQL script can be found inside [site
root]\Website\Data
·
Get the ECM post installation
SQL script (Sitecore.EmailExperienceManager.sql) and run it against the
analytic database.
·
Download and execute
the WFFM.StoredProcesdure.AddFormStatisticsByContact.sql from https://kb.sitecore.net/en/Articles/2015/06/23/16/27/282795.aspx against the analytic database and do the requested
change in the Sitecore.Analytics.Processing.Aggregation.config file.
4.
In file [site
root]\Website\App_Config\ConnectionStrings.config, set the reporting.secondary property
to the newly attached database as follows:
<add name = "reporting.secondary" connectionString
= "user id = sql_server_user; password
= user_password; Data
Source = sql_server; Database
= WebsiteName_Analytics_Secondary"
/>
5.
Set the time to clear the storage
setting in file
[site
root]\Website\App_Config\IncludeSitecore.Analytics.Processing.Aggregation.config as
follows:
<reportingStorageManager type="Sitecore.Analytics.Aggregation.History.ReportingStorageManager" singleInstance="true">
<TimeToClearStorage>0.00:05:00</TimeToClearStorage>
Note: Within this time we have to run some SQL scripts against the analytic database. So make sure set time is enough (usually 5-10 minutes) that is needed.
6. Delete
the content inside [site
root]\Data\indexes\sitecore_analytics_index folder.
7. Restart
the IIS website. (I skipped this step)
8. Comment
the reporting.secondary connection string in file
[site root]\Website\App_Config\ConnectionStrings.config.
9. Ensure
that the dbo.Segments table in WebsiteName_Analytics
database is not empty, by running: SELECT
* FROM [WebsiteName_Analytics].[dbo].[Segments]
If this table is empty, then in
web browser window, open link: <sitename>/sitecore/admin/redeploymarketingdata.aspx
page and click the Redeploy Segments button.
10. Change
the Deploy Date for the segment definitions in the Segments table of your Analytics
(reporting) database to the date earlier than the first visit from your
historic data was performed by running following query:
UPDATE [WebsiteName_Analytics].[dbo].[Segments]
SET [DeployDate] = '2015-02-05 09:09:45.500'
Note: if the deploy date is set to earlier date, then this step is already done.
Note: if the deploy date is set to earlier date, then this step is already done.
11. Uncomment
the reporting.secondary connection string in file
[site
root]\Website\App_Config\ConnectionStrings.config.
12. Make sure the following files in [site root]\Website\App_Config\Include folder are enabled.
·
Sitecore.Analytics.Processing.Aggregation.Services.config
·
Sitecore.Analytics.Processing.Services.config
13. In
the web browser window, open the rebuild reporting database history processing
page using the link: <sitename>/sitecore/admin/RebuildReportingDB.aspx
14. Click Start to
begin rebuilding the reporting database (synchronization processing).
15. In the Rebuild Reporting Database page, when
you see "WaitReadyToReceiveData" status, copy the following marketing
definition tables from the primary (WebsiteName _Analytics) to the secondary (WebsiteName_Analytics_Secondary) reporting database:
i.
CampaignActivityDefinitions
ii.
GoalDefinitions
iii.
OutcomeDefinitions
iv.
MarketingAssetDefinitions
v.
Taxonomy_TaxonEntity
vi.
Taxonomy_TaxonEntityFieldDefinition
vii.
Taxonomy_TaxonEntityFieldValue
Run
following query to do this:
INSERT INTO WebsiteName_Analytics_Secondary.dbo.CampaignActivityDefinitions
SELECT *
FROM WebsiteName_Analytics.dbo.CampaignActivityDefinitions ;
INSERT INTO WebsiteName_Analytics_Secondary.dbo.GoalDefinitions
SELECT *
FROM WebsiteName_Analytics.dbo.GoalDefinitions ;
INSERT INTO WebsiteName_Analytics_Secondary.dbo.OutcomeDefinitions
SELECT *
FROM WebsiteName_Analytics.dbo.OutcomeDefinitions ;
INSERT INTO WebsiteName_Analytics_Secondary.dbo.MarketingAssetDefinitions
SELECT *
FROM WebsiteName_Analytics.dbo.MarketingAssetDefinitions ;
INSERT INTO WebsiteName_Analytics_Secondary.dbo.Taxonomy_TaxonEntity
SELECT *
FROM WebsiteName_Analytics.dbo.Taxonomy_TaxonEntity ;
INSERT INTO WebsiteName_Analytics_Secondary.dbo.Taxonomy_TaxonEntityFieldDefinition
SELECT *
FROM WebsiteName_Analytics.dbo.Taxonomy_TaxonEntityFieldDefinition ;
INSERT INTO WebsiteName_Analytics_Secondary.dbo.Taxonomy_TaxonEntityFieldValue
SELECT *
FROM WebsiteName_Analytics.dbo.Taxonomy_TaxonEntityFieldValue ;
16. The rebuild reporting database tool provides
feedback while it is processing until the rebuild process is completed. Wait till "Process State: Completed".
17. Change "reporting" connecting
string "Database" attribute to Secondary reporting database and
comment "reporting.secondary" connection string as follows:
<add name = "reporting" connectionString =
"user id = sql_server_user; password
= user_password; Data
Source = sql_server; Database
= WebsiteName_Analytics_Secondary"
/>
<!-- <add name =
"reporting.secondary" connectionString = "user id = sql_server_user; password = user_password; Data Source = sql_server; Database = WebsiteName_Analytics_Secondary" /> -->
The
xDB now uses the ‘WebsiteName_Analytics_Secondary’ database as its
primary reporting database to collect new, live data from your website.
18. To verify that the
reporting database was successfully rebuilt, open the Experience Analyltics UI.
Your graphs and tables should be populated with data with the most recent
visits to your website.
19. Do the following steps after status get Completed. Check whether the dbo.TreeDefinitions
table of your reporting database contains any records.
If this table is empty, then copy data from the
TreeDefinitions table of the new instance Sitecore reporting (Analytics)
database.
I hope these
steps help you to rebuild reporting database.
References:
