Data Warehouse cho Doanh nghiệp Mỹ phẩm & Làm đẹp

Tóm tắt nhanh

Tổng quan dự án

Môn học: Cơ sở dữ liệu nâng cao
Công cụ / phương pháp: Data Warehouse · SSIS · SSAS · MDX · Power BI · OLAP

Vấn đề: khi dữ liệu doanh nghiệp nằm rải rác ở nhiều hệ thống khác nhau

Nhóm xây dựng một hệ thống Data Warehouse cho doanh nghiệp kinh doanh mỹ phẩm và dịch vụ làm đẹp tại Hoa Kỳ. Ý tưởng doanh nghiệp do nhóm tự xây dựng, dữ liệu được tạo bằng AI và tiếp tục chỉnh sửa để phù hợp với các nghiệp vụ phân tích thực tế. Bài toán đặt ra: doanh nghiệp quản lý dữ liệu bán hàng, dịch vụ, khách hàng và marketing trên nhiều hệ thống khác nhau, gây khó khăn trong việc tổng hợp và phân tích đa chiều — ban lãnh đạo không thể có một cái nhìn thống nhất để ra quyết định.

Mục tiêu của đồ án là xây dựng một kho dữ liệu hợp nhất, hỗ trợ phân tích doanh thu, hiệu quả chiến dịch marketing, hiệu suất chi nhánh và hành vi khách hàng theo nhiều chiều dữ liệu khác nhau — đi theo đúng chuẩn Microsoft BI Stack: SSIS cho ETL, SSAS cho OLAP Cube, MDX để truy vấn, và Power BI để trực quan hóa.

Vai trò: nghiên cứu kiến trúc và đồng hành cùng quá trình triển khai của nhóm

Do khối lượng công việc giữa các môn học trong học kỳ khá lớn, nhóm phân chia trọng tâm thực hiện giữa các thành viên. Ở project này, vai trò chủ yếu tập trung vào:

Nghiên cứu kiến trúc Data Warehouse và mô hình Constellation Schema
Theo dõi quá trình triển khai của nhóm, hỗ trợ hoàn thiện đồ án
Tìm hiểu quy trình làm việc với Microsoft BI Stack (SSIS, SSAS, MDX, Power BI)

Mặc dù không trực tiếp đảm nhận phần triển khai chính của pipeline, đây là project giúp lần đầu tiếp cận một quy trình Data Warehouse hoàn chỉnh — từ thiết kế mô hình dữ liệu, ETL, OLAP, đến trực quan hóa dữ liệu cuối cùng.

Quá trình: hiểu sự khác biệt giữa cơ sở dữ liệu giao dịch và kho dữ liệu phân tích

Quy trình thực hiện của nhóm đi theo các bước:

Xây dựng bài toán doanh nghiệp và xác định nhu cầu phân tích
Thiết kế mô hình Data Warehouse theo Constellation Schema, với các Fact và Dimension phục vụ phân tích bán hàng, dịch vụ và marketing
Chuẩn bị và tổ chức dữ liệu nguồn
Thực hiện quy trình ETL bằng SQL Server Integration Services (SSIS)
Xây dựng OLAP Cube bằng SQL Server Analysis Services (SSAS)
Thiết lập Hierarchy cho các Dimension
Viết các truy vấn MDX phục vụ phân tích đa chiều
Xây dựng Dashboard trên Power BI Desktop
Hoàn thiện báo cáo và trình bày

Kết quả đạt được

Hoàn thành hệ thống Data Warehouse theo mô hình Constellation Schema
Xây dựng ba nhóm truy vấn MDX phục vụ các nhu cầu phân tích khác nhau
Triển khai Dashboard trực quan trên Power BI
Hiểu được quy trình xây dựng một hệ thống Business Intelligence hoàn chỉnh theo chuẩn Microsoft BI Stack

Bài học lớn nhất

Project giúp hiểu rõ cách tổ chức dữ liệu trong Data Warehouse khác biệt như thế nào so với cơ sở dữ liệu giao dịch (OLTP) — kho dữ liệu được tối ưu cho việc đọc và phân tích đa chiều, trong khi cơ sở dữ liệu giao dịch tối ưu cho việc ghi và xử lý nghiệp vụ hằng ngày. Quy trình ETL và vai trò của SSIS trong việc trích xuất, biến đổi, nạp dữ liệu cũng là một khái niệm được hiểu sâu hơn, cùng với khái niệm Cube và OLAP trong phân tích dữ liệu đa chiều, và vai trò của MDX trong việc khai thác các chiều dữ liệu đó.

