Sitecore 8.0 rev 150621 - Rebuild Reporting Database

When I'm working with our Sitecore integrated website, I noticed that the recent visitor details are not getting recorded in the Sitecore Experience Profile. The reason is to this is the Sitecore reporting database has stopped working or broken.

These are the steps that I followed to rebuild the Sitecore reporting database.

Pre-Steps

1. Get a fresh Sitecore analytics database from a fresh Sitecore instance. Attach it to the SQL Server using a proper name.

2. 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

3. Get the ECM post installation sql script (Sitecore.EmailExperienceManager.sql) and run it against the analytic database.

4. 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.

5. Set db_owner user permission to the user for the analytic database.

6. Change the ConnectionString.config. Set the reporting.secondary property to the newly attached analytic database. Keep the reporting database to the existing database.

7. Change the time to clear the storage setting in Sitecore.Analytics.Processing.Aggregation.config file as bellow.

<reportingStorageManager type="Sitecore.Analytics.Aggregation.History.ReportingStorageManager" singleInstance="true">
        <TimeToClearStorage>0.00:05:00</TimeToClearStorage>

NOTE: Within this time we have to run couple of sql scripts against the analytic database. So make sure you set the time you need. (Rebuilding Steps 3)

Default : <TimeToClearStorage>0.00:01:00</TimeToClearStorage>

8. Delete content inside <DataFolder>/indexes/sitecore_analytics_index folder.

9. Restart the IIS website.

10. Ensure that the Segments table is not empty in current "reporting" database by running SELECT * FROM Segments against the current reporting database.

NOTE: If this table is empty, please copy data from a clean Sitecore reporting database or run /sitecore/admin/redeploymarketingdata.aspx

Example Query :
INSERT INTO DestinationDB..Segments
SELECT * FROM SourceDB..Segments
https://community.sitecore.net/developers/f/9/t/1028

11. Manually change the Deploy Date for the segment definitions in the Segments table of your reporting database to the date earlier than the first visit from your historic data was performed.

NOTE: if the deploy date is set to earlier year/date, then this step is already done.

12. Make sure the following config files are enabled.

• Sitecore.Analytics.Processing.Aggregation.Services.config
• Sitecore.Analytics.Processing.Services.config

Rebuilding Steps

1. open the rebuild reporting database history processing page in new browser window using the link: <hostname>/sitecore/admin/RebuildReportingDB.aspx

2. Click Start.

3. In the Rebuild Reporting Database page, when you see "WaitReadyToReceiveData" status, copy the following marketing definition tables from the primary to the secondary reporting database:

• CampaignActivityDefinitions
• GoalDefinitions
• OutcomeDefinitions
• MarketingAssetDefinitions
• Taxonomy_TaxonEntity
• Taxonomy_TaxonEntityFieldDefinition
• Taxonomy_TaxonEntityFieldValue

Use the bellow sql format to execute the data.

INSERT INTO <DestinationDB>..CampaignActivityDefinitions

SELECT * FROM <SourceDB>..CampaignActivityDefinitions

4. Wait till "Process State : Completed".

Post-Steps

Do the following steps after status get Completed.

1. Change "reporting" connecting string "Database" attribute to Sitecore_Reporting_Secondary database and comment "reporting.secondary" connection string.

2. Check whether the TreeDefinitions table of your reporting database contains any records.

NOTE: If this table is empty, please copy data from the TreeDefinitions table of the current or clean Sitecore reporting database

3. Change the pre-step 7 to default (0.00:01:00)

4. Go to the Sitecore Experience Profile and check it contains the latest visit details.


ISSUE

Sitecore analytic database getting broken time to time.

This happen to database which has more than 20 contacts in the xDB.
To avoid this apply the patch provide by Sitecore from https://kb.sitecore.net/articles/440768