How to Find a Clarity PPM Attribute in the DWH Schema?

book

Article ID: 141110

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

How can I find the a Clarity PPM Attribute (that is included in the Data Warehouse) in the Data Warehouse schema?

Resolution

On Clarity PPM:

  1. Get the object code and attribute code. The attribute code can be found in Administration > Studio > Objects > [object] > Attributes > Database Column (i.e.: 'project'/'obj_stakeholder1')
  2. Run the following query in the Clarity PPM schema, replacing parts between < >:
    SELECT OBJECT_CODE, ATTRIBUTE_CODE, SRC_TABLE_NAME, SRC_COLUMN_NAME, DWH_TABLE_NAME, DWH_COLUMN_NAME FROM DWH_META_COLUMNS WHERE OBJECT_CODE = '<object_code>' AND ATTRIBUTE_CODE = '<attribute_code>'

    (i.e.: SELECT OBJECT_CODE, ATTRIBUTE_CODE, SRC_TABLE_NAME, SRC_COLUMN_NAME, DWH_TABLE_NAME, DWH_COLUMN_NAME FROM DWH_META_COLUMNS WHERE OBJECT_CODE = 'project' AND ATTRIBUTE_CODE = 'obj_stakeholder1')

 

The DWH schema table will be returned as DWH_TABLE_NAME and the DWH attribute column will be returned as DWH_COLUMN_NAME.

e.g.:

DWH_TABLE_NAME: dwh_inv_project

DWH_COLUMN_NAME: business_owner_key