1. Giới Thiệu

Dự án Ecommerce Data Warehouse (DW) on-premises được triển khai nhằm xây dựng một hệ thống kho dữ liệu tập trung, tự động hóa quy trình xử lý dữ liệu từ hệ thống thương mại điện tử để phục vụ nhu cầu báo cáo và phân tích kinh doanh. Mục tiêu chính là đảm bảo tính nhất quán, chất lượng và độ tin cậy của dữ liệu, từ đó hỗ trợ đội ngũ quản lý và vận hành đưa ra các quyết định kịp thời và chính xác hơn.

Bộ dữ liệu nguồn được sử dụng trong dự án là Brazilian E‑Commerce Public Dataset by Olist, một bộ dữ liệu công khai phong phú với hơn 100.000 đơn hàng từ năm 2016 đến 2018, bao gồm thông tin chi tiết về khách hàng, đơn hàng, sản phẩm, thanh toán và đánh giá.

2. Mục Tiêu Dự Án

Dự án hướng tới các mục tiêu cốt lõi sau:

• Tập trung hóa dữ liệu: Giải quyết tình trạng dữ liệu phân tán và không đồng nhất bằng cách xây dựng một kho dữ liệu trung tâm.
• Đảm bảo chất lượng dữ liệu: Áp dụng các quy trình chuẩn hóa và kiểm tra dữ liệu để tạo ra một "single source of truth".
• Hỗ trợ báo cáo & phân tích: Cung cấp nền tảng dữ liệu đáng tin cậy cho việc tạo lập các báo cáo và dashboard phục vụ phân tích kinh doanh.
• Tối ưu vận hành và bảo mật: Tận dụng mô hình on-premises để kiểm soát tốt hơn về mặt bảo mật và hiệu suất xử lý dữ liệu.
• Tạo nền tảng mở rộng: Thiết kế hệ thống linh hoạt, dễ mở rộng cho các ứng dụng phân tích nâng cao trong tương lai.

3. Kiến Trúc Tổng Thể

Kiến trúc hệ thống được chia thành các tầng rõ ràng:

1. Source (Nguồn dữ liệu): Hệ thống cơ sở dữ liệu EcommerceDB chứa dữ liệu thô từ bộ dataset Olist.
2. CDC (Change Data Capture): Cơ chế phát hiện sự thay đổi trong dữ liệu nguồn.
3. Staging: Khu vực lưu trữ dữ liệu thô, thực hiện chuẩn hóa tối thiểu và kiểm tra trước khi chuyển vào DW.
4. Data Warehouse (DW): Kho dữ liệu trung tâm với mô hình Star/Galaxy schema, chứa các bảng Fact và Dimension đã được chuẩn hóa.
5. ETL Logs & Metadata: Lưu trữ nhật ký xử lý và thông tin mô tả dữ liệu.
6. Dashboard (BI Layer): Công cụ Power BI on-premises kết nối trực tiếp với DW để tạo báo cáo.
7. Workload Orchestration: SQL Server Agent đảm nhiệm lập lịch và điều phối toàn bộ quy trình ETL.

Luồng xử lý dữ liệu: Source -> Extract -> Staging -> Transform -> Load (DW) -> BI & Dashboard.

4. Thiết Kế Mô Hình Dữ Liệu

4.1 Dữ liệu nguồn (source)

Phần dữ liệu nguồn của dự án được lấy từ các file CSV → Database (on-prem): các file CSV được tiếp nhận vào thư mục landing, qua bước kiểm tra (manifest/checksum, header, encoding), rồi ingest vào database nguồn (EcommerceDB). Từ đây, database nguồn đóng vai trò source chính thức cho toàn bộ pipeline, thay vì đọc trực tiếp từ file.

Bộ dữ liệu chính: orders, order_items, customers, sellers, products, payments, reviews, geolocation, product_category_translation.

Dưới đây là database soucre sau khi được ingest các file csv vào và được thiết kế các ràng buộc dữ liệu, khoá ngoại.

Bảng customers

