Bối cảnh và vấn đề nghiên cứu

Tai nạn giao thông là vấn đề nghiêm trọng tác động trực tiếp đến sức khỏe, tính mạng con người và trật tự an toàn xã hội. Cùng với sự gia tăng nhu cầu đi lại và cơ sở hạ tầng trong đời sống hiện đại, tình trạng giao thông ngày càng trở nên phức tạp. Bên cạnh những tổn thất trực tiếp về người và phương tiện, tai nạn giao thông còn gây ra gánh nặng kinh tế - tài chính lớn cho cá nhân và ngân sách quốc gia thông qua các khoản chi phí y tế, bồi thường bảo hiểm, sửa chữa hạ tầng và phương tiện giao thông.

Đề tài "Analysis of Accident (Phân tích dữ liệu tai nạn giao thông)" được thực hiện bởi nhóm sinh viên Lê Hoàng Khang, Nguyễn Thanh Hùng, Nguyễn Duy Thái dưới sự hướng dẫn của Th.S Nguyễn Văn Thành tại Khoa Công nghệ Thông tin, Trường Đại học Sư phạm Kỹ thuật TP.HCM (tháng 5/2023), thuộc môn học Kho dữ liệu (Mã lớp: DAWH430784_22_2_01).

Mục tiêu và nhiệm vụ nghiên cứu của đề tài bao gồm:

  1. Nghiên cứu, thu thập và xử lý các tập dữ liệu lớn về tai nạn giao thông đường bộ và các phương tiện liên quan.
  2. Thiết kế mô hình kho dữ liệu đa chiều nhằm chuẩn hóa các thông tin về không gian, thời gian, điều kiện thời tiết, đặc trưng đường bộ, thông tin người điều khiển và phương tiện.
  3. Xây dựng quy trình trích xuất, biến đổi và nạp dữ liệu (ETL) tự động từ các nguồn dữ liệu thô vào các tầng lưu trữ trung gian và kho dữ liệu chính.
  4. Triển khai mô hình phân tích trực tuyến đa chiều (OLAP) và xây dựng hệ thống báo cáo trực quan hóa, hỗ trợ việc nắm bắt nguyên nhân, mô hình tai nạn phục vụ công tác quản lý, ban hành chính sách an toàn giao thông và định hướng phát triển các công nghệ hỗ trợ lái xe an toàn.

Phạm vi nghiên cứu của đề tài tập trung vào hai nguồn dữ liệu thực nghiệm:

  • Phạm vi không gian: Gồm 49 tiểu bang tại Hoa Kỳ và toàn bộ mạng lưới giao thông đường bộ tại Vương quốc Anh.
  • Phạm vi thời gian: Dữ liệu ghi nhận các vụ tai nạn giao thông tại Hoa Kỳ trong giai đoạn 2016 – 2021 và tại Vương quốc Anh trong giai đoạn 2005 – 2017.

Cơ sở lý thuyết và phương pháp

Đề tài vận dụng lý thuyết nền tảng về hệ thống thông tin, cơ sở dữ liệu nâng cao và kỹ thuật xây dựng kho dữ liệu doanh nghiệp (Data Warehouse - DWH) kết hợp phân tích đa chiều (OLAP):

  • Khung kiến trúc kho dữ liệu: Sử dụng mô hình lưu trữ nhiều tầng gồm tầng nguồn mở rộng (External Source - ACCIDENT_SOURCE), tầng trung gian lưu trữ và làm sạch (Stage - ACCIDENT_STAGE), và tầng kho dữ liệu phân tích tập trung (Data Warehouse - ACCIDENT_DW).
  • Mô hình dữ liệu đa chiều: Sử dụng lược đồ hình bông tuyết (Snowflake Schema) để chuẩn hóa các bảng chiều (Dimension Tables) có phân cấp, giúp tối ưu hóa không gian lưu trữ và giảm thiểu dư thừa dữ liệu.
  • Phương pháp xử lý và làm sạch dữ liệu: Sử dụng các kỹ thuật xử lý dữ liệu bị thiếu (missing values) bằng phương pháp gán ngẫu nhiên (random imputation) theo phân phối dữ liệu, kỹ thuật tách/ghép chuỗi thời gian, bổ sung các thuộc tính đặc trưng và chuẩn hóa kiểu dữ liệu.

