Load DWH Access rights fails on TRUNCATE TABLE DWH_SEC_UNIT_BY_MODE

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load DWH Access rights job used to work and now is failing every time with error:

Couldn't execute SQL: TRUNCATE TABLE DWH_SEC_UNIT_BY_MODE

[CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot TRUNCATE TABLE 'DWH_SEC_UNIT_BY_MODE' because it is being referenced by object 'DWH_SEC_RES_OBS_ENTITIES_MV'.

STEPS TO REPRODUCE:

1. On Clarity with SQL Server, schedule Load Data Warehouse Access rights

Expected Results: The job to run successfully every time

Actual Results: The job starts failing suddenly, after have been working, with error:

ERROR 2024-01-19 02:30:25,704 [dwh_sec_unit_by_mode UUID: xxxx] dwh.event Truncate Table - An error occurred executing this job entry : 
Couldn't execute SQL: TRUNCATE TABLE DWH_SEC_UNIT_BY_MODE

[CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot TRUNCATE TABLE 'DWH_SEC_UNIT_BY_MODE' because it is being referenced by object 'DWH_SEC_RES_OBS_ENTITIES_MV'.

As workaround, we were able to drop the dependent views, and run the job, and recreate the views after. Once the views are recreated, the job starts failing again

Resolution

Ensure that ppm_dwh user has all the required permissions to resolve the problem