Tổng quan Ứng dụng SQL trong Business Intelligence, Data Warehousing và OLAP

Trong bài viết này, Datapot sẽ chia sẻ tới các bạn về cách ứng dụng SQL trong Business Intelligence, Data Warehousing và OLAP.

Mục lục

1 Data Warehouse
- 1.1 Data Warehouse là gì?
2 Các khái niệm SQL cơ bản và nâng cao cho Data Warehouse
3 Ví dụ về ứng dụng SQL trong Data Warehosue
- 3.1 Ví dụ 1: Truy vấn dữ liệu bán hàng
- 3.2 Ví dụ 2: Sử dụng CTE
4 OLAP

Data Warehouse

Data Warehouse là gì?

Data Warehouse là một hệ thống lưu trữ tập trung, được thiết kế để chứa và quản lý dữ liệu từ nhiều nguồn khác nhau. Mục đích chính của Data Warehouse là hỗ trợ quá trình phân tích dữ liệu và ra quyết định trong doanh nghiệp. Nó tối ưu hóa cho các truy vấn và báo cáo phức tạp, giúp người dùng dễ dàng truy xuất và phân tích dữ liệu lớn. Dữ liệu trong Data Warehouse thường được tổ chức dưới dạng các bảng Fact và bảng Dimension, theo mô hình sao (Star Schema) hoặc bông tuyết (Snowflake Schema).

Các khái niệm SQL cơ bản và nâng cao cho Data Warehouse

JOIN

Phép JOIN giúp chúng ta có thể kết nối dữ liệu giữa các bảng. Một số phép JOIN thường được dùng như: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

GROUP BY và HAVING

Thực hiện thao tác nhóm dữ liệu và lọc dữ liệu với Aggregate Function

Sub Queries và CTEs

Để tổ chức, thuận tiện cho việc bảo trì, dễ dàng đọc hiểu các câu truy vấn phức tạp

Indexing, Performance Tuning

Thao tác Indexing sẽ giúp câu truy vấn trả về kết quả nhanh hơn khi áp dụng mệnh đề WHERE đối với những cột có thực hiện index. Trong khi đó, Performance Tuning sẽ đòi hỏi người viết truy vấn có thêm những kỹ năng nâng cao như: có thể đọc hiểu Execution Plan để phát hiện ra những điểm bị bottleneck,…từ đó sẽ giúp tối ưu được câu truy vấn

ETL (Extract, Transform, Loading)

Extract: ứng dụng SQL để trích xuất dữ liệu từ các nguồn khác nhau như cơ sở dữ liệu quan hệ, ứng dụng web, các nguồn dữ liệu phi cấu trúc khác.

Ví dụ trích xuất dữ liệu từ bảng FactInternetSales trong cơ sở dữ liệu AdventureWorksDW2019:

SELECT *
FROM FactInternetSales as FIS

Transform: ứng dụng SQL để chuyển đổi dữ liệu, bao gồm việc làm sạch dữ liệu, chuẩn hóa, tổng hợp và tính toán các chỉ số cần thiết

Ví dụ về chuyển đổi dữ liệu:

SELECT

CustomerKey,

UPPER(FirstName) AS customer_first_name,

SUBSTRING(EmailAddress, 1, CHARINDEX('@',EmailAddress)-1) email_cus

FROM DimCustomer;

Loading: ứng dụng SQL để ghi dữ liệu đã biến đổi vào kho dữ liệu.

Ví dụ về ghi dữ liệu vào bảng trong kho dữ liệu:

INSERT INTO DimCustomer (CustomerKey, FirstName, EmailAddress)

SELECT

CustomerKey,

UPPER(FirstName) customer_first_name,

SUBSTRING(EmailAddress, 1, CHARINDEX('@',EmailAddress)-1) email_cus

FROM DimCustomer;

Data Modeling

Schema Design: Tạo các lược đồ dữ liệu (schema) như Star Schema hoặc Snowflake Schema để tổ chức dữ liệu trong kho dữ liệu.

Ví dụ về tạo bảng trong kho dữ liệu:

CREATE TABLE TableInDW (

sale_id INT PRIMARY KEY,

product_id INT,

customer_id INT,

sale_date DATE,

amount DECIMAL(10, 2)

);

Ví dụ về ứng dụng SQL trong Data Warehosue

Ví dụ 1: Truy vấn dữ liệu bán hàng

Tính tổng doanh thu và số lượng sản phẩm bán được trong năm 2011 theo tên sản phẩm

SELECT

DP.EnglishProductName,

SUM(FIS.SalesAmount) AS TotalSales,

COUNT(DISTINCT FIS.ProductKey) AS NumberOfProducts

FROM

FactInternetSales as FIS

LEFT JOIN

