THIẾT KẾ HỆ THỐNG DATA WAREHOUSE ON-PREMISES

I. Giới thiệu bộ dữ liệu

    Bộ dữ liệu YouTube thu thập các thông tin liên quan đến xu hướng (trending) của video trên nền tảng YouTube tại các thời điểm cụ thể. Mỗi dòng dữ liệu đại diện cho một video xuất hiện trên bảng xếp hạng thịnh hành vào một ngày cụ thể.

    Link tham khảo bộ dữ liệu ở đây

    Các trường dữ liệu bao gồm:

II. Mục tiêu phân tích

Bộ dữ liệu hướng tới việc hỗ trợ phân tích và trả lời các câu hỏi như:

• Video nào có hiệu suất tốt nhất theo từng quốc gia, danh mục?
• Xu hướng tăng trưởng của các kênh theo thời gian?
• So sánh độ phổ biến giữa các quốc gia, thể loại nội dung?
• Phân tích vòng đời và hiệu suất của kênh từ thời điểm tạo đến hiện tại
• Dự báo độ phổ biến của video/kênh dựa trên lịch sử

III. Nhận xét về bộ dữ liệu

a. Tổng quan về bộ dữ liệu

*Tổng số bản ghi: 55884

*Tổng số bản ghi có giá trị NULL: 0

*Tổng số bản ghi có giá trị NAN: 0

*Tổng số bản ghi trùng nhau: 0

*Tổng số giá trị khác nhau của các trường:

b. Nhận xét về bộ dữ liệu

• Cùng một video_id nhưng lại có title khác nhau
• channel_id không map đúng với channel_title
• Cùng một video_id nhưng thuộc về nhiều channel_id
• Một channel_id lại thuộc nhiều country_code
• snapshot_date nhỏ hơn channel_date
• Nên chuẩn hóa các title, channel_title về chữ thường hết
• Định dạng ngày đang là String
• Có nhiều bản ghi trùng về channel_id, video_id và snapshot_date 

c. Clean Data

• Chuẩn hóa định dạng ngày từ string sang date
• Chuẩn hóa các title, channel_title thành chữ thường
• Map channel_title với channel_id
• Map video_id với channel_id
• Map country_code với channel_id
• Xóa bỏ dữ liệu bị trùng lặp ở channel_id, video_id và snapshot_date
• Tính lại snapshot_date khi snapshot_date nhỏ hơn channel_date
• Tạo thêm các cột publish_date_key, date_key_trending, date_key_snapshot, channel_date_key để làm các khóa liên kết tới dim_date
• Tạo thêm cột channel_age_days tính toán số ngày channel tới ngày snapshot

IV. Kiến trúc Data Warehouse (DWH)

      Giải pháp on-premises data warehouse sử dụng SQL Server là một lựa chọn phổ biến cho các doanh nghiệp mong muốn lưu trữ và phân tích dữ liệu trong môi trường nội bộ. Giải pháp này giúp doanh nghiệp quản lý và phân tích dữ liệu một cách hiệu quả bằng cách sử dụng nền tảng mạnh mẽ của SQL Server do Microsoft cung cấp. 

Quy trình xây dựng DWH tuân theo nguyên tắc ETL và kiến trúc staging → dimension/fact, cụ thể:

a. Công nghệ sử dụng

• Ngôn ngữ lập trình: Python
• Database: PostgreSQL
• Orchestration: Apache Airflow
• Container: Docker

b. Giai đoạn Staging

• Dữ liệu được đọc từ file CSV (ví dụ: youtube.csv) và nạp vào bảng staging.raw_data.
• Các xử lý tại bước này:
  • Làm sạch dữ liệu (chuyển đổi định dạng ngày, chuẩn hóa chữ thường, xử lý null…)
  • Chuẩn hóa khóa chính như channel_id, video_id, country_code
  • Sinh thêm các date_key (dạng YYYYMMDD) để phục vụ việc phân tích theo thời gian
  • Gán snapshot_date là ngày hệ thống ghi nhận dữ liệu

