1371606 - Snowflake Schema Based Data Warehouse for Analyzing Crash, Citation, and Warning Traffic Safety Records

Decision-makers in traffic agencies and police departments require a wide variety of high-quality data to support traffic safety problem identification, program implementation, and result evaluation. A major challenge is integrating traffic safety data collected and managed in different systems and databases, often across multiple agencies. Modern data warehouses, which support large data storage and multidimensional analysis for applications, provide an opportunity to overcome these challenges and are appropriate to utilize for traffic safety-related execution and assessment. This presentation describes the design and implementation of a Wisconsin traffic safety data warehouse for safety analysis around the six core traffic record systems defined by NHTSA’s model performance measures: crash, vehicle, driver, roadway, citation and adjudication, and injury surveillance. As a first step, the development of the Wisconsin data warehouse is focused on linking crash records to citations and warnings. The design of the data warehouse is determined by the selection of the data source, description of the data flow architecture, and design of the data warehouse schema. The snowflake schema design has been adopted to integrate multidimensional data sources because it provides a highly generalized representation that supports a wide variety of traffic safety queries and analyses. The suitability and validity of this data warehouse design would benefit traffic safety analysis research and interest other state agencies.