Data Warehousing, Skema Dimensi & Kubus OLAP

Tabel fakta dan dimensi, star schema, snowflake schema, fact constellation, proses ETL, serta operasi OLAP roll-up, drill-down, slice, dice, dan pivot.

Notebook lab (pratinjau)

Unduh lalu jalankan di Jupyter, VS Code, atau Google Colab.

Unduh lab.ipynb

Lab Minggu 3 — Data Warehouse, Star Schema & Kubus OLAP

Tujuan lab:

Proses ETL: mengubah tabel penjualan datar (OLTP export) menjadi star schema.
Memuat star schema ke SQLite (sebagai warehouse mini) dan menjalankan kueri analitik.
Mempraktikkan operasi OLAP: roll-up, drill-down, slice, dice, pivot.

[1]

import sys
sys.path.insert(0, "..")

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt

from common.datasets import penjualan_flat

df = penjualan_flat()
print(df.shape)
df.head()

1. Extract — lihat redundansi di tabel datar

Perhatikan kategori, harga_satuan, kota, wilayah diulang di setiap baris → boros dan rawan inkonsistensi. Star schema memisahkannya ke tabel dimensi.

[2]

print("Baris:", len(df))
print("Produk unik:", df.nama_produk.nunique(), "| Toko unik:", df.nama_toko.nunique(),
      "| Tanggal unik:", df.tanggal.nunique())

2. Transform — bangun tabel dimensi

[3]

dim_produk = (df[["nama_produk", "kategori", "harga_satuan"]].drop_duplicates()
              .sort_values("nama_produk").reset_index(drop=True))
dim_produk.insert(0, "id_produk", range(1, len(dim_produk) + 1))
dim_produk

[4]

dim_toko = (df[["nama_toko", "kota", "wilayah"]].drop_duplicates()
            .sort_values("nama_toko").reset_index(drop=True))
dim_toko.insert(0, "id_toko", range(1, len(dim_toko) + 1))
dim_toko

[5]

tgl = pd.Series(df["tanggal"].drop_duplicates().sort_values().values)
dim_waktu = pd.DataFrame({
    "id_waktu": tgl.dt.strftime("%Y%m%d").astype(int),
    "tanggal": tgl,
    "bulan": tgl.dt.month,
    "kuartal": tgl.dt.quarter,
    "tahun": tgl.dt.year,
    "nama_hari": tgl.dt.day_name(),
})
dim_waktu.head()

3. Tabel fakta (grain: satu item per transaksi)

[6]

fakta = (df.merge(dim_produk[["id_produk", "nama_produk"]], on="nama_produk")
           .merge(dim_toko[["id_toko", "nama_toko"]], on="nama_toko")
           .merge(dim_waktu[["id_waktu", "tanggal"]], on="tanggal")
           [["id_transaksi", "id_waktu", "id_produk", "id_toko", "jumlah", "total"]])
print(fakta.shape)
fakta.head()

Diagram star schema yang kita buat:

                 dim_waktu (id_waktu, tanggal, bulan, kuartal, tahun, nama_hari)
                       │
dim_produk ──── fakta_penjualan ──── dim_toko
(id_produk, nama,   (id_transaksi, id_waktu,   (id_toko, nama_toko,
 kategori, harga)    id_produk, id_toko,         kota, wilayah)
                     jumlah, total)

4. Load — ke SQLite (warehouse mini)

[7]

con = sqlite3.connect(":memory:")
fakta.to_sql("fakta", con, index=False)
dim_produk.to_sql("dim_produk", con, index=False)
dim_toko.to_sql("dim_toko", con, index=False)
dim_waktu.assign(tanggal=dim_waktu.tanggal.astype(str)).to_sql("dim_waktu", con, index=False)
pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", con)

5. Kueri OLAP dengan SQL

Roll-up: total penjualan per kuartal

[8]

q = """
SELECT w.kuartal, SUM(f.total) AS total
FROM fakta f JOIN dim_waktu w ON f.id_waktu = w.id_waktu
GROUP BY w.kuartal ORDER BY w.kuartal
"""
pd.read_sql(q, con)

