Rworld jive 18.104.22.168 instance is having 300 million records in activity fact table . We are facing frequent issues with the ETL job getting failed and hence we were thinking of purging the old analytic data, We are doing an end to end testing of this activity in QA following all scenarios of purging and restoration. PFB the steps followed.
1.Created a new table jivedw_activity_fact_archive of same table structure as that of jivedw_activity_fact table
2. Inserted all rows of jivedw_activity_fact to archive table
3.Verified the count of number of records in both tables
4. Took screenshot of CMR data from end user site
5. Ran the purge job for records older than 2 months and it successfully purged data older than 2 months .
6. Checked the number of records in jivedw_activity_fact table and also verified the CMR graphs again. The fact table had only data for 2 months. But the CMR graphs remained unchanged .
Restoration in case of any issue
1. Verified that earliest date in activity_fact table was 4-Jan-2016
2. Took back up and truncated aggregate tables
3. Ran ETL from admin console.
4. No change in CMR reports (was showing data from beginning).
5.Took backup and truncated all summary tables (around 30 tables)
6.Changed value of last ETL run values to 0 (Reset ETL last run date on jivedw_etl_task in the analytics DB to 0)
7.Ran ETL Process to rebuild CMR.
8. In CMR reports, we could see that only few graphs were reflecting this change (like user activity). All the other graphs still had the older data.
We had referred the below cases for the restoration activity
Is there any additional tables which we have to truncate so that all the graphs in CMR shows only the data for last 2 months(unpurged data). We have truncated all the summary and agg tables.
Please suggest on how to take this forward.