Tên Cột	Kiểu Dữ Liệu	Mô Tả
customer_id	Varchar(100)	ID định danh duy nhất cho mỗi khách hàng trong một đơn hàng cụ thể (PK của bảng này trong nguồn).
customer_unique_id	Varchar(100)	ID duy nhất cho mỗi khách hàng trên toàn bộ hệ thống (dùng để xác định khách hàng thực sự).
customer_zip_code_prefix	Varchar(10)	Mã ZIP prefix của khách hàng.
customer_city	Varchar(100)	Thành phố của khách hàng.
customer_state	Varchar(2)	Bang (state) của khách hàng (viết tắt, ví dụ: SP, RJ).

Bảng geolocation

Tên Cột	Kiểu Dữ Liệu	Mô Tả
geolocation_zip_code_prefix	Varchar(10)	Mã ZIP prefix.
geolocation_lat	Decimal(10,8)	Vĩ độ (latitude).
geolocation_lng	Decimal(10,8)	Kinh độ (longitude).
geolocation_city	Varchar(100)	Thành phố.
geolocation_state	Varchar(2)	Bang (state) (viết tắt).

Bảng orders

Tên Cột	Kiểu Dữ Liệu	Mô Tả
order_id	Varchar(100)	ID duy nhất của đơn hàng (PK).
customer_id	Varchar(100)	ID định danh khách hàng liên kết với đơn hàng (FK tới olist_customers_dataset).
order_status	Varchar(20)	Trạng thái của đơn hàng (delivered, shipped, canceled, unavailable, invoiced, processing, approved).
order_purchase_timestamp	Datetime	Thời điểm khách hàng mua hàng.
order_approved_at	Datetime (Nullable)	Thời điểm đơn hàng được phê duyệt thanh toán.
order_delivered_carrier_date	Date (Nullable)	Thời điểm đơn hàng được giao cho đơn vị vận chuyển.
order_delivered_customer_date	Date (Nullable)	Thời điểm khách hàng nhận được hàng.
order_estimated_delivery_date	Date	Ngày giao hàng ước tính được hiển thị cho khách hàng.

Bảng order_items

Tên Cột	Kiểu Dữ Liệu	Mô Tả
order_id	Varchar(100)	ID đơn hàng (FK tới olist_orders_dataset).
order_item_id	Int	Số thứ tự của item trong đơn hàng (dùng để xác định các dòng sản phẩm khác nhau trong cùng một đơn).
product_id	Varchar(100)	ID sản phẩm (FK tới olist_products_dataset).
seller_id	Varchar(100)	ID người bán (FK tới olist_sellers_dataset).
shipping_limit_date	Datetime	Ngày giới hạn vận chuyển do người bán cam kết.
price	Decimal(10,2)	Giá bán của sản phẩm.
freight_value	Decimal(10,2)	Phí vận chuyển cho sản phẩm này.

Bảng order_payments

Tên Cột	Kiểu Dữ Liệu	Mô Tả
order_id	Varchar(100)	ID đơn hàng (FK tới olist_orders_dataset).
payment_sequential	Int	Thứ tự thanh toán (dùng khi một đơn hàng có nhiều phương thức thanh toán).
payment_type	Varchar(20)	Loại hình thanh toán (credit_card, boleto, voucher, debit_card).
payment_installments	Int	Số kỳ trả góp.
payment_value	Decimal(10,2)	Giá trị của lần thanh toán này.

Bảng order_reviews

Tên Cột	Kiểu Dữ Liệu	Mô Tả
review_id	Varchar(100)	ID duy nhất của đánh giá (PK).
order_id	Varchar(100)	ID đơn hàng liên quan (FK tới olist_orders_dataset).
review_score	Int	Điểm đánh giá từ 1 đến 5.
review_comment_title	Varchar(255) (Nullable)	Tiêu đề bình luận.
review_comment_message	Text (Nullable)	Nội dung bình luận.
review_creation_date	Date	Ngày đánh giá được tạo.
review_answer_timestamp	Datetime	Thời điểm người bán hoặc hệ thống phản hồi đánh giá.

Bảng products

Tên Cột	Kiểu Dữ Liệu	Mô Tả
product_id	Varchar(100)	ID duy nhất của sản phẩm (PK).
product_category_name	Varchar(100) (Nullable)	Tên danh mục sản phẩm bằng tiếng Bồ Đào Nha.
product_name_lenght	Int (Nullable)	Độ dài của tên sản phẩm.
product_description_lenght	Int (Nullable)	Độ dài của mô tả sản phẩm.
product_photos_qty	Int (Nullable)	Số lượng ảnh của sản phẩm.
product_weight_g	Int (Nullable)	Khối lượng sản phẩm tính bằng gram.
product_length_cm	Int (Nullable)	Chiều dài sản phẩm tính bằng cm.
product_height_cm	Int (Nullable)	Chiều cao sản phẩm tính bằng cm.
product_width_cm	Int (Nullable)	Chiều rộng sản phẩm tính bằng cm.

