CAST Engineering Dashboard - List of XXX - No items to show - FRAME_PORTAL_INVESTIGATION_VIEW - categorized objects

When selecting some rules from the Investigation - Quality Model drilldown view it shows the following message "No items to show" in the violation panel when actually there are violations. This happens more frequently with rules with a high number of violations.

1. Go to Investigation drilldown view.

2. Select rule for example "Avoid stored procedures not returning a status value" for application.

3. No items to display.

Action Plan

1. Go through the known cases and identify your problem & apply the solution given . If you are not able to find a solution for your case then goto next step
2. Check for duplicates below
   
   1. If there are duplicates in the Action Plan then remove the duplicates
   2. If there are no duplicated then goto next step
3. Get the list of objects violating the quality rule in the KB below
   1. Check the property of these objects using the page - SQL Queries - CAST Knowledge Base - Queries on objects - How to check if objects are internal or external or generated from the application
      1. If the objects are non internal then it means there is a bug in the Stored procedure that computes of the quality rule . As a work around exclude the objects that are generated and contact CAST Technical Support with Relevant Inputs . For CAST Technical support only - This issue needs permanent fix.
      2. If the objects are internal then contact CAST Technical Support with Relevant input below
         For CAST Technical support only - Issue will be reproduced and the steps here will be followed - CAST Engineering Dashboard - Information - How to identify and execute SQL query involved in data display 

Relevant Inputs

1. Sherlock export with the options Export CAST Databases , Export Logs , Export Configuration Files checked
2. Complete screenshot of the dashboard with URL showing the problem.

Get the list of objects violating the quality rule in KB

Get the list of objects violating the quality rule in KB by running the following query

1. For Oracle

   select distinct k.IdKey, k.KeyNam
   from Keys k
    join DSS_METRIC_RESULTS dmr
      on k.IdKey = dmr.OBJECT_ID
     and dmr.METRIC_ID = <metric Id +1>
     and dmr.METRIC_VALUE_INDEX = 1
   order by k.KeyNam
   

2. For SQL Server and CSS

   select distinct k.IdKey, k.KeyNam
   from Keys k
   join DSS_METRIC_RESULTS dmr
   on k.IdKey = dmr.OBJECT_ID
   and dmr.METRIC_ID = <metric Id +1>
   and dmr.METRIC_VALUE_INDEX = 1
   order by k.KeyNam

Check for duplicates in the Action plan

 Issue can occur from the fact that you have duplicate entries in your action plan using SQL queries. Note that you should not use the queries to update internal table. Specifically, you should not use the user 500 to add an action plan to the VIEWER_ACTION_PLANS table as this is the automated user used by the automated action plan and is leading to misinterpretation of data in the table. To confirm this case, run the following SQL query on central database:

SELECT metric_id,
       object_id,
       last_snapshot_date,
       Count(1)
FROM   viewer_action_plans
GROUP  BY metric_id,
          object_id,
          last_snapshot_date
HAVING Count(1) > 1
ORDER  BY metric_id,
          object_id,
          last_snapshot_date 

If the query returns rows then there is duplication.

To remove the corruption created in the action plan, follow these steps:

1. Take a backup of your DBs.
2. Run the query bellow on your Central base.
3. Refresh the Dashboard.

update VIEWER_ACTION_PLANS
set METRIC_ID = - METRIC_ID;

insert into VIEWER_ACTION_PLANS (METRIC_ID, OBJECT_ID, FIRST_SNAPSHOT_DATE, LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY)
select -METRIC_ID, OBJECT_ID, min(FIRST_SNAPSHOT_DATE), LAST_SNAPSHOT_DATE, min(SEL_DATE), min(PRIORITY)
from VIEWER_ACTION_PLANS
group by METRIC_ID, OBJECT_ID, LAST_SNAPSHOT_DATE;

update VIEWER_ACTION_PLANS 
   set PRIORITY= ap.PRIORITY, 
       ACTION_DEF = ap.ACTION_DEF,
       USER_NAME = ap.USER_NAME
  from VIEWER_ACTION_PLANS  ap
 where VIEWER_ACTION_PLANS.METRIC_ID > 0
   and -ap.METRIC_ID = VIEWER_ACTION_PLANS.METRIC_ID
   and ap.OBJECT_Id = VIEWER_ACTION_PLANS.OBJECT_ID
   and ap.FIRST_SNAPSHOT_DATE = VIEWER_ACTION_PLANS.FIRST_SNAPSHOT_DATE;

delete from VIEWER_ACTION_PLANS 
where METRIC_ID < 0;

Known cases

Go through the below known cases and identify your problem & apply the solution given :

CASE #1 : if the issue occurs on distributions for the list of categorized objects then this issue is due to a known is a bug on the retriever of the list of object for distribution measures - RETRIEVER_RULE_INVEST_VIOLATION_TABLE. The bug is that the retriever only display the objects with violations and here it is expected to display the objects irrspective of whether they are in violation/not if they fall under the distributin metric. This bug has been fixed in 7.3.6 and higher versions. Move to a CAST AIP version >= 7.3.6 to solve this issue or you can apply the following work around:

1.Run the following query on your central schema

UPDATE VIEWER_PARAMETERS SET    VALUE           = '     join 
%PREFIX%DSS_METRIC_HISTO_TREE dmht    on  dmr.METRIC_ID = 
dmht.METRIC_ID    and  dmht.METRIC_PARENT_ID = %metric% /* metrique 
paire */    and  dmht.SNAPSHOT_ID = dmr.SNAPSHOT_ID     join 
%PREFIX%DSS_LINK_INFO li    on  dmr.OBJECT_ID = li.NEXT_OBJECT_ID    
and  li.LINK_TYPE_ID = 3    and  li.SNAPSHOT_ID = dmr.SNAPSHOT_ID     
join %PREFIX%DSS_MODULE_LINKS dml    on  li.PREVIOUS_OBJECT_ID = 
dml.MODULE_ID    and  dml.OBJECT_ID = %object%    and  dml.SNAPSHOT_ID =
 dmr.SNAPSHOT_ID     join %PREFIX%DSS_OBJECTS o    on  dmr.OBJECT_ID = 