DimProduct DP ON FIS.ProductKey = DP.ProductKey

WHERE

FIS.OrderDate BETWEEN '2011-01-01' AND '2011-12-31'

GROUP BY

DP.EnglishProductName

ORDER BY

TotalSales DESC;

Ví dụ 2: Sử dụng CTE

Sử dụng CTE phân tích dữ liệu khách hàng (khách hàng có tổng chi tiêu > 10000)

WITH CustomerSales AS (

SELECT

DC.FirstName,

SUM(FIS.SalesAmount) AS TotalSales

FROM

FactInternetSales as FIS

LEFT JOIN

DimCustomer as DC ON FIS.CustomerKey = DC.CustomerKey

GROUP BY

DC.FirstName

)

SELECT

FirstName,

TotalSales

FROM

CustomerSales

WHERE

TotalSales > 10000

ORDER BY

TotalSales DESC;

OLAP

Giới thiệu về OLAP

OLAP (Online Analytical Processing) cho phép người dùng dễ dàng truy vấn và phân tích dữ liệu từ nhiều góc độ khác nhau. OLAP giúp tổng hợp và xử lý dữ liệu lớn từ các kho dữ liệu để hỗ trợ quyết định kinh doanh. Các dữ liệu trong OLAP thường được tổ chức dưới dạng đa chiều, gọi là “cubes” (khối), cho phép thực hiện các phép tính như tổng hợp, trung bình, đếm, và phân tích theo các chiều khác nhau (như thời gian, sản phẩm, địa điểm). OLAP đặc biệt hữu ích trong việc tạo báo cáo và dashboard để hiểu rõ hơn về xu hướng và mô hình dữ liệu. OLAP thường được sử dụng để cung cấp thông tin chi tiết và chính xác cho việc ra quyết định

Các khái niệm SQL cơ bản và nâng cao cho OLAP

Window Function

Để thực hiện các phép tính nâng cao: Running Totals, Ranking,…

ROLLUP và CUBE

Để tạo ra các tập hợp dữ liệu đa chiều

Sự khác nhau giữa GROUP BY, ROLLUP và CUBE:

GROUP BY:

Chỉ trả về các nhóm cụ thể được chỉ định
Không bao gồm các Sub-total hoặc Grand Total

ROLLUP:

Thêm các Sub-total và Grand Total
Cấu trúc Sub-total là dạng thứ tự từ cột ngoài cùng bên trái đến cột ngoài cùng bên phải

CUBE:

Trả về tất cả các tổ hợp có thể của các nhóm
Bao gồm tất cả các Sub-total và Grand Total
Tạo ra một tập hợp kết hợp toàn diện hơn so với ROLLUP

Trong đó:

Sub-total: là giá trị tổng hợp của một nhóm con trong tập dữ liệu. Khi nhóm dữ liệu theo nhiều cột, giá trị này chính là tổng giá trị cho từng nhóm cụ thể

Grand Total: là giá trị tổng hợp của toàn bộ tập dữ liệu. Đại diện cho tổng giá trị của tất cả các hàng trong tập dữ liệu mà không phân biệt bất kỳ nhóm nào

Ví dụ: Tổng hợp dữ liệu bán hàng theo năm, quý và sản phẩm

Sử dụng GROUP BY:

SELECT

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName,

SUM(FIS.SalesAmount) AS TotalSales

FROM

FactInternetSales AS FIS

JOIN

DimDate AS DD ON FIS.OrderDateKey = DD.DateKey

JOIN

DimProduct AS DP ON FIS.ProductKey = DP.ProductKey

GROUP BY

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName

ORDER BY

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName;

Sử dụng ROLLUP:

SELECT

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName,

SUM(FIS.SalesAmount) AS TotalSales

FROM

FactInternetSales AS FIS

JOIN

DimDate AS DD ON FIS.OrderDateKey = DD.DateKey

JOIN

DimProduct AS DP ON FIS.ProductKey = DP.ProductKey

GROUP BY

ROLLUP(DD.CalendarYear, DD.CalendarQuarter, DP.EnglishProductName)

ORDER BY

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName;

Sử dụng CUBE:

SELECT

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName,

SUM(FIS.SalesAmount) AS TotalSales

FROM

FactInternetSales AS FIS

JOIN

DimDate AS DD ON FIS.OrderDateKey = DD.DateKey

JOIN

DimProduct AS DP ON FIS.ProductKey = DP.ProductKey

GROUP BY

CUBE(DD.CalendarYear, DD.CalendarQuarter, DP.EnglishProductName)

ORDER BY

DD.CalendarYear,

DD.CalendarQuarter,

DP.EnglishProductName;

Pivot và Unpivot

Dễ dàng chuyển đổi giữa các chiều và thực hiện các phép tổng hợp

Ví dụ:

SELECT *

FROM

(

SELECT

DD.CalendarYear AS Year,

FIS.SalesAmount

FROM

FactInternetSales AS FIS

JOIN

DimDate AS DD ON FIS.OrderDateKey = DD.DateKey

) AS SourceTable

PIVOT

(

SUM(SalesAmount)

FOR Year IN ([2010], [2011], [2012])

) AS PivotTable

Data Slicing and Dicing

Ứng dụng SQL để cắt và chia nhỏ dữ liệu theo nhiều chiều để tạo ra các tập dữ liệu cụ thể cho phân tích

Ví dụ: Chia nhỏ dữ liệu theo thuộc tính vị trí địa lí

SELECT DC.FirstName, DC.AddressLine1, DG.EnglishCountryRegionName

FROM DimCustomer AS DC

LEFT JOIN DimGeography AS DG

ON DC.GeographyKey = DG.GeographyKey

WHERE DG.EnglishCountryRegionName = 'Australia'

Ví dụ: Chia nhỏ dữ liệu theo thuộc tính màu sắc

SELECT DP.ProductKey, DP.EnglishProductName, DP.ListPrice, DP.Color

FROM DimProduct AS DP

WHERE Color = 'Red'

Ví dụ về ứng dụng SQL trong OLAP

Ví dụ 1: Sử dụng ROLLUP để tổng hợp dữ liệu bán hàng

SELECT

DP.EnglishProductName,

YEAR(FIS.OrderDate) AS OrderYear,

SUM(FIS.SalesAmount) AS TotalSales

FROM

FactInternetSales as FIS

LEFT JOIN

DimProduct as DP ON FIS.ProductKey = DP.ProductKey

GROUP BY

ROLLUP (DP.EnglishProductName, YEAR(FIS.OrderDate))

ORDER BY

DP.EnglishProductName,

OrderYear;

Ví dụ 2: Sử dụng Window Functions để tính doanh số lũy kế

SELECT

DP.EnglishProductName,

FIS.OrderDate,

FIS.SalesAmount,

SUM(FIS.SalesAmount) OVER (PARTITION BY DP.EnglishProductName ORDER BY FIS.OrderDate) AS CumulativeSales

FROM

FactInternetSales as FIS

LEFT JOIN

DimProduct as DP ON FIS.ProductKey = DP.ProductKey

ORDER BY

DP.EnglishProductName,

FIS.OrderDate;

Tài liệu tham khảo thêm:

Lưu Trọng Hưng

Data Analyst

Chia sẻ bài viết này

Tổng quan Ứng dụng SQL trong Business Intelligence, Data Warehousing và OLAP

Data Warehouse

Data Warehouse là gì?

Các khái niệm SQL cơ bản và nâng cao cho Data Warehouse

JOIN

GROUP BY và HAVING

Sub Queries và CTEs

Indexing, Performance Tuning

ETL (Extract, Transform, Loading)

Data Modeling

Ví dụ về ứng dụng SQL trong Data Warehosue

Ví dụ 1: Truy vấn dữ liệu bán hàng

Ví dụ 2: Sử dụng CTE

OLAP

Giới thiệu về OLAP

Các khái niệm SQL cơ bản và nâng cao cho OLAP

Window Function

ROLLUP và CUBE

Pivot và Unpivot

Data Slicing and Dicing

Ví dụ về ứng dụng SQL trong OLAP

Ví dụ 1: Sử dụng ROLLUP để tổng hợp dữ liệu bán hàng

Ví dụ 2: Sử dụng Window Functions để tính doanh số lũy kế

Bài viết liên quan

Để lại một bình luận Hủy

Data Warehouse

Data Warehouse là gì?

Các khái niệm SQL cơ bản và nâng cao cho Data Warehouse

JOIN

GROUP BY và HAVING

Sub Queries và CTEs

Indexing, Performance Tuning

ETL (Extract, Transform, Loading)

Data Modeling

Ví dụ về ứng dụng SQL trong Data Warehosue

Ví dụ 1: Truy vấn dữ liệu bán hàng

Ví dụ 2: Sử dụng CTE

OLAP

Giới thiệu về OLAP

Các khái niệm SQL cơ bản và nâng cao cho OLAP

Window Function

ROLLUP và CUBE

Pivot và Unpivot

Data Slicing and Dicing

Ví dụ về ứng dụng SQL trong OLAP

Ví dụ 1: Sử dụng ROLLUP để tổng hợp dữ liệu bán hàng

Ví dụ 2: Sử dụng Window Functions để tính doanh số lũy kế

Bài viết liên quan

Để lại một bình luận Hủy

Đăng nhập