Data Analytics

Appearance

🛠 Workshop — Xây Dashboard Tương Tác ​

Import cleaned CSV → tạo data model (star schema) → viết DAX measures (Revenue, Growth %, AOV) → xây dashboard tương tác (KPI cards + trend + breakdown + filter) → publish & share. Output: file .pbix/.twbx + link dashboard!

🎯 Mục tiêu workshop ​

Sau khi hoàn thành workshop này, bạn sẽ:

  1. Import data vào Power BI Desktop từ CSV files
  2. Transform data với Power Query — rename, change types, clean
  3. Tạo data model — star schema, relationships, DIM_DATE
  4. Viết DAX measures — Total Revenue, AOV, Growth %, Revenue Share %
  5. Xây dashboard tương tác — KPI cards, line trend, bar breakdown, slicers, cross-filter
  6. Publish & share — Power BI Service hoặc export .pbix

🧰 Yêu cầu ​

Yêu cầuChi tiết
Kiến thứcĐã hoàn thành Buổi 10 (Visualization) + Buổi 11 lý thuyết (Power BI/Tableau)
Công cụPower BI Desktop (free, Windows) HOẶC Tableau Public (free, Win/Mac)
DownloadPower BI: powerbi.microsoft.com/desktop
Input3 CSV files (tạo bằng Python bên dưới)
Thời gian90–120 phút

💡 Naming convention

Đặt tên file: HoTen_Buoi11_Dashboard.pbix (Power BI) hoặc HoTen_Buoi11_Dashboard.twbx (Tableau)

📦 Dataset: E-commerce Sales Data ​

Tạo dataset bằng Python ​

Chạy đoạn Python sau trong Jupyter Notebook để tạo 3 CSV files — đây sẽ là input cho Power BI/Tableau:

python
# Cell 1: Tạo 3 CSV files cho Power BI / Tableau workshop
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

np.random.seed(42)

# ═══════════════════════════════════════════
# TABLE 1: FACT_SALES — 10,000 transactions
# ═══════════════════════════════════════════
n_orders = 10000
start_date = datetime(2024, 1, 1)

# Generate dates with seasonality
dates = []
for i in range(n_orders):
    day_offset = np.random.randint(0, 730)  # 2 years
    d = start_date + timedelta(days=day_offset)
    dates.append(d)

categories = ["Electronics", "Fashion", "Food & Beverage", "Beauty", "Sports"]
cat_weights = [0.25, 0.22, 0.28, 0.15, 0.10]

regions = ["TP.HCM", "Hà Nội", "Đà Nẵng", "Cần Thơ", "Hải Phòng"]
region_weights = [0.35, 0.30, 0.15, 0.12, 0.08]

channels = ["Online", "Offline"]
channel_weights = [0.45, 0.55]

fact_sales = []
for i in range(n_orders):
    cat = np.random.choice(categories, p=cat_weights)

    # Base price per category
    base_price = {
        "Electronics": 3500000,
        "Fashion": 850000,
        "Food & Beverage": 250000,
        "Beauty": 620000,
        "Sports": 1200000
    }[cat]

    quantity = np.random.randint(1, 6)
    revenue = round(base_price * quantity * np.random.uniform(0.7, 1.3))
    cost = round(revenue * np.random.uniform(0.55, 0.75))
    profit = revenue - cost

    fact_sales.append({
        "order_id": f"ORD-{i+1:05d}",
        "order_date": dates[i].strftime("%Y-%m-%d"),
        "product_id": f"P{np.random.randint(1, 51):03d}",
        "customer_id": f"C{np.random.randint(1, 2001):04d}",
        "category": cat,
        "region": np.random.choice(regions, p=region_weights),
        "channel": np.random.choice(channels, p=channel_weights),
        "quantity": quantity,
        "revenue": revenue,
        "cost": cost,
        "profit": profit,
    })

df_sales = pd.DataFrame(fact_sales)

