Ạ Ọ Ố Ồ Đ 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
Ờ Ả Ơ 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ô!
Ậ Ả Ủ NH N XÉT C A GI NG VIÊN
….……………………………………………………………………...
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
……………………………………………………………………………………
…………
ụ ụ M c L c
ƯƠ
Ề Ề
Ổ
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:
https://data.gov.uk/dataset/roadaccidentssafetydata
ủ ề 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
ả ữ ệ
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:
https://www.kaggle.com/silicon99/dftaccidentdata
ả 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.
Field Name Description Type ST T
ụ 1 Accident_Index ạ Mã v tai n n String
ọ ươ ng ở 2 Location_Easting_OSGR String ộ ị T a đ đ a ph Anh (X)
ọ ươ ng ở 3 Location_Northing_OSGR String ộ ị T a đ đ a ph 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
ươ ệ ng ti n b h ị ư 8 Number_of_Vehicles Int ạ ố S ph ạ h i liên quan tai n n
ố ươ 9 Number_of_Casualties S th ng vong Int
ả 10 Date Date ạ Ngày x y ra tai n n
ứ 11 int Day_of_Week ầ Th trong tu n
ả 12 Time DateTime ờ Th i gian x y ra tai n nạ
ự 13 Local_Authority_(District) Int ươ ả ị Tên khu v c đ a ụ ệ ng x y ra v vi c ph
ả ng chính x y 14 Local_Authority_(Highway) String ườ Tên đ ra 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
ườ i đi 23 Int ườ Pedestrian_Crossing Human_Control ự ể S ki m soát ng ộ b qua đ ng
24 Int ườ Pedestrian_Crossing Physical_Facilities ơ ở ậ ấ C s ộ ườ i đi b qua đ ng v t ch t cho ng
ề ệ 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 Int Thành th ho c nông thông
ả
31 Int Did_Police_Officer_Attend_Sc ene_of_Accident C nh sát có tham gia ườ ệ ng tai vào hi n tr ạ n n hay không?
ủ ị 32 LSOA_of_Accident_Location String ả ị ự Khu v c đ a lý c a v ạ 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
(cid:0) B ng Dim_Urban_or_Rural_Area
ả
ặ Description ị Mã thành th ho c Type int Field Name Urban_or_Rural_Area
nông thôn
ị varchar Name_Urban_or_Rural_Area Tên thành th và
nông thôn
(cid:0) B ng Dim_Accident_Severity
ả
ệ Type int Field Name ID_Accident_Severity
ứ ộ ọ Description ứ ộ Mã m c đ nghi m tr ngọ M c đ nghiêm tr ng varchar Accident_Severity
(cid:0) B ng Dim_Police_Force
ả
ồ ả Description ồ ả Mã đ n c nh sát Đ n c nh sát Type int varchar Field Name ID_Police_Force Police_Force
(cid:0) B ng Dim_Road_Surface_Conditions
ả
ặ Type int
ặ varchar Field Name ID_Road_Surface_Condition s Road_Surface_Conditions
Description ệ ề Mã đi u ki n m t ngườ đ ệ ề Đi u ki n m t ngườ đ
(cid:0) B ng Dim_Weather_Conditions
ả
Description ờ ế t Mã th i ti ờ ế t Th i ti Type int varchar Field Name ID_Weather_Conditions Weather_Conditions
(cid:0) B ng Dim_Light_Conditions
ả
ệ ề Description ệ ề Mã đi u ki n ánh sáng Đi u ki n ánh sáng Type int varchar Field Name ID_Light_Conditions Light_Conditions
(cid:0) B ng Dim_Road_Type
ả
ngườ
Description Mã tên đ Tên đ ngườ Type int varchar Field Name ID_Road_Type Road_Type
(cid:0) B ng Dim_Date
ả
Type Date Field Name Date_Accident
Description ả Ngày x y ra tai n nạ Ngày Tháng Năm int int int Day_Accident Month_Accident Year_Accident
(cid:0) B ng Dim_Time
ả
ờ Type SmallDateTime Field Name Time_Accident
Description ả Th i gian x y ra tai n nạ Giờ Phút int int Hour_Accident Minutes_Accident
(cid:0) B ng Fact
ả
STT Field Name Description Type
ụ Accident_Index ạ Mã v tai n n Varchar 1
ồ ả ID_Police_Force Đ n c nh sát Int 2
ứ ộ ọ ID_Accident_Severity M c đ nghiêm tr ng Int 3
ươ Int Number_of_Vehicles 4 ị ư ệ ố ng ti n b h S ph ạ ạ h i liên quan tai n n
ố ươ Int Number_of_Casualties S th ng vong 5
ả Date_Accident ạ Ngày x y ra tai n n Date 6
ả Time_Accident SmallDateTime 7 ờ Th i gian x y ra tai n nạ
ạ ườ ID_Road_Type Lo i đ ng Int 8
ố ộ ớ ạ Speed_limit T c đ gi i h n Float 9
ề ệ 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 Int ặ ệ ề Đi u ki n m t ngườ đ
ị ặ 13 Urban_or_Rural_Area Int Thành th ho c nông thông
ơ ồ
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.
ệ ồ ả ạ ậ ố ệ ạ ề 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à 2070km/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
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.
ế ố ớ ả ấ 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.
ọ ể ế ố ớ ệ ả ị i h qu n tr
ọ Ch n OLEDB, sau đó ch n Add đ k t n i t 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.
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:
ả 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.
2.4 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
ạ
ớ
ở ộ Kh i đ ng Microsoft Visual Studio. T o m t
ộ Analysis Services Project m i có tên “SSAS
“.
ạ ứ ụ 3.1 T o ng d ng SSAS
ạ
3.2 T o Datasource
ả
ọ
ộ
c 1ướ : Trong khung Solution Explorer, ph i chu t Data Sources và ch n New
B 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.
ướ
ẽ ị
ị
ộ : Trong dialog box Connection Manager, ta s đ nh nghĩa giá tr thu c
- B
c 4
ọ
tính cho Data Source. Trong danh sách Provider, ch n Native OLE DB\SQL
Server Native Cilent 10.0.
ướ
: Trong server name gõ vào “LAPTOP9SK5B3CV”
- B
c 5
ướ
ể
ị
ượ
ọ
: Ki m tra giá tr Use Windows Authentication đ
c ch n, trong danh
- B
c 6
ở
ọ
sách Select or enter a database name ,
đây ta ch n “Accident DW”
ướ
ế ố ế
ể ể
ấ
ớ
:Nh n Test Connection đ ki m tra k t n i đ n v i SQL Server.
- B
c 7
ướ
ấ
ọ
Ch n OK và nh n Next.
- B
c 8:
ạ ử ổ
ẽ
t l p Security
- B
c 9ướ :T i c a s Impersonation Information, ta s thi ế ậ ọ ể ế ố ớ i Data Sources, ta ch n “Use the service
Credential cho SSAS đ k t n i t account”.
ướ
ạ
ế ậ
ủ
: T i màn hình Completing the wizard , thi
t l p tên c a Data Source
- B
c 10
ọ
là “Accident DW” và ch n Finish.
3.3 T o DataSource View
ả
ộ
c 1ướ : Trong khung Solution Explorer , ph i chu t Data Sources Views, và ọ
-B ch n New Data Sources Views.
ạ
ệ
ấ
ấ
- B
c 2ướ : Welcome to Data Sources Views Wizard xu t hi n, nh n Next.
ụ
ấ
ạ
i m c Relational Data Sources, ọ
ệ ồ
ư
ạ
c 3ướ : Dialog select Data source xu t hi n, t B ọ ch n “Accident DW” mà ta đã t o nh trên r i ch n Next.
ữ ệ
ả
ạ
ẽ ọ ố ượ
ố ượ
ẽ ọ
ướ T i Dialog Select Tables and Views, ta s ch n các b ng d li u - B c 4: ừ danh sách đ i t và views t
ng , ta s ch n các đ i t
ng.
ướ
ể
ả
ấ
Nh n nút “>” đ thêm các b ng vào danh sách Included Object.
B
c 5:
ấ
- B
c 6ướ : Nh n Next.
ộ
ế ậ
ấ
ể t l p là warehouse và nh n finish đ
c 7ướ : T i thu c tính Name, ta thi ấ
ạ ệ ị
B hoàn t
t vi c đ nh nghĩa data source view.
ữ ệ
ẽ
ệ
ề
ả
c này, ta s có khung nhìn toàn di n v các b ng d li u
ấ ướ t b ệ ủ
ố
Sau khi hoàn t và m i quan h c a chúng.
ả
ộ
ọ
c 1ướ : Trong pane Solutin Explorer, ph i chu t vào Cubes, ch n New
- B Cubes.
ạ ử ổ
ọ
- B
c 2ướ : T i c a s Welcome to Cubes Wizard, ch n Next.
ả
ả
ọ
c 3ướ : T i c a s Select Creation Method, đ m b o tùy ch n Use an
ạ ử ổ ượ
ấ
ọ
- B Existing Table đ
c ch n và nh n Next.
ạ 3.4 T o cube
ạ ử ổ
ả
ả
c 4ướ : T i c a s Select Measure Group Tables, đ m b o data source
ượ
- B view warehouse đ
ọ c ch n.
ề
ể
ấ
ị
c 5ướ : Nh n Suggest đ c a s Suggest Table hi n th và đ xu t các
ự
- B ầ table c n thi
ấ ể ử ổ ế ể t đ xây d ng các measure group.
ấ
- B
c 6ướ : Nh n Next
ạ ử ổ
ạ
ấ
i các measures .Nh n next
- B
c 7ướ : T i c a s Select Measure, xem l
ọ
c 8ướ : T i c a s Selecting Existing Dimensions, ch n Data
ạ ử ổ ượ ị
- B Dimension đã đ
c đ nh nghĩa
ạ ử ổ
ế ậ
ủ
t l p name c a Cube là
c 9ướ : T i c a s Completing the Wizard, thi
- B “Accident DW”
ướ
ể
ọ
ấ
ượ ạ
: Ch n Finish đ hoàn t
t, Cube đã đ
c t o.
- B
c 10
ử ả ề 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
ể
ườ
ng trong Dim_date vào Dim
- Trong Data Source View, di chuy n các tr date trong Attributes.
ớ ả
V i b ng Dim_Road_Type
ộ
ấ - Trong Folder Dimensions Nh p đúp chu t vào Dim Road Type.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr 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
ể
ườ
ng trong Dim_time vào
- Trong Data Source View, di chuy n các tr 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.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr 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.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr 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.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr 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.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr 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.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr 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.
ườ
ể
ng trong
- Trong Data Source View, di chuy n các tr Dim_Police_Force vào Dim Police Force trong Attributes.
-
ả
ộ
c 1ướ : Trong pane Solutin Explorer, ph i chu t vào Analysic Services
B ọ Project và ch n Properties.
-
ạ
ạ
ọ
i panel bên trái, ch n Deployment .
ể
B S a l
c 2ướ : T i Configuration Properties t ử ạ i Server và Database đ deploy.
3.6 Deploy cube lên server
ả
ộ
ọ
- 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.
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
(cid:0) Usage : là các hàm tính toán th ng kê.
(cid:0) Source table: b ngả
(cid:0) 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 YearMonthDay, 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, RightClick 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
ở Doubleclick vào Cube màn hình Solution Explorer:
ọ Ch n tab Calculations:
ố ở ụ ọ Rightclick 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
ố ượ ớ ệ ạ ề ệ ấ ỏ ng xe l n nh t thi t h i, s xe thi t h i nh nh t do đi u ki n
Câu 2: S l ề ặ ườ b m t đ ệ ạ ố ấ ng trong năm 2015? (Roll Up)
ấ
ớ ng xe l n nh t, ấ ố ượ ố ượ ( Dùng hàm [SoXeLonNhat] dùng hàm max tìm ra s l ỏ ng xe nh nh t ). [SoXeNhoNhat] dùng hàm min tìm ra s l
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
ố ổ ươ ừ ế ả năm 2008 đ n 2015
ố ỗ ng vong x y ra t ề ệ Câu 6: Th ng kê t ng s ng ạ t ố ườ ờ ế i m i thành ph cùng th i ti i th 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
ố ượ ấ ớ ệ
ấ ng xe l n nh t và nh nh t thi ượ ắ ỏ ế ề ặ ệ ạ t h i do đi u ki n b m t ố ượ ề ề ầ ả c s p x p theo chi u gi m d n theo s l ng xe
ấ ủ Câu 7: S l ườ đ ng (Không có Dry ) đ ớ 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
ồ ả ề ậ
t kê 10 đ n c nh sát ghi nh n tai n n, có s xe thi ơ ố ườ ừ ố ươ ớ ạ ng vong l n h n 10000 ng ệ ạ t h i nhi u ế i.T năm 2012 đ n năm
ệ Câu 8: Li ư ấ nh t, nh ng s th 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
ữ ề ế ằ ầ ả
ắ ạ ắ ầ ồ ả 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,
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.
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]
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.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]
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.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]
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.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 ườ ị ươ ng i b th
ứ ộ ấ ắ ớ ọ í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]. [TongSoThuongVong]>5000)
)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.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
,[Measures].[TocDoLonNhat] <= 70))}) }
, [Measures].[Fact Count] >= 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.
ấ ằ ự ệ
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.
ự ệ ấ ằ
Th c hi n câu truy v n b ng Pivot Excel.
ấ ằ ự ệ
3.9.17 Câu 17
ố ố ươ ừ ng vong theo t ng ngày, tháng,
Câu 17: Drill Down, Th ng kê s th 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, [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.
ấ ằ ự ệ
3.9.18 Câu 18
ố ổ ệ ạ t h i, trong năm 2005, năm
Câu 18: Slice & dice Th ng kê t ng xe thi 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.
ự ệ ấ ằ
ự ệ ằ ấ Th c hi n truy v n b ng Pivot Excel.
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.
ự
ệ
ầ
ậ
ấ
ọ
ọ
Ch n Build => Nh p server name và ch n database c n th c hi n sau đó nh n OK.
ọ 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
ạ 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 đó.
ọ Ch n Query Designer.
ầ ạ ọ ộ ọ Ch n thu c tính c n t o report và ch n Ok.
ọ 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:
ư ể ồ 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
ể ấ ồ ọ Sau đó ch n bi u đ tròn, nh n OK.
ủ ể ộ ồ 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
ọ ườ ạ ấ ộ ẫ ớ ả 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.
4.2.4 Báo cáo hoàn ch nhỉ
ố ố ệ ạ ố ườ ị ươ ố ụ ạ 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 đó.
ọ Ch n Query Designer.
ầ ạ ọ ộ ọ Ch n thu c tính c n t o report và ch n Ok.
ọ 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.
ả ộ ọ 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.
ả ọ Cick ph i Month_Accident ch n Add Group > Child Group
ấ ọ Ch n Weather_Conditions, tick Add Group Footer, sau đó nh n OK.
ả ọ Cick ph i Weather_Conditions, ch n Add Group > Child Group…
ấ ọ Ch n [TongXeThietHai], tick Add Group Footer, sau đó nh n OK.
ả ộ ọ Cick ph i chu t TongXeThietHai, ch n Add Group > Child Group
ọ ọ Ch n TongSoThuongVong, ch n OK.
ả ọ Cick ph i TongSoThuongVong, ch n Add Group > Child Group …
ấ ọ Ch n Fact_Count, nh n OK.
ạ ạ ị 4.3.4 T o Total và đ nh d ng Report
ả ọ ọ ộ Ch n TongXeThietHai click chu t ph i ch n Add Total.
ươ ự ớ 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.
ậ 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.
ồ ườ ọ ể Ch n bi u đ đ ấ ng, nh n OK.
ạ 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
ữ ử ữ ả ộ ọ ỉ 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
ử ữ ấ ọ ổ ỉ 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.
4.3.5 Báo cáo hoàn ch nhỉ
….…..
4
ổ ng,
ố ấ ả ố ộ ớ ạ ạ
ố ố ụ ấ ị ư ạ ố ườ ị ươ ạ ườ ớ ố ạ i b th ng v i t c ớ ơ ố ượ ặ ng v tai n n là l n h n ho c
ớ ổ ể ụ ứ ộ ử 4.4 Th ng kê theo tháng, năm t ng s xe b h h i, s ng ổ i các lo i đ t ng s v tai n n, t c đ l n nh t x y ra t ộ ớ ụ đ l n nh t là 2070km/h ,v i t ng s l ằ 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
ạ 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.
ầ ạ ọ ộ ọ Ch n thu c tính c n t o report và ch n Ok.
ọ 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…
ấ ọ 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
ấ ọ 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
ấ ọ 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…
ấ ọ 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.
ạ ạ ị 4.4.4 T o Total và đ nh d ng Report
ả ọ ọ ộ Ch n TongSoThuongVong click chu t ph i ch n Expression.
ệ ậ 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.
ạ ị Đ 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.
ữ ắ ổ ọ ổ Thay đ i màu ô ch , ch n Fill, thay đ i màu s c trong Fill Color.
ỉ ử Sau khi ch nh s a
….…..
4.4.5 Báo cáo hoàn ch nhỉ
ụ ị 4.5 Đăng kí d ch v Power BI
(cid:0) Ch n ọ Start free > Try free
ậ Truy c p link sau: https://powerbi.microsoft.com/enus/
(cid:0) Nh p email đăng kí và m t kh u
ậ ẩ ậ
(cid:0) Giao di n ệ M i thêm ng
ủ ạ ệ ạ ậ
ờ ệ ể ớ i ặ i thi u ho c ch n bè đ gi ườ hi n ra , b n có th nh p email c a b n ể ọ b quaỏ
(cid:0) Giao di n Power BI hi n ra
ệ ệ
(cid:0) 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.
ậ
ọ
- Nh p Server:
ậ LAPTOP9SK5B3CV, nh p Dattabase là “SSAS”,
ch n Import
ấ
Sau đó nh p OK.
ậ
ố
ọ
Sau đó ch n Cube mà mu n nh p vào report.
ấ
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.
ế
ươ ứ
ể ạ
ể
ng ng đ có th t o nên
ả ế Ti p đ n kéo th các Dimenstion và Mesure t 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
ự
ể
ộ
ồ
(cid:0) 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.
ự
ể
ồ
(cid:0) 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.
ồ ườ ể ạ 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.
ằ ạ ộ T o report b ng nhóm c t: Clustered Column Chart.
ổ ố ố ươ ề ệ ừ ng vong theo t ng đi u ki n
ờ ế ừ 4.6.2 Report 2: Th ng kê t ng s th 2005 2015. th i ti t qua các năm t
ệ ạ ổ ố ổ ổ
ỗ ồ ả ạ ả ố ươ ừ 4.6.3 Report 3: Th ng kê t ng xe thi t h i, t ng s th ố ụ s v tai n n x y ra tai m i đ n c nh sát qua các năm t ng vong, t ng 2005 2015.
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.
ọ ọ B c 4ướ : Ch n From Existing relation database or data warhouse. Ch n
Next.
ạ ộ ạ ọ 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.
ạ ộ
ấ
ạ
ọ
c 6ướ : T i h p tho i Select Data Source View ch n Accident DW. Nh n
B Next.
ướ
ạ ộ
ụ
ạ
ạ
ọ
T i h p tho i Specify Table Types. T i m c case, ch n Fact.
B
c 7:
ạ ộ
ạ
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
ạ ọ 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
ế ị 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 ạ x y ra tai n n giao thông l ở
ị ườ 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 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 x y ra tai n n l 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.
ậ 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 ) ị
ậ ụ ấ ả ả ạ ở ị 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ó 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
ờ ế ề ệ (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.
ố ủ ả i và không có ánh sáng c a đèn (ID = 6), thì kh năng cao
ạ ở ớ ệ ề Đi u ki n trong t ả x y ra tai n n ị ơ nông thôn cao h n so v i thành th .
ệ ẽ ả ả i và có ánh sáng (ID = 4), thì kh năng cao s x y ra
ờ ố ở ạ ị ơ ề Trong đi u ki n tr i t tai n n giao thông thành th h n nông thôn.
ậ ằ 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
Microsoft Decision Tree: 0.94 Score.
ự ị là khu v c thành th .
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 .
Microsoft Decision Tree: 0.91 Score.
Microsoft Clustering: 0.89 Score.
Microsoft Naive Bayes: 0.76 Score.
ƯƠ
Ả
Ệ
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ấ
https://docs.microsoft.com/enus/analysisservices/data
https://docs.microsoft.com/enus/sql/mdx/exceptmdxoperator?
mining/microsoftdecisiontreesalgorithm?view=asallproducts allversions
view=sqlserverver15
