Surendra Sharma

Surendra Sharma

Search This Blog

Monday, January 22, 2018

Sitecore: Rebuild Reporting Database

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.

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: