Giới thiệu kỹ thuật xử lý phân tích trực tuyến với SQL SERVER
OLAP (OnLine Analytical Processing) with SQL SERVER
Nguyễn Văn Chức - chuc1803@gmail.com
Trong công nghệ kho dữ liệu (Data Warehouse Technology), OLAP là kỹ thuật để truy xuất dữ liệu chủ yếu trong kho dữ liệu. Dữ liệu trong DW được tổ chức dưới dạng các khối dữ liệu đa chiều (Multi Dimensional Cube) và OLAP được dùng để phân tích trên dữ liệu khối (cube).
Bài viết này trình bày cách triển khai thực hiện kỹ thuật OLAP trên DBMS SQL Server phiên bản 2005 hoặc cao hơn.
1.Giải thích một số thuật ngữ
Dưới đây tóm lược các thuật ngữ được sử dụng trong bài viết:
Data Warehouse (DW): Được xem là tập các cơ sở dữ liệu hướng chủ đề, có tính lịch sử được tích hợp từ nhiều nguồn dữ liệu qua các quá trình trích lọc, hợp nhất, chuyển đổi, làm sạch.
Dữ liệu khối (Data Cube): Dữ liệu trong kho dữ liệu được thể hiện dưới dạng đa chiều (Multi Dimension) gọi là khối (cube). Mỗi chiều mô tả một đặc trưng nào đó của dữ liệu. Ví dụ với Data Cube bán hàng thì chiều hàng hóa (Item) mô tả chi tiết về hàng hóa, chiều thời gian (time) mô tả về thời gian bán hàng, chiều chi nhánh (Branch) mô tả thông tin về các đại lý bán hàng,…
Để rõ hơn về Data Cube, hình dưới đây minh họa Data Cube của dữ liệu bán hàng từ bảng dữ liệu (Spreadsheet) sang dữ liệu dạng khối với 3 dimensions là: Location (Cities), Time (Quarters) và Item (Types)
Lược đồ hình sao (Star Schema): Đây là mô hình biểu diễn dữ liệu của DW, lược đồ hình sao về cơ bản gồm có bảng sự kiện (Fact Table) và các bảng chiều (Dimension table). Fact table đùng để theo dõi các biến động của dữ liệu, cấu trúc của Fact table gồm các khóa ngoại đó là các khóa chính của cả bảng chiều (Dimension table). Dimension Table là các bảng mô tả các đặt trưng của các chiều như chiều thời gian, chiều khách hàng, chiều hàng hóa,…
Dưới đây minh họa lược đồ hình sao của bài toán bán hàng. Đây cũng là dữ liệu dùng để minh họa trong phần tiếp theo khi thực hiện OLAP trên SQL Server. Trong đó Fact table là Sales và 4 Dimension tables là time (chiều thời gian) , item (chiều hàng hóa) , location (chiều bị trí) và branch (chiều chi nhánh)
Measure (độ đo): Là đại lượng có thể tính toán được trên các thuộc tính của fact table. Đây là mục tiêu của OLAP và phải xác định trước khi tiến hành phân tích. Ví dụ như tổng tiền bán hàng của một chi nhánh, doanh thu của từng mặt hàng theo quí,…
Phân cấp (Hierarchies): Khái niệm này mô tả sự phân cấp thứ bậc (mức độ chi tiết của dữ liệu). Ví dụ đối với chiều thời gian, ta có thực bậc như sau: day<week<month<quarter<year. Tương tự đối với chiều location ta có thứ bậc street<city<province_or_state<country. Trong khi phân tích dữ liệu chúng ta rất cần khái niệm này để tổng hợp hay chi tiết từng hạng mục dữ liệu trong DW.
2. Mô tả ứng dụng phân tích OLAP
Bài toán mô tả trong phần này là bài toán bán hàng, gồm có 1 Fact table là Sales và 4 Dimension table là time, item, location và branch (Xem lược đồ hình sao trên).
Fact Table (Sales): Lưu giữ các biến động về quá trình bán hàng, gồm các khóa ngoại của 4 dimension tables và 2 thuộc tính là giá bán (dollars_ sold) và số lượng bán (units_sold)
Các dimension table:
Time: lưu giũ thông tin về thời gian bán hàng.
Location: Lưu giữ thông tin về vị trí
Branch: Lưu trữ thông tin về chi nhánh
Item: Lưu trữ thông tin về hàng hóa
Mục đích mô tả hoạt động OLAP để phân tích hoạt động bán hàng của một doanh nghiệp.
3. Triển khai OLAP trong SQL Server
Lưu ý: Để có công cụ phân tích OLAP, bạn phải cài đặt SQL Server 2005 (2008) phiên bản Developer hoặc phiên bản Enterprise Edition đầy đủ và khi cài đặt nhớ chọn mục “SQLServer Database Services” và “Analysis Services”. Công cụ cho phép thực hiện OLAP là “SQL Server Business Intelligence Development Studio - BIDS”. Khi cài SQL Server các phiên bản trên thì BIDS sẻ được tự động cài đặt.
Các bước thực hiện:
Khởi động SQL Server Management Studio và tạo CSDL có tên DW như sau và nhập vào các bảng một số records để phân tích.
Khởi động SQL Server Business Intelligence Development Studio
Tạo một Analysis Services Project mới có tên “OLAP_DW”
Trong của sổ Solution Explorer của Project OLAP_DW, bấm phím phải chuột vào Data Source để tạo một bộ kết nối đến dữ liệu dùng cho phân tích.
Xác định các tham số kết nối đến kho dữ liệu có tên “DW” đã tạo ra trong SQL Server Management Studio.
Đặt tên cho Data Source vàm bấm Finish để hoàn thành việc kết nối đến cơ sở dữ liệu.
Tạo Data Source View để lấy các bảng dữ liệu cần thiêt cần cho phân tích. Bấm phím phải chuột vào Data Source View trong của sổ Solution Explorer chọn New Data Source View
Xác định nguồn dữ liệu (Data Source) cần lấy là DW mới vừa tạo ra ở bước trước
Chọn Next và chọn các bảng cần cho phân tích
Chú ý: Nếu bạn muốn chọn bảng Fact và các bảng Dimension liên quan đến bảng Fact thì chỉ cần chọn Fact Table đưa qua khung bên phải và bấm nút "Add Related Tables" để tự động lấy các bảng Dimensions liên quan.
Sau khi hoàn thành, các bảng Fact và Dimension như sau:
Sau khi tao Data Source và Data Source View ta tạo dữ liệu khối cho phân tích bằng cách bấm chuột phải lên Cube trong Solution Explorer và chọn New Cube
Chọn Next và chọn nguồn dữ liệu cho Khối (DW), hệ thống sẽ tự động dò tìm fact và Dimension Tables
Kết quả như sau:
Bấm Next để thiết lập chiều thời gian. Chú ý, thời gian là một chiều rất quan trọng trong kho dữ liệu nói chúng và phân tích OLAP. Vì vậy nếu bạn không xác định chiều thời gian thì hệ thống sẽ tự động tạo ra một chiều thời gian để quản lý.
Bấm Next để xác định các độ đo (Measure) cho phân tích. Nhắc lại rằng độ đo là các đại lượng phản ảnh mục tiêu phân tích, tính toán. Đó là các phép toán trên thuộc tính có thể tính toán trong bảng Fact.
Bấm Next, hệ thống sẽ tự động phát hiện các cấu trúc phân cấp (Hierarchies) trong các Dimesion Tables
Xem lại các chiều trong khối
Đặt tên khối (DW)và bấm finish để sinh ra khối. Khối dữ liệu với các chiều được sinh ra
Sau khi tạo ra khối dữ liệu cho phân tích, để thực thi OLAP ta bấm phím phải chuột vào tên project trong Solution Explorer và chọn Deploy
Project được thực thi thành công như sau
Sau khi thực thi xong project, để thực hiện các phân tích OLAP phục vụ cho công tác quản lý, bấm phím phải chuột vào Cube trong Solution Explorer chọn Browse để xuất hiện mà hình phân tích:
Màn hình phân tích OLAP như sau:
Panel bên trái chứa các Measure và các Dimensions đã định nghĩa khi xây dựng khối.
Panel bên phải chia làm 2 cửa sổ, cửa sổ phía trên dùng để xác định các điều kiện để phân tích, cửa sổ phía dưới chứa kết quả các measure khi ta kéo thả (drag and drop) các measure từ panel bên trái qua. Tùy theo mục đích phân tích mà chúng ta xác lập các biểu thức phân tích cho phù hợp.
Ví dụ với thiết lập như dưới đây có nghĩa là yêu cầu cho biết số lần (Sales Count) và tổng số lượng (Unit Sold) hàng mà chi nhánh Danang đã bán.
Thiết lập dưới đây cho biết Mặt hàng của hãng Intel đã được bán bao nhiêu lần với tổng số lượng bao nhiêu tại chi nhánh HCM
Màn hình thiết kế OLAP rất dễ sử dụng và linh hoạt, bạn có thể kéo thả các Dimension và các Measure từ Panel bên trái sang panel bên phải. Ví dụ ta có thể kéo thả thuộc tính Branch Name trong Dimension Branches sang panel bên phải và hệ thống sẽ cho biết số lượng và số lần bán các sản phẩm theo từng chi nhánh như sau:
Tùy theo nhu cầu phân tích dữ liệu, bạn có thể tạo ra các lát cắt (slice) dữ liệu trên nhiều chiều khác nhau để sinh ra các tổng hợp dữ liệu cần thiết cho nhu cầu phân tích dữ liệu trong kho rất nhanh chóng và tiện lợi. Hình dưới đây cho biết số lượng và số lần bán các mặt hàng theo từng chi nhánh dựa trên lát cắt 2 chiều Branches và Items
Các tab Dimension Usage, Caculations, KPIs, Actions, Partitions, Perspectives, translations được dùng để mở rộng khả năng phân tích của OLAP.
Ngoài kỹ thuật phân tích OLAP, SQL Server Business Intelligence Development Studio còn cung cấp các kỹ thuật để khai phá dữ liệu như Regression, Association, Decision tree, Time Series, Clustering.. trong mục Mining Structure rất mạnh và tiện lợi để xây dựng các mô hình khai phá dữ liệu (sẽ trình bày ở bài viết khác)
Xem Video minh họa OLAP tại đây
All comments please send to chuc1803@gmail.com. Thank you and Welcome!