Table Equivalents In Clarity and DWH Databases

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

What are the common corresponding Clarity / Data Warehouse DWH tables?

 

 

Environment

Release : All

Resolution

After the Load DWH job completes, the following tables are common table references after the synchronization of the Clarity and DWH databases:

 

CLARITY DB	DWH DB/primary key	DWH table primary key	DWH DB JOIN Condition Example
RPT_CALENDAR	DWH_CMN_PERIOD	PERIOD_KEY	DWH_CMN_PERIOD.PERIOD_KEY = DWH_TME_ENTRY_FACTS.PERIOD_KEY
PRTIMEPERIOD	DWH_TME_PERIOD	TIME_PERIOD_KEY	DWH_TME_PERIOD.TIME_PERIOD_KEY = DWH_TME_ENTRY_FACTS.TIME_PERIOD_KEY
PRTIMESHEET	DWH_TME_SHEET	TIMESHEET_KEY	DWH_TME_SHEET.TIMESHEET_KEY = DWH_TME_ENTRY_FACTS.TIMESHEET_KEY
PRTIMEENTRY	DWH_TME_ENTRY	TIMEENTRY_KEY	DWH_TME_ENTRY.TIMEENTRY_KEY = DWH_TME_ENTRY_FACTS.TIMEENTRY_KEY
CMN_SEC_USERS  PRJ_RESOURCES	DWH_RES_RESOURCE	RESOURCE_KEY	DWH_RES_RESOURCE.RESOURCE_KEY = DWH_INV_ASSIGN_PERIOD_FACTS.RESOURCE_KEY
INV_INVESTMENTS	DWH_INV_INVESTMENT DWH_INV_SUMMARY_FACTS	INVESTMENT_KEY	DWH_INV_INVESTMENT.INVESTMENT_KEY = DWH_INV_ASSIGN_PERIOD_FACTS.INVESTMENT_KEY DWH_INV_INVESTMENT.INVESTMENT_KEY = DWH_INV_SUMMARY_FACTS.INVESTMENT_KEY
PRASSIGNMENT	DWH_INV_ASSIGNMENT	ASSIGNMENT_KEY	DWH_INV_ASSIGNMENT.ASSIGNMENT_KEY = DWH_INV_ASSIGN_PERIOD_FACTS.ASSIGNMENT_KEY
PRTASK	DWH_INV_TASK	TASK_KEY	DWH_INV_TASK.TASK_KEY = DWH_INV_ASSIGN_PERIOD_FACTS.TASK_KEY

Additional Information

Based on the above, an example query to confirm actual unit and cost is:

SELECT
    i.INVESTMENT_KEY,
    i.INVESTMENT_ID,
    i.INVESTMENT_NAME,
    i.INVESTMENT_TYPE_KEY,
    s.ACTUAL_TOTAL_COST,
    s.ACWP_TOTAL_COST,
    s.ACTUAL_LABOR_COST,
    s.ACTUAL_EQUIP_COST,
    s.ACTUAL_EXPENSE_COST,
    s.ACTUAL_MATERIAL_COST,
    s.DW_UPDATED_DATE
FROM DWH_INV_INVESTMENT i
INNER JOIN DWH_INV_SUMMARY_FACTS s ON i.INVESTMENT_KEY = s.INVESTMENT_KEY
ORDER BY i.INVESTMENT_NAME