Report Generator - Reports - Empty results - No Top Riskiest Transactions TWRI

This page provides the solution to the issue of report not containing the TWRI - Top Riskiest Transactions information when the report is generated using Report Generator.

Below is a screenshot showing the problem in the generated report:

Below is the step-by-step scenario leading to the problem:

1. Open Report Generator and connect to REST Web Service.
2. Generate the report using the template: Result-presentation-sample1.pptx
3. When the report is generated, go to the slide which has the title "Potential Points of failures: Transaction Risk Index" and TWRI will be missing for the Business Criterion.

The impact of the problem: The TWRI Values will not be available on the report for the user to see it.

This problem occurs because there are some queries which return the following error on the Report Generator log file -

Error execution :  The operation has timed out.

1. Refer to CAST Management Studio - Information - How to find logs page to know where the logs of Report Generator can be found.

2. To identify the queries which return the above mentioned error, activate the SQL Traces and it is explained on the page Health Dashboard - Information - How to activate SQL traces on AAD and RestAPI logs
   The following query is one of the examples due to which the error came in the logs:

   select distinct o.OBJECT_FULL_NAME as OBJECT_FULL_NAME, o.OBJECT_ID as TRANSACTION_ID, (select sum(r.VIOLATION_INDEX) from DSS_SNAPSHOT_RANKING r where r.BUSINESS_CRITERION_ID = 60013  and r.SNAPSHOT_ID = 40  and r.OBJECT_ID in (select distinct transaction_detail.NEXT_OBJECT_ID from DSS_LINK_INFO transaction_detail where transaction_detail.PREVIOUS_OBJECT_ID = o.OBJECT_ID and transaction_detail.SNAPSHOT_ID = r.SNAPSHOT_ID and transaction_detail.LINK_TYPE_ID between 11002 and 11005) ) as TRI from DSS_OBJECTS o join DSS_LINK_INFO l on l.PREVIOUS_OBJECT_ID = o.OBJECT_ID and l.LINK_TYPE_ID = 11004  and l.SNAPSHOT_ID = 40  where o.OBJECT_TYPE_ID = 30002  group by o.OBJECT_ID, o.OBJECT_FULL_NAME order by TRI desc, OBJECT_FULL_NAME asc limit 10 offset 0

   The above query when executed should return the TWRI results for the Robustness Business Criteria, but it results in the error of operation has timed out.

   To fix the problem, proceed as follows:

   1. The ID of Business Criterion can be checked in the Assessment model, by opening CAST-MS as shown below:

      
   2. Replace the transaction_detail.SNAPSHOT_ID = r.SNAPSHOT_ID with transaction_detail.SNAPSHOT_ID = ? in the query mentioned above which is causing the time out.

   3. Following is the generalized query which will not time out while execution. Execute this query on the Central Schema by replacing the <snapshot_id> with the snapshot ID for which results are required and <Business_Criteria_ID> with the ID of Business Criteria like Robustness, Performance, etc. ID's of business criterion are provided below:

      select distinct o.OBJECT_FULL_NAME as OBJECT_FULL_NAME, o.OBJECT_ID as TRANSACTION_ID, (select sum(r.VIOLATION_INDEX) from DSS_SNAPSHOT_RANKING r where r.BUSINESS_CRITERION_ID =<Business_Criteria_ID> and r.SNAPSHOT_ID = <snapshot_ID> and r.OBJECT_ID in (select distinct transaction_detail.NEXT_OBJECT_ID from DSS_LINK_INFO transaction_detail where transaction_detail.PREVIOUS_OBJECT_ID = o.OBJECT_ID and transaction_detail.SNAPSHOT_ID = <snapshot_ID> and transaction_detail.LINK_TYPE_ID between 11002 and 11005) ) as TRI from DSS_OBJECTS o join DSS_LINK_INFO l on l.PREVIOUS_OBJECT_ID = o.OBJECT_ID and l.LINK_TYPE_ID = 11004  and l.SNAPSHOT_ID = <snapshot_ID>  where o.OBJECT_TYPE_ID = 30002  group by o.OBJECT_ID, o.OBJECT_FULL_NAME order by TRI desc, OBJECT_FULL_NAME asc limit 10 offset 0

      The below query result is for the snapshot_id=34 and Business_Criteria_ID=60013  (ID for Robustness)

      Query result example

       "showEvent.jsp";29927;186

      "showAccount.jsp";29929;146

Impact of the solution is: This is a workaround which will provide the TRI values for different business criterion from the central schema.

Ticket # 5515

This is a known problem and will be fixed in CAST AIP 8.0.3.