# ═══════════════════════════════════════════
# TABLE 2: DIM_PRODUCT — 50 products
# ═══════════════════════════════════════════
products = []
product_names = {
    "Electronics": ["Laptop", "Phone", "Tablet", "Headphones", "Smartwatch",
                    "Camera", "Speaker", "Monitor", "Keyboard", "Mouse"],
    "Fashion": ["T-Shirt", "Jeans", "Dress", "Jacket", "Sneakers",
                "Handbag", "Sunglasses", "Watch", "Belt", "Scarf"],
    "Food & Beverage": ["Coffee", "Tea", "Snacks", "Juice", "Chocolate",
                        "Rice", "Noodles", "Sauce", "Milk", "Cookies"],
    "Beauty": ["Skincare Set", "Lipstick", "Perfume", "Shampoo", "Sunscreen",
               "Face Mask", "Serum", "Foundation", "Moisturizer", "Eye Cream"],
    "Sports": ["Running Shoes", "Yoga Mat", "Dumbbells", "Tennis Racket", "Swimming Goggles",
               "Basketball", "Football", "Gym Bag", "Water Bottle", "Fitness Tracker"],
}

pid = 1
for cat, names in product_names.items():
    for name in names:
        products.append({
            "product_id": f"P{pid:03d}",
            "product_name": name,
            "category": cat,
            "sub_category": f"{cat} - {name.split()[0]}",
            "unit_price": round(np.random.uniform(100000, 5000000)),
        })
        pid += 1

df_products = pd.DataFrame(products)

# ═══════════════════════════════════════════
# TABLE 3: DIM_CUSTOMER — 2,000 customers
# ═══════════════════════════════════════════
segments = ["Consumer", "Corporate", "Home Office"]
seg_weights = [0.50, 0.30, 0.20]

customers = []
for i in range(2000):
    customers.append({
        "customer_id": f"C{i+1:04d}",
        "customer_name": f"Customer_{i+1}",
        "segment": np.random.choice(segments, p=seg_weights),
        "region": np.random.choice(regions, p=region_weights),
        "join_date": (start_date + timedelta(days=np.random.randint(0, 1095))).strftime("%Y-%m-%d"),
    })

df_customers = pd.DataFrame(customers)

# ═══════════════════════════════════════════
# SAVE TO CSV
# ═══════════════════════════════════════════
df_sales.to_csv("fact_sales.csv", index=False, encoding="utf-8-sig")
df_products.to_csv("dim_product.csv", index=False, encoding="utf-8-sig")
df_customers.to_csv("dim_customer.csv", index=False, encoding="utf-8-sig")

print(f"✅ fact_sales.csv: {len(df_sales):,} rows × {len(df_sales.columns)} columns")
print(f"✅ dim_product.csv: {len(df_products):,} rows × {len(df_products.columns)} columns")
print(f"✅ dim_customer.csv: {len(df_customers):,} rows × {len(df_customers.columns)} columns")
print(f"\n📊 Tổng quan FACT_SALES:")
print(f"   Revenue range: {df_sales['revenue'].min():,.0f} – {df_sales['revenue'].max():,.0f} VND")
print(f"   Date range: {df_sales['order_date'].min()} → {df_sales['order_date'].max()}")
print(f"   Categories: {df_sales['category'].nunique()}")
print(f"   Regions: {df_sales['region'].nunique()}")

Phần 1: Import Data & Power Query ​

Import 3 CSV files vào Power BI, clean & transform trong Power Query

Bước 1.1: Import CSV files ​

Power BI Desktop:
━━━━━━━━━━━━━━━━
1. Mở Power BI Desktop
2. Home → Get Data → Text/CSV
3. Chọn file "fact_sales.csv" → Load
4. Lặp lại cho "dim_product.csv" và "dim_customer.csv"
5. Kết quả: 3 tables trong Fields pane (bên phải)

⚠️ Nếu dùng Tableau

Tableau Desktop / Public:
━━━━━━━━━━━━━━━━━━━━━━━
1. Mở Tableau → Connect → Text File
2. Chọn "fact_sales.csv" → drag vào canvas
3. Click "Add" → chọn "dim_product.csv"
4. Click "Add" → chọn "dim_customer.csv"
5. Tạo joins: fact_sales.product_id = dim_product.product_id
                fact_sales.customer_id = dim_customer.customer_id