Bảng sellers

Tên Cột	Kiểu Dữ Liệu	Mô Tả
seller_id	Varchar(100)	ID duy nhất của người bán (PK).
seller_zip_code_prefix	Varchar(10)	Mã ZIP prefix của người bán.
seller_city	Varchar(100)	Thành phố của người bán.
seller_state	Varchar(2)	Bang (state) của người bán (viết tắt).

Bảng product_category_name_translation

Tên Cột	Kiểu Dữ Liệu	Mô Tả
product_category_name	Varchar(100)	Tên danh mục sản phẩm bằng tiếng Bồ Đào Nha (PK).
product_category_name_english	Varchar(100)	Bản dịch tiếng Anh của tên danh mục.

4.2. Tầng Staging (stg)

Tầng staging đóng vai trò là kho chứa dữ liệu thô, gần nguyên bản từ nguồn. Mỗi bảng staging (stg.orders, stg.customers, stg.products,...) tương ứng với một bảng trong nguồn, bổ sung thêm các cột kỹ thuật như load_date, source_system để phục vụ audit và truy vết.

4.3. Tầng Data Warehouse (DW)

Tầng DW được thiết kế theo mô hình Galaxy Schema với các bảng Fact và Dimension.

Các bảng Dimension chính:

• dim.Date: Chứa thông tin ngày tháng, thứ, quý, năm...
• dim.Location: Thông tin địa lý (mã ZIP, thành phố, bang, tọa độ).
• dim.Customer: Thông tin khách hàng với quản lý SCD Type 2.
• dim.Seller: Thông tin người bán với quản lý SCD Type 2.
• dim.Product: Thông tin sản phẩm.
• dim.Payment: Thông tin loại hình thanh toán.
• dim.Order_Status: Các trạng thái của đơn hàng.

Các bảng Fact chính:

• fact.Order_Items: Chi tiết từng dòng sản phẩm trong đơn hàng.
• fact.Financial_Performance: Tổng hợp hiệu suất tài chính (doanh thu, số đơn, AOV...).
• fact.Operations: Tổng hợp hiệu suất vận hành (thời gian xử lý, giao hàng...).

Bảng Dimension (Bảng Chiều)

Dim_Date

Lưu trữ thông tin lịch và thời gian để phân tích theo ngày, tuần, tháng, quý, năm.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
date_key	Int	Khóa thay thế (Surrogate Key) theo định dạng yyyymmdd.
full_date	Date	Ngày đầy đủ (YYYY-MM-DD).
day_of_week	Int	Thứ trong tuần (1 = Chủ nhật, 2 = Thứ hai, ..., 7 = Thứ bảy).
day_name	Varchar(20)	Tên ngày trong tuần (Monday, Tuesday,... hoặc được nội địa hóa).
month	Int	Tháng (1–12).
month_name	Varchar(20)	Tên tháng (January, February,...).
quarter	Int	Quý (1–4).
year	Int	Năm.
is_weekend	Boolean	Cờ chỉ ngày cuối tuần (True/False).
is_holiday	Boolean	Cờ chỉ ngày nghỉ/lễ (nếu có lịch nghỉ được xác định).

Dim_Location

Lưu trữ thông tin địa lý về thành phố và bang, liên kết với thông tin từ khách hàng và người bán.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
location_key	Int	Khóa thay thế (Surrogate Key).
zip_code_prefix	Varchar(10)	Mã ZIP prefix.
city	Varchar(100)	Thành phố.
state	Varchar(3)	Bang/tỉnh (viết tắt, ví dụ: SP, RJ).
latitude	Decimal(10,8)	Vĩ độ.
longitude	Decimal(10,8)	Kinh độ.

Dim_Customer

Lưu trữ thông tin khách hàng với quản lý lịch sử thay đổi (SCD Type 2).