b. Giai đoạn Dimension & Fact

Sau khi staging xong, dữ liệu được phân tách thành các bảng:

1. Dimension Tables:

• dim_channel: Thông tin kênh YouTube, có xử lý SCD Type 2 để lưu vết thay đổi (ví dụ đổi tên kênh, thay đổi quốc gia, …)
• dim_country: Danh sách quốc gia, có xử lý theo SCD Type 1
• dim_date: Bảng thời gian chuẩn để join theo ngày
• dim_video: Thông tin video, có xử lý theo SCD Type 1
• dim_category: Thông tin về các loại hình video, có xử lý theo SCD Type 1

2. Fact Tables:

• fact_video_performance: Thống kê hiệu suất video theo từng ngày trending
• fact_channel_daily: Ảnh chụp thông tin kênh tại từng snapshot_date (tổng view, subs, video,…)

3. Sơ đồ Diagram

4. Chi tiết kiến trúc các bảng

4.a. Bảng staging.raw_data

4.b. Bảng dwh.dim_date

4.c. Bảng dwh.dim_country

4.d. Bảng dwh.dim_channel

4.e. Bảng dwh.dim_category

4.f. Bảng dwh.dim_video

4.g. Bảng dwh.fact_video_performance

4.h. Bảng dwh.fact_channel_daily

c. Quy trình ETL

1. Full load

Các script python được viết để chạy full load:

• connect_db.py: Dùng để kết nối tới Database
• cleanData_raw.py: Dùng làm sạch dữ liệu cũ ban đầu csv
• full_load_data.py: Dùng để đẩy dữ liệu vào Database các bảng dim, fact
• full_load.sql: File sql chứa các câu truy vấn để đẩy dữ liệu vào Database
• date_data.py: Tạo dữ liệu ngày tháng để đẩy vào dim_date

2. Incremental load

Các script python được viết để chạy incremental load:

• cleanData.py: Dùng để làm sạch dữ liệu mới
• incremental_load_data.py: Dùng để đẩy dữ liệu vào Database các bảng dim, fact
• incremental_load.sql: File sql chứa các câu truy vấn để đẩy dữ liệu vào

3. Lập lịch chạy tự động

Các bước và script pythond được viết để chạy lập lịch:

• etl.python: Dùng để chạy làm sạch dữ liệu với và Incremental load data
• Tạo một connections trong Airflow để có thể kết nối tới postgres:
  • Host: postgres
  • Login: airflow
  • Password: airflow
  • Port: 5432

V. Hướng dẫn sử dụng chương trình

a. Thư mục dags

Đây là nơi chứa các script python để airflow chạy tự động bao gồm:

• clean_data: chứa các script python để làm sạch dữ liệu raw và dữ liệu mới để thêm vào staging
• data: chứa dữ liệu
• sql: chứa các file sql để full load và incremental load, tạo bảng và kết nối tới database
• Các script etl.py, full_load_data.py và incremental_load_data.py để thực thi quy trình ETL

b. Thư mục script

Đây là nơi chứa file entrypoint.sh dùng để tải các thư viện tạo database cho airflow trước khi bắt đầu

c. File .env

Chứa các biến môi trường liên quan tới kết nối databse

d. Các file docker-compose.yml và dockerfile

Là các file dùng để tạo các container

e. File init.sql

Là file để tạo các bảng khi khởi động database lần đầu tiên

f. File requirements.txt

Các thư viện cần thiết cho airflow

VI. Trực quan hóa dữ liệu với Power BI

a. Ý tưởng

    Trong Power BI tạo thêm 2 bảng fact là fact_channel_monthly và fact_channel_yearly từ bảng fact_channel_daily để dễ dàng phân tích theo tháng và năm.

b. Phân tích hiệu suất channel

c. Phân tích hiệu suất video