Nguồn dữ liệu gốc được nhóm tác giả thu thập từ hai tập dữ liệu công khai trên nền tảng Kaggle:

  • Tập dữ liệu US Accidents (2016 – 2021): Gồm hơn 4 triệu bản ghi mô tả tai nạn giao thông trên đường cao tốc và đường phố tại 49 tiểu bang của Hoa Kỳ, thu thập từ các trạm thu phí, báo cáo của cảnh sát và người dùng. Dữ liệu gốc bao gồm 47 thuộc tính phản ánh chi tiết vị trí tọa độ, thời gian, mức độ nghiêm trọng, điều kiện thời tiết, hạ tầng giao thông và trạng thái ánh sáng.
  • Tập dữ liệu UK Road Safety Accidents and Vehicles (2005 – 2017): Tập trung vào bảng Vehicle_Information.csv gồm 1.488.981 dòng dữ liệu và 24 thuộc tính chi tiết về phương tiện và người điều khiển phương tiện (độ tuổi tài xế, giới tính, loại xe, dung tích động cơ, điểm va chạm đầu tiên, trạng thái trượt/lật xe).
Nguồn dữ liệu Số lượng bản ghi / Dòng Số lượng thuộc tính gốc Phạm vi địa lý / Thời gian
US_Accidents > 4.000.000 47 thuộc tính 49 tiểu bang Hoa Kỳ (2016 – 2021)
UK Road Safety (Vehicle) 1.488.981 24 thuộc tính Vương quốc Anh (2005 – 2017)

Thiết kế và triển khai

Kiến trúc mô hình dữ liệu Snowflake Schema

Kho dữ liệu được thiết kế theo Lược đồ hình bông tuyết (Snowflake Schema) với một bảng sự kiện trung tâm (FactAccident) liên kết với 11 bảng chiều (Dimension) thông qua các khóa ngoại.

Cấu trúc chi tiết của Bảng FACT (FactAccident):

STT Tên thuộc tính Kiểu dữ liệu Ràng buộc / Ý nghĩa
1 ID Varchar Khóa chính (Primary Key)
2 LocationKey Int Khóa ngoại (Foreign Key) liên kết DimLocation
3 DateKey Nvarchar Khóa ngoại (Foreign Key) liên kết DimDate
4 TwilightKey Int Khóa ngoại (Foreign Key) liên kết DimTwilight
5 RoadFeatureKey Int Khóa ngoại (Foreign Key) liên kết DimRoadFeature
6 WeatherKey Int Khóa ngoại (Foreign Key) liên kết DimWeather
7 DriverKey Int Khóa ngoại (Foreign Key) liên kết DimDriver
8 SpeedLimitKey Int Khóa ngoại (Foreign Key) liên kết DimSpeedLimit
9 VehicleKey Int Khóa ngoại (Foreign Key) liên kết DimVehicle
10 Distance(mi) Float Khoảng cách đoạn đường xảy ra tai nạn (dặm)
11 Number_of_Casualties Int Số lượng thương vong
12 Severity Int Mức độ nghiêm trọng của tai nạn (thang 1 - 4)
13 Start_Time_Accident Datetime Thời điểm bắt đầu tai nạn

Cấu trúc các bảng Chiều (Dimension Tables):

  • DimDate: Quản lý chiều thời gian phân cấp (DateKey, FullDate, Year, Quarter, Month, Day, Hour, Minute, Weekday).
  • DimDriver: Quản lý thông tin tài xế (DriverKey, ID, Age_Band_of_Driver, Sex_of_Driver, Driver_IMD_Decile, Driver_Home_Area_Type, Journey_Purpose_of_Driver).
  • DimLocation: Quản lý chiều không gian địa lý (LocationKey, ID, Number, Street, Side, City, County, State, Zipcode, Country).
  • DimRoadFeature: Quản lý các đặc tính hạ tầng đường bộ (RoadFeatureKey, ID, Amenity, Bump, Crossing, Give_Way, Junction, No_Exit, Railway, Roundabout, Station, Stop, Traffic_Calming, Traffic_Signal, Turning_Loop).
  • DimRoadSurfaceCondition: Quản lý điều kiện bề mặt đường (ID, Road_Surface_Conditions).
  • DimRoadType: Quản lý phân loại đường bộ (ID, Road_Type).
  • DimSpeedLimit: Quản lý thông tin giới hạn tốc độ (Primary key, ID, Speed_limit).
  • DimTwilight: Quản lý điều kiện ánh sáng tự nhiên (TwilightKey, ID, Sunrise_Sunset, Civil_Twilight, Nautical_Twilight, Astronomical_Twilight).
  • DimVehicle: Quản lý đặc trưng phương tiện (VehicleKey, ID, Engine_Capacity_CC, Propulsion_Code, make, model, Junction_Location, Vehicle_Manoeuvre, Vehicle_Location, Restricted_Lane, X1st_Point_of_Impact, Was_Vehicle_Left_Hand_Drive, Year).
  • DimWeather: Quản lý thông số khí tượng đo đạc (WeatherKey, ID, Temperature(F), Wind_Chill(F), Humidity(%), Pressure(in), Visibility(mi), Wind_Direction, Wind_Speed(mph), Precipitation(in)).
  • DimWeatherCondition: Quản lý mô tả phân loại thời tiết tổng quát (ID, Weather_Condition).

Công nghệ và môi trường triển khai

Đề tài sử dụng hệ sinh thái phần mềm phân tích dữ liệu chuyên nghiệp của Microsoft:

  • Microsoft SQL Server 2019: Đóng vai trò hệ quản trị cơ sở dữ liệu quan hệ, dùng để thiết lập và lưu trữ các cơ sở dữ liệu ACCIDENT_SOURCE, ACCIDENT_STAGE, và ACCIDENT_DW.
  • Microsoft Visual Studio 2019: Môi trường phát triển tích hợp (IDE) dùng để xây dựng dự án tích hợp dữ liệu (SSIS) và dự án phân tích đa chiều (SSAS).
  • SQL Server Integration Services (SSIS): Xây dựng các gói (package) ETL, cấu hình Data Flow và Control Flow để xử lý, chuyển đổi và nạp dữ liệu qua từng phân tầng.
  • SQL Server Analysis Services (SSAS): Xây dựng khối đa chiều (Cube), cấu hình Data Source, Data Source View (DSV), định nghĩa các chiều (Dimension) và triển khai (Deployment) phục vụ truy vấn OLAP.
  • Microsoft Power BI: Công cụ kết nối đến khối dữ liệu SSAS và SQL Server để trực quan hóa thông tin dạng bảng biểu, biểu đồ tương tác.

Quy trình ETL với SSIS

Quy trình tích hợp và chuyển đổi dữ liệu được thiết kế theo các giai đoạn cụ thể:

  1. Nạp dữ liệu thô vào External Source (ACCIDENT_SOURCE): Sử dụng công cụ SQL Server Import Data với Destination là SQL Server Native Client 11.0 để chuyển đổi tập tin Accident.csv và vehicle.csv vào các bảng dữ liệu nguồn.
  2. Nạp dữ liệu từ External Source vào Stage (ACCIDENT_STAGE): Tạo project SSIS mang tên Final Project, cấu hình package SRC_To_Stage chứa các Data Flow Tasks độc lập để xử lý song song từng chiều:
    • DF_StageLocation: Trích xuất dữ liệu vị trí từ nguồn SRC_USAccident, ánh xạ (mapping) và nạp vào bảng StageLocation.
    • DF_StageDate: Trích xuất chuỗi thời gian từ SRC_USAccident, sử dụng transformation Derived Column với các hàm biểu thức để bóc tách ngày, tháng, năm, giờ, phút, thứ trong tuần; dùng transformation Aggregate để nhóm (Group by) đảm bảo tính duy nhất của DateKey, sau đó qua Sort để sắp xếp tăng dần trước khi nạp vào StageDate.
    • DF_StageVehicle: Trích xuất thuộc tính phương tiện từ SRC_Vehicle, cấu hình ánh xạ vào StageVehicle.
    • DF_StageRoad, DF_StageTwilight, DF_StageWeather, DF_StageAccident: Sử dụng transformation Data Conversion để ép kiểu dữ liệu phù hợp (kiểu số nguyên, float, chuỗi ký tự, bit) trước khi ghi dữ liệu vào các bảng tầng Stage tương ứng.
  3. Nạp dữ liệu từ Stage vào Data Warehouse (ACCIDENT_DW): Dữ liệu sau khi làm sạch và chuẩn hóa ở tầng Stage được nạp vào các bảng Dim và bảng Fact của kho dữ liệu ACCIDENT_DW.

Nội dung chính theo từng chương

Báo cáo đồ án được cấu trúc thành các phần nội dung chính như sau:

I. Tổng quan đề tài

Phần này trình bày lý do lựa chọn đề tài, tính cấp thiết của việc nghiên cứu dữ liệu tai nạn giao thông đối với sức khỏe cá nhân, quản lý nhà nước và phát triển giải pháp công nghệ mới. Nhóm tác giả giới thiệu chi tiết về 2 tập dữ liệu gốc (US_Accidents với 47 thuộc tính và Vehicle_Information với 24 thuộc tính). Đồng thời, phần này mô tả các công cụ phần mềm sử dụng (Visual Studio 2019, SQL Server 2019), quy trình xử lý làm sạch dữ liệu khuyết thiếu (giá trị rỗng của các cột tài xế, phương tiện; bù đắp chuỗi ngày giờ bị thiếu; điền khuyết ngẫu nhiên cho các thông số thời tiết; bổ sung các cột điều kiện mặt đường, loại đường, số thương vong). Tiếp đó, phần I công bố bản vẽ thiết kế mô hình Snowflake Schema cùng định nghĩa chi tiết của bảng FACT và 11 bảng DIM.

II. Xây dựng kho dữ liệu (SSIS)

Phần này mô tả chi tiết chủ đề phân tích tai nạn giao thông theo các chiều không gian, thời gian, thời tiết, ánh sáng, phương tiện và tài xế. Nội dung trọng tâm đi sâu vào từng bước cấu hình gói ETL (SSIS) trong Visual Studio:

  • Quy trình Import Data từ các tệp .csv vào cơ sở dữ liệu ACCIDENT_SOURCE.
  • Cấu hình Package SRC_To_Stage với các tác vụ Data Flow (DF_StageLocation, DF_StageDate, DF_StageVehicle, DF_StageRoad, DF_StageTwilight, DF_StageWeather, DF_StageAccident).
  • Ứng dụng các công cụ chuyển đổi dữ liệu chuyên biệt trong SSIS như Derived Column, Data Conversion, Aggregate, Sort để đảm bảo tính toàn vẹn và chuẩn hóa dữ liệu trước khi đẩy vào ACCIDENT_STAGE và ACCIDENT_DW.

III. Phân tích kho dữ liệu (SSAS)

Phần này trình bày các bước thiết lập phân tích đa chiều trong Visual Studio và SQL Server Analysis Services:

  • Tạo mới SSAS Project, thiết lập kết nối Data Source tới cơ sở dữ liệu ACCIDENT_DW.
  • Xây dựng Data Source View (DSV) định hình mối quan hệ giữa bảng sự kiện và các bảng chiều.
  • Chỉnh sửa thuộc tính và phân cấp trong các bảng Dimension (DIM).
  • Tiến hành thực thi quy trình biên dịch và triển khai (Deployment) khối dữ liệu (Cube).
  • Thực thi các câu truy vấn phân tích dữ liệu đa chiều trên SSAS.
  • Kết nối và xây dựng các báo cáo trực quan hóa dữ liệu bằng Microsoft Power BI.
  • Tổng kết các kết quả đạt được, chỉ ra những mặt hạn chế, lập bảng phân công nhiệm vụ thành viên và danh mục tài liệu tham khảo.