Tên Cột	Kiểu Dữ Liệu	Mô Tả
customer_key	Bigint	Khóa thay thế (Surrogate Key).
customer_id	Varchar(100)	Khóa tự nhiên từ nguồn (customer_id).
customer_unique_id	Varchar(100)	ID duy nhất thực sự của khách hàng.
location_key	Int	Liên kết với bảng dim.Location.
effective_date	Datetime	Ngày hiệu lực của bản ghi.
end_date	Datetime (Nullable)	Ngày kết thúc hiệu lực của bản ghi. NULL nếu là bản ghi hiện hành.
is_current	Boolean	Cờ chỉ bản ghi hiện hành (True/False).

Dim_Seller

Lưu trữ thông tin người bán với quản lý lịch sử thay đổi (SCD Type 2).

Tên Cột	Kiểu Dữ Liệu	Mô Tả
seller_key	Bigint	Khóa thay thế (Surrogate Key).
seller_id	Varchar(100)	Khóa tự nhiên từ nguồn (seller_id).
location_key	Int	Liên kết với bảng dim.Location.
effective_date	Datetime	Ngày hiệu lực của bản ghi.
end_date	Datetime (Nullable)	Ngày kết thúc hiệu lực của bản ghi. NULL nếu là bản ghi hiện hành.
is_current	Boolean	Cờ chỉ bản ghi hiện hành (True/False).

Dim_Product

Lưu trữ thông tin sản phẩm. Sử dụng SCD Type 1 (cập nhật tại chỗ).

Tên Cột	Kiểu Dữ Liệu	Mô Tả
product_key	Int	Khóa thay thế (Surrogate Key).
product_id	Varchar(100)	Khóa tự nhiên từ nguồn (product_id).
product_category_name	Varchar(100)	Danh mục sản phẩm bằng tiếng Bồ Đào Nha.
product_category_name_english	Varchar(100)	Danh mục sản phẩm bằng tiếng Anh.
product_weight_g	Int (Nullable)	Khối lượng sản phẩm (gram).
product_length_cm	Decimal(10,2) (Nullable)	Chiều dài sản phẩm (cm).
product_height_cm	Decimal(10,2) (Nullable)	Chiều cao sản phẩm (cm).
product_width_cm	Decimal(10,2) (Nullable)	Chiều rộng sản phẩm (cm).
product_photos_qty	Int (Nullable)	Số lượng ảnh của sản phẩm.

Dim_Payment

Lưu trữ thông tin về loại hình thanh toán.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
payment_key	Int	Khóa thay thế (Surrogate Key).
payment_type	Varchar(50)	Loại thanh toán (credit_card, boleto, voucher, debit_card).
payment_category	Varchar(50)	Nhóm thanh toán tổng quát hơn (credit_card, boleto, others).
installment_enabled	Boolean	Có hỗ trợ trả góp (True/False).

Dim_Order_Status

Lưu trữ các trạng thái của đơn hàng để phân loại và phân tích.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
status_key	Int	Khóa thay thế (Surrogate Key).
status_name	Varchar(50)	Tên trạng thái (delivered, shipped, canceled, unavailable,...).
is_completed	Boolean	Cờ chỉ đơn hàng đã hoàn tất (True/False).
is_canceled	Boolean	Cờ chỉ đơn hàng bị hủy (True/False).
is_problematic	Boolean	Cờ chỉ đơn hàng có vấn đề (returned, unavailable, invoiced,...).

Bảng Fact (Bảng Sự Kiện)

Fact_Order_Items

Lưu trữ chi tiết từng sản phẩm trong mỗi đơn hàng (grain: mỗi dòng sản phẩm trong đơn hàng). Đây là bảng fact chi tiết nhất.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
order_item_key	Bigint	Khóa thay thế (Surrogate Key) cho bản ghi fact.
order_id	Varchar(100)	Mã đơn hàng (Degenerate Dimension).
order_item_id	Int	Số thứ tự dòng trong đơn.
product_key	Int	Liên kết với dim.Product.
seller_key	Bigint	Liên kết với dim.Seller.
customer_key	Bigint	Liên kết với dim.Customer.
location_key	Int	Liên kết với dim.Location (thông tin địa lý giao hàng).
status_key	Int	Liên kết với dim.Order_Status.
purchase_date_key	Int	Ngày mua hàng (FK tới dim.Date).
delivered_date_key	Int (Nullable)	Ngày giao hàng thành công (FK tới dim.Date).
primary_payment_type_key	Int	Liên kết với dim.Payment cho loại thanh toán chính của đơn.
price	Decimal(10,2)	Giá bán của dòng sản phẩm.
freight_value	Decimal(10,2)	Phí vận chuyển cho dòng sản phẩm.
total_value	Decimal(10,2)	Tổng giá trị (price + freight_value).
review_score	Int (Nullable)	Điểm đánh giá (1-5) liên quan đến đơn hàng chứa sản phẩm này.

