SQL Queries - CAST Central Base - Corruptions on Quality rule - How to check if a Quality rule is calculated at the application level but not at the union content module and vice versa

Purpose of Query

This page checks if a Quality rule is present in the application but not present in the union content module and vise versa.The union content module is a technical module that is created when the  Full Application mode is selected as a consolidation mode, for more information refer to CMS - Consolidation Settings tab

.

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server❓
Microsoft SQL Server❓
CSS2✅

Detect corruption

This section guide you to detect the corruption:

SELECT * 
FROM   (SELECT dmra.snapshot_id, 
               dmra.metric_id, 
               dmra.metric_value_index, 
               dmra.object_id, 
               dmra.metric_num_value 
        FROM   dss_metric_results dmra 
        WHERE  dmra.object_id IN (SELECT DISTINCT dos.object_id 
                                  FROM   dss_objects dos 
                                  WHERE  dos.object_type_id = -102) 
               AND dmra.metric_value_index = 0 
       --  and dmra.SNAPSHOT_ID = 32 
       --  and dmra.METRIC_ID in (7128, 61007) 
       ) dmra 
       FULL JOIN (SELECT dmru.snapshot_id, 
                         dmru.metric_id, 
                         dmru.metric_value_index, 
                         dmru.object_id, 
                         dmru.metric_num_value 
                  FROM   dss_metric_results dmru 
                  WHERE  dmru.object_id IN (SELECT DISTINCT dos.object_id 
                                            FROM   dss_objects dos 
                                            WHERE  Lower(dos.object_name) LIKE 
                                                   '%union%' 
                                                   AND dos.object_type_id = 
                                                       20000) 
                         AND dmru.metric_value_index = 0 
                 --  and dmru.SNAPSHOT_ID = 32 
                 --  and dmru.METRIC_ID in (7128, 61007) 
                 ) dmru 
              ON dmra.snapshot_id = dmru.snapshot_id 
                 AND dmra.metric_id = dmru.metric_id 
                 AND dmra.metric_value_index = dmru.metric_value_index 
                 AND dmra.metric_num_value = dmru.metric_num_value 
WHERE  dmra.object_id IS NULL 
        OR dmru.object_id IS NULL;

Query result example

;;;;;32;7128;0;916001;3.88627736716161

32;7129;0;3;3.74162557347584;;;;;

Query result interpretation

  1. In snapshot 32 the Quality Rule of ID 7128 is present in the union content module but not in the application.
  2. In snapshot 32 the Quality Rule of ID 7129 is present in the applicaion but not in the union content module.

Remediation

This section guide you to correct the corruption:

If the metric is calculated in the union content module but not in the application, then in order to fix this corruption proceed as follows:

  1. Install the stored procedure dss_copy_results.sql and DSS_COPY_MOD_SLICE_RESULTS.sql on the central schema

  2. Run the following query by entering the snapshot id from where you are observing the corruption:

DSS_PROPAGATE_RESULTS_TO_APP (snapshot_id)


   For example:

   **Query result example**

DSS_PROPAGATE_RESULTS_TO_APP (32)


If the metric is computed at application level but not the union content module then the issue can be resolved by computing a snapshot by skipping analysis.

**Query for Oracle**

```sql
Enter the SQL query

Query result example

Query result interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages