Query to identify mismatch in Studio objects between the Clarity and Data Warehouse Schemas

Products

• Clarity PPM SaaS

Issue/Introduction

You have a QA or DEV environment with a recent refresh from Production and you need to identify any potential mismatches for custom objects and custom attributes in the Data Warehouse

Environment

Clarity SaaS

Cause

A Database refresh or frequent Studio object/attribute changes are causing the Load DWH to fail

Resolution

Work with Support to run the following query against your clarity database:

/* Look for custom attributes to be included in the DWH but missing from the DWH_META_COLUMNS table. */
SELECT a.object_name object_code, 
       a.column_name as attribute, 
       'Column Name is missing from DWH_META_COLUMNS table.  Go to Administration/Objects and click on Attributes tab, uncheck the DWH Flag for that attribute, save and then re-check the DWH flag. This should fix the issue!' error_message
FROM   odf_custom_attributes a
       LEFT OUTER JOIN dwh_meta_columns c ON a.object_name = c.object_code
                                         AND a.column_name = c.attribute_code
WHERE  a.is_custom = 1 AND a.is_dw_enabled = 1 AND a.is_system = 0
AND   c.object_code IS NULL
UNION ALL
/* Look for custom objects to be included in the DWH but missing from the DWH_META_TABLES table. */
SELECT o.code object_code,
       '' as attribute,
       'Object is missing from DWH_META_TABLES table in Transactional DB.  Go to Administration/Objects and uncheck the DWH Flag for that object, save and then re-check the DWH flag.  This should fix the issue' error_message
FROM   odf_objects o
WHERE  is_custom = 1 AND is_dw_enabled = 1 AND is_system = 0
AND CONCAT('dwh_',code,'_v')::text NOT IN (select src_table_name from dwh_meta_tables)
UNION ALL
/* Look for custom attributes to be included in the DWH but missing from the DWH object view. */
SELECT co.object_code,
       co.attribute,
       CONCAT('Column Name is missing from the ',co.view_name,' view.  Go to Administration/Objects and click on Attributes tab, uncheck the DWH Flag for that attribute, save and then re-check the DWH flag. This should fix the issue!') error_message
FROM   information_schema.views v
       INNER JOIN (SELECT CONCAT('dwh_',o.code,'_v') view_name, o.code object_code, a.column_name AS attribute
                        FROM   odf_objects o
                               LEFT OUTER JOIN odf_custom_attributes a ON a.object_name = o.code
                                                                      AND a.is_custom = 1 
                                                                      AND a.is_dw_enabled = 1
                        WHERE  o.is_custom = 1 
                        AND    o.is_dw_enabled = 1
                        AND    o.is_system = 0) co ON v.table_name = co.view_name
WHERE v.table_schema = current_schema()
AND   position(co.attribute in v.view_definition) = 0
UNION ALL
/* Look for calculated attributes that have no calculation */
SELECT co.object_code,
       co.attribute,
       'Column Name does not have a defined calculation.  Go to Administration/Objects and click on Attributes tab.  Uncheck the DWH Flag for the calculated attribute and save.  This should fix the issue!' error_message
FROM   information_schema.views v
       INNER JOIN (SELECT CONCAT('dwh_',o.code,'_v') view_name, o.code object_code, a.column_name as attribute
                        FROM   odf_objects o
                               LEFT OUTER JOIN odf_custom_attributes a ON a.object_name = o.code
                                                                      AND a.is_custom = 1 
                                                                      AND a.is_dw_enabled = 1
                                                                      AND a.extended_type = 'calculated'
                        WHERE  o.is_custom = 1 
                        AND    o.is_dw_enabled = 1
                        AND    o.is_system = 0) co ON v.table_name = co.view_name
WHERE v.table_schema = current_schema()
AND   position(CONCAT('NULL::text AS ',co.attribute) in v.view_definition) = 0

 

Sample output: