ĐẠI HỌC QUỐC GIA HÀ NỘI
TRƯỜNG ĐẠI HỌC CÔNG NGHỆ TRẦN VĂN THÀNH NGHIÊN CỨU GIẢI PHÁP KHO DỮ LIỆU TRONG
SQL SERVER 2008 VÀ ÁP DỤNG TRONG THƯƠNG MẠI
LUẬN VĂN THẠC SĨ
Hà Nội - 2011
ĐẠI HỌC QUỐC GIA HÀ NỘI
TRƯỜNG ĐẠI HỌC CÔNG NGHỆ
LỜI MỞ ĐẦU 6
Chương 1. TỔNG QUAN VỀ KHO DỮ LIỆU 8
1.1. Các khái niệm 8
1.1.1. Kho dữ liệu 8
1.1.2. Mục đích của kho dữ liệu 8
1.1.3. Đặc tính của kho dữ liệu 8
1.1.4. Phân biệt kho dữ liệu với cơ sở dữ liệu tác nghiệp 10
1.2. Kiến trúc kho dữ liệu 11
1.2.1. Nguồn dữ liệu 11
1.2.2. Kho dữ liệu 11
1.2.3. Kho dữ liệu chuyên biệt 12
1.2.4. Kho siêu dữ liệu 12
1.2.5. Vùng chứa tạm 12
1.2.6. Người dùng 13
1.3. Qui trình xây dựng kho dữ liệu 13
1.3.1. Qui trình xây dựng kho dữ liệu 13
1.3.2. Chi tiết các bước xây dựng kho dữ liệu 14
1.4. Tổng kết chương 20
Chương 2. CÔNG NGHỆ KHO DỮ LIỆU TRONG SQL SERVER 21
2.1. Lý do sử dụng công nghệ kho dữ liệu của Microsoft 21
2.2. Giải pháp tổng thể kho dữ liệu của Microsoft 22
2.3. Cải tiến cho kho dữ liệu trong SQL Server 2008 23
2.4. Các công cụ cho kho dữ liệu trong SQL Server 2008 24
2.4.1. Dịch vụ tích hợp dữ liệu 25
2.4.2. Dịch vụ Báo cáo 29
2.4.3. Dịch vụ phân tích 31
2.4.4. Bộ công cụ phát triển trí tuệ doanh nghiệp 33
2.4.5. Công cụ quản lý SQL Server 34
2.4.6. Dịch vụ tác nhân SQL Server 35
2.5. Qui trình xây dựng kho dữ liệu của Microsoft 36
KẾT LUẬN 74
TÀI LIỆU THAM KHẢO 76
Tiếng Việt 76
Tiếng Anh 76
3 BẢNG DANH SÁCH CÁC TỪ VIẾT TẮT
Từ
Tiếng Anh
Tiếng Việt
CNTT
Công nghệ thông tin
CSDL
Cơ sở dữ liệu
BI
Business Intelligent
Trí tuệ doanh nghiệp
BIDS
Business Intelligent Development Studio
Trình phát triển trí tuệ doanh
nghiệp
DM
Data Mart
Kho dữ liệu chuyên biệt
DW
Data Warehouse
Relational OLAP
OLAP quan hệ
SCD
Slowly Changing Dimension
Chiều thay đổi chậm
SSAS
SQL Server Analysis Services
Dịch vụ phân tích của SQL
Server
SSIS
SQL Server Integration Service
Dịch vụ tích hợp của SQL Server
SSMS
SQL Server Management Studio
Trình quản lý của SQL Server
SSRS
SQL Server Reporting Services
Dịch vụ báo cáo của SQL Server
UDM
Unified Dimensional Model
Mô hình chiều thống nhất
4 DANH SÁCH HÌNH VẼ
Hình 1.1. Đặc tính của kho dữ liệu 8
Hình 1.2. Kiến trúc kho dữ liệu 11
Hình 1.3. Ví dụ về lược đồ hình sao 12
Hình 1.4. Ví dụ phân tích dữ liệu bởi người dùng 13
Hình 1.5. Qui trình xây dựng kho dữ liệu 14
Hình 3.18. Mẫu phân tích trong Excel sử dụng PivotTable 65
Hình 3.19. Màn hình SieuThi_Job của tác nhân SQL Server 67
Hình 4.1. Màn hình lịch sử chạy Job_SieuThi_01 70
Hình 4.2. Danh sách các báo cáo 71
Hình 4.3. Báo cáo “BC01 - Doanh thu bán hàng theo các năm” 71
Hình 4.4. Báo cáo “BC11 - Top 10 mặt hàng doanh số cao trong tháng” 72
Hình 4.5. Mẫu phân tích số lượng bán theo hàng hóa và thời gian 72
Hình 4.6. Mẫu phân tích doanh thu theo tháng giữa các năm 73
5 DANH SÁCH BẢNG BIỂU
Bảng 3.1. Danh sách các bảng trong kho dữ liệu SieuThi_SRC 42
Bảng 3.2. Danh sách các bảng trong kho dữ liệu SieuThi_DW 48
Bảng 3.3. Danh sách các bảng trong kho dữ liệu SieuThi_DM 49
Bảng 3.4. Danh sách các gói tích hợp cho SieuThi_STG 53
Bảng 3.5. Danh sách các gói tích hợp cho SieuThi_DW 56
Bảng 3.6. Danh sách các gói tích hợp cho SieuThi_DM 58
Bảng 4.1. Số bản ghi của các bảng trong SieuThi_SRC 68
Bảng 4.2. Số bản ghi của các bảng trong SieuThi_DW 69
Bảng 4.3. Số bản ghi của các bảng trong SieuThi_DM 69 6 LỜI MỞ ĐẦU
Việc xây dựng kho dữ liệu phục vụ cho phân tích và báo cáo của các doanh
nghiệp sẽ là xu thế tất yếu trong những năm tới ở Việt nam. Thực ra, kho dữ liệu đã
được áp dụng khá phổ biến tại các nước phát triển như Mỹ, Úc, Đức, Nhật Tại Việt
những gì liên quan đến việc xây dựng kho dữ liệu siêu thị. Nội dung bao gồm phạm vi
và yêu cầu bài toán, cấu trúc dữ liệu nguồn và đích, chi tiết các gói tích hợp dữ liệu cài
7 đặt bởi SSIS, chi tiết khối dữ liệu cài đặt bởi SSAS và cuối cùng các mẫu các báo cáo
và phân tích nhằm khai thác kho dữ liệu.
Chương 4. Kết quả cài đặt thử nghiệm. Chương này tóm tắt các phần đã được cài
đặt và chạy thử nghiệm như: dữ liệu nguồn và đích, thời gian chạy các gói tích hợp,
thời gian cập nhật khối dữ liệu, thời gian đáp ứng khi phân tích, và các mẫu phân tích
và báo cáo.
Phần kết luận trình bày tổng hợp các kết quả thực hiện luận văn cũng như các
phần cần mở rộng khi áp dụng cho thực tế.
8 Chương 1. TỔNG QUAN VỀ KHO DỮ LIỆU
1.1. Các khái niệm
1.1.1. Kho dữ liệu
Định nghĩa: kho dữ liệu (Data Warehouse - DW) là tuyển tập các CSDL tích hợp,
hướng chủ đề, được thiết kế để hỗ trợ cho chức năng trợ giúp quyết định, mà mỗi đơn
vị dữ liệu đều liên quan tới một khoảng thời gian cụ thể.
[1]
Thực ra, cũng có nhiều định nghĩa về kho dữ liệu khác nhau, nhưng tất cả đều
hướng đến mục đích là hỗ trợ cho chức năng trợ giúp quyết định.
Ngoài việc chứa đựng một CSDL quan hệ, kho dữ liệu còn bao gồm các bước
CSDL tác nghiệp lại cần những dữ liệu chi tiết, phục vụ trực tiếp cho
những yêu cầu xử lý theo các chức năng của lĩnh vực ứng dụng hiện thời.
Do vậy mối quan hệ của dữ liệu trong những hệ thống này cũng khác, đòi
hỏi phải có tính chính xác và có tính thời sự.
1.1.3.2. Tính tích hợp
Tính tích hợp được thể hiện bằng việc tích hợp dữ liệu từ các nguồn dữ liệu hỗn
tạp khác nhau. Các kỹ thuật làm sạch và tích hợp dữ liệu được áp dụng để đảm bảo sự
đồng nhất trong các quy ước tên, cấu trúc mã hóa, các đơn vị đo thuộc tính giữa các
nguồn khác nhau. Kho dữ liệu phải đưa các dữ liệu từ các nguồn khác nhau về định
dạng phù hợp. Ngoài ra cũng cần phải giải quyết những vấn đề như xung đột tên và
mâu thuẫn giữa các đơn vị đo.
1.1.3.3. Tính ổn định
Tính ổn định thể hiện ở chỗ dữ liệu trong kho dữ liệu chỉ đọc và khai thác, không
được sửa đổi bởi người sử dụng đầu cuối. Nó chỉ cho phép thực hiện hai thao tác cơ
bản:
Nạp dữ liệu vào kho dữ liệu
Truy cập dữ liệu từ kho dữ liệu
Thông tin trong kho dữ liệu được nạp vào sau khi dữ liệu trong hệ thống tác
nghiệp đã xong giao dịch. Tính không biến động thể hiện ở chỗ dữ liệu được lưu trữ
lâu dài trong kho dữ liệu. Mặc dù có thêm dữ liệu mới nhập vào nhưng dữ liệu cũ
10 trong kho vẫn không bị xoá và sửa đổi, điều đó cho phép cung cấp thông tin về một
khoảng thời gian dài, cung cấp đủ số liệu cần thiết cho các mô hình nghiệp vụ phân
tích, dự báo.
Tuy nhiên, trong thực tế nếu các bảng dữ liệu có kích thước quá lớn thì cũng phải
có kế hoạch để lưu trữ bớt các dữ liệu trong quá khứ, thời gian có thể sau 5-10 năm tuỳ
theo yêu cầu nghiệp vụ báo cáo liên quan. Sau khi lưu trữ dữ liệu cũ thì có thể xoá đi
hoặc lưu trữ sang chỗ khác để giảm bớt dung lượng cần cho việc lưu trữ và tăng tốc độ
Nhưng thực tế không phải lúc nào cũng cần có đầy đủ cả năm tầng này. Tầng Staging
Area và Data Mart có thể bỏ đi tùy theo yêu cầu cụ thể của từng dự án.
Hình 1.2. Kiến trúc kho dữ liệu
1.2.1. Nguồn dữ liệu
Nguồn dữ liệu cho kho dữ liệu có thể một trong các dạng sau:
CSDL của các phần mềm ứng dụng hoặc của các hệ thống tác nghiệp
được lưu trữ bởi một hệ quản trị CSDL như Oracle, SQL Server, Access,
DB2…
Các tập tin phẳng, các tập tin nhật ký, bảng tính Excel…
1.2.2. Kho dữ liệu
Kho dữ liệu hay còn gọi là kho dữ liệu mức doanh nghiệp là thành phần chứa dữ
liệu lịch sử của nhiều chủ đề khác nhau, nó bao gồm cả dữ liệu thô và dữ liệu đã được
tổng hợp ở một mức thấp. Cụ thể như sau:
Dữ liệu thô: đây là phần chứa toàn bộ dữ liệu ở mức chi tiết nhất được lấy
từ dữ liệu nguồn sau khi đã loại bỏ những dữ liệu không cần thiết và biến
đổi chúng, trong phần này dữ liệu thường vẫn được tổ chức tuân theo
chuẩn 3NF (Third Normal Form).
Dữ liệu tổng hợp: đây là phần chứa dữ liệu ở mức tổng hợp hơn, được
nhóm theo một số chiều nhất định, tùy theo mục đích cụ thể của từng tổ
chức mà tạo ra các bảng tổng hợp khác nhau. Các bảng dữ liệu tổng hợp
12 này thường tổ chức phi chuẩn và được dùng cho mục đích phân tích báo
cáo cũng như đầu vào dữ liệu cho việc xây dựng các kho dữ liệu chuyên
biệt.
1.2.3. Kho dữ liệu chuyên biệt
Kho dữ liệu chuyên biệt là thành phần chứa dữ liệu tổng hợp theo một chủ đề nào
đó như bán hàng, tồn kho, đặt hàng, thu nợ nhằm phục vụ cho việc truy vấn, báo cáo
phải có một phương pháp tiếp cận thích hợp. Hiện nay có nhiều cách tiếp cận khác
nhau nhưng đều dựa trên ba kiểu cơ bản sau:
Tiếp cận theo hướng tăng dần
Tiếp cận theo hướng từ trên xuống
Tiếp cận theo hướng từ dưới lên
Mỗi cách tiếp cận đều có các ưu và nhược điểm riêng, cách tiếp cận như Hình 1.5
sau đây là một phương pháp tiếp cận phổ biến hiện nay.
14
Hình 1.5. Qui trình xây dựng kho dữ liệu
Như vậy, để xây dựng và phát triển kho dữ liệu cần phải thực hiện các công việc
cơ bản sau:
Lập kế hoạch xây dựng kho dữ liệu: kết quả là phải đưa ra được tập các
yêu cầu cho việc xây dựng kho dữ liệu và kế hoạch thực hiện.
Phân tích yêu cầu người sử dụng: để tập hợp được tất cả các yêu cầu của
người sử dụng.
Xây dựng mô hình kho dữ liệu: định nghĩa mô hình nghiệp vụ, tạo mô
hình logic và tạo mô hình dữ liệu vật lý.
Lập kế hoạch cài đặt vật lý: bao gồm việc lựa chọn kiến trúc tính toán cho
hệ thống máy chủ phục vụ cho kho dữ liệu và lập kế hoạch lưu trữ dữ liệu.
Xây dựng qui trình tích hợp cho kho dữ liệu: xây dựng ra các công việc để
thực hiện việc tích hợp dữ liệu từ nguồn vào kho dữ liệu.
Quản trị kho dữ liệu: cài đặt đưa vào sử dụng kho dữ liệu, quản lý khai
thác và cập nhật dữ liệu liên tục cho kho dữ liệu.
1.3.2. Chi tiết các bước xây dựng kho dữ liệu
1.3.2.1. Lập kế hoạch xây dựng kho dữ liệu
Việc lập kế hoạch xây dựng kho dữ liệu bao gồm các nội dung cơ bản sau:
Lập kế hoạch tài chính: phải ước lượng được tổng chi phí cần đầu tư cho
1.3.2.3. Xây dựng mô hình kho dữ liệu
Việc xây dựng mô hình kho dữ liệu sẽ được thực hiện qua bốn bước cơ bản sau:
Xác định mô hình nghiệp vụ;
Tạo mô hình logic;
Tạo mô hình mức tổng hợp;
Tạo mô hình vật lý.
Tuần tự các bước được thể hiện trong Hình 1.6 bên dưới.
16
Hình 1.6. Các bước xây dựng mô hình kho dữ liệu
Việc xác định mô hình nghiệp vụ dựa trên các bước sau:
Xác định các yêu cầu nghiệp vụ.
Xác định các đại lượng tính toán như số lượng, thành tiền, khuyến mãi
Xác định các chiều dữ liệu như hàng hoá, khách hàng, kênh bán hàng,
vùng miền, thời gian
Xác định các định nghĩa nghiệp vụ và các qui tắc nghiệp vụ.
Xác định nguồn dữ liệu chính: nguồn dữ liệu có liên quan đến các nghiệp
vụ cần thiết cho kho dữ liệu.
Xác định nguồn dữ liệu khác: nguồn dữ liệu từ bên ngoài, dữ liệu không
phải dạng CSDL quan hệ.
Tạo mô hình logic: thực hiện sau khi xác định được các chủ đề cho kho dữ liệu
thì cần xác định mô hình logic của kho dữ liệu. Có ba loại kiểu mô hình thường dùng
cho kho dữ liệu là: mô hình chuẩn hoá, mô hình phi chuẩn hoá và mô hình hình sao.
Chi tiết cho từng mô hình như sau:
Mô hình chuẩn hoá: tức là dữ liệu được tổ chức theo chuẩn, thường là theo
chuẩn 3NF giống như khi tổ chức dữ liệu trong OLTP.
Mô hình phi chuẩn: dữ liệu được lưu trữ đầy trong các bảng. Mục đích là
phi chuẩn hoá các bảng để thêm hầu hết các cột được truy nhập vào một
Định nghĩa qui ước đặt tên và các chuẩn qui định chung cho kho dữ liệu.
Ví dụ: tên bảng chiều thì bắt đầu bằng tiền tố DIM_, tên bảng sự kiện thì
bắt đầu bằng FACT_, tên index thì bắt đầu bằng IDX_.
Thiết lập các chỉ mục: cho mục đích thực hiện truy vấn được nhanh hơn.
Thiết lập các phân vùng: cho mục đích truy vấn được nhanh và dễ bảo trì
kho dữ liệu sau này.
Cấu hình tối ưu cho kho dữ liệu: bằng cách thiết lập các tham số cho
CSDL để tăng hiệu năng thực hiện truy vấn và đảm bảo an toàn cho kho
dữ liệu.
Cấu hình cho kho dữ liệu chạy ở chế độ song song.
1.3.2.4. Lập kế hoạch cài đặt vật lý
Việc cài đặt vật lý cho kho dữ liệu cũng cần được xem xét một cách kỹ lưỡng khi
tiến hành xây dựng kho dữ liệu, nó bao gồm nhiều công việc khác nhau như chuẩn bị
cơ sở hạ tầng mạng máy tính, máy chủ, thiết bị lưu trữ, thiết bị sao lưu, thiết bị bảo
mật Nhưng chủ yếu sẽ tập trung vào hai yếu tố quan trọng khi cài đặt vật lý là: chọn
lựa mô hình vật lý và lập giải pháp lưu trữ cho kho dữ liệu.
Chọn lựa mô hình vật lý: việc xác định mô hình vật lý thường theo một trong các
mô hình phổ biến sau:
Mô hình SMP (Symmetric Multi-Processing): tức là kiến trúc mà nhiều
CPU trên cùng một máy chủ cùng chia sẻ một bộ nhớ và hệ thống đĩa, chi
tiết như Hình 1.8 bên dưới.
Hình 1.8. Mô hình SMP
Mô hình Cluster: là mô hình mà gồm có nhiều máy chủ được nối với nhau
thành một khối thống nhất và cùng chia sẻ chung hệ thống đĩa ngoài, như
Hình 1.9, cùng tham gia vào xử lý các công việc với nhau. Ở góc độ người
dùng có thể xem các máy chủ này tương đương như một máy chủ. Các
máy chủ này thường được nối với nhau thông qua một thiết bị hỗ trợ băng
hoặc theo từng bó.
1.3.2.6. Quản trị kho dữ liệu
20 Sau khi kho dữ liệu được đưa vào sử dụng thì yêu cầu rất quan trọng là nó phải
được quản lý và theo dõi thường xuyên, sao cho đảm bảo thông suốt cho người dùng
khai thác thông tin hiệu quả nhất. Việc quản trị kho dữ liệu bao gồm các tác vụ sau:
Quản lý về an toàn, bảo mật và độ ưu tiên.
Quản lý sự truy cập từ nhiều người khác nhau.
Kiểm tra chất lượng dữ liệu thường xuyên.
Kiểm tra quá trình tích hợp thường xuyên.
Quản lý và cập nhật kho siêu dữ liệu.
Giám sát và lập các báo cáo về tình hình sử dụng và trạng thái của kho dữ
liệu như thời gian sử dụng, số người khai thác, thời gian đáp ứng các yêu
cầu
Quản lý qui trình sao lưu.
Lập kế hoạch sẵn sàng phục hồi kho dữ liệu khi có sự cố.
Lập kế hoạch để nâng cấp và mở rộng hệ thống đĩa, bộ nhớ, băng thông
sẵn sàng cho sự gia tăng kích cỡ của kho dữ liệu theo thời gian.
Lập kế hoạch lưu trữ bớt các dữ liệu cũ không cần thiết cho việc khai thác
ra các thiết bị lưu trữ ngoài kho dữ liệu.
Lập kế hoạch mở rộng phạm vi dữ liệu của kho dữ liệu khi yêu cầu nghiệp
vụ thay đổi.
1.3.2.7. Hỗ trợ khai thác kho dữ liệu
Cái đích chính của việc xây dựng kho dữ liệu là cung cấp thông tin hỗ trợ cho
các nhà quản lý đưa ra các quyết định chiến lược hiệu quả hơn. Nên phải có kế hoạch
trang bị các công cụ và đào tạo sử dụng nhằm nâng cao khả năng khai thác kho dữ
liệu.
1.4. Tổng kết chương
trị SQL Server cũng dễ hơn các hệ quản trị CSDL khác nên giảm được số
lượng nhân lực và thời gian đào tạo đáng kể. Ngoài ra, ngày nay công
nghệ lập trình .Net sử dụng CSDL SQL Server là khá phổ biến tại các
doanh nghiệp nên họ có thể tận dụng được nguồn nhân lực này cho việc
tiếp nhận và phát triển mở rộng cho kho dữ liệu.
Tính mở: mặc dù có thể xây dựng một kho dữ liệu hoàn chỉnh chỉ sử dụng
duy nhất các công cụ của Microsoft nhưng các công cụ này hoàn toàn có
thể được thay thế bởi các công cụ của các nhà phát triển thứ ba.
Tính hiệu năng cao và mở rộng: tính đến thời điểm hiện nay thì kho dữ
liệu được xây dựng dựa trên công nghệ của Microsoft có dung lượng đến
10 terabytes khá phổ biến và đã có một số kho dữ liệu mà dung lượng đạt
đến mức 50 terabytes. Microsoft cũng đã và đang mở rộng các dòng sản
22 phẩm của SQL Server cho phép xử lý song song nhằm hướng đến kho dữ
liệu mức hàng trăm terabytes.
Microsoft đã đẩy mạnh đầu tư vào lĩnh vực trí tuệ doanh nghiệp, bản thân SQL
Server 2008 đã tích hợp bộ công cụ phát triển ứng dụng trí tuệ doanh nghiệp một cách
đầy đủ từ việc tích hợp dữ liệu, đến việc lập báo cáo, phân tích số liệu, và quản lý.
2.2. Giải pháp tổng thể kho dữ liệu của Microsoft
Những năm gần đây Microsoft đã tập trung vào giải pháp trí tuệ doanh nghiệp
nói chung hay hẹp hơn là giải pháp kho dữ liệu, họ xác định đây là một trong những
hướng chính mà hãng sẽ tiếp tục theo đuổi. Giải pháp tổng thể của Microsoft cho
DW/BI được thể hiện như Hình 2.1 bên dưới.
Hình 2.1. Giải pháp DW/BI của Microsoft
Tầng dữ liệu nguồn: là tầng chứa dữ liệu nguồn cho kho dữ liệu, có thể lấy dữ
liệu từ nhiều ứng dụng khác nhau như Microsoft Dynamic, Siebel, SAP và từ nhiều
hệ quản trị CSDL khác nhau như Oracle, DB2, Informix…
Cho phép tạo các bảng sự kiện với kích thước rất lớn lên tới 100 tỷ hàng.
Cung cấp câu lệnh thao thác gộp, câu lệnh này thực hiện các thao tác đối
với các bảng dữ liệu với hiệu năng rất tốt ngoài ra cũng nhờ gộp các thao
tác thêm mới, sửa và xóa vào một câu lệnh nên giúp việc viết các câu lệnh
sáng sủa và nhanh hơn.
Cải tiến câu lệnh chèn vào bảng một tập các hàng dữ liệu từ câu lệnh chọn
nhằm tăng tốc độ thực hiện và giảm thiểu bộ nhớ làm việc.
Hỗ trợ khả năng bắt các sự thay đổi (Change Data Capture - CDC) cho
CSDL nguồn chạy trên SQL SERVER 2008, đây là khả năng tự đánh dấu
sự thay đổi của một bảng theo thời gian và cho phép lấy ra tập dữ liệu thay
đổi trong một khoảng thời gian chỉ ra. Tính năng này thường áp dụng cho
các bảng dữ liệu nguồn mà chưa có cột ghi lại sự thay đổi dữ liệu theo thời
gian.
Cải tiến công cụ tra cứu dùng cho tích hợp, cho phép sử dụng bộ nhớ đệm
một cách tối đa tại máy chủ tích hợp.
Copyright: Tài liệu đại học ©