Fact_Financial_Performance

Lưu trữ các số liệu tổng hợp tài chính theo ngày, người bán và sản phẩm.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
date_key	Int	Ngày thống kê (FK tới dim.Date).
seller_key	Bigint	Người bán (FK tới dim.Seller).
product_key	Int	Sản phẩm (FK tới dim.Product).
location_key	Int	Khu vực (FK tới dim.Location).
total_orders	Int	Tổng số đơn hàng trong ngày.
total_items_sold	Int	Tổng số lượng sản phẩm bán ra trong ngày.
total_revenue	Decimal(12,2)	Tổng doanh thu trong ngày.
total_freight	Decimal(12,2)	Tổng phí vận chuyển trong ngày.
average_order_value	Decimal(10,2)	AOV = total_revenue / total_orders.

Fact_Operations

Lưu trữ các số liệu tổng hợp hiệu suất vận hành theo ngày và khu vực.

Tên Cột	Kiểu Dữ Liệu	Mô Tả
date_key	Int	Ngày thống kê (FK tới dim.Date).
seller_key	Bigint	Người bán (FK tới dim.Seller).
location_key	Int	Khu vực vận hành (FK tới dim.Location).
orders_processed	Int	Số đơn hàng được xử lý trong ngày.
avg_processing_time_days	Decimal(5,2)	Thời gian xử lý trung bình (từ duyệt đến giao cho vận chuyển).
avg_delivery_time_days	Decimal(5,2)	Thời gian giao hàng trung bình (từ giao cho vận chuyển đến giao thành công).
on_time_delivery_rate	Decimal(5,2)	Tỷ lệ giao hàng đúng hẹn (%).

5. Quy Trình ETL

Quy trình ETL (Extract, Transform, Load) là trái tim của hệ thống Data Warehouse, đảm bảo dữ liệu từ nguồn được thu thập, xử lý và tải vào kho dữ liệu một cách chính xác, hiệu quả và có thể lặp lại. Hệ thống hỗ trợ hai quy trình chính: Full Load và Incremental Load. Việc lựa chọn quy trình nào được điều khiển bởi hệ thống điều phối (SQL Server Agent) dựa trên cấu hình hoặc trạng thái lần chạy trước.

Full Load

Mục đích: Tải toàn bộ dữ liệu từ các bảng nguồn vào Data Warehouse. Thường được sử dụng cho lần chạy đầu tiên hoặc khi cần làm mới hoàn toàn dữ liệu.

Các bước chi tiết:

Bước 1: Extract từ Source

1. Kết nối: Thiết lập kết nối đến hệ thống nguồn (EcommerceDB).
2. Đọc dữ liệu: Đọc toàn bộ dữ liệu từ từng bảng nguồn (olist_orders, olist_customers,...) với mức độ cô lập giao dịch phù hợp để đảm bảo tính nhất quán trong quá trình đọc.
3. Lưu trữ tạm thời: Dữ liệu được đọc vào các bảng tạm, vùng nhớ đệm tạm thời.
4. Ghi log Extract: Ghi lại số lượng bản ghi đọc được từ từng bảng và có thể ghi checksum để kiểm tra tính toàn vẹn dữ liệu sau khi extract.

Bước 2: Load vào Staging (Truncate & Load)

1. Chuẩn bị: Đối với từng bảng Staging (stg.orders, stg.customers,...):
   • Thực hiện lệnh TRUNCATE TABLE stg.<table_name> để xóa toàn bộ dữ liệu cũ.
2. Insert dữ liệu:
   • Thực hiện lệnh INSERT INTO stg.<table_name> SELECT ... FROM <temporary_data> để nạp toàn bộ dữ liệu đã extract vào bảng Staging tương ứng.
   • Trong quá trình insert, thêm các cột kỹ thuật:
     • load_date: Ghi lại thời điểm dữ liệu được load vào Staging (thường là GETDATE() hoặc CURRENT_TIMESTAMP).
     • source_system: Nhận diện hệ thống nguồn (ví dụ: 'EcommerceDB').