Kết quả và đóng góp

  • Xử lý và tích hợp thành công hai tập dữ liệu quy mô lớn: Đã hoàn thành việc tiếp nhận, làm sạch và tích hợp hơn 4 triệu dòng dữ liệu tai nạn giao thông từ Hoa Kỳ và gần 1,5 triệu dòng dữ liệu phương tiện từ Vương quốc Anh.
  • Xây dựng hoàn chỉnh mô hình kho dữ liệu Snowflake Schema: Định nghĩa chặt chẽ cấu trúc bảng sự kiện trung tâm FactAccident cùng 11 bảng chiều liên quan, phân tách rõ ràng các cấp độ thông tin từ chi tiết đến tổng quát.
  • Hiện thực hóa quy trình ETL tự động hóa bằng SSIS: Xây dựng thành công quy trình chuyển đổi dữ liệu nhiều tầng (Source -> Stage -> Data Warehouse), ứng dụng hiệu quả các phép biến đổi kỹ thuật như Derived Column để bóc tách thời gian, Aggregate để loại bỏ trùng lặp và Data Conversion để chuẩn hóa kiểu dữ liệu.
  • Hỗ trợ phân tích đa chiều: Triển khai thành công Cube trên SSAS và kết nối trực quan hóa với Power BI, cho phép phân tích mối tương quan giữa mức độ nghiêm trọng của tai nạn với các yếu tố thời tiết (nhiệt độ, tầm nhìn, lượng mưa), đặc điểm đường bộ (đèn giao thông, lối rẽ, vòng xuyến), điều kiện ánh sáng (bình minh, hoàng hôn) và thông tin phương tiện.

Hạn chế và hướng nghiên cứu tiếp

Hạn chế của đề tài:

  • Phương pháp xử lý dữ liệu khuyết thiếu: Nhóm sử dụng phương pháp gán giá trị ngẫu nhiên (random) đối với các trường thông tin thời tiết (Temperature, Wind_Chill, Humidity, Pressure, Wind_Speed, Precipitation) và một số bản ghi ngày tháng bị thiếu, điều này có thể tạo ra độ lệch nhất định so với điều kiện thực tế của vụ tai nạn.
  • Dữ liệu phương tiện và dữ liệu tai nạn được lấy từ hai quốc gia khác nhau (Hoa Kỳ và Vương quốc Anh) dẫn đến việc kết hợp dữ liệu mang tính giả định và thử nghiệm kỹ thuật mô hình hóa thay vì phân tích thống kê thuần nhất trên một thực thể địa lý duy nhất.

Hướng nghiên cứu và phát triển tiếp:

  • Áp dụng các thuật toán máy học hoặc mô hình thống kê nâng cao (như nội suy không gian - thời gian, K-Nearest Neighbors) để điền khuyết dữ liệu chính xác hơn thay cho phương pháp ngẫu nhiên.
  • Mở rộng kho dữ liệu với các nguồn dữ liệu giao thông thời gian thực và tích hợp thêm các mô hình phân tích dự báo (Predictive Analytics) nhằm cảnh báo các điểm đen tai nạn.

Giá trị tham khảo

Đồ án là tài liệu tham khảo hữu ích cho:

  • Sinh viên chuyên ngành Công nghệ Thông tin, Khoa học Dữ liệu, Kỹ thuật Phần mềm và Hệ thống Thông tin đang học môn Kho dữ liệu hoặc chuẩn bị làm đồ án tốt nghiệp về Data Warehouse và Business Intelligence.
  • Người nghiên cứu cần tài liệu tham khảo thực tế về quy trình thiết kế lược đồ bông tuyết (Snowflake Schema), các bước xây dựng Data Flow / Control Flow trong SSIS và các bước triển khai Cube trong SSAS.
  • Phần đáng tham khảo nhất là bảng đặc tả thuộc tính chi tiết của bảng FACT và 11 bảng DIM, cùng với các bước cấu hình bóc tách dữ liệu ngày giờ (Derived Column + Aggregate) trong SSIS.

Câu hỏi thường gặp

