Dimensional Model

Overview

The data warehouse follows the Kimball method designed as a star-schema dimensional model and organized into two data marts, one per data source, sharing conformed dimensions that enable cross-mart (enterprise) analysis.

Mart Fact Table Grain
Mart 1: 311 Service Requests Fact_311Service_Request One row per unique 311 service request
Mart 2: Traffic Volume Counts Fact_Traffic_Count_15Min One row per 15-minute volume count at a specific location and direction

Model Diagram

The diagram below was generated from the DBML source file using dbdiagram.io. You can view and interact with the live diagram at the link below.

🔗 View interactive diagram on dbdiagram.io

ERD diagram showing two star schemas connected via conformed dimensions

Entity-relationship diagram of the NYC Traffic & Road Infrastructure data warehouse. Two fact tables share conformed Dim_Date, Dim_Time, and Dim_Location dimensions.

Conformed Dimensions

Three dimensions are shared (conformed) across both marts, enabling cross-mart joins for enterprise-level queries:

Dimension Key Purpose
Dim_Date date_id Calendar date (year, month, day, full_date)
Dim_Time time_id Time-of-day (hour, minutes)
Dim_Location location_id Borough-level geographic grouping

Data Mart 1: 311 Service Requests

Grain: Each row is a unique 311 service request.

Fact Table: Fact_311Service_Request

Column Type Constraint Notes
unique_key varchar(8) PK Source unique identifier
location_id int FK → Dim_Location Borough
agency_id int FK → Dim_Agency Responding agency
problem_id int FK → Dim_Problem Complaint type
time_id int FK → Dim_Time Time of original creation
date_id int FK → Dim_Date Date of original creation
incident_location_id int FK → Dim_Incident_Location Full address detail
created_date_id int FK → Dim_Date Request created date
closed_date_id int FK → Dim_Date Request closed date (nullable)
due_date_id int FK → Dim_Date Request due date (nullable)
created_time_id int FK → Dim_Time
closed_time_id int FK → Dim_Time nullable
due_time_id int FK → Dim_Time nullable
latitude decimal nullable Point location
longitude decimal nullable Point location
additional_details varchar nullable Status / resolution notes

Dimension Tables

Stores the complaint type and any sub-type details. Reduces cardinality vs. storing free-text directly on the fact table.

Column Type Constraint
problem_id int PK
problem_type varchar e.g. “Street Condition”, “Street Light Condition”
problem_details varchar nullable sub-type
Column Type
agency_id int PK
agency_code varchar(20) e.g. “DOT”
agency_name varchar(200) full name

High-cardinality address data kept in a separate dimension to avoid bloating the fact table. Includes zip code (used in choropleth map analysis), cross streets, precinct, council district, and community board.

Column Type Notes
incident_location_id int PK
location_type varchar(100) nullable
incident_zip_code varchar(10) used in zip-level analysis
incident_address varchar(200) high-cardinality; nullable
cross_street_1/2 varchar(100)
intersection_street_1/2 varchar(100)
police_precinct varchar
council_district varchar
community_board varchar

Data Mart 2: Traffic Volume Counts

Grain: Each row is a volume count for a specific 15-minute interval at a specific location and direction.

Fact Table: Fact_Traffic_Count_15Min

Column Type Constraint Notes
requestid int PK DOT sensor request ID
date_id int FK → Dim_Date
time_id int FK → Dim_Time 15-min bucket
location_id int FK → Dim_Location Borough (conformed)
segment_id bigint FK → Dim_Street_Segment Road segment
wkt_geom varchar nullable WKT geometry string
vol int NOT NULL Vehicle count for interval

Dimension Tables

Captures the specific road segment where the sensor is located, including direction of travel.

Column Type
segment_id int PK
street varchar(100)
from_st varchar(100)
to_st varchar(100)
direction varchar(20) e.g. “NB”, “SB”

Design Decisions & Notes

Tip

Why separate Dim_Incident_Location from Dim_Location?
Dim_Location is a conformed, low-cardinality borough-level dimension shared with the traffic mart. Dim_Incident_Location holds high-cardinality address-level data specific to 311 requests, preventing fact table bloat and preserving the conformed dimension’s reusability.

Tip

Multiple date foreign keys on the fact table
Each 311 request has three relevant dates: created, closed, and due. Rather than using a single date dimension, three foreign keys point to the same Dim_Date table, a standard Kimball role-playing dimension pattern.

Warning

Known data issue: duplicate keys in Dim_Incident_Location
In the built warehouse table (dim_incident_location), the key column incident_location_key is not unique: several rows share the same key, and each copy carries the same ZIP code. Joining the 311 fact table straight to this dimension repeats each complaint once per copy. This inflated the first version of the ZIP-level counts (Query 6) above the true borough totals. Query 6 now collapses the dimension to one row per key before joining, and the ZIP table reconciles to the borough totals. A unique test on this key should be added to the dbt project so the duplicates are caught at build time.

The model diagram shows logical column names (incident_location_id, incident_zip_code). The built table uses incident_location_key and zip_code.

Note

Analysis grain for traffic
The traffic fact table stores 15-minute counts per segment and direction. The DOT counts only a few segments per borough on a given day, so the analysis queries first sum vol to one value per segment per day and then average across those segment-days. Summing across every segment counted in a borough-day would reward boroughs that simply had more segments counted.

Warning

Known challenge: geographic join between marts
The 311 data provides borough + zip code, while the traffic data provides a road segment + WKT geometry. Cross-mart geographic joins are therefore at the borough level rather than street-segment level. This is noted as a limitation in the Conclusion section.

DBML Source

The full DBML source for this model is version-controlled in the project repository.

🔗 View DBML on GitHub