Tiểu luận Xây dựng kho dữ liệu và OLAP trên dữ liệu các vụ tai nạn giao thông tại Anh 2005 - 2015
LỜI CẢM ƠN
Đầu tiên, nhóm xin gởi lời cảm ơn chân thành đến cô Đỗ Thị Minh Phụng
(Giảng viên môn Kho dữ liệu và OLAP) đã giúp cho nhóm có những kiến thức
cơ bản làm nền tảng để thực hiện đề tài này. Cô đã trực tiếp hướng dẫn tận tình,
sửa chữa và đóng góp nhiều ý kiến quý báu giúp nhóm hoàn thành tốt báo cáo
môn học của mình. Trong thời gian một học kỳ thực hiện đề tài, nhóm đã vận
dụng những kiến thức nền tảng đã tích lũy đồng thời kết hợp với việc học hỏi và
nghiên cứu những kiến thức mới. Từ đó, nhóm vận dụng tối đa những gì đã thu
thập được để hoàn thành một báo cáo đồ án tốt nhất. Tuy nhiên, trong quá trình
thực hiện, nhóm không tránh khỏi những thiếu sót. Chính vì vậy, nhóm rất mong
nhận được những sự góp ý từ phía các cô nhằm hoàn thiện những kiến thức mà
nhóm đã học tập và là hành trang để nhóm thực hiện tiếp các đề tài khác trong
tương lai.
Xin chân thành cảm ơn cô!
1
NHẬN XÉT CỦA GIẢNG VIÊN
….……………………………………………………………………...…………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
2
Mục Lục
LỜI CẢM ƠN.......................................................................................................................................1
NHẬN XÉT CỦA GIẢNG VIÊN........................................................................................................2
CHƯƠNG 1: TỔNG QUAN VỀ ĐỀ TÀI...........................................................................................7
1.1 Giới thiệu bộ dữ liệu...............................................................................................................7
Thông tin về bộ dữ liệu.........................................................................................7
Ý nghĩa của đề tài .................................................................................................7
1.2 Mô tả bộ dữ liệu gốc ...............................................................................................................8
Mô tả dữ liệu .........................................................................................................8
Mô tả thuộc tính....................................................................................................8
1.3 Mô tả thuộc tính từng bảng khi tạo sơ đồ hình sao ...........................................................10
Bảng Dim_Urban_or_Rural_Area............................................................................10
Bảng Dim_Accident_Severity....................................................................................10
Bảng Dim_Police_Force.............................................................................................10
Bảng Dim_Road_Surface_Conditions ......................................................................11
Bảng Dim_Weather_Conditions................................................................................11
Bảng Dim_Light_Conditions .....................................................................................11
Bảng Dim_Road_Type ...............................................................................................11
Bảng Dim_Date...........................................................................................................11
Bảng Dim_Time..........................................................................................................11
Bảng Fact.....................................................................................................................12
1.4 Sơ đồ hình sao.......................................................................................................................13
1.5 Các câu hỏi truy vấn (15 câu)..............................................................................................13
CHƯƠNG 2: QUÁ TRÌNH SSIS ......................................................................................................15
2.1 Tạo Database trống trong SQL để đổ dữ liệu vào .............................................................15
2.2 Tạo Project và thiết lập kết nối ...........................................................................................15
Tạo mới “Integration Service Project”.............................................................15
2.3 Quá trình làm sạch dữ liệu ..................................................................................................19
2.4 Thực hiện tạo các bảng Dim ................................................................................................25
Tạo bảng Dim_Accident_Severity.....................................................................29
3
Tạo bảng Dim_Police_Force..............................................................................33
Tạo bản Dim_Light_Conditions........................................................................43
Tạo bảng Dim_Road_Type ................................................................................46
Tạo bảng Dim_Date............................................................................................50
Tạo bảng Dim_Time...........................................................................................57
2.5 Tạo bảng Fact .......................................................................................................................64
2.6 Tạo và xóa các ràng buộc SQL............................................................................................90
2.7 Thực thi Package..................................................................................................................98
CHƯƠNG 3: QUÁ TRÌNH SSAS...................................................................................................100
3.1 Tạo ứng dụng SSAS............................................................................................................100
3.2 Tạo Datasource ...................................................................................................................100
3.3 Tạo DataSource View.........................................................................................................105
3.4 Tạo cube ..............................................................................................................................109
3.5 Sửa bảng chiều....................................................................................................................113
3.6 Deploy cube lên server .......................................................................................................118
3.7 Tạo mới các Measures, Hierarchies và xóa dữ liệu Unknow ..........................................119
Tạo mới Measures ............................................................................................119
Xóa dòng dữ liệu Unknow................................................................................122
3.8 Định nghĩa Named set ........................................................................................................123
3.8.1 Tạo Named set...................................................................................................123
3.9 Thực hiện các câu truy vấn trên MDX, BI và Pivot Excel..............................................125
Câu 1..................................................................................................................126
Câu 2..................................................................................................................127
Câu 3..................................................................................................................128
Câu 4..................................................................................................................130
Câu 5..................................................................................................................132
Câu 6..................................................................................................................133
Câu 7..................................................................................................................135
Câu 8..................................................................................................................137
4
Câu 9..................................................................................................................139
Câu 10................................................................................................................140
Câu 11................................................................................................................142
Câu 12................................................................................................................144
Câu 13................................................................................................................146
Câu 14................................................................................................................148
Câu 15................................................................................................................150
Câu 16................................................................................................................152
Câu 17................................................................................................................153
Câu 18................................................................................................................155
CHƯƠNG 4: QUÁ TRÌNH SSRS...................................................................................................157
4.1 Tạo report bằng công cụ SSRS..........................................................................................157
Tạo Report Service Project..............................................................................157
Tạo và cấu hình Share Data Sources...............................................................157
Tạo Reports.......................................................................................................159
Tạo Dataset........................................................................................................160
Tạo table và định dạng report .........................................................................163
Báo cáo hoàn chỉnh...........................................................................................168
kiện thời tiết từ năm 2005 - 2015.............................................................................................169
Tạo Reports.......................................................................................................169
Tạo DataSet.......................................................................................................169
Tạo Table và Grouping ....................................................................................173
Tạo Total và định dạng Report .......................................................................179
Báo cáo hoàn chỉnh...........................................................................................186
Tạo Report ........................................................................................................187
Tạo DataSet.......................................................................................................188
Tạo Table và Grouping ....................................................................................190
Tạo Total và định dạng Report .......................................................................195
5
Báo cáo hoàn chỉnh...........................................................................................200
4.5 Đăng kí dịch vụ Power BI..................................................................................................201
4.6 Tạo Report bằng Power BI................................................................................................203
năm từ 2005 - 2015. ..........................................................................................................210
xảy ra tai mỗi đồn cảnh sát qua các năm từ 2005 - 2015...............................................212
CHƯƠNG 5: DATA MINING ........................................................................................................214
5.1 Thực hiện quá trình Data Mining .....................................................................................214
5.2 Tạo và xử lý Models ...........................................................................................................219
5.3 Cây quyết định....................................................................................................................220
5.4 Thuật toán Microsoft Clustering.......................................................................................223
5.5 Thuật toán Microsoft Naive Bayes....................................................................................226
5.6 Đánh giá các thuật toán bằng Mining Accuracy Chart...................................................228
CHƯƠNG 6 TÀI LIỆU THAM KHẢO .........................................................................................231
6
CHƯƠNG 1: TỔNG QUAN VỀ ĐỀ TÀI
1.1 Giới thiệu bộ dữ liệu
1.1.1 Thông tin về bộ dữ liệu
Đơn vị cung cấp dữ liệu: Bộ giao thông vận tải vương quốc Anh.
Thời gian thu thập dữ liệu: từ năm 2005 đến 2015.
Dữ liệu được xuất bản và công khai tại:
1.1.2 Ý nghĩa của đề tài
An toàn giao thông luôn là chủ đề được toàn xã hội quan tâm và
hướng tới xây dựng văn hóa giao thông tiến bộ nhất để giảm thiểu những
thiệt hại do tai nạn giao thông gây ra. Ngày nay, xã hội càng phố biến bấy
nhiêu kéo theo là sự đông đúc trên đường phố cùng với tai nạn giao thông
ngày càng tăng lên đáng kể. Vì vậy, vấn đề an toàn giao thông thực sự là
một vấn đề quan trọng của nhân loại.
Hiện nay mỗi ngày chúng ta có thể thống kê được rất nhiều những
vụ tai nạn giao thông để lại biết bao hậu quả đáng buồn. Tại sao việc an
toàn giao thông lại khó đến vậy? Nguyên nhân điều này là do đâu? Đó là
do người dân không chỉ chủ quan mà còn thiếu ý thức trách nhiệm trong
khi tham gia giao thông lạn lách, đánh võng, không đội nón bão hiểm..
Nhất là tình trạng người tham gia giao thông có nồng độ cồn vượt quá mức
quy định ảnh hưởng đến sự tỉnh táo của tay lái và gây tai nạn. Không ít
những trường hợp mà mẹ mất con, con mất cha, gia đình đau xót, cá nhân
mất mát sau những tai nạn như vậy, người còn sống cũng ít nhiều để lại
những hậu di chứng về sau. Đó đều là mất mát do giao thông gây nên.
Để lại nhiều hậu quả đau lòng như vậy, rõ ràng an toàn giao thông đóng
một vai trò quan trọng cho cá nhân và cho xã hội. Việc chấp hành các nội
7
dung trong điều luật về an toàn giao thông sẽ góp phần giảm thiểu số vụ
tai nạn đáng tiếc xảy ra cũng giảm theo và bớt đi phần nào những nổi đau
đớn mất mát mà gia đình và cá nhân phải chịu đựng khi có một người vì
tai nạn giao thông mà mất đi sinh mạng hoặc dị tật cả đời.
Thêm vào đó, giảm thiểu tai nạn giao thông cũng là giảm thiểu chi phí do
việc này gây ra. Đối với một xã hội mà an toàn giao thông được giữ vững,
luật giao thông được chấp nhận, người tham gia giao thông có ý thức và an
toàn thì nhất định là một xã hội ngày càng đi lên. Mỗi chúng ta để thực
hiện được an toàn giao thông thì cần tự xác lập cho mình ý thức trách nhiệm
về việc chấp hành những quy định khi tham gia giao thông.
Nhưng ngày nay, không thiếu những hành vi coi thường luật giao
thông để rồi gây ra những hậu quả đáng tiếc cho gia đình và xã hội. Những
hành vi ấy nhất định cần có biện pháp xử lí đích đáng.’Phía trước tay lái là
cuộc sống”. Hãy nhớ khẩu hiệu đó và luôn ý thức trách nhiệm giữ an toàn
giao thông cho người khác ở mọi lúc mọi nơi.
1.2 Mô tả bộ dữ liệu gốc
1.2.1 Mô tả dữ liệu
Các vụ tai nạn ô tô ở vương quốc Anh từ năm 2005- 2015.
Link:
1.2.2 Mô tả thuộc tính
File dataset: Accidents0515
Accidents0515: gồm 1.780.653 dòng dữ liệu, 32 thuộc tính.
STT
Field Name
Accident_Index
Description
Type
1
Mã vụ tai nạn
String
8
Tọa độ địa phương ở
2
3
Location_Easting_OSGR
Location_Northing_OSGR
String
String
Anh (X)
Tọa độ địa phương ở
Anh (Y)
4
5
6
7
Longitude
Kinh độ
Vĩ độ
String
String
Int
Latitude
Police_Force
Accident_Severity
Đồn cảnh sát
Mức độ nghiêm trọng
Int
Số phương tiện bị hư
hại liên quan tai nạn
8
9
Number_of_Vehicles
Number_of_Casualties
Int
Số thương vong
Ngày xảy ra tai nạn
Thứ trong tuần
Int
Date
int
10 Date
11 Day_of_Week
12 Time
Thời gian xảy ra tai nạn DateTime
Tên khu vực địa
Int
13 Local_Authority_(District)
14 Local_Authority_(Highway)
phương xảy ra vụ việc
Tên đường chính xảy ra
String
tai nạn
15 1st_Road_Class
16 1st_Road_Number
17 Road_Type
Phân loại đường bộ
Số đường
Int
Int
Loại đường
Int
18 Speed_limit
Tốc độ giới hạn
Chi tiết giao lộ
Kiểm soát giao lộ
Phân loại đường bộ
Số đường
float
Int
19 Junction_Detail
20 Junction_Control
21 2nd_Road_Class
22 2nd_Road_Number
Int
Int
Int
Pedestrian_Crossing-
Human_Control
Sự kiểm soát người đi
bộ qua đường
23
Int
Int
Pedestrian_Crossing-
24
Cơ sở vật chất cho
người đi bộ qua đường
Physical_Facilities
9
25 Light_Conditions
Điều kiện ánh sáng
Điều kiện thời tiết
Điều kiện mặt đường
Điều kiện đặc biệt
Mối hiểm họa
Int
Int
Int
Int
Int
26 Weather_Conditions
27 Road_Surface_Conditions
28 Special_Conditions_at_Site
29 Carriageway_Hazards
Thành thị hoặc nông
30 Urban_or_Rural_Area
Int
Int
thông
Cảnh sát có tham gia
vào hiện trường tai nạn
hay không?
Did_Police_Officer_Attend_Sc
31
ene_of_Accident
Khu vực địa lý của vị
trí xảy ra tai nạn
32 LSOA_of_Accident_Location
String
1.3 Mô tả thuộc tính từng bảng khi tạo sơ đồ hình sao
• Bảng Dim_Urban_or_Rural_Area
Field Name
Urban_or_Rural_Area
Description
Mã thành thị hoặc
nông thôn
Type
int
Name_Urban_or_Rural_Area Tên thành thị và
varchar
nông thôn
• Bảng Dim_Accident_Severity
Field Name
Description
Type
ID_Accident_Severity
Mã mức độ nghiệm
trọng
int
Accident_Severity
Mức độ nghiêm trọng
varchar
• Bảng Dim_Police_Force
Field Name
ID_Police_Force
Police_Force
Description
Mã đồn cảnh sát
Đồn cảnh sát
Type
int
varchar
• Bảng Dim_Road_Surface_Conditions
10
Field Name
Description
Type
ID_Road_Surface_Conditions Mã điều kiện mặt
int
đường
Road_Surface_Conditions
Điều kiện mặt đường varchar
• Bảng Dim_Weather_Conditions
Field Name
ID_Weather_Conditions
Weather_Conditions
Description
Mã thời tiết
Thời tiết
Type
int
varchar
• Bảng Dim_Light_Conditions
Field Name
Description
Type
ID_Light_Conditions
Light_Conditions
Mã điều kiện ánh sáng int
Điều kiện ánh sáng
varchar
• Bảng Dim_Road_Type
Field Name
ID_Road_Type
Road_Type
Description
Mã tên đường
Tên đường
Type
int
varchar
• Bảng Dim_Date
Field Name
Description
Type
Date_Accident
Ngày xảy ra tai Date
nạn
Day_Accident
Month_Accident
Year_Accident
Ngày
Tháng
Năm
int
int
int
• Bảng Dim_Time
Field Name
Time_Accident
Description
Thời gian xảy
ra tai nạn
Giờ
Type
SmallDateTime
Hour_Accident
Minutes_Accident
int
int
Phút
• Bảng Fact
11
STT
Field Name
Accident_Index
Description
Type
Varchar
Int
1
2
3
Mã vụ tai nạn
ID_Police_Force
Đồn cảnh sát
ID_Accident_Severity
Mức độ nghiêm trọng
Int
Số phương tiện bị hư
hại liên quan tai nạn
4
Number_of_Vehicles
Int
5
6
Number_of_Casualties
Date_Accident
Số thương vong
Int
Ngày xảy ra tai nạn
Date
Thời gian xảy ra tai
nạn
7
Time_Accident
SmallDateTime
8
9
ID_Road_Type
Speed_limit
Loại đường
Int
Float
Int
Tốc độ giới hạn
Điều kiện ánh sáng
Điều kiện thời tiết
Điều kiện mặt đường
10 ID_Light_Conditions
11 ID_Weather_Conditions
12 ID_Road_Surface_Conditions
Int
Int
Thành thị hoặc nông
thông
13 Urban_or_Rural_Area
Int
12
1.4 Sơ đồ hình sao
1.5 Các câu hỏi truy vấn (15 câu)
Câu 1: Thống kê số vụ tai nạn giao thông ở nông thôn, thành thị, cả nước là
bao nhiêu?
Câu 2: Số lượng xe lớn nhất thiệt hại, số xe thiệt hại nhỏ nhất do điều kiện
bề mặt đường trong năm 2015?
Câu 3: Sắp xếp số người thương vong theo chiều tăng dần, theo các năm?
Câu 4: Top 3 khung giờ xảy ra nhiều tai nạn nhất?
Câu 5: Top 3 khung giờ xảy ra ít tai nạn nhất?
Câu 6: Thống kê tổng số người thương vong xảy ra từ năm 2008 đến 2015
tại mỗi thành phố cùng thời tiết và điều kiện ánh sáng?
Câu 7: Số lượng xe lớn nhất và nhỏ nhất thiệt hại do điều kiện bề mặt
đường (Không có Dry ) được sắp xếp theo chiều giảm dần theo số lượng xe
lớn nhất, của năm 2013.
13
Câu 8: Liệt kê 10 đồn cảnh sát ghi nhận tai nạn, có số xe thiệt hại nhiều
nhất, nhưng số thương vong lớn hơn 10000 người.Từ năm 2012 đến năm
2015?
Câu 9: Thống kê số thương vong theo từng tháng của năm 2007?
Câu 10: Sắp xếp các đồn cảnh sát bắt đầu bằng chữ S theo chiều giảm dần
số vụ tai nạn giao thông, qua các tháng trong năm?
Câu 11: Truy vấn 5 loại đường,tổng số xe tai nạn được sắp xếp giảm dần,
lấy bắt đầu từ vị trí thứ 2. Thống kê theo từng năm.
Câu 12: Thống kê theo năm, vùng (nông thôn hay thành thị) 5 điều kiện thời
tiết gây ra số lượng vụ tai nạn cao nhất và ngoại trừ vùng Unallocated
Câu 13: Thống kê ở thành phố (Urban) các loại đường, điều kiện ánh sáng,
điều kiện thời tiết được sắp xếp giảm dần theo số vụ tai nạn. Ứng với tốc độ
lớn hơn 30 km/h.
Câu 14: Thống kê các tên đồn cảnh sát có tổng số của những người bị
thương ít nhất là 5000 với mức độ nghiêm trọng (Dim_Accident_Severity)
–filter(sắp xếp tăng dần theo tốc độ lớn nhất).
Câu 15: Thống kê theo năm, tháng tổng số xe bị hư hại, số người bị thương,
tổng số vụ tai nạn, tốc độ lớn nhất xảy ra tại các loại đường với tốc độ lớn
nhất là 20-70km/h ,với tổng số lượng vụ tai nạn là lớn hơn hoặc bằng 5 vụ,
mức độ nguy hiểm là tử vong (Fatal).
14
CHƯƠNG 2: QUÁ TRÌNH SSIS
2.1 Tạo Database trống trong SQL để đổ dữ liệu vào
Để tạo Database mới có tên là “Accidents” ta thực hiện như sau:
- Khởi động SQL Server 2014 Management.
- Viết câu lệnh CREATE DATABASE Accident_DW.
- Nhấn Execute.
2.2 Tạo Project và thiết lập kết nối
2.2.1 Tạo mới “Integration Service Project”
- Khởi động Visual Studio 2017.
- Chọn File ->New->Project.
- Ở Installed, chọn tab Business Intelligence -> Integration
Services Project.
- Đặt tên Project là “SSIS_Accidents”.
- Nhấn Ok.
15
2.2.2 Thêm và cấu hình kết nối tới Database của hệ quản trị SQL
Server
2.2.2.1 Kết nối tới Database “Accident_DW”
- Tại phần Solution Explorer, click chuột phải vào folder
Connection Managers, sau đó chọn New Connection Manager
để tiến hành tạo một kết nối mới.
- Chọn OLEDB, sau đó chọn Add để kết nối tới hệ quản trị SQL
Server.
16
- Chọn New để tiếp tục.
- Điền tên Server Name, chọn database “Accident_DW” trong
Select or enter a database name. Sau đó nhấn ok để hoàn tất.
17
18
Tải về để xem bản đầy đủ
232 trang
22820
Free
Bạn đang xem 20 trang mẫu của tài liệu "Tiểu luận Xây dựng kho dữ liệu và OLAP trên dữ liệu các vụ tai nạn giao thông tại Anh 2005 - 2015", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- tieu_luan_xay_dung_kho_du_lieu_va_olap_tren_du_lieu_cac_vu_t.docx