Bước 1.2: Power Query Transformations ​

Power BI → Home → Transform Data (mở Power Query Editor):
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Bảng fact_sales:
  1. Kiểm tra Column Quality: View → Column Quality ✓
  2. Change type "order_date" → Date (nếu chưa đúng)
  3. Change type "revenue", "cost", "profit" → Whole Number
  4. Change type "quantity" → Whole Number
  5. Rename: "order_date" → "Order Date", "revenue" → "Revenue"
     (Right-click column → Rename)

Bảng dim_product:
  1. Change type "unit_price" → Whole Number
  2. Rename columns theo PascalCase: "product_name" → "Product Name"

Bảng dim_customer:
  1. Change type "join_date" → Date
  2. Rename columns theo PascalCase

→ Close & Apply (Home → Close & Apply)

Bước 1.3: Tạo DIM_DATE (Date Table) ​

Trong Power BI, tạo Date table bằng DAX — bắt buộc cho time intelligence functions:

dax
// Tạo bảng DIM_DATE — vào Modeling → New Table
DIM_DATE =
    ADDCOLUMNS(
        CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)),
        "Year", YEAR([Date]),
        "Quarter", "Q" & FORMAT([Date], "Q"),
        "Month Number", MONTH([Date]),
        "Month Name", FORMAT([Date], "MMMM"),
        "Month Short", FORMAT([Date], "MMM"),
        "Year-Month", FORMAT([Date], "YYYY-MM"),
        "Day of Week", FORMAT([Date], "dddd"),
        "Week Number", WEEKNUM([Date]),
        "Is Weekend", IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE())
    )

Sau khi tạo: Right-click DIM_DATE → Mark as Date Table → chọn column Date.

Phần 2: Data Model — Relationships ​

Tạo star schema: FACT_SALES ← DIM_PRODUCT, DIM_CUSTOMER, DIM_DATE

Bước 2.1: Tạo Relationships ​

Power BI → Model view (icon bên trái):
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Kéo fact_sales[product_id] → dim_product[product_id]
   → Relationship: Many-to-One, Single direction ✓

2. Kéo fact_sales[customer_id] → dim_customer[customer_id]
   → Relationship: Many-to-One, Single direction ✓

3. Kéo fact_sales[Order Date] → DIM_DATE[Date]
   → Relationship: Many-to-One, Single direction ✓

Kết quả: Star schema với 1 fact + 3 dimensions

Bước 2.2: Verify Model ​

Kiểm tra trong Model View:
━━━━━━━━━━━━━━━━━━━━━━━━
  ✅ 3 relationships: FACT → DIM_PRODUCT, DIM_CUSTOMER, DIM_DATE
  ✅ Tất cả Many-to-One (* ─── 1)
  ✅ Tất cả Single direction (mũi tên 1 chiều: DIM → FACT)
  ✅ Không có bi-directional
  ✅ DIM_DATE marked as Date Table

Bước 2.3: Hide Technical Columns ​

Trong Model view hoặc Data view:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Right-click → Hide in Report View:
  ✅ fact_sales[product_id] — key chỉ dùng cho relationship
  ✅ fact_sales[customer_id] — key chỉ dùng cho relationship
  ✅ fact_sales[order_id] — ID, không cần trong report
  ✅ dim_product[product_id] — key
  ✅ dim_customer[customer_id] — key

Phần 3: DAX Measures ​

Viết 8 DAX measures cho dashboard: Revenue, Orders, AOV, Customers, Growth, Profit Margin, Share %

Bước 3.1: Basic Measures ​

Tạo measures bằng: Home → New Measure, hoặc right-click table → New Measure:

dax
// ═══════════════════════════════════════════
// MEASURE GROUP 1: BASIC AGGREGATIONS
// ═══════════════════════════════════════════

// M1: Total Revenue
Total Revenue = SUM(fact_sales[Revenue])

