SQL Queries - CAST Central Base - SQL Queries on Metrics - How to check the values of a quality measure for a module

 This page provides a query to check the values for a quality measure for a module.

First confirm that the metric was properly calculated for the snapshot by using the following query.

Substitute  the metric_id and for the date add a date before the snapshot was run ( for date something like '2019-11-3'):

SELECT *
FROM   DSS_HISTORY dh
WHERE  dh.DESCRIPTION LIKE '%<metric_id>%'
AND    dh.ACTION_DATE    > '2019-11-30';

The results should look something like this (for metric_id 7128). It shows that the metric was properly calculated:

Now that we know it has been calculated  then we can get the module values for this quality measure with the following query, by providing the metric id for the quality measure:

SELECT ds.FUNCTIONAL_DATE ,
ds.SNAPSHOT_ID ,
ds.SNAPSHOT_NAME ,
dos.OBJECT_NAME AS MODULE_NAME,
dmr.METRIC_NUM_VALUE AS RATIO ,
dmrw.METRIC_NUM_VALUE AS WEIGHT
FROM DSS_SNAPSHOTS ds
JOIN DSS_METRIC_RESULTS dmr
ON dmr.SNAPSHOT_ID = ds.SNAPSHOT_ID
AND dmr.METRIC_ID = <metric_id>
AND dmr.METRIC_VALUE_INDEX = 1
JOIN DSS_OBJECTS dos
ON dos.OBJECT_ID = dmr.OBJECT_ID
AND dos.OBJECT_TYPE_ID = 20000
JOIN DSS_METRIC_RESULTS dmrw
ON dmrw.SNAPSHOT_ID = ds.SNAPSHOT_ID
AND dmrw.OBJECT_ID = dos.OBJECT_ID
AND dmrw.METRIC_ID = 10152
AND dmrw.METRIC_VALUE_INDEX = 1
ORDER BY ds.FUNCTIONAL_DATE,
dos.OBJECT_NAME;

This query will provide something like the following showing the date, snapshot_id, module name, value for the quality measure and the weight of the quality measure (missing items most likely are items which are below the quality measure threshold value in the assessment model):