3. Ghi ETL Logs cho Staging: Ghi log chi tiết cho bước này, bao gồm:
   • Tên bảng nguồn và bảng Staging tương ứng.
   • Số lượng bản ghi đầu vào (rows_in).
   • Số lượng bản ghi đầu ra (rows_out). Lý tưởng là rows_in = rows_out.
   • Thời gian bắt đầu, kết thúc và tổng thời gian thực hiện.
   • Trạng thái (SUCCESS/FAILED).
   • ID lô (batch_id) để truy vết.

Bước 3: Transform tại Staging (Chuẩn bị cho Full Load)

1. Chuẩn hóa dữ liệu:
   • Chuyển đổi kiểu dữ liệu nếu cần (ví dụ: từ VARCHAR sang DATE).
   • Chuẩn hóa giá trị NULL, xóa khoảng trắng thừa.
   • Ánh xạ các giá trị tham chiếu (ví dụ: ánh xạ payment_type từ credit sang credit_card nếu cần thiết cho thống nhất).
2. Tính toán các trường phụ trợ:
   • Tính các giá trị cần thiết để map với Dimension trong DW, ví dụ như customer_unique_id để chuẩn bị cho việc tìm customer_key.
   • Chuẩn bị các cột có thể dùng để join với các bảng Dimension (ví dụ: customer_id, product_id, order_purchase_date...).

Bước 4: Load vào Dimension trong DW (Full Load)

1. Xử lý các Dimension Tĩnh/Ít Thay Đổi:
   • Load các bảng Dimension không thay đổi theo thời gian hoặc thay đổi rất ít như dim.Payment, dim.Order_Status, dim.Date.
   • Đối với dim.Date: Được tạo sẵn bằng script trước khi ETL chạy.
   • Đối với dim.Payment và dim.Order_Status: Sử dụng TRUNCATE và INSERT toàn bộ dữ liệu đã được chuẩn hóa từ Staging hoặc định nghĩa sẵn.
2. Xử lý các Dimension Thay Đổi (SCD - Slowly Changing Dimensions):
   • SCD Type 1 (Cập nhật tại chỗ): Áp dụng cho dim.Product, dim.Location.
     • TRUNCATE bảng Dimension.
     • INSERT toàn bộ dữ liệu mới đã transform từ Staging.
   • SCD Type 2 (Lưu lịch sử): Áp dụng cho dim.Customer, dim.Seller.
     • TRUNCATE bảng Dimension (vì đây là Full Load, nên lịch sử cũ không cần giữ lại).
     • INSERT toàn bộ dữ liệu mới đã transform từ Staging, tạo customer_key/seller_key mới, effective_date là ngày chạy ETL, end_date là NULL, is_current là 1.

Bước 5: Load vào Fact trong DW (Full Load)

1. Chuẩn bị:
   • Đối với từng bảng Fact (fact.Order_Items, fact.Financial_Performance, fact.Operations):
     • Thực hiện lệnh TRUNCATE TABLE fact.<table_name> để xóa toàn bộ dữ liệu cũ.
2. Map Surrogate Keys và Tính Measures:
   • Sử dụng các câu lệnh INSERT INTO ... SELECT ... FROM stg.<table> s JOIN dim.<dimension> d ON ... để join dữ liệu từ Staging với các Dimension và lấy Surrogate Key tương ứng (ví dụ: customer_key, product_key, date_key).
   • Đồng thời, tính toán các measure (số đo) cần thiết:
     • Ví dụ trong fact.Order_Items: total_value = s.price + s.freight_value.
     • Ví dụ trong fact.Financial_Performance: total_revenue = SUM(s.price), total_orders = COUNT(DISTINCT s.order_id).
3. Insert dữ liệu:
   • Thực hiện INSERT INTO fact.<table_name> SELECT ... với đầy đủ Surrogate Keys và Measures đã tính toán.

Bước 6: Ghi ETL Logs cho DW (Full Load)

1. Ghi log chi tiết cho từng bước Load Dimension và Load Fact:
   • Tên bảng Fact/Dimension.
   • Số lượng bản ghi được INSERT hoặc UPDATE.
   • Thời gian bắt đầu, kết thúc và tổng thời gian thực hiện.
   • Trạng thái (SUCCESS/FAILED).
   • ID lô (batch_id).
