CA PPM Data Warehouse (DWH) Conventions

Use the following Data Warehouse (DWH) standards and conventions to generate reporting data using your preferred reporting tools:
ccppmop155
HID_admin_datawarehouse_conventions
Use the following Data Warehouse (DWH) standards and conventions to generate reporting data using your preferred reporting tools:
2
 
Data Warehouse Table Prefixes
The Data Warehouse schema uses the prefixes in the following table in its table naming convention.
Prefix
Table Description
DWH_CFG
Configuration tables that are used to supply log and audit information
DWH_CMN
Common database objects that are used across most areas
DWH_CMP
Company database objects
DWH_FIN
Financial management database objects
DWH_INV
Investment management database objects
DWH_LKP
Lookup database objects
DWH_META
Metadata tables that help determine the DWH structure
DWH_ODF
Customer-specific database objects
DWH_PFM
Portfolio management database objects
DWH_RES
Resource management database objects
DWH_RIM
Risk and Issue management database objects
DWH_TME
Time management database objects
DWH_X
Internal database objects that are used to help populate the fact tables
Data Warehouse Static Lookup Standards
In the Data Warehouse, each lookup has its own table. The lookup values are stored separately in the selected languages for the Data Warehouse. For example, if the Data Warehouse is stored in English and Spanish, two records exist for each lookup value.
The following table shows the structure of a static lookup in the schema.
Column
Data Type
Description
[LOOKUP_NAME]_key
number or varchar(30)
The key value of the lookup. If the hidden key in 
Clarity PPM
 is lookup_enum, the key in the Data Warehouse is populated with the lookup_enum. Same for lookup_code. Example: investment_status_key.
LANGUAGE_CODE_KEY
 
Number ID from the 
Clarity PPM
languages table.
LANGUAGE_CODE
Varchar(30)
Unique language code from the 
Clarity PPM
languages table.
[LOOKUP_NAME]
Varchar(255)
Descriptive name of the lookup; for example: investment_status
SORT_ORDER
Number
The order in which the user wants to see the values
IS_ACTIVE
Number
Indicates whether the current lookup value is active
CLARITY_UPDATED_DATE
Date
The last time the record was updated in 
Clarity PPM
.
DW_UPDATED_DATE
Date
The last time the record was updated in the Data Warehouse.
Data Warehouse Dynamic Lookup Standards
Each dynamic lookup has its own table. Each table structure can be different depending on the lookup. If the lookup is language-dependent, the langage_code_key and language_code are stored. Otherwise, there is one record for each value.
The following table shows the structure of a dynamic lookup in the schema.
Column
Data Type
Description
[lookup_name]_key
...
The key value of the dynamic lookup. The value depends on the NSQL hidden value.
Language_code_key
Number
Number ID from the 
Clarity PPM
languages table, if applicable.
Language_code
Varchar(30)
Unique language code from the 
Clarity PPM
languages table, if applicable.
[lookup_name]
...
Descriptive name of the lookup. Example: investment_status.
...
...
Miscellaneous columns specific to the lookup.
Clarity_updated_date
Date
The last time the record was updated in 
Clarity PPM
.
DW_updated_date
Date
The last time the record was updated in the Data Warehouse.
Data Warehouse Fact Tables
In the data warehouse, fact tables use the following conventions:
  • Fact table names end with the suffix
    _FACTS
    .
  • Tables with
    _PERIOD_
    in the name store facts by defined periods.
  • Tables with
    _SUMMARY_
    in the name store summarized facts. Summary tables exist for many of the facts. If you are matching summary numbers to period facts, qualify the period facts by a period type.
  • Tables with a
    DWH_X_
    in the name are internal fact tables. These tables are used to populate the period and summary fact tables in the most efficient way. These tables are not available to users.
  • The fact table keys all have referential integrity.
  • Calculated facts are stored in the tables for consistency.
  • Summary rollups exist in the Data Warehouse. Assignments roll up to tasks and tasks roll up to assignments.
  • Hidden time slices aggregate the data into weekly, monthly, and fiscal periods.
The following table includes examples of fact tables.
Fact Description
Fact Table Name
Aggregation
Financial Transaction
DWH_FIN_TRANSACTION_FACTS
Daily
Time Entry
DWH_TME_ENTRY_FACTS
Daily
Financial Benefit
DWH_FIN_BENEFIT_PERIOD_FACTS
Fiscal
Financial Plan
DWH_FIN_PLAN_PERIOD_FACTS
Fiscal
Task Assignment
DWH_INV_ASSiGN_PERIOD_FACTS
Fiscal, Weekly, Monthly
Investment Task
DWH_INV_TASK_PERIOD_FACTS
Fiscal, Weekly, Monthly
Investment Team
DWH_INV_TEAM_PERIOD_FACTS
Fiscal, Weekly, Monthly
Investment
DWH_INV_PERIOD_FACTS
Fiscal, Weekly, Monthly
Resource
DWH_RES_PERIOD_FACTS
Fiscal, Weekly, Monthly
The following four period types are used for storing facts:
  • Daily
  • Weekly
  • Monthly
  • Fiscal Period
Example: Summary Fact Table Names
The following table includes examples of summary fact table names.
Fact Description
Fact Table Name
Financial Benefit
DWH_FIN_BENEFIT_SUMMARY_FACTS
Financial Plan
DWH_FIN_PLAN_SUMMARY_FACTS
Task Assignment
DWH_INV_ASSIGN_SUMMARY_FACTS
Investment Task
DWH_INV_TASK_SUMMARY_FACTS
Investment Team
DWH_INV_TEAM_SUMMARY_FACTS
Investment
DWH_INV_SUMMARY_FACTS
Data Warehouse Query Example
The Data Warehouse queries are simpler than the queries against the 
Clarity PPM
transactional database in the following ways:
  • No need to join to lookup tables.
  • The joins between tables are consistent. The key is always the resource ID.
  • The column names are consistent between tables.
  • The table names are named according to a standard naming convention.
The following example shows a new team query.
SELECT i.investment_manager, i.investment_name, t.resource_name, t.role_name, tl.booking_status, tl.request_status, p.period_start_date, tf.alloc_hours, tf.alloc_cost FROM   dwh_inv_team t INNER JOIN dwh_inv_team_ln tl ON t.team_key = tl.team_key INNER JOIN dwh_inv_investment i ON t.investment_key = i.investment_key INNER JOIN dwh_inv_team_period_facts tf ON t.team_key = tf.team_key INNER JOIN dwh_cmn_period p ON tf.period_key = p.period_key WHERE  SYSDATE BETWEEN p.year_start_date AND p.year_end_date AND p.period_type_key = 'MONTHLY' AND tl.language_code = 'en'
 
Referential Integrity
To improve data accuracy, tables use primary and foreign keys.
  • Language tables (ending in
    _ln
    ) have foreign keys to the master table.
  • Fact tables have foreign keys to the master tables.
  • Foreign key constraints end with FK1.
  • Primary key constraints end with PK.
This convention reduces errors and eliminates orphan records (detail records without a header). When a record gets deleted, any records in other tables that have a foreign key to the current record are automatically deleted.