What’s your approach to handling slowly changing dimensions or historical reporting in a dashboard model?
Write a PostgreSQL query that attributes each sale to the dimension version valid on the sale date, then produces the requested historical reporting output.
report_month, service_line, monthly_revenue, previous_reported_revenue, and revenue_change.service_line, then report_month ascending.| Column | Type | Description |
|---|---|---|
| employee_skPK | INT | Surrogate key for a dimension version |
| employee_id | VARCHAR(10) | Stable employee business key |
| employee_name | VARCHAR(100) | Employee name for the dimension version |
| service_line | VARCHAR(50) | Service line assigned during the effective period |
| effective_from | DATE | Inclusive start date of the dimension version |
| effective_to | DATE | Exclusive end date, or NULL for the current version |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sale identifier |
| employee_id | VARCHAR(10) | Employee business key associated with the sale |
| sale_date | DATE | Date on which the sale occurred |
| revenue | NUMERIC(12,2) | Revenue recognized for the sale |