Hạn chế

Mức độ tham gia triển khai trong project còn hạn chế do nhóm phân chia khối lượng công việc giữa nhiều môn học cùng lúc. Chưa có cơ hội trực tiếp xây dựng toàn bộ pipeline ETL hoặc phát triển Cube từ đầu, và chưa triển khai trên môi trường Cloud hoặc các nền tảng dữ liệu hiện đại hơn.

Nếu làm lại

Trực tiếp xây dựng toàn bộ Data Warehouse thay vì chỉ tham gia nghiên cứu
Thiết kế ETL theo hướng tự động hóa nhiều hơn
Áp dụng Slowly Changing Dimension (SCD) phù hợp cho các Dimension thay đổi theo thời gian
Bổ sung Incremental Load thay vì nạp toàn bộ dữ liệu mỗi lần chạy ETL
Thử nghiệm triển khai trên Microsoft Fabric hoặc Azure Data Factory
Xây dựng Dashboard với các KPI phục vụ ra quyết định thực tế hơn

MDX Analysis Queries

These MDX queries were used to analyze branch revenue, service performance and marketing campaign effectiveness from the OLAP cube.

Query 1 — Branch Revenue & Product Contribution

WITH
    MEMBER [Measures].[Total Branch Revenue] AS
            ([Measures].[Net Revenue],
         [DIM BRANCH].[Branch Name].CurrentMember,
         [DIM TIME].[Day Time Bucket].DefaultMember,
         [DIM PRODUCT].[Product Name].DefaultMember),
        FORMAT_STRING = "Currency"
    MEMBER [Measures].[Product Contribution to Branch] AS
        IIF(
            [Measures].[Total Branch Revenue] = 0,
            NULL,
            [Measures].[Net Revenue] / [Measures].[Total Branch Revenue]
        ),
        FORMAT_STRING = "Percent"
    MEMBER [Measures].[Total Quantity] AS
        SUM(
            [DIM TIME].[Day Time Bucket].[Day Time Bucket].Members,
            [Measures].[Quantity]
        ),
        FORMAT_STRING = "#,##0"
    MEMBER [Measures].[Discount Impact on Quantity] AS
        IIF(
            [Measures].[Discount Percent] = 0,
            NULL,
            [Measures].[Total Quantity] / [Measures].[Discount Percent]
        ),
        FORMAT_STRING = "#,##0.00"
SELECT
    NON EMPTY {
        [Measures].[Net Revenue],
        [Measures].[Total Quantity],
        [Measures].[Discount Percent],
        [Measures].[Total Branch Revenue],
        [Measures].[Product Contribution to Branch],
        [Measures].[Discount Impact on Quantity]
    } ON COLUMNS,
    NON EMPTY (
        NONEMPTY(
            GENERATE(
                NONEMPTY(
                    TOPCOUNT(
                        [DIM BRANCH].[Branch Name].[Branch Name].Members,
                        10,
                        [Measures].[Net Revenue]
                    ),
                    [Measures].[Net Revenue]
                ),
                CROSSJOIN(
                    [DIM BRANCH].[Branch Name].CurrentMember,
                    CROSSJOIN(
                        NONEMPTY(
                            TOPCOUNT(
                                [DIM PRODUCT].[Product Name].[Product Name].Members,
                                1,
                                [Measures].[Net Revenue]
                            ),
                            [Measures].[Net Revenue]
                        ),
                        CROSSJOIN(
                            NONEMPTY(
                                TOPCOUNT(
                                    [DIM TIME].[Day Time Bucket].[Day Time Bucket].Members,
                                    1,
                                        ([Measures].[Net Revenue],
                     [DIM BRANCH].[Branch Name].CurrentMember,
                    [DIM PRODUCT].[Product Name].CurrentMember)
                                ),
                                [Measures].[Net Revenue]
                            ),
                            [DIM DATE].[Quarter].[Quarter].Members
                        )
                    )
                )
            ),
            [Measures].[Net Revenue]
        )
    ) ON ROWS
FROM [CSDLNC_COSMETIC]
WHERE (
    [DIM DATE].[Year].[2023]
)

Query 2 — Service Cost & Customer Usage