2. Cập nhật Bảng Điều Khiển (etl.Control):
   • Ghi lại LastRunEndTime (thời điểm kết thúc lần chạy).
   • Ghi lại Status là 'SUCCESS' nếu toàn bộ quy trình thành công.
   • Ghi lại HighWatermarkDateTime (có thể là ngày/giờ lớn nhất trong dữ liệu nguồn được xử lý, ví dụ: MAX(order_purchase_timestamp)).

---

Incremental Load

Mục đích: Chỉ tải những phần dữ liệu mới hoặc đã thay đổi từ nguồn kể từ lần chạy ETL trước đó. Đây là quy trình chạy định kỳ hàng ngày để cập nhật DW với dữ liệu mới nhất.

Các bước chi tiết:

Bước 1: Check CDC / Xác Định Delta ở Source

1. Kết nối: Thiết lập kết nối đến hệ thống nguồn.
2. Xác định Watermark/Giới hạn: Truy vấn bảng điều khiển etl.Control để lấy LastHighWatermarkDateTime (LSN khi dùng CDC) từ lần chạy trước.
3. Xác định dữ liệu mới/thay đổi: Truy vấn các bảng nguồn để tìm các bản ghi có timestamp (ví dụ: order_purchase_timestamp, review_creation_date) lớn hơn LastHighWatermarkDateTime.

Bước 2: Extract (Source → Tables + Logs)

1. Kết nối và Truy vấn Delta: Thực hiện các truy vấn SELECT từ các bảng nguồn với điều kiện WHERE <timestamp_column> > @LastHighWatermarkDateTime.
2. Lưu trữ tạm thời: Dữ liệu delta được lưu vào các bảng tạm hoặc vùng nhớ đệm.
3. Ghi log Extract Delta: Ghi log số lượng bản ghi delta được trích xuất từ từng bảng nguồn và cửa sổ thời gian (FROM: @LastHighWatermarkDateTime TO: @CurrentRunStartTime).

Bước 3: Staging (Truncate & Incremental Load)

1. Làm sạch Bảng Staging Làm Việc:
   • Mỗi lần chạy Incremental, TRUNCATE bảng Staging chính được dùng cho Incremental.
2. Load Delta vào Staging:
   • INSERT dữ liệu delta từ vùng tạm thời vào các bảng Staging tạm thời.
   • Thêm cột kỹ thuật load_date và source_system.

Bước 4: Transform tại Staging (Chuẩn bị cho Incremental Load)

1. Chuẩn hóa dữ liệu delta: Tương tự như bước Transform trong Full Load, nhưng chỉ áp dụng cho dữ liệu delta.
2. Tính toán trường phụ trợ: Tính các trường cần thiết để map với Dimension.

Bước 5: Incremental Load → Data Warehouse

5.1. Load Dimensions (Xử lý SCD)

1. SCD Type 1 (Cập nhật tại chỗ): Áp dụng cho dim.Product, dim.Location, dim.Payment, dim.Order_Status.

   • Sử dụng MERGE hoặc kết hợp UPDATE và INSERT để:
     • Cập nhật các bản ghi đã tồn tại nếu có sự thay đổi trong dữ liệu nguồn.
     • Chèn các bản ghi hoàn toàn mới.
   • Không thay đổi Surrogate Key của bản ghi đã tồn tại.

2. SCD Type 2 (Lưu lịch sử): Áp dụng cho dim.Customer, dim.Seller.

   • Với mỗi bản ghi delta từ Staging:
     • Kiểm tra xem có tồn tại bản ghi is_current = 1 trong Dimension có cùng Natural Key (customer_id, seller_id) hay không.
     • Nếu có và dữ liệu thay đổi:
       • Đóng bản ghi cũ: UPDATE bản ghi is_current = 1 thành is_current = 0 và đặt end_date = @CurrentRunStartTime.
       • Mở bản ghi mới: INSERT một bản ghi mới với customer_key/seller_key mới, dữ liệu mới, effective_date = @CurrentRunStartTime, end_date = NULL, is_current = 1.
     • Nếu không tồn tại bản ghi nào hoặc không có thay đổi: Chỉ cần INSERT nếu là bản ghi hoàn toàn mới.

5.2. Load Facts