1. Đồ án sử dụng những nguồn dữ liệu nào và dung lượng của chúng ra sao? Đồ án sử dụng hai bộ dữ liệu công khai trên Kaggle:

  • Bộ dữ liệu US_Accidents (2016 – 2021) gồm hơn 4 triệu bản ghi và 47 thuộc tính ban đầu, thu thập từ 49 tiểu bang tại Hoa Kỳ.
  • Bộ dữ liệu UK Road Safety Accidents and Vehicles (2005 – 2017) (tập tin Vehicle_Information.csv) gồm 1.488.981 dòng và 24 thuộc tính.

2. Nhóm tác giả đã xử lý các dữ liệu bị thiếu (missing values) như thế nào?

  • Đối với tập dữ liệu vehicle.csv: Nhóm xử lý giá trị rỗng trên các cột Age_Band_of_Driver, Age_of_Vehicle, Driver_Home_Area_Type, Sex_of_Driver, và Junction_Location.
  • Đối với tập dữ liệu Accident.csv: Những bản ghi bị mất phần ngày (chỉ còn phần giờ, ví dụ "3/25/2016 16:36" bị mất thành "16:36") được thay thế bằng phương pháp lấy ngày ngẫu nhiên phù hợp; các cột thời tiết (Temperature, Wind_Chill, Humidity, Pressure, Wind_Speed, Precipitation) được điền khuyết bằng phương pháp random.

3. Bảng FACT trong kho dữ liệu chứa các thông tin đo lường (measures) và khóa ngoại nào? Bảng sự kiện FactAccident gồm khóa chính ID, các thước đo: Distance(mi), Number_of_Casualties, Severity, Start_Time_Accident, cùng với 8 khóa ngoại liên kết tới các bảng chiều: LocationKey, DateKey, TwilightKey, RoadFeatureKey, WeatherKey, DriverKey, SpeedLimitKey, và VehicleKey.

4. Những công cụ và kỹ thuật biến đổi nào trong SSIS được sử dụng để xử lý dữ liệu thời gian trong DF_StageDate? Trong Data Flow DF_StageDate, nhóm tác giả sử dụng transformation Derived Column cùng các hàm biểu thức để trích xuất các thành phần ngày, tháng, năm, giờ, phút, thứ từ chuỗi thời gian gốc; sau đó dùng transformation Aggregate để nhóm (Group by) tạo khóa DateKey duy nhất, và dùng Sort để sắp xếp dữ liệu tăng dần trước khi nạp vào StageDate.

5. Hệ sinh thái công nghệ được sử dụng để hoàn thành đồ án gồm những phần mềm nào? Hệ thống sử dụng trọn bộ giải pháp của Microsoft gồm: Microsoft SQL Server 2019 (quản trị CSDL và lưu trữ DWH), Microsoft Visual Studio 2019 (môi trường lập trình tích hợp), SQL Server Integration Services - SSIS (xây dựng quy trình ETL), SQL Server Analysis Services - SSAS (xây dựng và triển khai khối phân tích đa chiều), và Microsoft Power BI (trực quan hóa dữ liệu và báo cáo).

Kết luận

Đồ án môn học "Analysis of Accident (Phân tích dữ liệu tai nạn giao thông)" của nhóm sinh viên Trường Đại học Sư phạm Kỹ thuật TP.HCM đã hoàn thành trọn vẹn quy trình xây dựng hệ thống kho dữ liệu từ khâu chuẩn bị, làm sạch dữ liệu lớn đến thiết kế mô hình đa chiều Snowflake Schema. Bằng việc ứng dụng đồng bộ bộ công cụ Microsoft SQL Server 2019, SSIS, SSAS và Power BI, đề tài đã thiết lập thành công đường ống tích hợp dữ liệu nhiều tầng và khối đa chiều phục vụ phân tích tai nạn giao thông theo nhiều góc nhìn. Toàn bộ các bước thực hiện, cấu trúc bảng dữ liệu và luồng xử lý ETL đều được ghi nhận rõ ràng, mang lại giá trị thực hành cao cho việc học tập và nghiên cứu trong lĩnh vực kỹ thuật dữ liệu.