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:

https://doc.sitecore.net/sitecore_experience_platform/81/setting_up_and_maintaining/xdb/server_considerations/walkthrough_rebuilding_the_reporting_database

http://sitecorelabs.blogspot.in/2016/01/sitecore-80-rev-150621-rebuild.html