Defects linked to TERs - not all associations displayed in Insight Report
Hi,
I have an Insight report (v 1.0.1.1) which pulls data from RQM and RTC (3.0.1.5).
The report uses data from the EXECRES_REQUEST_LOOKUP table to join together Execution Records from RQM with Defects from RTC. Most of the time this works fine. However, we have found some TERs aren't displaying their associated defects. There is no record in EXECRES_REQUEST_LOOKUP for these cases, but there IS a relevant record in EXECSTEPRES_REQUEST_LOOKUP.
I've checked the Data Manager logs and there are no errors or rejected rows that I can see.
I had assumed that if there was a defect associated at the Step level then that same defect would also be seen to be associated at the Execution Result level (i.e. would appear in EXECRES_REQUEST_LOOKUP). Is this a valid assumption or do I need to change my report to also display associations found in the EXECSTEPRES_REQUEST_LOOKUP table?
Thanks, Jill
Accepted answer
I see you are using Insight 1.0.1.1 and CLM 3.x, there is a DW trigger issue there. When some fields of the defect (e.g. the State of defect) is modified, the lookup in EXECRES_REQUEST_LOOKUP will be clear by the wrong trigger. It is an existing defect The lookup relationship of execution result and defect is lost after changing the state of the defect then running delta loading of data collection jobs (189000) and was fixed in RRDI 2.0 (map to CLM 4.x and Insight 1.1). Please checking if existing the wrong trigger in your DW as similar as the following and please manually remove it.
CREATE TRIGGER TRG_REQLOG
AFTER UPDATE OF REC_DATETIME ON REQUEST
REFERENCING NEW AS NEWROW
OLD AS OLDROW
FOR EACH ROW MODE DB2SQL
WHEN (NEWROW.REQUEST_CLASS_ID<>(SELECT REQUEST_CLASS_ID FROM REQUEST_CLASSIFICATION WHERE NAME ='RQM WorkItem'))
BEGIN ATOMIC
DELETE FROM EXECRES_REQUEST_LOOKUP WHERE REQUEST_ID=NEWROW.REQUEST_ID; END;
Comments
Correct my word: The defects you have seen in EXECSTEPRES_REQUEST_LOOKUP should be also in the EXECRES_REQUEST_LOOKUP table where the execution result includes the execution step result.
Yes this trigger does exist, so it sounds like it is the cause of my problems. Many thanks for such a quick and accurate answer!