WITH
    MEMBER [Measures].[Total Branch Service Cost] AS
            ([Measures].[Service Cost],
         [DIM BRANCH].[Branch Name].CurrentMember,
         [DIM TIME].[Day Time Bucket].DefaultMember,
         [DIM SERVICE TYPE].[Type Name].DefaultMember),
        FORMAT_STRING = "Currency"
    MEMBER [Measures].[Service Contribution to Branch] AS
        IIF(
            [Measures].[Total Branch Service Cost] = 0,
            NULL,
            [Measures].[Service Cost] / [Measures].[Total Branch Service Cost]
        ),
        FORMAT_STRING = "Percent"
    MEMBER [Measures].[Total Customer Usage] AS
        COUNT(
            NONEMPTY(
                [DIM CUSTOMER].[Customer ID].[Customer ID].Members,
                [Measures].[Service Cost]
            )
        ),
        FORMAT_STRING = "#,##0"
    MEMBER [Measures].[Service Cost per Customer] AS
        IIF(
            [Measures].[Total Customer Usage] = 0,
            NULL,
            [Measures].[Service Cost] / [Measures].[Total Customer Usage]
        ),
        FORMAT_STRING = "Currency"
SELECT
    NON EMPTY {
        [Measures].[Service Cost],
        [Measures].[Total Customer Usage],
        [Measures].[Total Branch Service Cost],
        [Measures].[Service Contribution to Branch],
        [Measures].[Service Cost per Customer]
    } ON COLUMNS,
    NON EMPTY (
        GENERATE(
            TOPCOUNT(
                [DIM BRANCH].[Branch Name].[Branch Name].Members,
                10,
                [Measures].[Service Cost]
            ),
            CROSSJOIN(
                [DIM BRANCH].[Branch Name].CurrentMember,
                CROSSJOIN(
                    TOPCOUNT(
                        [DIM SERVICE TYPE].[Type Name].[Type Name].Members,
                        1,
                        [Measures].[Service Cost]
                    ),
                    TOPCOUNT(
                        [DIM TIME].[Day Time Bucket].[Day Time Bucket].Members,
                        1,
                        ([Measures].[Service Cost],
            [DIM BRANCH].[Branch Name].CurrentMember,
            [DIM SERVICE TYPE].[Type Name].CurrentMember)
                    )
                )
            )
        )
    ) ON ROWS
FROM [CSDLNC_COSMETIC]
WHERE (
    [DIM DATE].[Year].[2023]
)

Query 3 — Promotion ROI & Revenue per Unit Sold

WITH
    MEMBER [Measures].[Total Units Sold Across Months] AS
        SUM(
            NONEMPTY(
                [DIM DATE].[Month].[Month].Members,
                [Measures].[Unit Sold]
            ),
            [Measures].[Unit Sold]
        ),
        FORMAT_STRING = "#,##0"
    MEMBER [Measures].[Promotion ROI] AS
        IIF(
            [Measures].[Promotion Cost] = 0,
            NULL,
            ([Measures].[Promotion Revenue] - [Measures].[Promotion Cost]) / [Measures].[Promotion Cost] * 100
        ),
        FORMAT_STRING = "#,##0.00"
    MEMBER [Measures].[Revenue per Unit Sold] AS
        IIF(
            [Measures].[Unit Sold] = 0,
            NULL,
            [Measures].[Promotion Revenue] / [Measures].[Unit Sold]
        ),
        FORMAT_STRING = "Currency"
SELECT
    NON EMPTY {
        [Measures].[Promotion Revenue],
        [Measures].[Unit Sold],
        [Measures].[Promotion Cost],
        [Measures].[Total Units Sold Across Months],
        [Measures].[Promotion ROI],
        [Measures].[Revenue per Unit Sold]
    } ON COLUMNS,
    NON EMPTY (
        NONEMPTY(
            GENERATE(
                NONEMPTY(
                    TOPCOUNT(
                        [DIM MARKETING CAMPAIGN].[Campaign Name].[Campaign Name].Members,
                       10,
                        [Measures].[Promotion Revenue]
                    ),
                    [Measures].[Promotion Revenue]
                ),
                CROSSJOIN(
                    {[DIM MARKETING CAMPAIGN].[Campaign Name].CurrentMember},
                    CROSSJOIN(
                        NONEMPTY(
                            TOPCOUNT(
                                [DIM PRODUCT].[Product Name].[Product Name].Members,
                                1,
                                [Measures].[Unit Sold]
                            ),
                            [Measures].[Unit Sold]
                        ),
                        NONEMPTY(
                            TOPCOUNT(
                                [DIM DATE].[Month].[Month].Members,
                                1,
                                [Measures].[Promotion Revenue]
                            ),
                            [Measures].[Promotion Revenue]
                        )
                    )
                )
            ),
            [Measures].[Promotion Revenue]
        )
    ) ON ROWS
FROM [CSDLNC_COSMETIC]
WHERE (
    [DIM DATE].[Year].[2023]
)