Case grain — pzInsKey as natural key; cycle-time fields.
CREATE TABLE curated_fct_pega_case AS
SELECT
c.pz_ins_key,
c.py_id,
c.py_class,
c.py_status_work,
c.py_urgency,
c.px_create_datetime,
c.px_resolved_ts,
CASE WHEN c.py_status_work LIKE 'Resolved-%' THEN TRUE ELSE FALSE END AS is_resolved
FROM raw_pega_case c;
Curated dim operator / SLA breach fact
Operator without email/name; SLA breach fact from SLA event.
CREATE TABLE curated_dim_pega_operator AS
SELECT
py_user_identifier,
py_org_unit
-- omit py_email_address, py_user_name from default analytics dims
FROM raw_pega_operator;
CREATE TABLE curated_fct_pega_sla_breach AS
SELECT
s.sla_event_id,
s.case_id,
s.goal_passed,
s.deadline_passed,
s.created_at
FROM raw_pega_sla_event s
WHERE s.case_id IS NOT NULL;
Curated measure SELECTs
Cases created/resolved, SLA breaches, cycle time — adapt period filters.
-- Cases created in period
SELECT COUNT(*) AS cases_created
FROM curated_fct_pega_case
WHERE px_create_datetime BETWEEN :period_start AND :period_end;
-- Cases resolved in period
SELECT COUNT(*) AS cases_resolved
FROM curated_fct_pega_case
WHERE is_resolved = TRUE
AND px_resolved_ts BETWEEN :period_start AND :period_end;
-- SLA deadline breaches in period
SELECT COUNT(*) AS sla_breaches
FROM curated_fct_pega_sla_breach
WHERE deadline_passed = TRUE
AND created_at BETWEEN :period_start AND :period_end;
-- Average cycle time (resolved cases)
SELECT AVG(px_resolved_ts - px_create_datetime) AS avg_cycle_time
FROM curated_fct_pega_case
WHERE is_resolved = TRUE;
Tools
Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.