Time-Varying Attributes in Data Warehouse Design

Products

• Clarity PPM SaaS
• Clarity FedRAMP
• Clarity PPM On Premise

Issue/Introduction

Inquiry regarding how Time-varying attributes for a Custom Object are stored within the Data Warehouse schema, along with a request to explain the differences in data storage between standard Fact tables and CRV Fact tables.

Environment

Clarity 16.3.2 onwards 

Resolution

In Clarity PPM, Time-Varying Attributes (TSVs) on custom objects undergo a significant transformation when moving from the transactional schema (ODF) to the Data Warehouse (DWH). While the application stores them in specialized "blob" or "slice" tables, the DWH flattens this data into a relational Fact-Period structure to make reporting much easier.

Below are the details on how its stored in DWH Schema

1. The Core Storage Table: When you enable a custom TSV for the Data Warehouse, the Load Data Warehouse job populates the data into Fact Tables based on the time scale.
   • For custom objects, the data is typically stored in tables following this naming convention:
     
     • DWH_CMN_PERIOD: This table contains the Clarity Fiscal Time Periods.
     • DWH_<CUSTOM_OBJECT_NAME>_FACTS: This table stores the Curve Facts of the TSV's of the object where TSV attribute is created 
     • DWH_ODF_<CUSTOM_OBJECT_NAME>_CRV_FACTS: This table stores Financial Plan data specifically.
       
       
2. The Data Structure (Fact vs. Dimension): Unlike the application side (where data might be in a single row with many columns or binary slices), the DWH uses a Long Format. Each "cell" in your TSV grid becomes a row in the Fact table
   
   
3. Inquiry regarding how Time-varying attributes for a Custom Object are stored within the Data Warehouse schema, along with a request to explain the differences in data storage between standard Fact tables and CRV Fact tables. Supporting Dimension Tables: To make sense of the money attribute, you will usually join the Fact table with these dimensions:
   • • DWH_CMN_PERIOD: To get the Start Date, End Date, and Period Name (e.g., "Jan 2026")
     • DWH_ODF_<CUSTOM_OBJECT_NAME> : To get the name and details of the custom object instance

Additional Information

In the Application Schema (Transactional), TSVs are stored in ODF_SSL_... tables which are optimized for fast UI rendering. In the DWH, the data is "De-normalized." This allows you to write a simple SUM(VALUE) query grouped by PERIOD_KEY without having to calculate slice overlaps or handle binary data.

Important Note: For a TSV attribute to appear in the DWH at all, you must ensure:

• The "Include in Data Warehouse" checkbox is checked on the attribute properties in Studio.
• Under Administration --> System Options --> Data Warehouse options Include Sql Curve Data must be checked followed by running Load Data warehouse Job in full Load.