SQL Queries - CAST Measure Base - Checking and removing corruptions - Missing technological objects

Purpose of Query

This page helps you to identify and re-mediate missing technological objects.

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✅

Query for CSS

SELECT DISTINCT doi.snapshot_id,
				doi.object_id, 
                doi.object_type_id, 
                dot.object_type_name AS OBJECT_NAME, 
                'Technologic ' 
                || dot.object_type_name 
                || ' object'         AS OBJECT_DESCRIPTION, 
                dot.object_type_name AS OBJECT_FULL_NAME 
FROM   dss_link_info dli 
       JOIN dss_object_info doi 
         ON doi.snapshot_id = dli.snapshot_id 
            AND doi.object_id = dli.next_object_id 
       JOIN dss_object_types dot 
         ON dot.object_type_id = doi.object_type_id 
       LEFT JOIN dss_objects dos 
              ON dos.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND dos.object_id IS NULL

Query result example

1;192994;10003000;“SOA_Assistant”;“Technologic SOA_Assistant object”;“SOA_Assistant”

Query result interpretation

The technological object"SOA_Assistant" is missing from the snapshot of id 1

How to fix the issue?
  1. Remove the corruption from the CB of the corresponding snapshot - Corruption due to missing technological objects
  2. Remove the consolidated snapshot from the measure schema
  3. Consolidate the snapshot again

Query for Oracle

SELECT DISTINCT doi.snapshot_id,
				doi.object_id, 
                doi.object_type_id, 
                dot.object_type_name AS OBJECT_NAME, 
                'Technologic ' 
                || dot.object_type_name 
                || ' object'         AS OBJECT_DESCRIPTION, 
                dot.object_type_name AS OBJECT_FULL_NAME 
FROM   dss_link_info dli 
       JOIN dss_object_info doi 
         ON doi.snapshot_id = dli.snapshot_id 
            AND doi.object_id = dli.next_object_id 
       JOIN dss_object_types dot 
         ON dot.object_type_id = doi.object_type_id 
       LEFT JOIN dss_objects dos 
              ON dos.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND dos.object_id IS NULL

Query result example

1;192994;10003000;“SOA_Assistant”;“Technologic SOA_Assistant object”;“SOA_Assistant”

Query result interpretation

The technological object"SOA_Assistant" is missing from the snapshot of id 1

How to fix the issue?
  1. Remove the corruption from the CB of the corresponding snapshot - Corruption due to missing technological objects
  2. Remove the consolidated snapshot from the measure schema
  3. Consolidate the snapshot again

Query for SQL server

SELECT DISTINCT doi.snapshot_id,
				doi.object_id, 
                doi.object_type_id, 
                dot.object_type_name AS OBJECT_NAME, 
                'Technologic ' 
                || dot.object_type_name 
                || ' object'         AS OBJECT_DESCRIPTION, 
                dot.object_type_name AS OBJECT_FULL_NAME 
FROM   dss_link_info dli 
       JOIN dss_object_info doi 
         ON doi.snapshot_id = dli.snapshot_id 
            AND doi.object_id = dli.next_object_id 
       JOIN dss_object_types dot 
         ON dot.object_type_id = doi.object_type_id 
       LEFT JOIN dss_objects dos 
              ON dos.object_id = dli.next_object_id 
WHERE  dli.link_type_id = 2 
       AND dos.object_id IS NULL

Query result example

1;192994;10003000;“SOA_Assistant”;“Technologic SOA_Assistant object”;“SOA_Assistant”

Query result interpretation

The technological object"SOA_Assistant" is missing from the snapshot of id 1

How to fix the issue?
  1. Remove the corruption from the CB of the corresponding snapshot - Corruption due to missing technological objects
  2. Remove the consolidated snapshot from the measure schema
  3. Consolidate the snapshot again

Notes/comments

Related Pages