KB485727: Move historical data to current fact tables in Platform Analytics

Symptom

Platform Analytics makes use of fact tables for displaying data contained in the Platform Analytics Repository. By default, Platform Analytics keeps all the fact tables in its Repository for a period of 180 days. After this number of days is surpassed, all the data contained in the fact tables will pass to the historical fact tables. This period is set by default in the PAConsumerConfig.yaml file, please review the KB485690 for understanding better how to modify this limit.  
If older data was moved to historical Platform Analytics tables, then this moved data will not return to current fact tables on its own automatically. The purpose of this article is to explain how to do it.
 

Cause

This is a default setting provided by Strategy to guarantee an adequate performance of Platform Analytics. In this way we avoid the saturation of the fact tables, and it makes easier and faster to retrieve the data. If the user needs to have access to tables older than 180 days in the current fact tables, then it is needed to transfer the data.
 

Action

It is possible to move the data from historical tables to current fact tables:

1. Stop the Platform Analytics Consumer service.
2. Create a copy (backup) of the PAConsumerConfig.yaml found in Strategy/PlatformAnalytics/conf folder and save it somewhere else.
3. Modify the value of currentFactDataKeepDays. Increase the value to the desired days to keep (bigger than 180 days), if you need more details about this feature please review KB485690 and save the changes.
4. Perform a backup of the Platform Analytics Repository (please check here).
5. Connect to the Platform Analytics Warehouse using DB Query tool. Then check the integrity of the historical tables.
   
   Run each SQL query in DB Query tool and export the result output to CSV/XLS or please note it down if select count(*) is used:

SELECT count(*) FROM platform_analytics_wh.historical_access_transactions;SELECT count(*) FROM platform_analytics_wh.historical_fact_action_cube_cache;SELECT count(*) FROM platform_analytics_wh.historical_fact_action_security_filter;SELECT count(*) FROM platform_analytics_wh.historical_fact_object_change_journal;SELECT count(*) FROM platform_analytics_wh.historical_fact_performance_monitor;SELECT count(*) FROM platform_analytics_wh.historical_fact_prompt_answers group;SELECT count(*) FROM platform_analytics_wh.historical_fact_report_columns;SELECT count(*) FROM platform_analytics_wh.historical_fact_sql_stats;SELECT count(*) FROM platform_analytics_wh.historical_fact_step_sequence;SELECT count(*) FROM platform_analytics_wh.historical_lu_session;

 

1. Check the integrity of the current tables:
   
   Run each SQL query in DB Query tool and export the result output to CSV/XLS or please note it down if select count(*) is used:
   
   

   SELECT count(*) FROM platform_analytics_wh.access_transactions;	SELECT count(*) FROM platform_analytics_wh.fact_action_cube_cache;	SELECT count(*) FROM platform_analytics_wh.fact_action_security_filter;	SELECT count(*) FROM platform_analytics_wh.fact_object_change_journal;	SELECT count(*) FROM platform_analytics_wh.fact_performance_monitor;	SELECT count(*) FROM platform_analytics_wh.fact_prompt_answers;	SELECT count(*) FROM platform_analytics_wh.fact_report_columns;	SELECT count(*) FROM platform_analytics_wh.fact_sql_stats;	SELECT count(*) FROM platform_analytics_wh.fact_step_sequence;	SELECT count(*) FROM platform_analytics_wh.lu_session;

1. Copy the data from the Historical tables to current tables:
   
   

   INSERT INTO access_transactions SELECT * FROM historical_access_transactions;	INSERT INTO fact_action_cube_cache SELECT * FROM historical_fact_action_cube_cache;	INSERT INTO fact_action_security_filter SELECT * FROM historical_fact_action_security_filter;	INSERT INTO fact_object_change_journal SELECT * FROM historical_fact_object_change_journal;	INSERT INTO fact_performance_monitor SELECT * FROM historical_fact_performance_monitor;	INSERT INTO fact_prompt_answers SELECT * FROM historical_fact_prompt_answers;	INSERT INTO fact_report_columns SELECT * FROM historical_fact_report_columns;	INSERT INTO fact_sql_stats SELECT * FROM historical_fact_sql_stats;	INSERT INTO fact_step_sequence SELECT * FROM historical_fact_step_sequence;	INSERT INTO lu_session SELECT * FROM historical_lu_session;

   
   
   NOTE: Even if all the tables are copied, the data kept will be limited by the currentFactDataKeepDays value in PAConsumerConfig.yaml file. Also, you need to consider that if there is no data in the historical tables or it is empty, nothing will be copied.
   
   If you encounter the error:
   ERROR: duplicate key value violates unique constraint "access_transactions_src_tran_valid_account_id_ts", please review the KB484071 to troubleshoot it properly.

​​​​​​​

1. Re-check the integrity of the current tables following the steps in the point 6. This is not forcibly required since it is just a confirmation. The number of rows of the current tables should be the sum of the current tables + the historical tables (before restarting the Platform Analytics Consumer).
2. If the transfer was done successfully, remove the unneeded data in from the historical tables.
   
   

   TRUNCATE TABLE platform_analytics_wh.historical_access_transactions;	TRUNCATE TABLE platform_analytics_wh.historical_fact_action_cube_cache;	TRUNCATE TABLE platform_analytics_wh.historical_fact_action_security_filter;	TRUNCATE TABLE platform_analytics_wh.historical_fact_object_change_journal;	TRUNCATE TABLE platform_analytics_wh.historical_fact_performance_monitor;	TRUNCATE TABLE platform_analytics_wh.historical_fact_prompt_answers;	TRUNCATE TABLE platform_analytics_wh.historical_fact_report_columns;	TRUNCATE TABLE platform_analytics_wh.historical_fact_step_sequence;	TRUNCATE TABLE platform_analytics_wh.historical_lu_session;	TRUNCATE TABLE platform_analytics_wh.fact_sql_stats;

 

1. Start Platform Analytics Consumer.

NOTE: Be aware that increasing too much the amount of data in the current tables may lead to a performance repercussion or a bigger amount of memory will be needed depending on the amount of data managed and contained in the tables. 
​​​​​​​