Governance and reference
Data source register
Every visual in the report and the source it depends on, quoted from MODEL.md. MODEL.md names 23 source tables; none has been checked against the EPMS data model, which holds 74 tables and is at Pass 3 — a design in progress, not a populated system. Nothing in this register is marked confirmed, because nothing in this repository can confirm it. This is the list the EPMS data model owner answers against.
UNVERIFIED25CONFIRMED0DESIGN ONLY0DERIVED7PLANNED ONLY12
| Visual | Measure or field it reads | Source table, as MODEL.md names it | State | Note |
|---|---|---|---|---|
| 1.1 | Projects in delivery (T-01) | seco_project · seco_stage | UNVERIFIED | Stage-at-today read live from the project record. |
| 1.2 | Allocation against capacity (T-02), next 4 weeks | seco_assignment · seco_capacityentry | PLANNED ONLY | Planned hours against available hours. No actual hours exist in this model; the screen reads as occupancy and does not say so plainly. Databricks capacity tables sit on a test connection; no columns seen (Q12). Smartsheet machine capacity diaries stop in 2023. |
| 1.3 | On-time gate rate (T-03), rolling 90 days | seco_projectgate | UNVERIFIED | Planned date against actual date. Gates are captured in EPMS from go-live. No gate dates exist in any mapped Smartsheet sheet; per-project task plans (BV_UK_IH00xx) carry task dates for about ten projects only. |
| 1.4 | Overdue actions (T-04) | seco_action · seco_actionlog | UNVERIFIED | Per-project action logs exist in Smartsheet (Paul, 10 Sep) but are not in the UK mapping. |
| ⚑ 1.5 | Request-to-route cycle time (T-05) | seco_request · seco_projectgate | UNVERIFIED | MODEL.md gives Request days-to-qualification and names no route decision timestamp. The business does hold the clock: the UK Smartsheet mapping carries Date_the_project_was_loaded_onto_Smartsheet, Project_Request_Sent_to_RM, Project_Evaluation_by_Regional_Manager and Project_review_date, and already computes Time_taken_from_loading_to_project_review_DAYS. The gap is in MODEL.md, not in the business. |
| ⚑ 1.6 | Feedback completion rate (T-06) | Not named in MODEL.md · held today in Smartsheet | UNVERIFIED | MODEL.md names no feedback source, but the business holds one: the UK Smartsheet mapping carries the Innovation_Hub_Master_CUSTOMER_Feedback_Form and Survey_Questionnaire sheets, with Satisfaction_report_sent_to_customer, Satisfaction_report_sent_to_the_TSE, Number_of_completed_surveys and Average_satisfaction_score. |
| 1.7 | Projects by stage over 12 months — Date[Month] × Stage[Group] | Project snapshot (derived) · seco_stage | DERIVED | MODEL.md: "Built by the pipeline, not read live." No Dataverse table. The nightly snapshot job does not exist. |
| 1.8 | Open projects by i-Hub | seco_project · seco_ihub | UNVERIFIED | |
| 1.9 | Attention strip — Blocker cause, Project, i-Hub, Owner, Days blocked, Stage | Blocker cause (derived) · seco_project · seco_action · seco_projectgate | DERIVED | MODEL.md: blocker cause "does not exist in Dataverse. It is computed in the pipeline." Days blocked derives from the same pipeline. |
| ⚑ 1.9 (prototype) | Customer, Decision needed | account · not named in MODEL.md | UNVERIFIED | Prototype adds a Customer column and a Decision needed column. PAGES.md 1.9 lists neither. Both recorded, neither resolved. |
| 2.1 | The week ahead — Planned hours, Allocation against capacity, over-allocation flag | seco_assignment · seco_capacityentry · systemuser/seco_userprofile/seco_resource · seco_project | PLANNED ONLY | Forward horizon 7/14/28 days. Planned hours only. Databricks capacity tables sit on a test connection; no columns seen (Q12). Smartsheet machine capacity diaries stop in 2023. |
| 2.2 | Who is on what — Person × Project, Allocation against capacity | seco_assignment · seco_capacityentry | PLANNED ONLY | Databricks capacity tables sit on a test connection; no columns seen (Q12). Smartsheet machine capacity diaries stop in 2023. |
| ⚑ 2.3 | Project Leaders by stage — Person × Stage[Group], Projects | seco_project · seco_stage · systemuser/seco_userprofile | UNVERIFIED | Project Leader is a Person attribute of the project; MODEL.md names no lead column on seco_project. |
| ⚑ 2.1 (prototype) | Customer | account | UNVERIFIED | Prototype shows Customer as its own clearance-withheld column; PAGES.md 2.1 does not list it. |
| 3.1 | Projects by stage — i-Hub × Stage[Group] | seco_project · seco_stage · seco_ihub | UNVERIFIED | Stage-at-today, read live. Does not need the snapshot. |
| 3.2 | Stage ageing — Median days in stage | Project snapshot (derived) | DERIVED | Days in stage cannot be computed without the daily snapshot. Whole visual depends on an unbuilt pipeline. |
| 3.3 | Volume over time — Projects created, small multiples by Region | seco_project · seco_country (Region) | UNVERIFIED | 24 months. Region grouping comes from seco_country. |
| 3.4 | Volume by segment — Customer segment × Projects | seco_customersegment · account | UNVERIFIED | |
| 3.x stage strip | Stage-at-date wherever the strip appears | Project snapshot (derived) | DERIVED | Any historic stage reading, on any page, is snapshot-dependent. |
| 4.1 | Blocker table — Blocker cause, Project, i-Hub, Owner, Days blocked, Stage, Last activity | Blocker cause (derived) · seco_action · seco_projectgate · seco_project | DERIVED | Last activity requires a modified-on sweep across child records; MODEL.md describes the rule, names no table. |
| 4.2 | Overdue actions by owner × age band | seco_action · seco_actionlog | UNVERIFIED | Age band 1–7 / 8–30 / 31+ is derived in the model from statuscode and due date. Per-project action logs exist in Smartsheet (Paul, 10 Sep) but are not in the UK mapping. |
| ⚑ 4.3 | Cycle time distribution — days request → route decision | seco_request · seco_projectgate | UNVERIFIED | Same route-decision clock as 1.5: held in Smartsheet today, unnamed in MODEL.md. |
| 4.4 | On-time gate rate — Date[Month] × rate | seco_projectgate | UNVERIFIED | Gates are captured in EPMS from go-live. No gate dates exist in any mapped Smartsheet sheet; per-project task plans (BV_UK_IH00xx) carry task dates for about ten projects only. |
| ⚑ 4.x (prototype) | Approval steps unresolved beyond SLA | Not named in MODEL.md | DERIVED | MODEL.md defines the derivation but names no approval-step table or SLA column in Dataverse. |
| 5.1–5.4 | Allocation against capacity (T-07 to T-10) — UK, Germany, US, global | seco_assignment · seco_capacityentry · seco_ihub | PLANNED ONLY | Same scale on all four. Planned against available; no actuals. Databricks capacity tables sit on a test connection; no columns seen (Q12). Smartsheet machine capacity diaries stop in 2023. |
| 5.5 | Capacity grid — Person × Week (12 forward), Allocation against capacity | seco_assignment · seco_capacityentry | PLANNED ONLY | Forward-looking by construction. Databricks capacity tables sit on a test connection; no columns seen (Q12). Smartsheet machine capacity diaries stop in 2023. |
| ⚑ 5.6 | Capacity by skill — Skill × Planned hours vs Available hours | Skill: no dimension in MODEL.md · seco_assignment · seco_capacityentry | PLANNED ONLY | MODEL.md's eleven dimensions include no Skill. Competency data has no named source. Hours are planned only. Candidate, unverified: PROJUS dimcompetence. |
| 5.7 | Unallocated capacity — Date[Week] × i-Hub, Unallocated hours | seco_capacityentry − seco_assignment | PLANNED ONLY | Available minus planned. Not idle time; nothing here observes what people did. |
| A · Header | Project name, reference, Customer, i-Hub, Project type, Project Leader, Stage, restriction flag | seco_project · account · seco_ihub · seco_projecttype · seco_stage | UNVERIFIED | Restriction flag is a seco_project attribute per MODEL.md; clearance state sits on Person. |
| A · Gate history | Planned vs actual date per gate, Gate variance days | seco_projectgate | UNVERIFIED | |
| A · Open actions | Action, Owner, Due date, Action age days | seco_action · seco_actionlog | UNVERIFIED | |
| A · Team | Person, Planned hours next 8 weeks | seco_assignment | PLANNED ONLY | |
| ⚑ A · Activity | Last 20 events, newest first | Not named in MODEL.md | UNVERIFIED | No audit or event fact in the star schema. Source to be identified, or the section drops. |
| B · Header | Name, i-Hub, Role, Manager, clearances with expiry state | systemuser · seco_userprofile · seco_resource · seco_ihub | UNVERIFIED | MODEL.md flattens these three into Person and states clearance state is carried. |
| B · Allocation | Allocation against capacity (T-14), 12 forward weeks | seco_assignment · seco_capacityentry | PLANNED ONLY | |
| B · Projects | Project, Stage, their Planned hours | seco_assignment · seco_project · seco_stage | PLANNED ONLY | |
| B · Open actions | Action, Project, Due date, Action age days | seco_action | UNVERIFIED | |
| B · Gates owned | Gate, Project, planned date, days to or past | seco_projectgate | UNVERIFIED | |
| T1 | Full eight-stage breakdown for the hovered i-Hub | seco_stage · seco_gatetemplatestep · seco_project | UNVERIFIED | Eight-stage spine comes from the Stage dimension, not from gate numbers. |
| T2 | That person's next four weeks of allocation | seco_assignment · seco_capacityentry | PLANNED ONLY | |
| T3 | That owner's five oldest overdue actions | seco_action | UNVERIFIED | |
| T4 | Which projects make up that allocation in that week | seco_assignment · seco_project | PLANNED ONLY | |
| ⚑ P · Cycle time and delivery (proposal) | Stage-to-stage durations, gate variance, on-time trend | seco_projectgate · seco_request · Project snapshot (derived) | DERIVED | Proposal screen, unnumbered, awaiting decision. Its stage-duration reading is snapshot-dependent. C.2 and C.7: Gates are captured in EPMS from go-live. No gate dates exist in any mapped Smartsheet sheet; per-project task plans (BV_UK_IH00xx) carry task dates for about ten projects only. |
| ⚑ P · Request funnel (proposal) | Request counts by status — Raised, Qualified, Rejected; days to qualification | seco_request | UNVERIFIED | Proposal screen. MODEL.md names seco_request but not its status values; the prototype's three states come from the prototype's own seed. |
⚑ marks a row where MODEL.md and the prototype disagree, or where MODEL.md names no source at all. Both readings are recorded; neither is resolved here.
Filters — source line (D4-16)
| Filter | Source line |
|---|---|
| Region | Smartsheet |
| Country | Smartsheet |
| Sales area | Smartsheet |
| Customer | Smartsheet |
| Segment | Smartsheet |
| Project Leader | Smartsheet |
| Stage | No source yet |
| Gate status | No source yet |
| Team | No source yet |
| Route type | No source yet |
Country, Sales area, Team and Route type values in the prototype are placeholders — source unconfirmed.
Fields the migration must carry
| Field | Needed by | What the prototype does today |
|---|---|---|
| Date the project was loaded onto Smartsheet · Project request sent to RM · Project evaluation by Regional Manager · Project review date · Time taken from loading to project review (days) | Request-to-route cycle time (1.5, 4.3, C.1, C.4) | Held in the UK Smartsheet mapping and already computed there. MODEL.md names none of these columns, so the migration must be told to carry them. |
| Customer feedback form and Survey questionnaire sheets · Satisfaction report sent to customer · Satisfaction report sent to the TSE · Number of completed surveys · Average satisfaction score | Feedback completion rate (1.6) | Held in the UK Smartsheet mapping. MODEL.md names no feedback fact at all, so the measure has no home in the specification even though the business has the data. |
| Assignment timestamp on an action | Blocker causes Awaiting customer and Awaiting partner (4.1, 1.9) | The clock runs from the action's creation date, because no column records when the action was assigned to a customer or partner contact. Both ages are therefore the age of the action, not the age of the wait. |
What the five states mean
| UNVERIFIED | MODEL.md names the table; nobody has confirmed it exists in EPMS. |
| CONFIRMED | The table exists in the EPMS model and is reporting-enabled. Nothing in this register carries this state. |
| DESIGN ONLY | The table exists in the model but is not populated. |
| DERIVED | No source table at all; produced by a pipeline that is not built. |
| PLANNED ONLY | The figure is a plan, never an actual. |
Planned only is not a data quality caveat. It is the boundary the programme chose: this model holds planned assignment hours and available capacity hours, and no actual hours at all. A reader who sees allocation 75.7% and understands real occupancy is reading it wrong, and the screens do not yet say so plainly.