Data Warehouse Facts tables values for time varying column is not matching MUX

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The values for a TSV column of the key results fact table (dwh_okr_key_result_facts) do not match what is seen on the MUX screen.

For example, the MUX per periods metric shows value 7.69 for TSV field "A", but the database shows 27684

MUX:

Query example

SELECT KEY_RESULT_KEY, A FROM dwh_okr_key_result_facts WHERE PERIOD_KEY =3009497 AND KEY_RESULT_KEY = 5014370

Environment

Clarity 16.4.0

Cause

This discrepancy occurs because of how time-based fields are stored in the Data Warehouse (DWH).

Resolution

While the MUX screen displays time in hours, the Data Warehouse stores these values in seconds. To align the database values with the UI, you must convert the raw seconds back into hours.

The Conversion Logic
To derive the human-readable value, follow these steps:

• Identify the unit: The raw value is stored in total seconds.
• Apply the formula: Divide the value by 3600 (the number of seconds in one hour).
• Format the output: Round the final result to two decimal places.

Calculation Example:

Using value of 27692.307692:

27692.307692 / 3600 = 7.6923

Once rounded to two decimal places, this equals 7.69 (or 7.7 depending on UI rounding).

 
SQL Snippet 
When querying the dwh_okr_key_result_facts table, use the following logic to ensure your reports match the MUX screen:

SELECT ROUND(A / 3600, 2) AS actual_result_hours

FROM

dwh_okr_key_result_facts WHERE PERIOD_KEY =3009497 AND KEY_RESULT_KEY = 5014370;