Catalog the questions research admins want to ask, then test the schema against them

[nate-layman]

Context

The schema-design issues we've raised so far (#43–#48) argue from concepts. A complementary, possibly more useful, way to validate the UDM is to start from the questions a research administrator actually wants to ask and work backwards. If the schema makes a question trivial, great. If it makes a common question a multi-hour SQL exercise, that's a gap.

This issue is a place to collect those questions. The goal is not to answer them here, but to enumerate them so we can pressure-test the UDM and the reference views against the real workload.

Seed questions

A few to start with. Some are already easy in the model; others surface gaps:

• How many NSF proposals did we submit last year, and what was the indirect-cost rate on each?
  Touches: sponsor type / funding source (Sponsor type needs a richer taxonomy than the current Organization_Type value #46, Funding source as a first-class concept distinct from Sponsor #47), proposal lifecycle, indirect-rate history.
• How much time was provided by the PI for review by OSP before each proposal was due?
  Touches: temporal events (Deadline as a first-class concept across the UDM #44), status-change history, deadlines on RFAs vs. internal review timestamps.
• How many proposals does each PI have in flight, broken down by status?
• What's the median time from proposal submission to award notification, by sponsor?
• Which awards are within 90 days of expiration and don't have a no-cost extension on file?
• Which active awards have overdue technical or financial deliverables?
• What's the F&A revenue forecast for the next four budget periods?
• Which departments are over-committed on effort relative to FTE?
• Which compliance protocols are expiring in the next 60 days, and which awards do they cover?
• How many active subawards do we have, who's the responsible PI, and which subrecipients haven't submitted invoices in the last quarter?
• Which awards crossed the single-audit threshold this fiscal year?
• Which RFAs have an upcoming deadline that no proposal has been started against yet?
• For a given PI, show every funded effort over their career and the originating sponsor of each.

Why this is worth doing now

• Validation. The fastest way to find out whether the UDM is the right shape is to throw the workload at it.
• Reference views. Every recurring question is a candidate for a reference view; the eight we ship today were chosen ad hoc.
• Conference and audience input. The AI4RA workshop audience is exactly the population whose questions we want here. Surfacing this issue gives a single place for them to drop suggestions.

How to contribute

Drop a comment with one or more questions in plain language. Optional: tag with the role (PI, OSP analyst, leadership, finance), the cadence (daily, monthly, annual), and whether you can answer it today with your current systems.

What to do with the answers

• Cluster questions by domain.
• For each cluster, audit the UDM tables involved. Note any gap.
• Promote frequently-asked questions to reference views (vw_*) where the JOIN pattern is common enough to standardize.
• Feed the gaps back into the schema-design issues (Award lifetime, proposal UIDs, and the project/account boundary #43, Deadline as a first-class concept across the UDM #44, Sponsor type needs a richer taxonomy than the current Organization_Type value #46, Funding source as a first-class concept distinct from Sponsor #47, Disambiguate the three sponsor relationships: who pays, where the money originated, and what kind of sponsor it is #48).

Related issues

• Award lifetime, proposal UIDs, and the project/account boundary #43 (Award lifetime, proposal UIDs)
• Deadline as a first-class concept across the UDM #44 (Deadlines / temporal events)
• Project terminology collides with PeopleSoft's Project; disambiguation needed #45 (Project terminology collision)
• Sponsor type needs a richer taxonomy than the current Organization_Type value #46 (Sponsor type taxonomy)
• Funding source as a first-class concept distinct from Sponsor #47 (Funding source as a concept)
• Disambiguate the three sponsor relationships: who pays, where the money originated, and what kind of sponsor it is #48 (Three sponsor relationships)