1. Xác định phạm vi ảnh hưởng: Dữ liệu Fact thường bị ảnh hưởng bởi các đơn hàng mới hoặc cập nhật trạng thái đơn hàng trong khoảng thời gian delta.
2. Map Surrogate Keys: Join dữ liệu delta từ Staging với các Dimension (lưu ý phải lấy Surrogate Key đúng theo effective_date đối với SCD Type 2).
3. Xử lý dữ liệu Fact:
   • Chèn bản ghi mới: Nếu là đơn hàng mới hoặc dòng sản phẩm mới, thực hiện INSERT vào bảng Fact tương ứng.
   • Cập nhật bản ghi hiện có (nếu cần): Một số Fact như fact.Financial_Performance có thể yêu cầu cập nhật/cộng dồn số liệu cho ngày đã tồn tại. Sử dụng MERGE hoặc UPDATE.
   • Lưu ý: Việc cập nhật Fact có thể phức tạp, tùy theo logic kinh doanh. Trong nhiều trường hợp, ETL chỉ chèn mới và để các báo cáo tổng hợp xử lý việc cộng dồn.

Bước 6: ETL Logs (DW - Incremental Load)

1. Ghi log chi tiết cho từng bước Incremental Load Dimension và Fact:
   • Tên bảng.
   • Số lượng bản ghi được INSERT, UPDATE, DELETE.
   • Thời gian thực hiện.
   • Trạng thái.
   • ID lô.
2. Cập nhật Bảng Điều Khiển (etl.Control):
   • Chỉ cập nhật LastHighWatermarkDateTime nếu toàn bộ quy trình Incremental Load thành công. Đây là điểm rất quan trọng để đảm bảo tính toàn vẹn dữ liệu và khả năng re-run an toàn.
   • LastRunEndTime, Status cũng được cập nhật tương ứng.
   • Nếu quy trình thất bại, LastHighWatermarkDateTime giữ nguyên, cho phép quy trình được re-run từ điểm checkpoint trước mà không bị thiếu dữ liệu.

---

Tổng Kết Quy Trình

• Full Load: Là quy trình làm mới hoàn toàn dữ liệu, thường dùng khi khởi tạo hệ thống hoặc khi cần đảm bảo tính nhất quán tuyệt đối. Nó đơn giản về mặt logic nhưng tốn tài nguyên.
• Incremental Load: Là quy trình hiệu quả hơn cho việc cập nhật định kỳ. Nó yêu cầu logic xử lý phức tạp hơn, đặc biệt là với SCD, nhưng giúp tiết kiệm tài nguyên và thời gian.
• Logging và Điều Khiển: Việc ghi log chi tiết và duy trì bảng điều khiển là yếu tố then chốt để theo dõi, gỡ lỗi, đảm bảo chất lượng và quản lý quy trình ETL một cách tự động và an toàn.

6. Báo Cáo & Phân Tích

Hệ thống cung cấp hai dashboard chính được xây dựng trên Power BI:

1. Sales Performance Dashboard

• Mục tiêu: Theo dõi hiệu quả kinh doanh tổng thể. \n
• KPI: Total Revenue, Total Orders, Average Order Value (AOV), Cancellation Rate.
• Phân tích: Biểu đồ xu hướng doanh thu/số đơn, doanh thu theo danh mục và theo bang, giúp xác định mùa vụ, sản phẩm chủ lực và thị trường trọng điểm.

2. Operations Dashboard:
   • Mục tiêu: Đánh giá hiệu quả hoạt động vận chuyển và giao nhận.
   • KPI: Average Delivery Days, Average Review Score, Average Freight Cost.
   • Phân tích: Thời gian giao trung bình và chi phí vận chuyển theo bang, giúp xác định khu vực cần cải thiện và tối ưu tuyến đường, đối tác vận chuyển.

7. Kết Luận

Dự án Ecommerce Data Warehouse on-premises đã thiết lập một nền tảng dữ liệu vững chắc, tự động hóa quy trình xử lý dữ liệu và cung cấp các báo cáo trực quan, hỗ trợ đắc lực cho hoạt động kinh doanh. Việc áp dụng kiến trúc rõ ràng, quy trình ETL hiệu quả và mô hình dữ liệu chuẩn hóa đảm bảo hệ thống có thể vận hành ổn định và dễ dàng mở rộng trong tương lai.