Programs and Projects
1. Dataset name
Programs and Projects - Resource Management
2. Description
Org-unit-level dataset that holds every Resource Management program and project, with its attributes, user groups, age, and duration. Each org unit carries a roll-up of the demands tagged to it (total, staffed, open, draft, plus first and last demand dates) and the bookings made on those demands. Each row represents a single org unit × user group combination. It is built from customer_views.rm_programs_projects_view, with demand counts from customer_views.positions and booking counts from customer_views.employee_bookings. It is designed to be the primary source for program and project creation, completion, duration, and booking-volume analyses.
3. Source tables
- customer_views.rm_programs_projects_view — programs and projects; the anchor of the dataset
- customer_views.positions — demands, filtered to the resourcing system; used for the demand roll-up
- customer_views.employee_bookings — demand bookings; used for the booking roll-up
4. Grain
One row per org_unit_id × user group. An org unit assigned to multiple user groups appears once per user group. An org unit with no user groups appears once, with programs_projects_user_groups set to (No user group). Programs and projects share the dataset and are distinguished by org_unit_type.
5. Dashboards supported
- Resource Management
6. Metrics supported
| Metric | Supported breakdowns |
| Programs Created | — |
| Program Creation Trend | — |
| Projects Created | — |
| Project Creation Trend | — |
| Program Completion Rate | — |
| Active Programs by Booking Count | — |
| Active Projects by Booking Count | — |
| Program Duration Distribution | — |
| Project Duration Distribution | — |
7. Known exclusions and filters applied
- Deleted programs and projects are excluded.
- Programs and projects are held in the same dataset. Program metrics filter to program org units and project metrics to project org units, using
org_unit_type. - Because an org unit spans one row per user group, org-unit counts must use
COUNT(DISTINCT org_unit_id). The demand and booking counts are org-unit-level totals that repeat on every user-group row of that org unit, so they must not be summed across rows. - Demand counts include only resourcing demands that have not been deleted.
- A demand tagged with both a program and a project counts toward both. A program's counts include only demands tagged directly with that program; they are not rolled up from its child projects.
-
total_bookingsis a staffing roll-up: it counts demand bookings on the org unit's demands, excluding rejected and archived bookings. It intentionally differs from Bookings Created in the Bookings – Resource Management dataset, which counts bookings of every status to measure usage volume. -
org_unit_age_daysmeasures the time from org unit creation to today for every org unit, whatever its status. -
org_unit_duration_daysandorg_unit_duration_bucketare null when the org unit's start date or end date is missing. - Org units with no demands or bookings show 0 for all demand and booking counts.
- The dataset logic applies no date filter; it covers all-time history.
8. Schema
| Column name | Data type | Notes |
| org_unit_id | BIGINT | Primary org unit key; join key to the program and project IDs on demands |
| org_unit_name | VARCHAR | Name of the program or project |
| org_unit_type | VARCHAR | Type of org unit: program or project |
| org_unit_status | VARCHAR | Status of the program or project |
| org_unit_start_date | DATETIME | Date the program or project starts |
| org_unit_end_date | DATETIME | Date the program or project ends |
| org_unit_external_id | VARCHAR | External identifier of the program or project |
| parent_ext_id | VARCHAR | External identifier of the parent org unit; for a project, links to its program's org_unit_external_id |
| org_unit_created_at | DATETIME | Org unit creation timestamp; basis for Programs Created, Projects Created, and the creation trends |
| org_unit_updated_at | DATETIME | Timestamp of the org unit's most recent update |
| user_groups | VARCHAR | Full list of user groups assigned to the org unit, as stored on the record |
| programs_projects_user_groups | VARCHAR | Derived: a single user group ID per row, unnested from user_groups; (No user group) when none are assigned |
| org_unit_age_days | BIGINT | Derived: days from org_unit_created_at to today |
| org_unit_duration_days | BIGINT | Derived: days from org_unit_start_date to org_unit_end_date |
| org_unit_duration_bucket | VARCHAR | Derived bucket of org_unit_duration_days: A) <1 year, B) 1–2 years, C) 2–3 years, D) 3–4 years, E) 4–5 years, F) >5 years; NULL when either date is missing |
| first_demand_ts | DATETIME | Derived: creation timestamp of the earliest demand tagged to the org unit |
| last_demand_ts | DATETIME | Derived: creation timestamp of the most recent demand tagged to the org unit |
| total_demands | BIGINT | Derived: distinct demands tagged to the org unit |
| staffed_demands | BIGINT | Derived: distinct demands tagged to the org unit with status Staffed |
| open_demands | BIGINT | Derived: distinct demands tagged to the org unit with status Open |
| draft_demands | BIGINT | Derived: distinct demands tagged to the org unit with status Draft |
| total_bookings | BIGINT | Derived: non-rejected, non-archived demand bookings on the org unit's demands; powers Active Programs/Projects by Booking Count |