# Design a data model for HR reporting and analytics

A warehouse design for HR data with effective dating, clear grain, agreed metric definitions, and access controls for sensitive fields.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Advanced
- Tags: HR & Hiring, Data & Analytics, Leadership, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/design-a-data-model-for-hr-reporting-and-analytics

## Prompt

```text
Help me design a data model for HR reporting and analytics in our data warehouse.

**Source systems:** [HRIS, applicant tracking, payroll, performance, learning, benefits - e.g. "Workday, Greenhouse, ADP"]
**Data in scope:** [headcount, hires, exits, compensation, performance, benefits, engagement]
**Questions it must answer:** [e.g. "monthly headcount by department", "attrition by manager", "time to fill", "pay equity by level"]
**Tools:** [warehouse, transformation tool, BI tool]
**Who will use it:** [HR business partners, finance, executives, data scientists]

Design for these HR-specific needs:
- **Effective dating:** people change jobs, managers, departments, and pay over time. Use slowly changing dimensions or effective-dated snapshots so I can report "as of" any date.
- **Grain:** define the grain of every fact table (e.g. one row per employee per month for headcount, one row per event for hires, exits, and promotions).
- **Org hierarchy:** handle reorganizations and manager chains without breaking history.
- **Sensitive data:** keep compensation, performance, health, and demographic data in restricted tables, with row- and column-level security and minimum group sizes so small groups can't be re-identified.
- **Definitions:** one agreed definition each for headcount, FTE, attrition (voluntary, involuntary, regretted), and tenure.

Return:
1. A star-schema outline: fact and dimension tables, grain, key columns, and keys.
2. A metric dictionary: Metric | Definition | Formula | Source table.
3. An access-control plan by user group.
4. Data quality checks to automate (e.g. duplicate employee IDs, missing managers, overlapping effective dates).
5. Build order: what to deliver first for quick value.

Flag where privacy or employment law in [COUNTRY or REGION] may limit what we store or analyze, and tell me to confirm with legal or our data protection officer.
```

## What to fill in

- warehouse, transformation tool, BI tool
- HR business partners, finance, executives, data scientists
- COUNTRY or REGION

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.