›

Đề tài 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

docx 232 trang thiennha21 14/04/2022 9421
Download
Bạn đang xem 20 trang mẫu của tài liệu "Đề tài 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 bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • docxde_tai_xay_dung_kho_du_lieu_va_olap_tren_du_lieu_cac_vu_tai.docx

Nội dung text: Đề tài 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

  1. ĐẠI HỌC QUỐC GIA TP. HỒ CHÍ MINH TRƯỜNG ĐẠI HỌC CÔNG NGHỆ THÔNG TIN  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 Hồ Chí Minh, Ngày 30/ 11/ 2020 1
  2. 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
  3. NHẬN XÉT CỦA GIẢNG VIÊN . 2
  4. 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 1.1.1 Thông tin về bộ dữ liệu 7 1.1.2 Ý nghĩa của đề tài 7 1.2 Mô tả bộ dữ liệu gốc 8 1.2.1 Mô tả dữ liệu 8 1.2.2 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 2.2.1 Tạo mới “Integration Service Project” 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 16 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 2.4.1 Tạo bảng Dim_Urban_or_Rural_Area 26 2.4.2 Tạo bảng Dim_Accident_Severity 29 3
  5. 2.4.3 Tạo bảng Dim_Police_Force 33 2.4.4 Tạo bảng Dim_Road_Surface_Conditions 37 2.4.5 Tạo bảng Dim_Weather_Conditions 40 2.4.6 Tạo bản Dim_Light_Conditions 43 2.4.7 Tạo bảng Dim_Road_Type 46 2.4.8 Tạo bảng Dim_Date 50 2.4.9 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 3.7.1 Tạo mới Measures 119 3.7.2 Tạo mới Hierarchies và định nghĩa Attribute Relationship 120 3.7.3 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 3.9.1 Câu 1 126 3.9.2 Câu 2 127 3.9.3 Câu 3 128 3.9.4 Câu 4 130 3.9.5 Câu 5 132 3.9.6 Câu 6 133 3.9.7 Câu 7 135 3.9.8 Câu 8 137 4
  6. 3.9.9 Câu 9 139 3.9.10 Câu 10 140 3.9.11 Câu 11 142 3.9.12 Câu 12 144 3.9.13 Câu 13 146 3.9.14 Câu 14 148 3.9.15 Câu 15 150 3.9.16 Câu 16 152 3.9.17 Câu 17 153 3.9.18 Câu 18 155 CHƯƠNG 4: QUÁ TRÌNH SSRS 157 4.1 Tạo report bằng công cụ SSRS 157 4.1.1 Tạo Report Service Project 157 4.1.2 Tạo và cấu hình Share Data Sources 157 4.2 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? 159 4.2.1 Tạo Reports 159 4.2.2 Tạo Dataset 160 4.2.3 Tạo table và định dạng report 163 4.2.4 Báo cáo hoàn chỉnh 168 4.3 Thống kê số xe thiệt hại, số người bị thương, số vụ tai nạn giao thông theo từng điều kiện thời tiết từ năm 2005 - 2015 169 4.3.1 Tạo Reports 169 4.3.2 Tạo DataSet 169 4.3.3 Tạo Table và Grouping 173 4.3.4 Tạo Total và định dạng Report 179 4.3.5 Báo cáo hoàn chỉnh 186 4.4 Thống kê theo tháng, năm 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) 187 4.4.1 Tạo Report 187 4.4.2 Tạo DataSet 188 4.4.3 Tạo Table và Grouping 190 4.4.4 Tạo Total và định dạng Report 195 5
  7. 4.4.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 4.6.1 Roport 1: Thống kê số vụ tai nạn giao thông qua các khu vực 206 4.6.2 Report 2: Thống kê tổng số thương vong theo từng điều kiện thời tiết qua các năm từ 2005 - 2015. 210 4.6.3 Report 3: Thống kê tổng xe thiệt hại, tổng số thương vong, tổng số vụ tai nạn 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
  8. 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
  9. 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 Description Type 1 Accident_Index Mã vụ tai nạn String 8
  10. Tọa độ địa phương ở 2 Location_Easting_OSGR String Anh (X) Tọa độ địa phương ở 3 Location_Northing_OSGR String Anh (Y) 4 Longitude Kinh độ String 5 Latitude Vĩ độ String 6 Police_Force Đồn cảnh sát Int 7 Accident_Severity Mức độ nghiêm trọng Int Số phương tiện bị hư 8 Number_of_Vehicles Int hại liên quan tai nạn 9 Number_of_Casualties Số thương vong Int 10 Date Ngày xảy ra tai nạn Date 11 Day_of_Week Thứ trong tuần int 12 Time Thời gian xảy ra tai nạn DateTime Tên khu vực địa 13 Local_Authority_(District) Int phương xảy ra vụ việc Tên đường chính xảy ra 14 Local_Authority_(Highway) String tai nạn 15 1st_Road_Class Phân loại đường bộ Int 16 1st_Road_Number Số đường Int 17 Road_Type Loại đường Int 18 Speed_limit Tốc độ giới hạn float 19 Junction_Detail Chi tiết giao lộ Int 20 Junction_Control Kiểm soát giao lộ Int 21 2nd_Road_Class Phân loại đường bộ Int 22 2nd_Road_Number Số đường Int Pedestrian_Crossing- Sự kiểm soát người đi 23 Int Human_Control bộ qua đường Pedestrian_Crossing- Cơ sở vật chất cho 24 Int Physical_Facilities người đi bộ qua đường 9
  11. 25 Light_Conditions Điều kiện ánh sáng Int 26 Weather_Conditions Điều kiện thời tiết Int 27 Road_Surface_Conditions Điều kiện mặt đường Int 28 Special_Conditions_at_Site Điều kiện đặc biệt Int 29 Carriageway_Hazards Mối hiểm họa Int Thành thị hoặc nông 30 Urban_or_Rural_Area Int thông Cảnh sát có tham gia Did_Police_Officer_Attend_Sc 31 vào hiện trường tai nạn Int ene_of_Accident hay không? Khu vực địa lý của vị 32 LSOA_of_Accident_Location String trí xảy ra tai nạn 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 Description Type Urban_or_Rural_Area Mã thành thị hoặc int nông thôn 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 int trọng Accident_Severity Mức độ nghiêm trọng varchar • Bảng Dim_Police_Force Field Name Description Type ID_Police_Force Mã đồn cảnh sát int Police_Force Đồn cảnh sát varchar • Bảng Dim_Road_Surface_Conditions 10
  12. 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 Description Type ID_Weather_Conditions Mã thời tiết int Weather_Conditions Thời tiết varchar • Bảng Dim_Light_Conditions Field Name Description Type ID_Light_Conditions Mã điều kiện ánh sáng int Light_Conditions Điều kiện ánh sáng varchar • Bảng Dim_Road_Type Field Name Description Type ID_Road_Type Mã tên đường int Road_Type Tên đường varchar • Bảng Dim_Date Field Name Description Type Date_Accident Ngày xảy ra tai Date nạn Day_Accident Ngày int Month_Accident Tháng int Year_Accident Năm int • Bảng Dim_Time Field Name Description Type Time_Accident Thời gian xảy SmallDateTime ra tai nạn Hour_Accident Giờ int Minutes_Accident Phút int • Bảng Fact 11
  13. STT Field Name Description Type 1 Accident_Index Mã vụ tai nạn Varchar 2 ID_Police_Force Đồn cảnh sát Int 3 ID_Accident_Severity Mức độ nghiêm trọng Int Số phương tiện bị hư 4 Number_of_Vehicles Int hại liên quan tai nạn 5 Number_of_Casualties Số thương vong Int 6 Date_Accident Ngày xảy ra tai nạn Date Thời gian xảy ra tai 7 Time_Accident SmallDateTime nạn 8 ID_Road_Type Loại đường Int 9 Speed_limit Tốc độ giới hạn Float 10 ID_Light_Conditions Điều kiện ánh sáng Int 11 ID_Weather_Conditions Điều kiện thời tiết Int 12 ID_Road_Surface_Conditions Điều kiện mặt đường Int Thành thị hoặc nông 13 Urban_or_Rural_Area Int thông 12
  14. 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
  15. 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
  16. 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
  17. 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
  18. - 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
  19. - Kết nối đã thành công. 2.3 Quá trình làm sạch dữ liệu - Tạo “Data Flow Task”: Kéo thả công cụ Data Flow Task ở SSIS Toolbox sang vùng làm việc Control Flow. Sau đó tiến hành đổi tên “Quá trình làm sạch dữ liệu”: - Click vào tab Data Flow để tiến hành làm sạch dữ liệu: 19
  20. - Kéo thả Flat File Source, Conditional Split, 2 bảng OLE DB Destination, Sort. + Đổi tên Flat File Source thành Data Gốc Accident CSV. + Đổi tên 1 bảng OLE DB Destination thành Data Sạch. - Kích chuột vào Data Gốc Accident CSV, chọn edit. - Tại phần Flat file connection manager, chọn New. 20
  21. - Chọn đường dẫn tới file Accidents0515. Sau đó nhấn OK. 21
  22. - Tích chọn những cột cần thiết. Sau đó nhấn OK. - Kích chuột vào Conditional Split, chọn edit. - Nhằm thực hiện chia dữ liệu thành 2 phần là DataNull và DataNotNull. - Tại ô Output Name ta sửa thành “DataNull”, tại ô condition ta thêm câu lệnh sau: ISNULL([Accident_Index])||ISNULL([Police_Force])|| ISNULL([Accident_Severity])||ISNULL([Number_of_Vehicles])|| ISNULL([Number_of_Casualties])|| ISNULL([Date])||ISNULL([Time])|| ISNULL([Road_Type])|| ISNULL([Speed_limit])|| ISNULL([Light_Conditions])|| ISNULL([Weather_Conditions])|| ISNULL([Road_Surface_Conditions])|| ISNULL([Urban_or_Rural_Area]) 22
  23. - Tại ô Default out name điền “DataNotNull”. - Sau đó nhấn OK. - Thực hiện phân chia dữ liệu cho DataNull và DataNotNull trên OLE DB Destination. - Thực hiện DataNotNull. 23
  24. - Sau đó nhấn “OK”. - Thực hiện DataNull. 24
  25. - Sau đó nhấn OK. 2.4 Thực hiện tạo các bảng Dim 25
  26. - Kéo thả Sequence Container. - Đổi tên thành” Tạo các bảng Dim”. 2.4.1 Tạo bảng Dim_Urban_or_Rural_Area - Kéo thả Data Flow Task vào Control Flow. Sau đó đổi tên Data Flow thành Tạo Dim_Urban_or_Rural_Area. - Chọn Flat File Source, Sort, OLE DB Destination. - Đổi tên Flat File Source thành Data Gốc Urban_or_Rural_Area. - Đổi tên OLE DB Destination thành Dim_Urban_or_Rural_Area. 26
  27. - Nhấn chuột vào Data Gốc Urban_or_Rural_Area chọn edit. - Sau đó chọn New. - Chọn đường dẫn đến file Urban_Rural.csv, sau đó nhấn OK. 27
  28. - Nhấn Sort, chọn edit. Sau đó nhấn OK. 28
  29. - Tạo và cấu hình Dim_Urban_or_Rural_Area. 2.4.2 Tạo bảng Dim_Accident_Severity - Tạo và cấu hình Flat File Source. - Chọn công cụ “Flat File Source” tại SSIS Toolbox vào vùng làm việc Data Flow, và đổi tên thành Data Gốc Accident_Severity. - Nhấn đúp chuột vào Data Gốc Accident_Severity và thực hiện: - Chọn New. 29
  30. - Chọn đường dẫn tới file. - Sau đó nhấn OK. 30
  31. - Tạo và cấu hình Sort như sau: 31
  32. - Tạo và cấu hình OLE DB Destination. Đổi tên thành Dim_Accident_Severity. 32
  33. - Nhấn OK. 2.4.3 Tạo bảng Dim_Police_Force - Tạo và cấu hình Flat File Source. Đổi tên thành Data Gốc Police_Force. 33
  34. - Nhấn đúp chuột vào Data Gốc Police_Force và thực hiện: - Chọn New. - Chọn đường dẫn tới file. - Sau đó nhấn OK. 34
  35. - Tạo và cấu hình “Sort” 36
  36. - Tạo và cấu hình OLE DB Destination, đổi tên thành Dim_Police_Force. - Sau đó nhấn OK. 2.4.4 Tạo bảng Dim_Road_Surface_Conditions - Tạo và cấu hình Excel Source, đổi tên thành Data Gốc Road_Surface_Conditions. 37
  37. - Nhấn New. Chọn đường dẫn đến file excel, sau đó OK. 38
  38. - Chọn và cấu hình Sort. 39
  39. - Chọn OLE DB Destination, đổi tên thành Dim_Road_Surface_Conditions, cấu hình như sau: 2.4.5 Tạo bảng Dim_Weather_Conditions - Tạo Excel Source và đổi tên thành Data Gốc Weather, cấu hình như sau: 40
  40. - Tạo và cấu hình Sort. 42
  41. - Tạo OLE DB Destination đổi tên thành Dim_Weather_Conditions, cấu hình như sau: 2.4.6 Tạo bản Dim_Light_Conditions - Tạo Excel Source đổi tên thành Data Gốc Light_Conditions, cấu hình như sau: 43
  42. - Tạo và cấu hình Sort. 45
  43. - Tạo OLE DB Destination đổi tên thành Dim_Light_Conditions, cấu hình như sau: 2.4.7 Tạo bảng Dim_Road_Type - Tạo Flat File Source và đổi tên thành Data Gốc Road_Type, cấu hình như sau: 46
  44. - Tạo và cấu hình Sort 49
  45. - Tạo OLE DB Destination đổi tên thành Dim_Road_Type, cấu hình như sau: 2.4.8 Tạo bảng Dim_Date - Kéo thả Data Flow Task. Và đổi tên thành”Tạo Dim_Date” - Sử dụng OLE DB Source , Aggregate , Sort, Script Component, OLE DB Destination. 50
  46. - Thực hiện bên trong OLE DB Source. - Sau đó nhấn “OK”. 51
  47. - Thực hiện bên trong Aggregate - Sau đó nhấn “OK”. 52
  48. - Thực hiện bên trong Sort - Sau đó nhấn “OK”. 53
  49. - Thực hiện bên trong Script Component - Vào Script để chỉnh sửa code. - Vào Edit Script 54
  50. - Sau đó nhấp “OK”. - Vào Input Columns. 55
  51. - Vào Inputs and Outputs. - Sau đó nhấn “OK”. 56
  52. - Thực hiện bên trong OLE DB Destination. - Sau đó nhấn “OK”. 2.4.9 Tạo bảng Dim_Time - Kéo thả Data Flow Task. Và đổi tên thành”Tạo Dim_Time” - Sử dụng OLE DB Source , Aggregate , Sort, Script Component, OLE DB Destination. 57
  53. - Thực hiện bên trong OLE DB Source. - Sau đó nhấn “OK”. 58
  54. - Thực hiện bên trong Aggregate - Sau đó nhấn “OK”. 59
  55. - Thực hiện bên trong Sort - Sau đó nhấn “OK”. 60
  56. - Thực hiện bên trong Script Component - Vào Script để chỉnh sửa code. - Vào Edit Script 61
  57. - Sau đó nhấp “OK”. - Vào Input Columns. 62
  58. - Vào Inputs and Outputs. -Sau đó nhấn “OK”. 63
  59. - Thực hiện bên trong OLE DB Destination. - Sau đó nhấn “OK”. 2.5 Tạo bảng Fact - Kéo thả Data Flow Task. Và đổi tên thành” Create table Fact_Accidents” - Sử dụng OLE DB Soucre, Lookup, OLE DB Destination. 64
  60. - Thực hiện OLE DB Soucre. - Sau đó nhấp OK. 65
  61. - Thực hiện Lookup. - Thực hiện Lookup cho Dim_Accident_Severity. 66
  62. - Sau đó nhấp OK. 68
  63. - Thực hiện Lookup Dim_Date 69
  64. - Sau đó nhấn OK. 71
  65. - Thực hiện Lookup Dim_Light_Conditions. 72
  66. - Sau đó nhấp OK. 74
  67. - Thực hiện Lookup Dim_Police_Force 75
  68. - Sau đó nhấp OK. 76
  69. - Thực hiện Lookup Dim_Road_Surface_Conditions. 77
  70. - Thưc hiện Lookup Dim_Road_Type. 80
  71. - Sau đó nhấp OK. 81
  72. -Thưc hiện Lookup Dim_Time. 82
  73. - Sau đó nhấp OK. 83
  74. -Thưc hiện Lookup Dim_Urban_or_Rural_Area. 84
  75. - Sau đó nhấn OK. 85
  76. - Thực hiện Lookup với Dim_Weather_Conditions. 86
  77. - Sau đó nhấn OK - Tạo và cấu hình OLE DB Destination. 88
  78. - Sau đó nhấp OK. 2.6 Tạo và xóa các ràng buộc SQL - Tạo và cấu hình “Execute SQL Task”. Xóa dữ liệu DataNull, DataNotNull trong Database “Data_Emptied”. 90
  79. - Tạo và cấu hình “Execute SQL Task”. Xóa các ràng buộc khóa ngoại giữa Fact và các bảng Dim. - Câu lệnh SQL xóa các khóa ngoại như sau: ALTER TABLE FACT DROP CONSTRAINT fk_time; Go ALTER TABLE FACT DROP CONSTRAINT fk_date; Go ALTER TABLE FACT 91
  80. DROP CONSTRAINT fk_police; Go ALTER TABLE FACT DROP CONSTRAINT fk_severity; Go ALTER TABLE FACT DROP CONSTRAINT fk_Area; Go ALTER TABLE FACT DROP CONSTRAINT fk_Light; Go ALTER TABLE FACT DROP CONSTRAINT fk_Weather; Go ALTER TABLE FACT DROP CONSTRAINT fk_Road; Go ALTER TABLE FACT DROP CONSTRAINT fk_RoadType; 92
  81. - Tạo và cấu hình “Execute SQL Task”. Xóa các bảng Dim. - Câu lệnh SQL xóa bảng Dim như sau: delete from Dim_Accident_Severity; delete from Dim_Date; delete from Dim_Light_Conditions; delete from Dim_Police_Force; delete from Dim_Road_Surface_Conditions; delete from Dim_Road_Type; delete from Dim_Time; delete from Dim_Urban_or_Rural_Area; delete from Dim_Weather_Conditions; 93
  82. - Tạo và cấu hình “Execute SQL Task”. Xóa bảng Fact. 94
  83. - Tạo và cấu hình “Execute SQL Task”. Tạo các ràng buộc giữa Fact và các bảng Dim. - Câu lệnh SQL tạo khóa ngoại như sau: Alter table FACT add constraint fk_time foreign key (Time_Accident) references Dim_Time (Time_Accident); Alter table FACT add constraint fk_date foreign key (Date_Accident) 95
  84. references Dim_Date (Date_Accident); Alter table FACT add constraint fk_police foreign key (ID_Police_Force) references Dim_Police_Force (ID_Police_Force); Alter table FACT add constraint fk_severity foreign key (ID_Accident_Severity) references Dim_Accident_Severity(ID_Accident_Severity); Alter table FACT add constraint fk_Area foreign key (Urban_or_Rural_Area) references Dim_Urban_or_Rural_Area (Urban_or_Rural_Area); Alter table FACT add constraint fk_Light foreign key (ID_Light_Conditions) references Dim_Light_Conditions (ID_Light_Conditions); Alter table FACT add constraint fk_Weather foreign key (ID_Weather_Conditions) references Dim_Weather_Conditions (ID_Weather_Conditions); 96
  85. Alter table FACT add constraint fk_Road foreign key (ID_Road_Surface_Conditions) references Dim_Road_Surface_Conditions (ID_Road_Surface_Conditions); Alter table FACT add constraint fk_RoadType foreign key (ID_Road_Type) references Dim_Road_Type (ID_Road_Type); 97
  86. 2.7 Thực thi Package 98
  87. CHƯƠNG 3: QUÁ TRÌNH SSAS 3.1 Tạo ứng dụng SSAS - Khởi động Microsoft Visual Studio. - Tạo một Analysis Services Project mới có tên “SSAS“. 3.2 Tạo Datasource Bước 1: Trong khung Solution Explorer, phải chuột Data Sources và chọn New Data Source. 100
  88. - Bước 2:Tại màn hình Welcome to Data Source Wizard, nhấn Next để chuyển sang cửa sổ Select how to define connection - Bước 3: Tại cửa sổ Select how to define connection page, ta có thể định nghĩa một data source bằng cách tạo mới connection hoặc trên một connection đã có sẵn. Ở đây ta tạo mới connection. Nhấn New. 101
  89. - Bước 4: Trong dialog box Connection Manager, ta sẽ định nghĩa giá trị thuộc tính cho Data Source. Trong danh sách Provider, chọn Native OLE DB\SQL Server Native Cilent 10.0. - Bước 5: Trong server name gõ vào “LAPTOP-9SK5B3CV” - Bước 6: Kiểm tra giá trị Use Windows Authentication được chọn, trong danh sách Select or enter a database name , ở đây ta chọn “Accident DW” - Bước 7:Nhấn Test Connection để kiểm tra kết nối đến với SQL Server. - Bước 8: Chọn OK và nhấn Next. - Bước 9:Tại cửa sổ Impersonation Information, ta sẽ thiết lập Security Credential cho SSAS để kết nối tới Data Sources, ta chọn “Use the service account”. 103
  90. - Bước 10: Tại màn hình Completing the wizard , thiết lập tên của Data Source là “Accident DW” và chọn Finish. 104
  91. 3.3 Tạo DataSource View - Bước 1: Trong khung Solution Explorer , phải chuột Data Sources Views, và chọn New Data Sources Views. - Bước 2: Welcome to Data Sources Views Wizard xuất hiện, nhấn Next. 105
  92. - Bước 3: Dialog select Data source xuất hiện, tại mục Relational Data Sources, chọn “Accident DW” mà ta đã tạo như trên rồi chọn Next. - Bước 4: Tại Dialog Select Tables and Views, ta sẽ chọn các bảng dữ liệu và views từ danh sách đối tượng , ta sẽ chọn các đối tượng. - Bước 5: Nhấn nút “>” để thêm các bảng vào danh sách Included Object. - Bước 6: Nhấn Next. 106
  93. Bước 7: Tại thuộc tính Name, ta thiết lập là warehouse và nhấn finish để hoàn tất việc định nghĩa data source view. 107
  94. Sau khi hoàn tất bước này, ta sẽ có khung nhìn toàn diện về các bảng dữ liệu và mối quan hệ của chúng. 108
  95. 3.4 Tạo cube - Bước 1: Trong pane Solutin Explorer, phải chuột vào Cubes, chọn New Cubes. - Bước 2: Tại cửa sổ Welcome to Cubes Wizard, chọn Next. - Bước 3: Tại cửa sổ Select Creation Method, đảm bảo tùy chọn Use an Existing Table được chọn và nhấn Next. - Bước 4: Tại cửa sổ Select Measure Group Tables, đảm bảo data source view warehouse được chọn. 109
  96. - Bước 5: Nhấn Suggest để cửa sổ Suggest Table hiển thị và đề xuất các table cần thiết để xây dựng các measure group. - Bước 6: Nhấn Next - Bước 7: Tại cửa sổ Select Measure, xem lại các measures .Nhấn next 110
  97. - Bước 8: Tại cửa sổ Selecting Existing Dimensions, chọn Data Dimension đã được định nghĩa 111
  98. - Bước 9: Tại cửa sổ Completing the Wizard, thiết lập name của Cube là “Accident DW” - Bước 10: Chọn Finish để hoàn tất, Cube đã được tạo. 112
  99. 3.5 Sửa bảng chiều ❖ Với bảng Dim_Date - Trong Folder Dimensions Nhấp đúp chuột vào Dim date - Trong Data Source View, di chuyển các trường trong Dim_date vào Dim date trong Attributes. 113
  100. ❖ Với bảng Dim_Road_Type - Trong Folder Dimensions Nhấp đúp chuột vào Dim Road Type. - Trong Data Source View, di chuyển các trường trong Dim_Road_Type vào Dim Road Type trong Attributes. ❖ Với bảng Dim_time - Trong Folder Dimensions Nhấp đúp chuột vào Dim Time - Trong Data Source View, di chuyển các trường trong Dim_time vào Dim Time trong Attributes. 114
  101. ❖ Với bảng Dim_Urban_Or_Rural_Area - Trong Folder Dimensions Nhấp đúp chuột vào Dim Time. - Trong Data Source View, di chuyển các trường trong Dim_Urban_Or_Rural_Area vào Dim Urban Or Rural trong Attributes. ❖ Với bảng Dim_Road_Surface_Conditions - Trong Folder Dimensions Nhấp đúp chuột vào Dim Road Surface Conditions. - Trong Data Source View, di chuyển các trường trong Dim_Road_Surface_Conditions vào Dim Road Surface Conditions trong Attributes. 115
  102. ❖ Với bảng Dim_Weather_Conditions - Trong Folder Dimensions Nhấp đúp chuột vào Dim Weather Conditions. - Trong Data Source View, di chuyển các trường trong Dim_Weather_Conditions vào Dim Weather Conditions trong Attributes ❖ Với bảng Dim_Accident_Severity - Trong Folder Dimensions. Nhấp đúp chuột vào Dim Accident Severity. - Trong Data Source View, di chuyển các trường trong Dim_Accident_Severity vào Dim Accident Severity trong Attributes. 116
  103. ❖ Với bảng Dim_Light_Conditions - Trong Folder Dimensions. Nhấp đúp chuột vào Dim Light Conditions. - Trong Data Source View, di chuyển các trường trong Dim_Light_Conditions vào Dim Light Conditions trong Attributes. ❖ Với bảng Dim_Police_Force - Trong Folder Dimensions. Nhấp đúp chuột vào Dim Police Force. - Trong Data Source View, di chuyển các trường trong Dim_Police_Force vào Dim Police Force trong Attributes. 117
  104. 3.6 Deploy cube lên server - Bước 1: Trong pane Solutin Explorer, phải chuột vào Analysic Services Project và chọn Properties. - Bước 2: Tại Configuration Properties tại panel bên trái, chọn Deployment . Sửa lại Server và Database để deploy. - Bước 3: Phải chuột Analysic Services Tutorial project và chọn Deploy 118
  105. Như vậy project SSAS đã được deploy lên server. 3.7 Tạo mới các Measures, Hierarchies và xóa dữ liệu Unknow 3.7.1 Tạo mới Measures - Bước 1: Chuyển sang Cube Designer bằng cách nhấn đúp chuột Accident DW.cube - Bước 2: Trong tab Cube Structure. Ở ô Measures, nhấp phải chuột vào Fact, chọn New Measure. - Bước 3: Thực hiện thêm measure: với 119
  106. • Usage : là các hàm tính toán thống kê. • Source table: bảng • Souce column: cột - Ta có các measure sau: - Bước 4:Nhấp phải chuột vào Accident DW.cube. Chọn Process - Bước 5: Chọn Run. - Bước 6: OK 3.7.2 Tạo mới Hierarchies và định nghĩa Attribute Relationship Ở phần này sẽ tiến hành tạo thuộc tính Hierarchies và định nghĩa Attribute Relationships cho bảng Dim_Date Attribute Relationships có tác dụng: - Tăng thời gian xử lý cube và các bảng Dimension. - Tăng dung lượng dữ liệu trên đĩa. - Tăng hiệu suất câu truy vấn. Tạo Hierarchy phân cấp theo Year-Month-Day, Year_Month Kéo thả các thuộc tính Year Accident, Month Accident, Day Accident, cần thiết sang cột Hierarchies. Với thứ tự từ trên xuống là phân cấp từ cao đến thấp nhất: 120
  107. + Ta tạo mới 2 Hierachies là: Y_M_D và Y_M. Chuyển sang tab Attribute Relationships để tiến hành định nghĩa Attribute Relationships: Tiến hành kéo thả phân cấp từ nhỏ đến lớn theo thứ tự từ phải sang trái. Chỉnh sửa Relationship Type thành Regid: 121
  108. 3.7.3 Xóa dòng dữ liệu Unknow Chọn bảng Dim Police_Force, Right-Click vào tên bảng Dim ở cột Attributes. Chỉnh sửa ở dòng UnknowMemberName, mặc định là Visible thành Hidden: 122
  109. Ta thực hiện tương tự như trên với các bảng Dim khác. 3.8 Định nghĩa Named set 3.8.1 Tạo Named set Double-click vào Cube ở màn hình Solution Explorer: 123
  110. Chọn tab Calculations: 124
  111. Right-click vùng trống ở mục Script Organizer và chọn New Named set để tiến hành tạo 1 Named Set mới. Đặt tên Name set tại ô Name. Kéo thả hàm cần chọn từ Funtions vào ô Expression. 3.9 Thực hiện các câu truy vấn trên MDX, BI và Pivot Excel 125
  112. 3.9.1 Câu 1 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? (Fact Count ->Dùng hàm Count để đếm các dòng dữ liệu).  Chạy câu truy vấn thực hiện bằng ngôn ngữ MDX. Select {[Measures].[Fact Count]} on columns, non empty {[Dim Urban Or Rural Area].[Name Urban Or Rural Area].members} on Rows From [Accident DW]  Chạy câu truy vấn bằng công cụ BI. 126
  113.  Chạy câu truy vấn trên Pivot Excel. 3.9.2 Câu 2 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? (Roll Up) ( Dùng hàm [SoXeLonNhat] dùng hàm max tìm ra số lượng xe lớn nhất, [SoXeNhoNhat] dùng hàm min tìm ra số lượng xe nhỏ nhất ).  Chạy câu truy vấn bằng ngôn ngữ MDX. select ({[Measures].[SoXeLonNhat],[Measures].[SoXeNhoNhat]}) on columns, Non empty {[Dim Road Surface Conditions].[Road Surface Conditions].members} on rows From [Accident DW] where [Dim Date].[Year Accident].&[2015]  Chạy câu truy vấn bằng công cụ BI. 127
  114.  Chạy câu truy vấn trên Pivot Excel. 3.9.3 Câu 3 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? (Dùng hàm Order để sắp xếp theo chiều tăng dần, [TongSoThuongVong] dùng hàm Sum)  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select {[Measures].[TongSoThuongVong]} on columns, non empty Order ( {[Dim Date].[Year Accident].children} ,[Measures].[TongSoThuongVong] , ASC) on Rows from [Accident DW] 128
  115.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 129
  116. 3.9.4 Câu 4 Câu 4: Top 3 khung giờ xảy ra nhiều tai nạn nhất? (Dùng hàm Head kết hợp Order)  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select {[Measures].[Fact Count]} on columns, Head( Order( [Dim Time].[Hour Accident].children ,[Measures].[Fact Count] , Desc) ,3) on rows from [Accident DW] 130
  117.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 131
  118. 3.9.5 Câu 5 Câu 5: Top 3 khung giờ xảy ra ít tai nạn nhất? (Dùng hàm Tail kết hợp Order)  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select {[Measures].[Fact Count]} on columns, non empty Tail( Order([Dim Time].[Hour Accident].children,[Measures].[Fact Count],DESC) ,3) on rows from [Accident DW]  Thực hiện câu truy vấn bằng BI. 132
  119.  Thực hiện câu truy vấn bằng Pivot Excel. 3.9.6 Câu 6 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? (Dùng CrossJoin)  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select {[Measures].[TongSoThuongVong]} on 0, non empty CrossJoin ({[Dim Weather Conditions].[Weather Conditions].children *[Dim Light Conditions].[Light Conditions].children} ,{[Dim Police Force].[Police Force].children} )on 1 133
  120. From [Accident DW] Where ([Dim Date].[Year Accident].&[2008] :[Dim Date].[Year Accident].[2015]);  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 134
  121. 3.9.7 Câu 7 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.(Dùng operator"-").  Thực hiện câu truy vấn bằng ngôn ngữ MDX. Select {[Measures].[SoXeLonNhat],[Measures].[SoXeNhoNhat]} on 0, non empty Order ( {[Dim Road Surface Conditions].[Road Surface Conditions].members} - {[Dim Road Surface Conditions].[Road Surface Conditions].&[Dry]} ,[Measures].[SoXeLonNhat],DESC)on 1 From [Accident DW] Where ([Dim Date].[Year Accident].&[2013]); 135
  122.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 136
  123. 3.9.8 Câu 8 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? (Dùng TopCount kết hợp Filter)  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select {[Measures].[TongSoThuongVong], [Measures].[TongXeThietHai]}on 0, TopCount( Filter([Dim Police Force].[Police Force].children,[Measures].[TongSoThuongVong]>10000) ,10,[Measures].[TongXeThietHai]) on 1 From [Accident DW] Where ([Dim Date].[Year Accident].&[2012]:[Dim Date].[Year Accident].&[2015]); 137
  124.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 138
  125. 3.9.9 Câu 9 Câu 9: Thống kê số thương vong theo từng tháng của năm 2007?(Drill Down)  Thực hiện câu truy vấn bằng MDX. Select [Measures].[TongSoThuongVong] on 0, [Dim Date].[Y_M].[Year Accident].&[2007].children on 1 From [Accident DW]  Thực hiện câu truy vấn bằng BI. 139
  126.  Thực hiện câu truy vấn bằng Pivot Excel. 3.9.10 Câu 10 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? select [Measures].[Fact Count] on 0, Order( generate([Dim Date].[Year Accident].children, 140
  127. Filter([Dim Police Force].[Police Force].children ,Left([Dim Police Force].[Police Force].CurrentMember.Name,1)="S")*[Dim Date].[Year Accident].CurrentMember*[Dim Date].[Month Accident].children ) ,[Measures].[Fact Count],DESC)on 1 From [Accident DW]  Thực hiện bằng BI.  Thực hiện bằng Pivot Excel. 141
  128. 3.9.11 Câu 11 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.  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select {[Measures].[Fact Count],[Measures].[TongXeThietHai]} on 0, Generate([Dim Date].[Year Accident].children, Subset([Dim Date].[Year Accident].CurrentMember*Order([Dim Road Type].[Road Type].children,[Measures].[TongXeThietHai],DESC),2,5)) on 1 From [Accident DW] 142
  129.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 143
  130. 3.9.12 Câu 12 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  Thực hiện câu truy vấn bằng ngôn ngữ MDX. Select {[Measures].[Fact Count]} on 0, CrossJoin([Dim Date].[Year Accident].children ,EXCEPT([Dim Urban Or Rural Area].[Name Urban Or Rural Area].children,[Dim Urban Or Rural Area].[Name Urban Or Rural Area].&[Unallocated]) ,TopCount([Dim Weather Conditions].[Weather Conditions].children,5,[Measures].[Fact Count]) )on 1 From [Accident DW] 144
  131.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 145
  132. 3.9.13 Câu 13 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.  Thực hiện câu truy vấn bằng ngôn ngữ MDX. Select {[Measures].[TocDoLonNhat],[Measures].[Fact Count]} on 0, Non Empty(CrossJoin({[Dim Road Type].[Road Type].children* [Dim Light Conditions].[Light Conditions].children}, Order( (Filter([Dim Weather Conditions].[Weather Conditions].children,[Measures].[TocDoLonNhat]>30)) ,[Measures].[Fact Count],DESC)))on 1 From [Accident DW] where [Dim Urban Or Rural Area].[Name Urban Or Rural Area].&[Urban] 146
  133.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 147
  134. 3.9.14 Câu 14 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).  Thực hiện câu truy vấn bằng ngôn ngữ MDX. select [Measures].[TongSoThuongVong] on 0, generate( [Dim Accident Severity].[Accident Severity].children, Filter([Dim Accident Severity].[Accident Severity].CurrentMember* Order([Dim Police Force].[Police Force].children,[Measures].[TongSoThuongVong],ASC),[Measures].[TongSoT huongVong]>5000) )on 1 From [Accident DW] 148
  135.  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 149
  136. 3.9.15 Câu 15 Câu 15: Thống kê 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).  Thực hiện câu truy vấn bằng ngôn ngữ MDX. Select {[Measures].[TongSoThuongVong],[Measures].[TongXeThietHai],[Measures]. [Fact Count],[Measures].[TocDoLonNhat]} on 0, Filter( {INTERSECT( {Generate([Dim Date].[Year Accident].Children, Filter([Dim Date].[Year Accident].CurrentMember*[Dim Date].[Month Accident].children*[Dim Road Type].[Road Type].children ,[Measures].[TocDoLonNhat] >= 20) )} , {Generate([Dim Date].[Year Accident].Children, Filter([Dim Date].[Year Accident].CurrentMember*[Dim Date].[Month Accident].children*[Dim Road Type].[Road Type].children 150
  137. ,[Measures].[TocDoLonNhat] = 5 ) on 1 From [Accident DW] Where [Dim Accident Severity].[Accident Severity].&[Fatal]  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 151
  138. 3.9.16 Câu 16 Câu 16: Roll Up, Tổng số vụ tai nạn giao thông xảy ra trong năm 2009  Thực hiện câu truy vấn bằng ngôn ngữ MDX. Select [Measures].[Fact Count] on 0, [Dim Date].[Year Accident].&[2009] on 1 From [Accident DW]  Thực hiện câu truy vấn bằng BI. 152
  139.  Thực hiện câu truy vấn bằng Pivot Excel. 3.9.17 Câu 17 Câu 17: Drill Down, Thống kê số thương vong theo từng ngày, tháng, năm trong năm 2012  Thực hiện câu truy vấn bằng ngôn ngữ MDX. Select [Measures].[TongSoThuongVong] on 0, 153
  140. [Dim Date].[Year Accident].&[2012]*[Dim Date].[Month Accident].children*[Dim Date].[Day Accident].children on 1 From [Accident DW]  Thực hiện câu truy vấn bằng BI.  Thực hiện câu truy vấn bằng Pivot Excel. 154
  141. 3.9.18 Câu 18 Câu 18: Slice & dice Thống kê tổng xe thiệt hại, trong năm 2005, năm 2015  Thực hiện câu truy vấn bằng MDX. Select [Measures].[TongXeThietHai] on 1, {[Dim Date].[Year Accident].&[2005],[Dim Date].[Year Accident].&[2015]} on 0 From [Accident DW]  Thực hiện truy vấn bằng BI. 155
  142.  Thực hiện truy vấn bằng Pivot Excel. 156
  143. CHƯƠNG 4: QUÁ TRÌNH SSRS 4.1 Tạo report bằng công cụ SSRS 4.1.1 Tạo Report Service Project Tạo mới Report Services project. Chọn dòng Report Services Project. Đặt tên Project và chọn chỗ lưu. 4.1.2 Tạo và cấu hình Share Data Sources Click chuột phải vào Share Data Sources chọn Add New Data Source Type chọn Microsoft SQL Analysis Service. 157
  144. Chọn Build => Nhập server name và chọn database cần thực hiện sau đó nhấn OK. 158
  145. Credentials chọn Use windows Authentication (Integrated security) 4.2 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? 4.2.1 Tạo Reports Click chuột phải vào Reports chọn Add => New Item chọn Report. Đặt tên cho Report và nhấn Add 159
  146. 4.2.2 Tạo Dataset Click chuột phải vào Datasets chọn Add dataset Đặt tên Dataset. Chọn Use a dataset embedded inmy report. Chọn Data Source được tạo trước đó. 160
  147. Chọn Query Designer. 161
  148. Chọn thuộc tính cần tạo report và chọn Ok. 162
  149. Chọn OK. 4.2.3 Tạo table và định dạng report Click chuột phải chọn Insert => Table, chọn các thuộc tính cần thiết như: Name_Urban_Or_Rural_Area, Fact_Count. - Đổi màu tô vàng ô chữ: click vào ô cần chỉnh sửa, sau đó chỉnh sửa trong bảng properties, thay đổi thuộc tính BackgroundColor thành “Gold: như sau: 163
  150. - Thêm biểu đồ tròn vào báo cáo ta làm như sau: Click phải chuột trong phần report chọn insert -> chart 164
  151. Sau đó chọn biểu đồ tròn, nhấn OK. 165
  152. Thêm các thuộc tính khác của biểu đồ tròn. - Cách chèn hình ảnh vào report. Cick chuột phải chọn insert -> Image 166
  153. Trong hộp thoại Image Properties, nhấn Import chọn đường dẫn tới hình ảnh, sau đó nhấn OK. - Thêm Text Box ta làm như sau: Cick phải chọn Insert -> Text Box. 167
  154. 4.2.4 Báo cáo hoàn chỉnh 168
  155. 4.3 Thống kê số xe thiệt hại, số người bị thương, số vụ tai nạn giao thông theo từng điều kiện thời tiết từ năm 2005 - 2015. 4.3.1 Tạo Reports Click chuột phải vào Reports chọn Add => New Item chọn Report. Đặt tên cho Report và nhấn Add 4.3.2 Tạo DataSet Click chuột phải vào Datasets chọn Add dataset Đặt tên Dataset. Chọn Use a dataset embedded inmy report. Chọn Data Source được tạo trước đó. 169
  156. Chọn Query Designer. 170
  157. Chọn thuộc tính cần tạo report và chọn Ok. 171
  158. Chọn OK. 4.3.3 Tạo Table và Grouping Click chuột phải chọn Insert => Table Kéo thả thuộc tính Year_Accident vào bảng Click chuột phải chọn Add Group => Row Group chọn Parent Group Chọn Year_Accident và tick vào Add Group Footer, sau đó nhấn OK. 173
  159. Cick chuột phải Year_Accident chọn Add Group -> Child Group Chọn Month_Accident, tick vào Add Group Footer, sau đó nhấn OK. 174
  160. Cick phải Month_Accident chọn Add Group -> Child Group Chọn Weather_Conditions, tick Add Group Footer, sau đó nhấn OK. 175
  161. Cick phải Weather_Conditions, chọn Add Group -> Child Group Chọn [TongXeThietHai], tick Add Group Footer, sau đó nhấn OK. 176
  162. Cick phải chuột TongXeThietHai, chọn Add Group -> Child Group Chọn TongSoThuongVong, chọn OK. 177
  163. Cick phải TongSoThuongVong, chọn Add Group -> Child Group Chọn Fact_Count, nhấn OK. 178
  164. 4.3.4 Tạo Total và định dạng Report Chọn TongXeThietHai click chuột phải chọn Add Total. 179
  165. Tương tự với TongSoThuongVong, Fact Count, Month Accident, Year Accident. Hoặc ta có thể làm theo cách sau đây: Chọn TongXeThietHai, click chuột phải chọn Expression. 180
  166. Ta thêm “= sum(Fields!TongXeThietHai.Value)” vào khung nhập. Sau đó nhấn OK. Làm tương tự với TongSoThuongVong, Fact Count ứng với giá trị tương ứng. Thêm biểu đồ đường vào Report như sau: Click chuột phải chọn Insert -> Chart. 181
  167. Chọn biểu đồ đường, nhấn OK. 182
  168. Tại ô Values thêm [Sum(TongXeThietHai)], [Sum(TongSoThuongVong)], [Sum(Fact_Count)]. Tại Category Groups, chọn Year_Accident. Ta có được biểu đồ như sau Định dạng Report 183
  169. Chỉnh sửa Font chữ, màu chữ, click chuột phải chọn Text Box Properties. Chỉnh sửa Font: chọn Font, và tùy chỉnh theo ý. Nhấn OK 184
  170. Chỉnh sửa màu ô chữ: Chọn Fill, thay đổi Fill Color theo ý. Nhấn OK. Sau khi định dạng xong ta có như sau: Tạo Page header và Page Footer Click chuột phải chọn Add Page Header/ Add Page Footer. Sau đó tiến hành chỉnh sửa. 185
  171. 4.3.5 Báo cáo hoàn chỉnh 4 . 186
  172. 4.4 Thống kê theo tháng, năm 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). 4.4.1 Tạo Report Click chuột phải vào Reports chọn Add => New Item chọn Report. Đặt tên cho Report và nhấn Add 187
  173. 4.4.2 Tạo DataSet Click chuột phải vào Datasets chọn Add dataset Đặt tên Dataset. Chọn Use a dataset embedded inmy report. Chọn Data Source được tạo trước đó. Chọn Query Designer. 188
  174. Chọn thuộc tính cần tạo report và chọn Ok. 189
  175. Chọn OK. 4.4.3 Tạo Table và Grouping Click chuột phải chọn Insert => Table Kéo thả Year_Accident vào bảng. Click chuột phải chọn Add Group => Row Group chọn Parent Group 190
  176. Chọn Year_Accident và tick vào Add Group Footer, sau đó nhấn OK. Cick chuột phải Year_Accident chọn Add Group -> Child Group 191
  177. Chọn Month_Accident, tick vào Add Group Footer, sau đó nhấn OK. Cick phải Month_Accident chọn Add Group -> Child Group 192
  178. Chọn Road_Type, tick Add Group Footer, sau đó nhấn OK. Click chuột phải Road_Type, chọn Add Group -> Child Group 193
  179. Chọn TongSoThuongVong, tick Add Group Footer. Sau đó nhấn OK. Ta kéo thả thêm các TongXeThietHai, Fact Count, TocDoLonNhat vao bảng. 194
  180. 4.4.4 Tạo Total và định dạng Report Chọn TongSoThuongVong click chuột phải chọn Expression. 195
  181. Nhập câu lệnh “=Sum(Field!TongSoThuongVong.Value)”. Ta làm tương tự với các cột TongXeThietHai, Fact Count, TocDoLonNhat. 196
  182. Định dạng Report Chỉnh sửa font chữ, màu chữ, cỡ chữ, Chọn ô cần chỉnh sửa, click chuột phải chọn Text Box Properties. 197
  183. Thay đổi màu ô chữ, chọn Fill, thay đổi màu sắc trong Fill Color. 198
  184. Sau khi chỉnh sửa 199
  185. 4.4.5 Báo cáo hoàn chỉnh . 200
  186. 4.5 Đăng kí dịch vụ Power BI -Truy cập link sau: • Chọn Start free -> Try free 201
  187. • Nhập email đăng kí và mật khẩu • Giao diện Mời thêm người hiện ra , bạn có thể nhập email của bạn bè để giới thiệu hoặc chọn bỏ qua • Giao diện Power BI hiện ra 202
  188. • Chọn Cách tạo báo cáo -> Tải xuống Desktop 4.6 Tạo Report bằng Power BI - Khởi động Power BI - Chọn Get Data -> Chọn nguồn dữ liệu để import, ở đây mình chọn là Analysis Services. 203
  189. - Nhập Server: LAPTOP-9SK5B3CV, nhập Dattabase là “SSAS”, chọn Import - Sau đó nhấp OK. - Sau đó chọn Cube mà muốn nhập vào report. 204
  190. - Sau đó nhấp Load. - Nếu kết nối thành thì các trường dữ liệu sẽ hiển thị phía bên phải. 205
  191. - Tiếp đến kéo thả các Dimenstion và Mesure tương ứng để có thể tạo nên report mong muốn. 4.6.1 Roport 1: Thống kê số vụ tai nạn giao thông qua các khu vực • Xây dựng report bằng biểu đồ hình cột : Mình sẽ chọn Visualization mà mình muốn trong Visualizations Panel, ở đây mình chọn là Column Chart. 206
  192. • Xây dựng report bằng biểu đồ hình tròn : Mình sẽ chọn Visualization mà mình muốn trong Visualizations Panel, ở đây mình chọn là Pie Chart. 207
  193. Tạo report qua biểu đồ đường: Mình sẽ chọn Visualization mà mình muốn trong Visualizations Panel, ở đây mình chọn là Line Chart. 208
  194. Tạo report bằng nhóm cột: Clustered Column Chart. 209
  195. 4.6.2 Report 2: Thống kê tổng số thương vong theo từng điều kiện thời tiết qua các năm từ 2005 - 2015. 210
  196. 4.6.3 Report 3: Thống kê tổng xe thiệt hại, tổng số thương vong, tổng số vụ tai nạn xảy ra tai mỗi đồn cảnh sát qua các năm từ 2005 - 2015. 212
  197. CHƯƠNG 5: DATA MINING 5.1 Thực hiện quá trình Data Mining ❖ Tạo Mining Structure Bước 1: Phải chuột vào project và chọn Deploy. Bước 2 : Trong Solution Explorer, phải chuột vào Mining Structures. Chọn New Mining Structure Bước 3: Welcome to Data Mining Wizard xuất hiện, nhấn Next. 214
  198. Bước 4: Chọn From Existing relation database or data warhouse. Chọn Next. 215
  199. Bước 5: Tại hộp thoại Creat the Data Mining Structure. Chọn Creat mining structure with a mining model. Chọn Microsoft Decision Trees. Nhấn next. Bước 6: Tại hộp thoại Select Data Source View chọn Accident DW. Nhấn Next. 216
  200. Bước 7: Tại hộp thoại Specify Table Types. Tại mục case, chọn Fact. 217
  201. Bước 8: Tại hộp thoại Specify the Training Data, chọn ➢ Key : Fact ➢ 5 Input: ID_Weather_Conditions, ID_Road_Type, ID_Road_Surface, Speech_limit, ID_Light_Conditions. ➢ 1 Prediction: Urban_Or_Rural_Area 218
  202. Bước 9: Tại màn hình Specify Columns’ Content and Data Type chọn kiểu Content Type và Data type như hình. Nhấn Next 5.2 Tạo và xử lý Models Tại màn hình Create Testing Set: Chọn phần tram dữ liệu cho testing là 30% Số trường hợp lớn nhất cho mẫu testing: 100 000 trường hợp 219
  203. 5.3 Cây quyết định Trong điều kiện tốc độ giới hạn lớn hơn hoặc bằng 21 và nhỏ hơn 35, điều kiện ánh sáng (ID=4) trong buổi tối và có đèn, loại đường (ID = 6) đường đơn, điều kiện mặt đường (ID = 2) ẩm ướt, điều kiện thời tiết (ID = 2) mưa và không có gió lớn. Thì tỷ lệ xảy ra tai nạn giao thông ở thành thị là 91,01%. (35661 trường hợp). Trong điều kiện: tốc độ lớn hơn hoặc bằng 56 và nhỏ hơn 63, loại đường (ID=6) đường đơn, điều kiện ánh sáng (ID = 1) ánh sáng ban ngày, điều kiện 220
  204. mặt đường không phải là (ID =4) sương mù, và điều kiện thời tiết (ID=1) điều kiện tốt không có gió lớn. Thì tỷ lệ xảy ra tai nạn ở nông thôn là 96.02% (130304 trường hợp).  Dependency Network của thuật toán Microsoft Decision Tree cho thấy những thuộc tính có ảnh hưởng tới việc xảy ra tai nạn giao thông.  Những thuộc tính có ảnh hưởng giảm dần là: Speed Limit -> ID Road Type -> ID Light Conditions -> ID Road Surface Conditions -> ID Weather Conditions. 221
  205. 5.4 Thuật toán Microsoft Clustering Cluster Diagram của thuật toán với Shading Variable Urban Or Rural Area, value = 1 (Urban - thành thị) 223
  206. Tập lục cho ta thấy khả năng xảy ra tai nạn giao thông ở thành thị cao nhất là 90,5%. Với những điều kiện như sau: Tốc độ giới hạn lớn hơn hoặc bằng 19,29 km/h và nhỏ hơn 20,69 km/h, loại đường là (ID = 2, ID = 9, ID = 6) đường một chiều, đường đơn, đường không xác định; điều kiện mặt đường (ID = 1) mặt đường khô, điều kiện thời tiết (ID = 1, ID = 9) điều kiện tốt và không có gió 224
  207. lớn, thời tiết không xác định, điều kiện ánh sáng (ID = 1,4,5) ánh sáng ban ngày hoặc ban đêm có đèn hoặc không có đèn. Tập màu đỏ cho ta thấy tỷ lệ xảy ra tai nạn giao thông ở nông thôn là 95%. Với những điều kiện như sau: Tốc độ giới hạn là 60% (Speed Limit = 60), điều kiện trong tối và không có đèn xe (ID Light Conditions=6 Darkness - no lighting), loại đường là đường đơn (ID Road Type=6 Single carriageway), điều kiện mặt đường là ẩm ướt (ID Road Surface Conditions=2 Wet/Damp), điều kiện mặt đường có sương mù và băng tuyết (ID Road Surface Conditions=4 Frost/Ice), điều kiện thời tiết có sương mù (ID Weather Conditions=7 Fog or mist), thời tiết mưa mà không có gió lớn (ID Weather Conditions=2 Raining without high winds), điều kiện mặt đường có tuyết (ID Road Surface Conditions=3 Snow), điều kiện thời tiết có mưa và gió lớn (ID Weather Conditions=5 Raining with high winds), điều kiện mặt đường là ngập lụt với mức nước từ 3cm trở lên (ID Road Surface Conditions=5 Flood over 3cm of water), điều kiện thời tiết tốt và có gió lớn 225
  208. (ID Weather Conditions=4 Fine with high winds), điều kiện thời tiết có tuyết và có gió lớn (ID Weather Conditions=3 Snowing with high winds), điều kiện thời tiết có tuyết nhưng không có gió lớn (ID Weather Conditions=6 Snowing without high winds). 5.5 Thuật toán Microsoft Naive Bayes Dependency Network của thuật toán Microsoft Naive Bayes cho ta mức độ ảnh hưởng của các yếu tố đến việc xảy ra tai nạn giao thông ở các khu vực. 226
  209. Điều kiện trong tối và không có ánh sáng của đèn (ID = 6), thì khả năng cao xảy ra tai nạn ở nông thôn cao hơn so với thành thị. Trong điều kiện trời tối và có ánh sáng (ID = 4), thì khả năng cao sẽ xảy ra tai nạn giao thông ở thành thị hơn nông thôn. 227
  210. 5.6 Đánh giá các thuật toán bằng Mining Accuracy Chart Thiết lập thông số Input Selection với khu vực là thành thị (ID = 1). Line chart cho ta thấy tỷ lệ chính xác của 3 thuật toán khi mining trường hợp là khu vực thành thị. ✓ Microsoft Decision Tree: 0.94 Score. 228
  211. ✓ Microsoft Clustering: 0.93 Score. ✓ Microsoft Naive Bayes: 0.84 Score. Thiết lập thông số Input Selection với khu vực là nông thôn (ID = 2). Line chart cho ta thấy tỷ lệ chính xác của 3 thuật toán khi mining trường hợp là khu vực thành thị. 229
  212. ✓ Microsoft Decision Tree: 0.91 Score. ✓ Microsoft Clustering: 0.89 Score. ✓ Microsoft Naive Bayes: 0.76 Score. 230
  213. CHƯƠNG 6 TÀI LIỆU THAM KHẢO ➢ Các slide bài giảng môn Kho Dữ Liệu Và OLAP được giảng viên cung cấp ➢ mining/microsoft-decision-trees-algorithm?view=asallproducts- allversions ➢ operator?view=sql-server-ver15 231