Finding Originating Clarity Tables for Data Warehouse Tables (Oracle)

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

To identify the source Clarity table or view that populates a specific Data Warehouse (DWH) target table, use the built-in metadata tables within the DWH schema. This is useful for troubleshooting data derivations or understanding table relationships.

Environment

Clarity PPM (All supported releases) Data Warehouse (DWH)

Resolution

To check the source-object mapping for DWH target tables:

DWH_META_TABLES (Table available in both DB sources to find table-level mapping): src_table_name, src_key_column, src_display_column, src_date_column, dwh_table_name, dwh_key_column, ..., is_fact

DWH_META_COLUMNS (Table available in both DB sources to find column-level mapping): object_code, attribute_code, src_table_name, src_column_name, dwh_table_name, dwh_column_name, is_custom, is_fiscal_tsv, ...

Here are some sample diagnostic queries to find source details for more than one DWH target tables: DWH_CMN_HIERARCHY, DWH_CMN_HIERARCHY_NODE

SELECT dwh_table,
src_table
FROM DWH_META_TABLES dmt
WHERE UPPER(dwh_table) IN ('DWH_CMN_HIERARCHY','DWH_CMN_HIERARCHY_NODE') ORDER BY dwh_table;

Once you have the source table DWH table name or view name then the following queries can be used:

The view code to find referenced columns

SELECT text FROM all_views WHERE view_name = 'DWH_PROJECT_V'

Based on the view code optionally check dependencies:

SELECT referenced_owner, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'DWH_PROJECT_V'
AND type = 'VIEW'
ORDER BY referenced_name;

To validate referenced columns from the source views:

SELECT column_name,
data_type,
data_length,
char_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns
WHERE table_name = 'DWH_PROJECT_V'
ORDER BY column_name;

Additional Information

• Data Warehouse Schema Explorer (Beta):  This Beta feature In the Clarity Modern UX Reporting allows you to browse tables/views and review available columns and data types directly within the application.
• Schema Index (Downloads): From the Schema Index page, download the compressed database schema files for your specific database (Oracle, MSSQL, or PostgreSQL). These files include an index.html file that provides a detailed map of all tables and columns.