// M2: Total Orders
Total Orders = COUNTROWS(fact_sales)

// M3: Total Profit
Total Profit = SUM(fact_sales[profit])

// M4: Total Customers (unique)
Total Customers = DISTINCTCOUNT(fact_sales[customer_id])

// M5: Average Order Value (AOV)
AOV = DIVIDE([Total Revenue], [Total Orders], 0)

Bước 3.2: Advanced Measures — Growth & Share ​

dax
// ═══════════════════════════════════════════
// MEASURE GROUP 2: GROWTH & SHARE
// ═══════════════════════════════════════════

// M6: Revenue Previous Year (cần DIM_DATE đúng)
Revenue PY =
    CALCULATE(
        [Total Revenue],
        SAMEPERIODLASTYEAR(DIM_DATE[Date])
    )

// M7: Year-over-Year Growth %
YoY Growth % =
    VAR CurrentRev = [Total Revenue]
    VAR PrevRev = [Revenue PY]
    RETURN
        IF(
            PrevRev = 0,
            BLANK(),
            DIVIDE(CurrentRev - PrevRev, PrevRev, 0)
        )

// M8: Profit Margin %
Profit Margin % = DIVIDE([Total Profit], [Total Revenue], 0)

// M9: Revenue Share % (dùng ALL để tính tổng không bị filter)
Revenue Share % =
    DIVIDE(
        [Total Revenue],
        CALCULATE([Total Revenue], ALL(fact_sales[category])),
        0
    )

💡 Verify measures

Sau khi tạo measures, kéo vào 1 Table visual để verify:

  1. Rows: dim_product[category]
  2. Values: [Total Revenue], [Revenue Share %]
  3. Kiểm tra: Revenue Share % tổng = 100%
  4. Thêm slicer Region → click TP.HCM → Revenue Share % phải thay đổi (dynamic!)

Phần 4: Dashboard — Xây Visuals ​

Tạo dashboard tương tác: KPI cards + line trend + bar breakdown + table + slicers

Bước 4.1: Page Layout ​

