KB486849: Platform Analytics Repository Statistics failing to collect

Symptom 

After starting the Repository Administration service during the daily statistics collection procedure, the following error is encountered in the mstr-repo-dba-operations.log:

ERROR: duplicate key value violates unique constraint "fact_repository_stats_pkey"

 

Cause

A previous operation for statistics collection encountered a fatal error before it was able to complete successfully. This causes an interim etl table to not be cleaned up as expected which conflicts with future insert attempts. Successive restart attempts will continue to compound the issue by failing during the database level transformation sequence.
 

Solution

Attached to this Knowledge Base Article is a sql file, sp_repository_stats_insert.txt. This can be deployed against the Platform Analytics Warehouse to create a new Stored Procedure, sp_repository_stats_insert, to the default 'mstr_pa' User. If a different User is desired, the last line of the syntax may be modified.

ALTER PROCEDURE platform_analytics_wh.fix_repository_stats_sp()
OWNER TO mstr_pa;

After downloading the script the following steps should be taken:

1. Stop the Repository Administration Service
2. Take a backup of the Platform Analytics Warehouse
3. Deploy the script against the Platform Analytics Warehouse using a SQL utility such as DB Query Tool or pgAdmin
4. Execute the script with command 'CALL platform_analytics_wh.repository_stats_insert();'
5. Validate that the data is populated for fact_repository_stats and the etl_fact_repository_stats table is now empty
6. Restart the Repository Administration Service.