o.OBJECT_ID     left join %PREFIX%DSS_SNAPSHOT_RANKING sr    on  
dmr.OBJECT_ID = sr.OBJECT_ID    and  sr.BUSINESS_CRITERION_ID = 
%bcid%    and  sr.SNAPSHOT_ID = dmr.SNAPSHOT_ID     where 
dmr.METRIC_VALUE_INDEX = 1    and  dmr.SNAPSHOT_ID = %snapshot%    order
 by %~ORDER_BY%   ' WHERE  ID              = 'SELECT_QUERY2' AND    
COMPONENT_ID    = 'RETRIEVER_RULE_INVEST_VIOLATION_TABLE' AND    
PARAMETER_INDEX = 0

CASE #2 : If you are on 7.2 and if issue occurs on quality rules, then this issue is caused as there is NO index on DSS_VIOLATION_STATUSES table. This issue is planned to be fixed in CAST v7.2.6.

Work Around is to create index on DSS_VIOLATION_STATUSES.

Execute the below on the central base :

create index DSS_VIOLATION_STATUSES_1 on DSS_VIOLATION_STATUSES(SNAPSHOT_ID, OBJECT_ID, DIAG_ID)

CASE #3 : If you are on 7.3 and if issue occurs on all quality rules, then this issue is caused due to the presence of duplicate rows in DSS_VIOLATION_STATUSES table after snapshot consolidation.

To confirm if there exists any duplicates in the DSS_VIOLATION_STATUSES table you can activate the SHOW SQL traces on dashboard logs and look for Error "more than one row returned by subquery". After seeing this error you can execute the below query on the Central Base -

select SNAPSHOT_ID,
DIAG_ID,
OBJECT_ID, count(1)
from DSS_VIOLATION_STATUSES
group by SNAPSHOT_ID, DIAG_ID, OBJECT_ID
having count(1) > 1
order by SNAPSHOT_ID, DIAG_ID, OBJECT_ID;

If the query returns rows then this confirms that there are duplicates. These duplicates are caused due to the consolidation action that you have performed in CAST-MS and it is a bug which is planned to be fixed in 7.3.5
But if you have only one snapshot the work around for this is given below :

1. Clean up the  DSS_VIOLATION_STATUSES table

   truncate table DSS_VIOLATION_STATUSES

2. Refill the table by executing the below procedure

   select ADG_COMPUTE_VIOLATION_STATUSES(<Snapshot_id_with_duplicates>)

Note the above work around for CASE #3 can be applied only when you have one snapshot in your Central Base.

CASE #5 : Quality rule is Avoid Classes using "new" to allocate Instances but not defining a copy Constructor

This rule is taking into account the objects that are not part of any module also. Hence, though the grade is displayed , since the object is not part of module, the object itself is not displayed on the dashboard. To confirm the same follow the steps as mentioned below -

1. Execute the below query on local Database

select *
from dss_metric_results dmr
join objfulnam ofn
on ofn.idobj = dmr.object_id
where metric_id = 593

Result

61835;"[D:\CAST\DMT_81\DEPLOY\ABC\EventBase.h].[EventBase]"

61932;"[D:\CAST\DMT_81\DEPLOY\ABC\CdmBeverageService.h].[CdmBeverageService]"
696113;"[D:\CAST\DMT_81\DEPLOY\ABC\CdmIngredientService.h].[CdmIngredientService]"

We can see that there are 3 objects in violation.

2. Check if these objects belong to any modules by using the page - SQL Queries - CAST Knowledge Base - Queries on Module - Module content and properties. If the objects do not belong to any module then this issue is fixed in CAST AIP 8.1.5 , where the quality rule will mark objects in violation only if they are in any of the functional modules.

As a WA if you want the objects to be displayed on the dashboard then you can edit your module content in CMS to include these objects.

CASE #6 : If you are on a version lower than 7.1 -

If the dashboard log has the following SQL error:[ERROR]: [SQL] ERROR => ERROR: more than one row returned by a subquery used as an expression for RETRIEVER_RULE_INVEST_VIOLATION_TABLE,you can repair the central database executing the following 2 queries :
1.Remove Action Selection History:
delete from VIEWER_ACTION_PLANS where LAST_SNAPSHOT_ID != 1000000000

2.Remove Action Selection for Mishandled metric :
delete from VIEWER_ACTION_PLANS where METRIC_ID=<Metric_id> (use the metric id of the metrics which display empty list of violations)

This will allow you to correctly display the lists of "Objects with Violations" for every failed quality rule,however it will have the below drawbacks:
- loose history of selection (not severe because you are rarely interested in past selections)
- loose selection of violations to rule (you can redo the selection)

Note: Issue is observed in 7.0.12 and the fix will be available from CAST AIP 7.1 version.

CASE #7 : For the PeopleSoft technology the list of violated objects shows "No items to display"

For the PeopleSoft technology the list of violated objects shows "No items to display" though some standard quality rules have been violated. The reason for this is the standard quality rules and distributions need to be deactivated for PeopleSoft analysis. Please make sure that you have deactivated all the standard QR and distributions which are not prefixed with PeopleSoft: in the assessment model.

Please take a backup of your Central Base before performing the actions mentioned above.

Ticket # 8013