Report view → Format → Page size:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Width: 1280
  Height: 720
  Background: White (#FFFFFF)

Layout plan (Z-pattern):
┌────────────────────────────────────────────────┐
│ 🔽 Slicer: Region    🔽 Slicer: Year-Month    │
├──────────┬──────────┬──────────┬───────────────│
│ Revenue  │ Orders   │ AOV      │ YoY Growth    │
│ KPI Card │ KPI Card │ KPI Card │ KPI Card      │
├──────────┴──────────┴──────────┴───────────────│
│ 📈 Revenue Trend (Line)    │ 📊 Revenue by    │
│ Monthly, 24 months         │ Category (Bar)    │
│ (Hero Chart — 50% width)   │ (30% width)       │
├────────────────────────────┴───────────────────│
│ 📋 Top 10 Products (Table)     │ 🍩 Channel    │
│ Product, Revenue, Qty, Margin  │ Split (Donut) │
└────────────────────────────────────────────────┘

Bước 4.2: KPI Cards (Row 1) ​

Tạo 4 KPI Cards:
━━━━━━━━━━━━━━━━
1. Visualizations → Card visual
2. Kéo [Total Revenue] vào Fields
3. Format → Data label → Display units: Millions, Decimal: 1
4. Format → Category label: "Total Revenue"
5. Lặp lại cho: [Total Orders], [AOV], [YoY Growth %]

Format YoY Growth %:
  → Display as: Percentage
  → Conditional formatting: Green if > 0, Red if < 0
     (Format → Data label → fx → Rules)

Bước 4.3: Revenue Trend — Line Chart (Hero) ​

Revenue Trend Line Chart:
━━━━━━━━━━━━━━━━━━━━━━━━
1. Visualizations → Line Chart
2. X-axis: DIM_DATE[Year-Month]
3. Y-axis: [Total Revenue]
4. Sort: X-axis ascending (chronological)

Customize:
  → Data colors: #1976D2 (blue) — single line
  → Data labels: ON — show values
  → Title: "Monthly Revenue Trend (2024–2025)"
  → Y-axis: Format → Display units: Millions
  → Markers: ON
  → Size: ~50% page width, ~30% height

Bước 4.4: Revenue by Category — Bar Chart ​

Category Bar Chart:
━━━━━━━━━━━━━━━━━━
1. Visualizations → Clustered Bar Chart (horizontal)
2. Y-axis: dim_product[category]
3. X-axis: [Total Revenue]
4. Sort: [Total Revenue] descending

Customize:
  → Data colors: Use theme colors
  → Data labels: ON — show values in Millions
  → Title: "Revenue by Category"
  → Size: ~30% page width

Bước 4.5: Top Products Table ​

Top Products Table:
━━━━━━━━━━━━━━━━━━
1. Visualizations → Table
2. Columns: dim_product[Product Name], [Total Revenue],
            [Total Orders], [Profit Margin %]
3. Sort: [Total Revenue] descending
4. TopN filter: Top 10 by [Total Revenue]

Customize:
  → Conditional formatting on Revenue: Data bars (blue)
  → Conditional formatting on Margin: Color scale (red → green)
  → Style: Alternating rows

Bước 4.6: Channel Split — Donut Chart ​

Channel Donut:
━━━━━━━━━━━━━━
1. Visualizations → Donut Chart
2. Legend: fact_sales[channel]
3. Values: [Total Revenue]

Customize:
  → Colors: Online=#1976D2 (blue), Offline=#FF9800 (orange)
  → Detail labels: Category + Percentage
  → Title: "Online vs Offline"

Bước 4.7: Slicers — Region & Date ​

Slicer 1: Region
━━━━━━━━━━━━━━━━
1. Visualizations → Slicer
2. Field: dim_customer[region]
3. Format → Slicer settings → Style: Dropdown
4. Position: Top-left

Slicer 2: Year-Month
━━━━━━━━━━━━━━━━━━━
1. Visualizations → Slicer
2. Field: DIM_DATE[Year-Month]
3. Format → Slicer settings → Style: Between (range slider)
4. Position: Top-right

Bước 4.8: Cross-filter & Interactions ​

Test Cross-filter:
━━━━━━━━━━━━━━━━━━
1. Click "Electronics" trên bar chart
   → Kiểm tra: KPI cards filter? ✅ Line chart filter? ✅ Table filter? ✅
2. Click "TP.HCM" trên slicer
   → Kiểm tra: All visuals update for HCM? ✅
3. Edit Interactions (optional):
   → Click bar chart → Format → Edit Interactions
   → Chọn filter/highlight/none cho từng visual khác

Phần 5: Dashboard Polish ​

Title, theme, mobile layout

Title:
━━━━━━
1. Insert → Text box
2. Text: "📊 E-Commerce Sales Dashboard — FY2024-2025"
3. Font: Segoe UI, 18pt, Bold
4. Position: Top center

Footer:
━━━━━━━
1. Insert → Text box
2. Text: "Data refreshed: [date] | Source: E-commerce Database | Built with Power BI"
3. Font: 8pt, Gray, Italic
4. Position: Bottom

Bước 5.2: Theme ​

Theme:
━━━━━━
1. View → Themes → Browse for themes
2. Hoặc: View → Themes → Customize current theme
   → Colors: Primary=#1976D2, Secondary=#FF9800
   → Font: Segoe UI
   → Background: White
3. Apply → tất cả visuals tự update

Bước 5.3: Mobile Layout ​

Mobile Layout:
━━━━━━━━━━━━━━
1. View → Mobile Layout
2. Kéo visuals vào phone canvas:
   → Row 1: Slicer Region (dropdown)
   → Row 2-3: 4 KPI Cards (stack vertical)
   → Row 4-5: Revenue Trend (line chart)
   → Row 6-7: Category Breakdown (bar)
   → Row 8: Channel Split (donut)
3. Không kéo Table vào mobile (quá chi tiết cho phone)

Phần 6: Publish & Share ​

Bước 6.1: Save file ​

File → Save As:
━━━━━━━━━━━━━━
  Filename: HoTen_Buoi11_Dashboard.pbix
  Location: Thư mục workshop

Bước 6.2: Publish (nếu có Power BI Pro) ​

Publish:
━━━━━━━━
1. Home → Publish
2. Chọn Workspace (My Workspace hoặc team workspace)
3. Đợi upload → "Success"
4. Click link → mở trên Power BI Service (browser)
5. Share: Share → nhập email → chọn quyền View/Edit

Bước 6.3: Export (nếu không có Pro license) ​

Export Options:
━━━━━━━━━━━━━━
  File → Export → PDF → all pages
  File → Export → PowerPoint
  Hoặc: Share file .pbix trực tiếp qua email/drive

💡 Tableau alternative

Nếu dùng Tableau Public:

  1. File → Save to Tableau Public
  2. Login account → Upload
  3. Share link từ Tableau Public profile
  4. Hoặc: File → Export as Packaged Workbook (.twbx)

📋 Deliverable ​

Sau khi hoàn thành workshop, nộp:

#FileMô tả
1HoTen_Buoi11_Dashboard.pbixPower BI file hoàn chỉnh (hoặc .twbx cho Tableau)
2fact_sales.csvDataset chính — 10,000 transactions
3dim_product.csvDimension table — 50 products
4dim_customer.csvDimension table — 2,000 customers
5Link dashboard (optional)URL Power BI Service hoặc Tableau Public
6Screenshot dashboardPNG screenshot cho archive

💡 Checklist trước khi nộp

  • [ ] 3 CSV files đã tạo thành công
  • [ ] Data model — 3 relationships, star schema, DIM_DATE created
  • [ ] 8+ DAX measures — Revenue, Orders, AOV, Customers, Growth, Profit Margin, Share %, Revenue PY
  • [ ] Dashboard có: 4 KPI cards + Line trend + Bar breakdown + Table + Donut + 2 Slicers
  • [ ] Cross-filter hoạt động — click visual A → visual B update
  • [ ] Slicer hoạt động — filter Region, filter Date range
  • [ ] Mobile layout đã tạo
  • [ ] Title + Footer có trên dashboard
  • [ ] Theme consistent — 1 color palette, readable font

📊 Rubric — Thang điểm ​

Tiêu chíĐiểmMô tả
Data Import & Power Query (Phần 1)103 CSV imported, data types correct, columns renamed
DIM_DATE Table (Phần 1)5Date table created with DAX, marked as date table
Data Model (Phần 2)15Star schema, 3 relationships, Many-to-One, single direction, hidden keys
Basic DAX Measures (Phần 3)15M1-M5: Revenue, Orders, Profit, Customers, AOV — correct & verified
Advanced DAX Measures (Phần 3)15M6-M9: Revenue PY, YoY Growth %, Profit Margin %, Revenue Share %
Dashboard Visuals (Phần 4)204 KPI cards + Line + Bar + Table + Donut + 2 Slicers — Z-pattern layout
Interactivity (Phần 4)10Cross-filter working, slicers filter all visuals, data labels
Polish & Mobile (Phần 5)5Title, footer, theme, mobile layout created
Publish / Export (Phần 6)5.pbix/.twbx file saved, screenshot, optional publish link
Tổng100

⚠️ Lưu ý quan trọng

  • Star schema bắt buộc — không dùng flat table (1 bảng to). Phải có FACT + DIM separate.
  • DAX measures bắt buộc — không dùng drag-and-drop default aggregation. Phải viết measure rõ ràng.
  • DIM_DATE bắt buộc — phải tạo date table cho time intelligence (SAMEPERIODLASTYEAR).
  • Cross-filter phải hoạt động — click 1 visual → visual khác phải phản ứng.
  • Mobile layout phải có — mất 5 điểm nếu thiếu.
  • Mỗi visual phải có title mô tả insight — không chỉ label.
  • Dashboard tổng cộng ≤ 8 visuals (không tính slicers) — nếu nhiều hơn sẽ bị trừ điểm visual overload.