Drill-down: kuartal → bulan

[9]

q = """
SELECT w.kuartal, w.bulan, SUM(f.total) AS total
FROM fakta f JOIN dim_waktu w ON f.id_waktu = w.id_waktu
GROUP BY w.kuartal, w.bulan ORDER BY w.kuartal, w.bulan
"""
pd.read_sql(q, con)

Slice: hanya kategori Elektronik, total per wilayah

[10]

q = """
SELECT t.wilayah, SUM(f.total) AS total
FROM fakta f
JOIN dim_produk p ON f.id_produk = p.id_produk
JOIN dim_toko t   ON f.id_toko = t.id_toko
WHERE p.kategori = 'Elektronik'
GROUP BY t.wilayah
"""
pd.read_sql(q, con)

Dice: Fashion & Makanan, di Jakarta & Bandung, kuartal 1–2

[11]

q = """
SELECT p.kategori, t.kota, w.kuartal, SUM(f.jumlah) AS unit, SUM(f.total) AS total
FROM fakta f
JOIN dim_produk p ON f.id_produk = p.id_produk
JOIN dim_toko t   ON f.id_toko = t.id_toko
JOIN dim_waktu w  ON f.id_waktu = w.id_waktu
WHERE p.kategori IN ('Fashion', 'Makanan')
  AND t.kota IN ('Jakarta', 'Bandung')
  AND w.kuartal IN (1, 2)
GROUP BY p.kategori, t.kota, w.kuartal
"""
pd.read_sql(q, con)

6. Operasi OLAP dengan Pandas (pivot / kubus)

[12]

df["kuartal"] = df["tanggal"].dt.quarter
kubus = df.pivot_table(index="kategori", columns="kuartal", values="total",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="TOTAL")
(kubus / 1e6).round(1)   # dalam juta rupiah

[13]

# Pivot (rotasi): tukar sumbu
(df.pivot_table(index="kuartal", columns="kategori", values="total", aggfunc="sum") / 1e6).round(1)

[14]

(df.pivot_table(index="kuartal", columns="kategori", values="total", aggfunc="sum") / 1e6).plot(
    kind="bar", figsize=(8, 4), title="Penjualan per kuartal per kategori (juta Rp)")
plt.show()

7. Semua cuboid sekaligus (GROUP BY CUBE secara manual)

Dengan 3 dimensi ada 2³ = 8 cuboid.

[15]

from itertools import combinations

dims = ["kategori", "wilayah", "kuartal"]
for r in range(len(dims) + 1):
    for combo in combinations(dims, r):
        if combo:
            n = df.groupby(list(combo))["total"].sum().shape[0]
        else:
            n = 1
        print(f"cuboid {str(combo):40s} -> {n} sel")

8. Jebakan measure aljabar: AVG

[16]

per_toko = df.groupby("nama_toko")["total"].agg(["mean", "count"])
salah = per_toko["mean"].mean()
benar = (per_toko["mean"] * per_toko["count"]).sum() / per_toko["count"].sum()
print(f"Rata-rata dari rata-rata (SALAH): {salah:,.0f}")
print(f"Rata-rata tertimbang (BENAR)    : {benar:,.0f}")
print(f"Cek langsung                    : {df['total'].mean():,.0f}")

✍️ Coba sendiri

Ubah dimensi produk menjadi snowflake: pisahkan dim_kategori(id_kategori, nama_kategori). Tulis kueri SQL total per kategori dengan 2 JOIN.
Tulis kueri SQL: 3 produk terlaris (berdasarkan unit) di wilayah Barat pada kuartal 4.
Buat pivot: baris = nama_hari, kolom = kategori, nilai = jumlah transaksi. Hari apa paling ramai?
Gambar diagram star schema versimu sendiri untuk bisnis lain (mis. bioskop) di sel markdown.

[17]

# 1.

[18]

# 2.

[19]

# 3.

Diagram no. 4:

...

Selanjutnya

Kerjakan kuis dan exercise.py, lalu jalankan python check.py 3 dari folder utama.