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à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ô! NHẬN XÉT CỦA GIẢNG VIÊN .... Mục Lục CHƯƠNG 1: TỔNG QUAN VỀ ĐỀ TÀI 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: https://data.gov.uk/dataset/road-accidents-safety-data 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 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 Mô tả dữ liệu Các vụ tai nạn ô tô ở vương quốc Anh từ năm 2005- 2015. Link: https://www.kaggle.com/silicon99/dft-accident-data 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 2 Location_Easting_OSGR Tọa độ địa phương ở Anh (X) String 3 Location_Northing_OSGR Tọa độ địa phương ở Anh (Y) String 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 8 Number_of_Vehicles Số phương tiện bị hư hại liên quan tai nạn Int 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 13 Local_Authority_(District) Tên khu vực địa phương xảy ra vụ việc Int 14 Local_Authority_(Highway) Tên đường chính xảy ra tai nạn String 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 23 Pedestrian_Crossing-Human_Control Sự kiểm soát người đi bộ qua đường Int 24 Pedestrian_Crossing-Physical_Facilities Cơ sở vật chất cho người đi bộ qua đường Int 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 30 Urban_or_Rural_Area Thành thị hoặc nông thông Int 31 Did_Police_Officer_Attend_Scene_of_Accident Cảnh sát có tham gia vào hiện trường tai nạn hay không? Int 32 LSOA_of_Accident_Location Khu vực địa lý của vị trí xảy ra tai nạn 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 Description Type Urban_or_Rural_Area Mã thành thị hoặc nông thôn int Name_Urban_or_Rural_Area Tên thành thị và nông thôn varchar 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 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 Field Name Description Type ID_Road_Surface_Conditions Mã điều kiện mặt đường int 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 nạn Date 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 ra tai nạn SmallDateTime Hour_Accident Giờ int Minutes_Accident Phút int Bảng Fact 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 4 Number_of_Vehicles Số phương tiện bị hư hại liên quan tai nạn Int 5 Number_of_Casualties Số thương vong Int 6 Date_Accident Ngày xảy ra tai nạn Date 7 Time_Accident Thời gian xảy ra tai nạn SmallDateTime 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 13 Urban_or_Rural_Area Thành thị hoặc nông thông Int 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. 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). CHƯƠNG 2: QUÁ TRÌNH SSIS 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. 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. 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. - 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. - Kết nối đã thành công. 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: - 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. - Chọn đường dẫn tới file Accidents0515. Sau đó nhấn OK. - 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]) - 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. - Sau đó nhấn “OK”. - Thực hiện DataNull. - Sau đó nhấn OK. Thực hiện tạo các bảng Dim - 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. - 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. - Nhấn Sort, chọn edit. Sau đó nhấn OK. - 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. - Chọn đường dẫn tới file. - Sau đó nhấn OK. - Tạo và cấu hình Sort như sau: - Tạo và cấu hình OLE DB Destination. Đổi tên thành Dim_Accident_Severity. - 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. - 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. - Tạo và cấu hình “Sort” - 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. - Nhấn New. Chọn đường dẫn đến file excel, sau đó OK. - Chọn và cấu hình Sort. - 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: - Tạo và cấu hình Sort. - 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: - Tạo và cấu hình Sort. - 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: - Tạo và cấu hình Sort - 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. - Thực hiện bên trong OLE DB Source. - Sau đó nhấn “OK”. - Thực hiện bên trong Aggregate - Sau đó nhấn “OK”. - Thực hiện bên trong Sort - Sau đó nhấn “OK”. - Thực hiện bên trong Script Component - Vào Script để chỉnh sửa code. - Vào Edit Script - Sau đó nhấp “OK”. - Vào Input Columns. - Vào Inputs and Outputs. - Sau đó nhấn “OK”. - 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. - Thực hiện bên trong OLE DB Source. - Sau đó nhấn “OK”. - Thực hiện bên trong Aggregate - Sau đó nhấn “OK”. - Thực hiện bên trong Sort - Sau đó nhấn “OK”. - Thực hiện bên trong Script Component - Vào Script để chỉnh sửa code. - Vào Edit Script Sau đó nhấp “OK”. Vào Input Columns. - Vào Inputs and Outputs. -Sau đó nhấn “OK”. - 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. - Thực hiện OLE DB Soucre. - Sau đó nhấp OK. - Thực hiện Lookup. - Thực hiện Lookup cho Dim_Accident_Severity. - Sau đó nhấp OK. - Thực hiện Lookup Dim_Date - Sau đó nhấn OK. - Thực hiện Lookup Dim_Light_Conditions. - Sau đó nhấp OK. - Thực hiện Lookup Dim_Police_Force - Sau đó nhấp OK. - Thực hiện Lookup Dim_Road_Surface_Conditions. - Thưc hiện Lookup Dim_Road_Type. - Sau đó nhấp OK. -Thưc hiện Lookup Dim_Time. - Sau đó nhấp OK. -Thưc hiện Lookup Dim_Urban_or_Rural_Area. - Sau đó nhấn OK. - Thực hiện Lookup với Dim_Weather_Conditions. - Sau đó nhấn OK - Tạo và cấu hình OLE DB Destination. - 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”. - 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 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; - 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; - Tạo và cấu hình “Execute SQL Task”. Xóa bảng Fact. - 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) 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); 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); 2.7 Thực thi Package 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“. Tạo Datasource Bước 1: Trong khung Solution Explorer, phải chuột Data Sources và chọn New Data Source. 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. 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”. 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. 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. - 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. 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. 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. 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. 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 Bước 8: Tại cửa sổ Selecting Existing Dimensions, chọn Data Dimension đã được định nghĩa 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. 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. 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. 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. 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. 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. 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 Như vậy project SSAS đã được deploy lên server. 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 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: + 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: 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: 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: Chọn tab Calculations: 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 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. 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. 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] 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. 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] 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. 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. 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 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. 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]); 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. 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]); 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. 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. Thực hiện câu truy vấn bằng Pivot Excel. 3.9.10 Câu 10