APA YANG SUDAH KITA TAHU:
  ✓ OLTP → dirancang untuk menulis (write-heavy), normalized, cepat per transaksi
  ✓ OLAP/DW → dirancang untuk membaca (read-heavy), denormalized, cepat per agregasi
  ✓ Data warehouse memiliki 4 karakteristik Inmon
  ✓ ETL menjembatani OLTP ke DW
  ✓ Dimensional thinking: dunia dibagi menjadi FAKTA dan DIMENSI

PERTANYAAN YANG BELUM TERJAWAB SECARA DETAIL:
  ✗ Bagaimana TEPATNYA cara merancang skema warehouse dari awal?
  ✗ "Grain" itu apa dan bagaimana menentukan yang tepat?
  ✗ Bagaimana membangun tabel dimensi yang benar-benar berguna?
  ✗ Bagaimana mengimplementasikan semua ini dalam SQL?

→ Itulah yang akan kita jawab di Pertemuan 12!

DESAIN A — Grain: Satu baris per pesanan (per struk)
┌─────────────┬─────────────────┬──────────────────────────────────────────────┐
│ kunci_waktu │ kunci_pelanggan │ kunci_apoteker │ total_transaksi │ jml_item  │
├─────────────┼─────────────────┼────────────────┼─────────────────┼───────────┤
│ 20240315    │ 1001            │ 3              │ 285.000         │ 4         │
│ 20240315    │ 1002            │ 5              │ 67.500          │ 1         │
│ 20240315    │ 1001            │ 3              │ 150.000         │ 2         │
└─────────────┴─────────────────┴────────────────┴─────────────────┴───────────┘

DESAIN B — Grain: Satu baris per item per pesanan (per baris struk)
┌─────────────┬─────────────────┬────────────────┬──────────────┬─────┬───────────┐
│ kunci_waktu │ kunci_pelanggan │ kunci_apoteker │ kunci_obat   │ qty │ subtotal  │
├─────────────┼─────────────────┼────────────────┼──────────────┼─────┼───────────┤
│ 20240315    │ 1001            │ 3              │ OBT-0091     │ 2   │ 120.000   │
│ 20240315    │ 1001            │ 3              │ OBT-0045     │ 1   │ 85.000    │
│ 20240315    │ 1001            │ 3              │ OBT-0123     │ 3   │ 80.000    │
│ 20240315    │ 1002            │ 5              │ OBT-0045     │ 1   │ 67.500    │
└─────────────┴─────────────────┴────────────────┴──────────────┴─────┴───────────┘

EMPAT LANGKAH DESAIN DIMENSIONAL (KIMBALL):

┌─────────────────────────────────────────────────────────────┐
│  LANGKAH 1: PILIH PROSES BISNIS                            │
│  "Business process apa yang akan dimodelkan?"               │
│                                                             │
│  → Proses bisnis = aktivitas organisasi yang menghasilkan  │
│    data terukur                                             │
│  → Satu model dimensional = satu proses bisnis             │
│  → Contoh proses bisnis:                                   │
│    • Penjualan (retail/e-commerce)                         │
│    • Pengiriman pesanan (logistik)                         │
│    • Kunjungan pasien (rumah sakit)                        │
│    • Pengambilan mata kuliah (akademik)                    │
│    • Klaim asuransi (finansial)                            │
│                                                             │
│  ❌ BUKAN: "analisis bisnis" atau "laporan manajemen"       │
│     → Terlalu abstrak. Tanya: aktivitas spesifik apa?      │
└─────────────────────────────────────────────────────────────┘
                           ↓
┌─────────────────────────────────────────────────────────────┐
│  LANGKAH 2: DEKLARASIKAN GRAIN                             │
│  "Satu baris di tabel fakta mewakili APA?"                  │
│                                                             │
│  → Grain = tingkat detail paling atomik yang akan          │
│    disimpan di tabel fakta                                  │
│  → Harus dinyatakan dalam SATU KALIMAT yang jelas          │
│  → Semua dimensi dan measures harus konsisten dgn grain    │
│                                                             │
│  PALING PENTING dari keempat langkah!                      │
│  Kesalahan grain = fondasi yang salah → desain ulang total  │
└─────────────────────────────────────────────────────────────┘
                           ↓
┌─────────────────────────────────────────────────────────────┐
│  LANGKAH 3: IDENTIFIKASI DIMENSI                           │
│  "Konteks apa yang memberi makna pada setiap fakta?"        │
│                                                             │
│  → Dimensi = "label" untuk fakta                           │
│  → Tanyakan: Siapa? Apa? Kapan? Di mana? Mengapa? Bagaimana│
│  → Setiap dimensi harus KONSISTEN dengan grain yang sudah  │
│    dideklarasikan di Langkah 2                             │
└─────────────────────────────────────────────────────────────┘
                           ↓
┌─────────────────────────────────────────────────────────────┐
│  LANGKAH 4: IDENTIFIKASI FAKTA (MEASURES)                  │
│  "Apa yang diukur dari kejadian bisnis ini?"                │
│                                                             │
│  → Measures = nilai numerik yang bisa diagregasi           │
│  → Setiap measure harus ada di SETIAP baris (pada grain    │
│    yang sudah ditentukan) — bukan hanya di sebagian baris  │
│  → Tentukan tipe: additive / semi-additive / non-additive  │
└─────────────────────────────────────────────────────────────┘

Langkah 1 — Proses Bisnis:
  "Penjualan batik melalui platform e-commerce"

Langkah 2 — Grain:
  "Satu baris mewakili satu item baris (line item) dalam satu 
   pesanan pelanggan pada satu tanggal transaksi"
  (= satu SKU dalam satu pesanan = level paling atomik dari transaksi)

Langkah 3 — Dimensi:
  • dim_waktu      → KAPAN transaksi terjadi?
  • dim_pelanggan  → SIAPA yang membeli?
  • dim_produk     → APA yang dibeli (SKU)?
  • dim_lokasi     → DI MANA (kota pengiriman)?
  • dim_kanal      → MELALUI APA pembelian dilakukan? (web, app, marketplace)

Langkah 4 — Measures (Fakta):
  • qty              → Jumlah unit yang dibeli (additive)
  • harga_satuan     → Harga per unit saat transaksi (non-additive)
  • diskon           → Nominal diskon yang diberikan (additive)
  • revenue_bersih   → qty × harga_satuan − diskon (additive)
  • biaya_kirim      → Ongkos kirim untuk item ini (additive)

GRAIN YANG SALAH = FONDASI YANG RETAK

Kesalahan grain tidak bisa diperbaiki dengan menambah kolom atau
mengubah nama tabel. Ia harus redesign dari awal, termasuk:
→ ETL pipeline harus dibangun ulang
→ Semua query yang sudah ada harus ditulis ulang
→ Laporan historis bisa berubah hasilnya

TIGA ATURAN GRAIN (Kimball):
  1. Deklarasikan grain SEBELUM memilih dimensi atau measures
  2. Pilih grain yang PALING ATOMIK yang masuk akal secara bisnis
  3. Semua measures dalam satu tabel fakta HARUS ada pada grain yang sama

ILUSTRASI: Sistem Perpustakaan

GRAIN TERLALU TINGGI (aggregate grain):
  "Satu baris per anggota per bulan"
  → Kolom: kunci_anggota, kunci_bulan, total_buku_dipinjam, total_denda
  
  MASALAH:
  → Tidak bisa tahu buku apa yang dipinjam
  → Tidak bisa analisis per kategori buku
  → Tidak bisa tahu pola peminjaman di hari tertentu
  → Sekali grain ditetapkan, detail sudah hilang — tidak bisa "drill down" lagi!

GRAIN TERLALU RENDAH (unnecessarily granular):
  "Satu baris per halaman yang dibaca anggota"
  → Kolom: kunci_anggota, kunci_buku, nomor_halaman, waktu_baca
  
  MASALAH:
  → Tidak realistis (data ini tidak tersedia)
  → Volume data sangat besar tanpa nilai bisnis tambahan
  → Pemborosan storage

GRAIN YANG TEPAT:
  "Satu baris mewakili satu transaksi peminjaman satu buku oleh satu anggota"
  → Kolom: kunci_waktu, kunci_anggota, kunci_buku, durasi_pinjam_hari,
           denda, kunci_petugas
  
  KENAPA TEPAT:
  ✓ Bisa roll up ke bulan, kuartal, tahun (via dim_waktu)
  ✓ Bisa analisis per anggota, per buku, per kategori buku
  ✓ Bisa drill down ke setiap transaksi individual
  ✓ Volume data realistis dan tersedia

PRINSIP FUNDAMENTAL:
  Jangan mencampur grain yang berbeda dalam satu tabel fakta!

CONTOH MASALAH (Sistem Hotel):
  "Fakta_Pemesanan" berisi:
  → total_harga_kamar (grain: satu baris per pemesanan)
  → biaya_room_service (grain: satu baris per item room service)
  → biaya_laundry (grain: satu baris per pakaian)

  MASALAH: Grain tidak konsisten!
  → total_harga_kamar muncul berulang kali untuk satu pemesanan
    jika ada banyak item room service
  → Agregasi menjadi salah (double-counting)

SOLUSI — BUAT DUA TABEL FAKTA TERPISAH:
  fakta_pemesanan
    Grain: satu baris per pemesanan kamar
    Measures: total_harga_kamar, lama_menginap, deposit

  fakta_transaksi_tambahan
    Grain: satu baris per transaksi layanan tambahan
    Measures: qty, harga_satuan, subtotal, jenis_layanan

  → Kedua tabel bisa di-JOIN saat query jika diperlukan

TABEL FAKTA TERDIRI DARI DUA JENIS KOLOM:

┌──────────────────────────────────────────────────────────────┐
│                    FAKTA_PENJUALAN                           │
│                                                              │
│  KUNCI ASING (Foreign Keys) → referensi ke dimensi           │
│  ────────────────────────────────────────────────            │
│  kunci_waktu        INT (FK → dim_waktu.kunci_waktu)        │
│  kunci_pelanggan    INT (FK → dim_pelanggan.kunci_pelanggan) │
│  kunci_produk       INT (FK → dim_produk.kunci_produk)       │
│  kunci_lokasi       INT (FK → dim_lokasi.kunci_lokasi)       │
│  kunci_kanal        INT (FK → dim_kanal.kunci_kanal)         │
│                                                              │
│  MEASURES (Nilai yang Diukur) → angka-angka bisnis           │
│  ────────────────────────────────────────────────            │
│  qty                SMALLINT                                 │
│  harga_satuan       DECIMAL(15,2)                            │
│  diskon             DECIMAL(15,2)                            │
│  revenue_bersih     DECIMAL(15,2)                            │
│  biaya_kirim        DECIMAL(15,2)                            │
└──────────────────────────────────────────────────────────────┘

KARAKTERISTIK TABEL FAKTA:
  → Biasanya NARROW (sedikit kolom — terutama FK dan measures)
  → Biasanya TALL (sangat banyak baris — jutaan hingga miliar)
  → Tidak ada data deskriptif — semua deskripsi ada di dimensi
  → PK biasanya adalah composite key dari semua FK
  → Mendominasi ukuran storage data warehouse (80–95%)

1. ADDITIVE MEASURES (Ukuran Aditif)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Definisi: Bisa dijumlahkan (SUM) secara valid di SEMUA dimensi
  tanpa menghasilkan angka yang misleading.

  Contoh: qty, revenue_bersih, biaya_kirim, jumlah_kunjungan, denda

  VALID untuk semua kombinasi:
  SUM(revenue_bersih) GROUP BY tahun        ← ✓ Revenue tahunan
  SUM(revenue_bersih) GROUP BY kota         ← ✓ Revenue per kota
  SUM(revenue_bersih) GROUP BY produk       ← ✓ Revenue per produk
  SUM(revenue_bersih)                       ← ✓ Total revenue

  → Mayoritas measures yang baik adalah additive
  → Jika bisa, desain measures agar additive

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

2. SEMI-ADDITIVE MEASURES (Ukuran Semi-Aditif)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Definisi: Bisa dijumlahkan di BEBERAPA dimensi, tapi TIDAK SEMUA.
  Biasanya tidak bisa dijumlahkan melewati dimensi WAKTU.

  Contoh khas: saldo_rekening, jumlah_stok, jumlah_karyawan_aktif,
               nilai_piutang, jumlah_pasien_rawat_inap

  ILUSTRASI — saldo_rekening:
  SUM(saldo_rekening) GROUP BY bank_id      ← ✓ Total saldo di bank X
  SUM(saldo_rekening) GROUP BY nasabah_id   ← ✗ Tidak valid!
                                                (menjumlah saldo bulan Jan+Feb+Mar
                                                 = bukan total saldo 3 bulan,
                                                 tapi angka tidak bermakna)

  CARA BENAR untuk dimensi waktu:
  → Gunakan AVG untuk rata-rata antar periode
  → Gunakan snapshot terbaru (MAX date)
  → Contoh: AVG(jumlah_stok) GROUP BY bulan = rata-rata stok bulanan
            MAX(saldo) OVER (PARTITION BY bulan) = snapshot akhir bulan

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

3. NON-ADDITIVE MEASURES (Ukuran Non-Aditif)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Definisi: TIDAK bisa dijumlahkan di dimensi manapun tanpa
  menghasilkan angka yang salah.

  Contoh: harga_satuan, rasio/persentase, suhu, rating,
          nilai_tukar_kurs, margin_persen

  ILUSTRASI — harga_satuan:
  SUM(harga_satuan) dari 3 baris berbeda    ← ✗ Tidak bermakna!
  (Batik Tulis Rp 600rb + Batik Cap Rp 250rb + Aksesoris Rp 75rb
   = Rp 925rb tidak merepresentasikan apapun yang berguna)

  CARA BENAR:
  → Simpan komponen aditif yang bisa digunakan untuk kalkulasi:
    qty (additive) + revenue_bersih (additive)
    → harga_rata_rata = SUM(revenue_bersih) / SUM(qty)  ← BENAR!
  → Jangan SUM(harga_satuan) — tapi boleh AVG(harga_satuan) untuk rata-rata
  → Simpan harga_satuan di tabel fakta untuk keperluan audit/drill-down,
    bukan untuk diagregasi

TABEL RINGKASAN:
┌──────────────────┬─────────────────────────────┬───────────────────────────────┐
│ Jenis            │ Operasi Valid                │ Contoh                        │
├──────────────────┼─────────────────────────────┼───────────────────────────────┤
│ Additive         │ SUM di semua dimensi        │ revenue, qty, biaya, denda    │
├──────────────────┼─────────────────────────────┼───────────────────────────────┤
│ Semi-additive    │ SUM di beberapa dimensi,    │ saldo, stok, jumlah aktif     │
│                  │ AVG/snapshot di waktu       │                               │
├──────────────────┼─────────────────────────────┼───────────────────────────────┤
│ Non-additive     │ AVG, atau kalkulasi dari    │ harga_satuan, rasio, rating   │
│                  │ komponen aditif             │                               │
└──────────────────┴─────────────────────────────┴───────────────────────────────┘

DEGENERATE DIMENSION adalah atribut yang berperilaku seperti dimensi
(digunakan untuk filter/grouping) tetapi tidak punya tabel dimensi sendiri
karena setelah diekstrak dari tabel OLTP, tidak ada lagi atribut deskriptif
yang tersisa untuk dijadikan tabel terpisah.

CONTOH PALING UMUM: Nomor pesanan (id_pesanan)
  id_pesanan di OLTP berisi banyak informasi → JOIN ke tabel pesanan
  id_pesanan di DW: setelah semua atribut fakta diambil sebagai measures
  dan semua atribut deskriptif diambil ke dimensi, hanya tersisa "nomor"
  itu sendiri — tidak punya atribut lain yang berguna di tabel dimensi.

  → Simpan id_pesanan LANGSUNG di tabel fakta sebagai degenerate dimension!
  → Tidak perlu buat dim_pesanan yang hanya berisi satu kolom nomor.

KAPAN TERJADI:
  → Nomor dokumen/transaksi: id_pesanan, nomor_faktur, kode_tiket
  → Nomor batch proses: nomor_pengiriman, kode_produksi

CARA IDENTIFIKASI:
  Tanya: "Jika saya buat tabel dimensi ini, apakah ada atribut lain
         selain natural key-nya? Apakah tabel ini punya nilai deskriptif?"
  Jika TIDAK → kandidat degenerate dimension

CONTOH DI FAKTA PENJUALAN TOKO BATIK:
  fakta_penjualan:
    kunci_waktu         FK ke dim_waktu
    kunci_pelanggan     FK ke dim_pelanggan
    kunci_produk        FK ke dim_produk
    id_pesanan          ← DEGENERATE DIMENSION (disimpan langsung di fakta)
    id_item_pesanan     ← DEGENERATE DIMENSION
    qty, harga_satuan, diskon, revenue_bersih  ← measures

  → id_pesanan berguna untuk: "Tampilkan semua item dalam pesanan #10045"
    (drill-through ke detail transaksi)

PRINSIP DESAIN DIMENSI KIMBALL:
  "Dimension tables should be wide and short."
  (Dimensi harus lebar dan pendek — banyak kolom, sedikit baris)

LEBAR: Banyak atribut deskriptif
  → Jangan ragu menambahkan kolom ke dimensi!
  → Setiap kolom = satu sudut pandang analitik baru
  → Lebih baik terlalu banyak kolom daripada terlalu sedikit

PENDEK: Relatif sedikit baris
  → dim_pelanggan: ribuan hingga jutaan baris (vs fakta: ratusan juta)
  → dim_produk: ratusan hingga ribuan baris
  → dim_waktu: ~3.650 baris (10 tahun) atau ~36.500 baris (100 tahun)

DENORMALIZED (DISENGAJA):
  Ini adalah PENYIMPANGAN dari aturan 3NF — dan itu SENGAJA!

  OLTP (3NF): redundansi dihindari untuk menjaga integritas DML
  DW (dimensi): redundansi diterima untuk mempercepat query SELECT

  CONTOH:
  OLTP (3NF):
    produk(id_produk, nama, id_kategori)
    kategori(id_kategori, nama_kategori, id_kategori_induk)
    kategori_induk(id_kategori_induk, nama_kategori_induk)

  DW (denormalized):
    dim_produk(kunci_produk, nama_produk, subkategori, kategori, nama_produk_lengkap,
               supplier, asal_kota, rentang_harga, ...)
    → Semua atribut hierarki kategori di-flatten ke satu tabel!
    → JOIN hanya satu hop dari fakta ke dimensi — jauh lebih cepat

SURROGATE KEY: Kunci buatan yang dibuat oleh sistem warehouse
  → Biasanya integer auto-increment (1, 2, 3, ...)
  → Tidak punya makna bisnis
  → WAJIB digunakan sebagai PK di tabel dimensi
  
  MENGAPA SURROGATE KEY DIPERLUKAN:

  Alasan 1 — Mendukung SCD Type 2 (dibahas di P13):
    Natural key (mis: id_pelanggan) tidak bisa menjadi PK
    jika satu pelanggan punya multiple versi historis.
    Surrogate key memberi identitas unik pada setiap versi.

  Alasan 2 — Independensi dari sumber OLTP:
    id_pelanggan di MySQL berbeda dengan customer_id di CRM.
    Surrogate key menyatukan keduanya dengan identitas baru.

  Alasan 3 — Performa JOIN:
    Integer join lebih cepat dari string join
    (VARCHAR(50) join >> INT join dari sisi performa)

  Alasan 4 — Proteksi dari perubahan sumber:
    Jika OLTP mengubah format primary key, warehouse tidak terpengaruh
    selama mapping surrogate ↔ natural key di-maintain.

NATURAL KEY: Kunci asli dari sistem sumber OLTP
  → Harus TETAP DISIMPAN di dimensi sebagai kolom terpisah
  → Digunakan untuk proses ETL (lookup: "apakah record ini sudah ada?")
  → Digunakan untuk audit dan penelusuran balik ke sumber
  → JANGAN dijadikan PK dimensi di warehouse

KONVENSI PENAMAAN:
  kunci_pelanggan   → surrogate key (PK dimensi)
  id_pelanggan_src  → natural key dari sumber (bukan PK)

TABEL OLTP (3NF):
  produk(id_produk, nama_produk, id_kategori, harga_jual, id_supplier)
  kategori(id_kategori, nama_kategori, id_kategori_parent)
  supplier(id_supplier, nama_supplier, kota_supplier, negara_supplier)

DIMENSI PRODUK DI WAREHOUSE (denormalized, wide):
  dim_produk:
  ┌─────────────────────────────────────────────────────────────────────┐
  │ kunci_produk          INT   PK, surrogate key (auto-increment)      │
  │ id_produk_src         INT   natural key dari OLTP                   │
  │ nama_produk           VARCHAR(200)                                  │
  │ deskripsi_singkat     VARCHAR(500)                                  │
  │ subkategori           VARCHAR(100)  ← dari tabel kategori (denorm)  │
  │ kategori              VARCHAR(100)  ← kategori induk (denorm)       │
  │ kelompok_kategori     VARCHAR(100)  ← kategori tertinggi (denorm)   │
  │ nama_supplier         VARCHAR(150)  ← dari tabel supplier (denorm)  │
  │ kota_supplier         VARCHAR(100)  ← dari tabel supplier (denorm)  │
  │ asal_produk           VARCHAR(100)  ← 'Lokal' / 'Impor'            │
  │ harga_jual            DECIMAL(15,2)                                 │
  │ rentang_harga         VARCHAR(30)   ← 'Budget'/'Mid'/'Premium'      │
  │ bahan_utama           VARCHAR(100)  ← 'Katun'/'Sutra'/'Sifon'      │
  │ motif                 VARCHAR(100)  ← 'Parang'/'Kawung'/'Mega Mendung'│
  │ teknik_pembuatan      VARCHAR(50)   ← 'Tulis'/'Cap'/'Printing'      │
  │ is_aktif              TINYINT(1)                                    │
  │ berlaku_dari          DATE          ← untuk SCD (dibahas P13)       │
  │ berlaku_sampai        DATE          ← untuk SCD (dibahas P13)       │
  └─────────────────────────────────────────────────────────────────────┘

PERHATIKAN:
  → 19 kolom vs 5 kolom di OLTP — jauh lebih kaya!
  → Semua hierarki kategori sudah "di-flatten" — tidak perlu JOIN ke kategori
  → Atribut supplier sudah di-embed — tidak perlu JOIN ke supplier
  → Kolom turunan (rentang_harga, asal_produk) sudah dikalkulasi saat ETL
  → Setiap kolom = satu kemungkinan sudut pandang analitik baru

CREATE TABLE dim_pelanggan (
    -- SURROGATE KEY
    kunci_pelanggan      INT UNSIGNED     NOT NULL AUTO_INCREMENT,
 
    -- NATURAL KEY dari sumber
    id_pelanggan_src     INT UNSIGNED     NOT NULL,
 
    -- ATRIBUT IDENTITAS
    nama_lengkap         VARCHAR(150)     NOT NULL,
    jenis_kelamin        CHAR(1)          NULL,      -- 'L' / 'P'
    kelompok_usia        VARCHAR(20)      NULL,      -- 'Remaja', 'Dewasa Muda', 'Dewasa', 'Senior'
    rentang_usia         VARCHAR(20)      NULL,      -- '18-24', '25-34', '35-44', ...
 
    -- ATRIBUT LOKASI
    kota                 VARCHAR(100)     NOT NULL,
    kabupaten_kota       VARCHAR(100)     NOT NULL,
    provinsi             VARCHAR(100)     NOT NULL,
    pulau                VARCHAR(50)      NOT NULL,  -- 'Jawa', 'Sumatera', ...
    wilayah              VARCHAR(50)      NOT NULL,  -- 'Jawa Tengah & DIY', 'Jabodetabek', ...
 
    -- ATRIBUT KEANGGOTAAN
    tgl_daftar           DATE             NOT NULL,
    tahun_daftar         SMALLINT         NOT NULL,  -- derived dari tgl_daftar
    bulan_daftar         TINYINT          NOT NULL,  -- derived dari tgl_daftar
    segmen_pelanggan     VARCHAR(30)      NOT NULL,  -- 'Baru', 'Regular', 'Loyal', 'VIP'
    lama_keanggotaan_thn SMALLINT         NULL,      -- berapa tahun sudah menjadi member
 
    -- ATRIBUT HISTORIS (untuk SCD — dibahas P13)
    berlaku_dari         DATE             NOT NULL,
    berlaku_sampai       DATE             NOT NULL DEFAULT '9999-12-31',
    is_current           TINYINT(1)       NOT NULL DEFAULT 1,
 
    CONSTRAINT pk_dim_pelanggan PRIMARY KEY (kunci_pelanggan),
    INDEX idx_natural_key (id_pelanggan_src),
    INDEX idx_segmen (segmen_pelanggan),
    INDEX idx_current (is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Pelanggan — Toko Batik Online DW';

MENGAPA TIDAK CUKUP SIMPAN TANGGAL SAJA DI TABEL FAKTA?

Jika fakta hanya menyimpan: tgl_transaksi DATE

Maka untuk query analitik:
  SELECT YEAR(tgl_transaksi), MONTH(tgl_transaksi), SUM(revenue)
  FROM fakta_penjualan
  GROUP BY YEAR(tgl_transaksi), MONTH(tgl_transaksi);

  MASALAH:
  → Fungsi YEAR() dan MONTH() dijalankan saat query — untuk jutaan baris!
  → Tidak bisa filter dengan mudah "apakah hari ini adalah hari libur nasional?"
  → Tidak bisa filter "apakah bulan ini adalah bulan Ramadan?"
  → Tidak bisa grouping berdasarkan "semester akademik" atau "musim"

DENGAN dim_waktu yang pre-populated:
  SELECT w.tahun, w.nama_bulan, SUM(f.revenue)
  FROM fakta_penjualan f
  JOIN dim_waktu w ON f.kunci_waktu = w.kunci_waktu
  WHERE w.is_hari_libur = 1  -- mudah filter hari libur!
  GROUP BY w.tahun, w.nama_bulan;

  → Semua kalkulasi temporal sudah dilakukan SEKALI saat populate dim_waktu
  → Query lebih bersih, lebih cepat, dan lebih fleksibel

-- ============================================================
-- DIMENSI WAKTU — dim_waktu
-- Pre-populated untuk rentang 10 tahun (mis: 2020-2029)
-- Jumlah baris: 3.652 baris (termasuk tahun kabisat)
-- ============================================================
CREATE TABLE dim_waktu (
    -- SURROGATE KEY dalam format YYYYMMDD — juga berfungsi sebagai smart key
    kunci_waktu          INT UNSIGNED     NOT NULL,   -- e.g. 20240315
 
    -- TANGGAL PENUH
    tanggal_penuh        DATE             NOT NULL,
 
    -- HIERARKI HARI
    hari_dalam_bulan     TINYINT UNSIGNED NOT NULL,   -- 1–31
    hari_dalam_tahun     SMALLINT UNSIGNED NOT NULL,  -- 1–366
    nama_hari_en         VARCHAR(10)      NOT NULL,   -- Monday, Tuesday, ...
    nama_hari_id         VARCHAR(10)      NOT NULL,   -- Senin, Selasa, ...
    nomor_hari_minggu    TINYINT UNSIGNED NOT NULL,   -- 1=Senin, 7=Minggu (ISO)
    is_weekend           TINYINT(1)       NOT NULL,   -- 0=weekday, 1=weekend
    is_hari_libur_nas    TINYINT(1)       NOT NULL DEFAULT 0,
    nama_hari_libur      VARCHAR(100)     NULL,       -- 'Hari Raya Idul Fitri 1', dst.
 
    -- HIERARKI MINGGU
    minggu_dalam_bulan   TINYINT UNSIGNED NOT NULL,   -- 1–5
    minggu_dalam_tahun   TINYINT UNSIGNED NOT NULL,   -- 1–53 (ISO week)
    minggu_label         VARCHAR(20)      NOT NULL,   -- 'W12 2024'
 
    -- HIERARKI BULAN
    bulan                TINYINT UNSIGNED NOT NULL,   -- 1–12
    nama_bulan_en        VARCHAR(15)      NOT NULL,   -- January, February, ...
    nama_bulan_id        VARCHAR(15)      NOT NULL,   -- Januari, Februari, ...
    nama_bulan_singkat   CHAR(3)          NOT NULL,   -- Jan, Feb, Mar, ...
    hari_pertama_bulan   DATE             NOT NULL,
    hari_terakhir_bulan  DATE             NOT NULL,
    jumlah_hari_bulan    TINYINT UNSIGNED NOT NULL,   -- 28, 29, 30, 31
 
    -- HIERARKI KUARTAL
    kuartal              TINYINT UNSIGNED NOT NULL,   -- 1, 2, 3, 4
    nama_kuartal         CHAR(2)          NOT NULL,   -- Q1, Q2, Q3, Q4
    hari_pertama_kuartal DATE             NOT NULL,
    hari_terakhir_kuartal DATE            NOT NULL,
 
    -- HIERARKI TAHUN
    tahun                SMALLINT UNSIGNED NOT NULL,  -- 2020, 2021, ...
    is_tahun_kabisat     TINYINT(1)        NOT NULL,  -- 0/1
    semester             TINYINT UNSIGNED  NOT NULL,  -- 1 atau 2
    nama_semester        VARCHAR(20)       NOT NULL,  -- 'Semester 1', 'Semester 2'
    tahun_semester       VARCHAR(15)       NOT NULL,  -- '2024/1', '2024/2'
 
    -- ATRIBUT KHUSUS BISNIS (dapat dikustomisasi)
    is_bulan_puasa       TINYINT(1)        NOT NULL DEFAULT 0,
    nama_musim           VARCHAR(20)       NULL,      -- 'Kemarau', 'Hujan'
    periode_fiskal       VARCHAR(20)       NULL,      -- untuk tahun fiskal berbeda
    label_relatif        VARCHAR(30)       NULL,      -- 'Bulan Ini', 'Bulan Lalu', dll.
 
    CONSTRAINT pk_dim_waktu PRIMARY KEY (kunci_waktu),
    INDEX idx_tanggal (tanggal_penuh),
    INDEX idx_bulan_tahun (tahun, bulan),
    INDEX idx_kuartal_tahun (tahun, kuartal),
    INDEX idx_hari_libur (is_hari_libur_nas)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Waktu — pre-populated untuk periode analisis';

-- Prosedur sederhana untuk populate dim_waktu
-- Mengisi satu tahun tertentu (contoh: 2024)
-- Dalam praktik, jalankan untuk semua tahun yang diperlukan
 
DELIMITER //
CREATE PROCEDURE populate_dim_waktu(p_tahun SMALLINT)
BEGIN
    DECLARE v_tanggal DATE;
    DECLARE v_akhir_tahun DATE;
    
    SET v_tanggal    = MAKEDATE(p_tahun, 1);        -- 1 Januari tahun p_tahun
    SET v_akhir_tahun = MAKEDATE(p_tahun + 1, 1) - INTERVAL 1 DAY;  -- 31 Des
    
    WHILE v_tanggal <= v_akhir_tahun DO
        INSERT INTO dim_waktu (
            kunci_waktu,
            tanggal_penuh,
            hari_dalam_bulan,
            hari_dalam_tahun,
            nama_hari_en,
            nama_hari_id,
            nomor_hari_minggu,
            is_weekend,
            minggu_dalam_tahun,
            minggu_label,
            bulan,
            nama_bulan_en,
            nama_bulan_id,
            nama_bulan_singkat,
            hari_pertama_bulan,
            hari_terakhir_bulan,
            jumlah_hari_bulan,
            minggu_dalam_bulan,
            kuartal,
            nama_kuartal,
            hari_pertama_kuartal,
            hari_terakhir_kuartal,
            tahun,
            is_tahun_kabisat,
            semester,
            nama_semester,
            tahun_semester
        ) VALUES (
            DATE_FORMAT(v_tanggal, '%Y%m%d') + 0,  -- YYYYMMDD sebagai INT
            v_tanggal,
            DAY(v_tanggal),
            DAYOFYEAR(v_tanggal),
            DAYNAME(v_tanggal),                         -- English day name
            ELT(DAYOFWEEK(v_tanggal), 'Minggu','Senin','Selasa','Rabu',
                'Kamis','Jumat','Sabtu'),               -- Indonesia day name
            -- ISO: Senin=1 ... Minggu=7
            CASE DAYOFWEEK(v_tanggal)
                WHEN 1 THEN 7 ELSE DAYOFWEEK(v_tanggal) - 1 END,
            IF(DAYOFWEEK(v_tanggal) IN (1,7), 1, 0),   -- is_weekend
            WEEK(v_tanggal, 3),                          -- ISO week number
            CONCAT('W', LPAD(WEEK(v_tanggal,3),2,'0'), ' ', YEAR(v_tanggal)),
            MONTH(v_tanggal),
            MONTHNAME(v_tanggal),
            ELT(MONTH(v_tanggal),'Januari','Februari','Maret','April','Mei',
                'Juni','Juli','Agustus','September','Oktober','November','Desember'),
            LEFT(MONTHNAME(v_tanggal), 3),
            DATE_FORMAT(v_tanggal, '%Y-%m-01'),
            LAST_DAY(v_tanggal),
            DAY(LAST_DAY(v_tanggal)),
            CEIL(DAY(v_tanggal)/7.0),
            CEIL(MONTH(v_tanggal)/3.0),
            CONCAT('Q', CEIL(MONTH(v_tanggal)/3.0)),
            MAKEDATE(YEAR(v_tanggal), (CEIL(MONTH(v_tanggal)/3.0)-1)*91+1),
            LAST_DAY(DATE_ADD(MAKEDATE(YEAR(v_tanggal),
                (CEIL(MONTH(v_tanggal)/3.0))*3-0,1)-INTERVAL 1 DAY)),
            YEAR(v_tanggal),
            IF(YEAR(v_tanggal) % 4 = 0 AND (YEAR(v_tanggal)%100 != 0
               OR YEAR(v_tanggal)%400 = 0), 1, 0),
            IF(MONTH(v_tanggal) <= 6, 1, 2),
            IF(MONTH(v_tanggal) <= 6, 'Semester 1', 'Semester 2'),
            CONCAT(YEAR(v_tanggal), '/', IF(MONTH(v_tanggal) <= 6, 1, 2))
        );
        SET v_tanggal = v_tanggal + INTERVAL 1 DAY;
    END WHILE;
END //
DELIMITER ;
 
-- Jalankan untuk mengisi:
CALL populate_dim_waktu(2022);
CALL populate_dim_waktu(2023);
CALL populate_dim_waktu(2024);
CALL populate_dim_waktu(2025);

DEFINISI:
  Dimensi yang SAMA (identik atau kompatibel) digunakan oleh
  BEBERAPA tabel fakta berbeda dalam data warehouse.

ANALOGINYA:
  dim_pelanggan yang sama dipakai oleh:
  → fakta_penjualan     (pelanggan beli produk)
  → fakta_retur         (pelanggan retur produk)
  → fakta_interaksi_cs  (pelanggan hubungi customer service)
  → fakta_ulasan        (pelanggan beri ulasan)

  → Semua tabel fakta ini "berbicara" tentang pelanggan yang SAMA,
    dengan definisi yang SAMA, dengan surrogate key yang SAMA!

MANFAAT:
  → Bisa JOIN atau UNION antar tabel fakta melalui dimensi yang sama
  → Definisi "pelanggan" konsisten di seluruh laporan
  → "Drill across": lihat perilaku satu pelanggan dari berbagai proses

CONTOH DRILL ACROSS (menggabungkan dua tabel fakta):
  -- Berapa pelanggan yang beli tapi tidak pernah retur?
  SELECT p.nama_lengkap, SUM(f.revenue_bersih) AS total_belanja
  FROM fakta_penjualan f
  JOIN dim_pelanggan p ON f.kunci_pelanggan = p.kunci_pelanggan
  WHERE NOT EXISTS (
      SELECT 1 FROM fakta_retur r
      WHERE r.kunci_pelanggan = p.kunci_pelanggan
  )
  GROUP BY p.kunci_pelanggan, p.nama_lengkap;

DEFINISI:
  Satu tabel dimensi yang digunakan BEBERAPA KALI dalam satu tabel fakta,
  masing-masing dengan peran (role) yang berbeda.

CONTOH PALING UMUM — dim_waktu dipakai berkali-kali:
  Sistem logistik pengiriman:
  fakta_pengiriman:
    kunci_waktu_pesan      → dim_waktu (tanggal pesanan dibuat)
    kunci_waktu_kirim      → dim_waktu (tanggal paket dikirim)
    kunci_waktu_terima     → dim_waktu (tanggal paket diterima)
    kunci_waktu_retur      → dim_waktu (tanggal retur jika ada)

  → Keempat FK semua merujuk ke DIM_WAKTU yang SAMA!
  → Tapi masing-masing memiliki makna/peran yang berbeda

CARA IMPLEMENTASI — menggunakan VIEW (alias):
  -- Buat alias view untuk setiap peran
  CREATE VIEW dim_waktu_pesan AS SELECT * FROM dim_waktu;
  CREATE VIEW dim_waktu_kirim AS SELECT * FROM dim_waktu;
  CREATE VIEW dim_waktu_terima AS SELECT * FROM dim_waktu;

  -- Query menggunakan alias view:
  SELECT
      w_pesan.tahun         AS tahun_pesanan,
      w_pesan.nama_bulan_id AS bulan_pesanan,
      w_terima.tahun        AS tahun_terima,
      DATEDIFF(f.kunci_waktu_terima, f.kunci_waktu_kirim) AS hari_kirim
  FROM fakta_pengiriman f
  JOIN dim_waktu_pesan  w_pesan  ON f.kunci_waktu_pesan  = w_pesan.kunci_waktu
  JOIN dim_waktu_terima w_terima ON f.kunci_waktu_terima = w_terima.kunci_waktu;

DEFINISI:
  Dimensi yang menggabungkan beberapa atribut flag/status berkardinasi rendah
  yang tidak cukup penting untuk menjadi dimensi tersendiri, tapi tidak
  elegan jika diletakkan langsung di tabel fakta.

MASALAH YANG DISELESAIKAN:
  Tabel fakta penjualan punya banyak flag kecil:
  is_transaksi_web, is_member, is_promo, is_bayar_dp, is_pengiriman_express
  
  Jika diletakkan di tabel fakta → 5 kolom tambahan yang membuat fakta "gemuk"
  Jika buat 5 dimensi terpisah → overhead JOIN yang tidak perlu
  
  SOLUSI → Gabungkan ke SATU "junk dimension":
  
  dim_tipe_transaksi:
  ┌───────────────────┬──────────────┬───────────┬────────────┬───────────────────┐
  │ kunci_tipe_trans  │ via_web      │ is_member │ ada_promo  │ is_express        │
  ├───────────────────┼──────────────┼───────────┼────────────┼───────────────────┤
  │ 1                 │ 1            │ 1         │ 1          │ 1                 │
  │ 2                 │ 1            │ 1         │ 1          │ 0                 │
  │ 3                 │ 1            │ 1         │ 0          │ 1                 │
  │ 4                 │ 1            │ 1         │ 0          │ 0                 │
  │ 5                 │ 1            │ 0         │ 1          │ 1                 │
  │ ...               │ ...          │ ...       │ ...        │ ...               │
  │ 16                │ 0            │ 0         │ 0          │ 0                 │
  └───────────────────┴──────────────┴───────────┴────────────┴───────────────────┘
  
  → 4 flag boolean = 2^4 = 16 kombinasi → 16 baris di dim_tipe_transaksi
  → Tabel fakta hanya butuh satu FK: kunci_tipe_trans
  → Analisis tetap bisa dilakukan: WHERE dim_tipe_trans.is_member = 1

                         ┌──────────────────┐
                         │   dim_waktu       │
                         │  ─────────────── │
                         │  kunci_waktu  PK  │
                         │  tanggal_penuh    │
                         │  tahun           │
                         │  nama_bulan_id   │
                         │  kuartal         │
                         │  is_weekend      │
                         │  ... (30+ kolom) │
                         └────────┬─────────┘
                                  │
          ┌──────────────┐        │        ┌──────────────────┐
          │ dim_pelanggan│        │        │   dim_produk      │
          │ ──────────── │        │        │  ──────────────── │
          │ kunci_pel. PK│        │        │  kunci_produk  PK │
          │ nama_lengkap │        │        │  nama_produk      │
          │ kota         │        │        │  kategori         │
          │ provinsi     │        │        │  subkategori      │
          │ segmen       │        │        │  teknik_pembuatan │
          │ kelompok_usia│        │        │  rentang_harga    │
          │ ...          │        │        │  ...              │
          └──────┬───────┘        │        └────────┬─────────┘
                 │                │                 │
                 └────────────────┼─────────────────┘
                                  │
                    ┌─────────────▼──────────────────┐
                    │       FAKTA_PENJUALAN           │
                    │  ─────────────────────────────  │
                    │  kunci_waktu          FK        │
                    │  kunci_pelanggan      FK        │
                    │  kunci_produk         FK        │
                    │  kunci_lokasi_kirim   FK        │
                    │  kunci_kanal          FK        │
                    │  id_pesanan           (degen.)  │
                    │  id_item_pesanan      (degen.)  │
                    │  ─────────────────────────────  │
                    │  qty                 measure    │
                    │  harga_satuan        measure    │
                    │  diskon              measure    │
                    │  revenue_bersih      measure    │
                    │  biaya_kirim         measure    │
                    └─────────┬──────────────────────┘
                              │
               ┌──────────────┴──────────────┐
               │                             │
    ┌──────────▼────────┐         ┌──────────▼──────────┐
    │  dim_lokasi_kirim  │         │     dim_kanal        │
    │  ──────────────── │         │  ────────────────── │
    │  kunci_lokasi   PK│         │  kunci_kanal      PK│
    │  kota_kirim       │         │  nama_kanal          │
    │  kabupaten_kota   │         │  tipe_kanal          │
    │  provinsi_kirim   │         │  platform            │
    │  pulau            │         │  is_online           │
    │  zona_pengiriman  │         │                     │
    └───────────────────┘         └─────────────────────┘

-- ============================================================
-- DATABASE WAREHOUSE TOKO BATIK ONLINE
-- Star Schema — Proses Bisnis: Penjualan
-- Dibuat untuk: Data Modelling MK — Pertemuan 12
-- ============================================================
 
-- Buat database warehouse (terpisah dari database OLTP)
CREATE DATABASE IF NOT EXISTS dw_batik_online
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;
 
USE dw_batik_online;
 
-- ──────────────────────────────────────────────────────────────
-- TABEL DIMENSI (harus dibuat SEBELUM tabel fakta)
-- ──────────────────────────────────────────────────────────────
 
-- 1. DIMENSI WAKTU
CREATE TABLE dim_waktu (
    kunci_waktu          INT UNSIGNED      NOT NULL,   -- Format YYYYMMDD
    tanggal_penuh        DATE              NOT NULL,
    hari_dalam_bulan     TINYINT UNSIGNED  NOT NULL,
    hari_dalam_tahun     SMALLINT UNSIGNED NOT NULL,
    nama_hari_en         VARCHAR(10)       NOT NULL,
    nama_hari_id         VARCHAR(10)       NOT NULL,
    nomor_hari_minggu    TINYINT UNSIGNED  NOT NULL,   -- ISO: Sen=1 Min=7
    is_weekend           TINYINT(1)        NOT NULL DEFAULT 0,
    is_hari_libur_nas    TINYINT(1)        NOT NULL DEFAULT 0,
    nama_hari_libur      VARCHAR(100)      NULL,
    minggu_dalam_tahun   TINYINT UNSIGNED  NOT NULL,
    bulan                TINYINT UNSIGNED  NOT NULL,
    nama_bulan_id        VARCHAR(15)       NOT NULL,
    nama_bulan_singkat   CHAR(3)           NOT NULL,
    kuartal              TINYINT UNSIGNED  NOT NULL,
    nama_kuartal         CHAR(2)           NOT NULL,
    semester             TINYINT UNSIGNED  NOT NULL,
    tahun                SMALLINT UNSIGNED NOT NULL,
    is_tahun_kabisat     TINYINT(1)        NOT NULL DEFAULT 0,
    tahun_semester       VARCHAR(15)       NOT NULL,
    CONSTRAINT pk_dim_waktu PRIMARY KEY (kunci_waktu),
    INDEX idx_tgl (tanggal_penuh),
    INDEX idx_tahun_bulan (tahun, bulan),
    INDEX idx_tahun_kuartal (tahun, kuartal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Waktu — pre-populated';
 
-- 2. DIMENSI PELANGGAN
CREATE TABLE dim_pelanggan (
    kunci_pelanggan      INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    id_pelanggan_src     INT UNSIGNED  NOT NULL,       -- natural key dari OLTP
    nama_lengkap         VARCHAR(150)  NOT NULL,
    jenis_kelamin        CHAR(1)       NULL,           -- 'L'/'P'
    kelompok_usia        VARCHAR(20)   NULL,
    kota                 VARCHAR(100)  NOT NULL,
    kabupaten_kota       VARCHAR(100)  NOT NULL,
    provinsi             VARCHAR(100)  NOT NULL,
    pulau                VARCHAR(50)   NOT NULL,
    wilayah              VARCHAR(50)   NOT NULL,
    tgl_daftar           DATE          NOT NULL,
    tahun_daftar         SMALLINT      NOT NULL,
    segmen_pelanggan     VARCHAR(30)   NOT NULL DEFAULT 'Baru',
    -- Kolom SCD Type 2 (dibahas detail di Pertemuan 13)
    berlaku_dari         DATE          NOT NULL,
    berlaku_sampai       DATE          NOT NULL DEFAULT '9999-12-31',
    is_current           TINYINT(1)    NOT NULL DEFAULT 1,
    CONSTRAINT pk_dim_pelanggan PRIMARY KEY (kunci_pelanggan),
    INDEX idx_natural_key_pel (id_pelanggan_src),
    INDEX idx_segmen (segmen_pelanggan),
    INDEX idx_current_pel (is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Pelanggan — dengan kolom SCD untuk P13';
 
-- 3. DIMENSI PRODUK
CREATE TABLE dim_produk (
    kunci_produk         INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    id_produk_src        INT UNSIGNED  NOT NULL,       -- natural key dari OLTP
    nama_produk          VARCHAR(200)  NOT NULL,
    subkategori          VARCHAR(100)  NOT NULL,
    kategori             VARCHAR(100)  NOT NULL,
    kelompok_kategori    VARCHAR(100)  NOT NULL,
    nama_supplier        VARCHAR(150)  NOT NULL,
    kota_supplier        VARCHAR(100)  NOT NULL,
    asal_produk          VARCHAR(30)   NOT NULL DEFAULT 'Lokal',
    harga_jual           DECIMAL(15,2) NOT NULL,
    rentang_harga        VARCHAR(30)   NOT NULL,       -- 'Budget'/'Mid'/'Premium'
    bahan_utama          VARCHAR(100)  NULL,
    motif                VARCHAR(100)  NULL,
    teknik_pembuatan     VARCHAR(50)   NULL,           -- 'Tulis'/'Cap'/'Printing'
    is_aktif             TINYINT(1)    NOT NULL DEFAULT 1,
    -- Kolom SCD
    berlaku_dari         DATE          NOT NULL,
    berlaku_sampai       DATE          NOT NULL DEFAULT '9999-12-31',
    is_current           TINYINT(1)    NOT NULL DEFAULT 1,
    CONSTRAINT pk_dim_produk PRIMARY KEY (kunci_produk),
    INDEX idx_natural_key_prod (id_produk_src),
    INDEX idx_kategori (kategori),
    INDEX idx_teknik (teknik_pembuatan),
    INDEX idx_current_prod (is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Produk — denormalized dari OLTP';
 
-- 4. DIMENSI LOKASI PENGIRIMAN
CREATE TABLE dim_lokasi_kirim (
    kunci_lokasi         INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    kota_kirim           VARCHAR(100)  NOT NULL,
    kabupaten_kota       VARCHAR(100)  NOT NULL,
    provinsi_kirim       VARCHAR(100)  NOT NULL,
    pulau                VARCHAR(50)   NOT NULL,
    wilayah_kirim        VARCHAR(50)   NOT NULL,       -- 'Jabodetabek', 'Jawa Tengah', dll.
    zona_pengiriman      VARCHAR(20)   NOT NULL,       -- 'Zona 1', 'Zona 2', 'Zona 3'
    estimasi_hari_kirim  TINYINT       NOT NULL,       -- estimasi hari pengiriman reguler
    CONSTRAINT pk_dim_lokasi PRIMARY KEY (kunci_lokasi),
    INDEX idx_provinsi (provinsi_kirim),
    UNIQUE INDEX idx_kota_prov (kota_kirim, provinsi_kirim)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Lokasi Pengiriman';
 
-- 5. DIMENSI KANAL PENJUALAN
CREATE TABLE dim_kanal (
    kunci_kanal          TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nama_kanal           VARCHAR(100)     NOT NULL,    -- 'Website', 'Tokopedia', dst.
    tipe_kanal           VARCHAR(50)      NOT NULL,    -- 'Marketplace', 'Direct', 'Sosmed'
    platform             VARCHAR(50)      NOT NULL,    -- 'Web', 'Mobile App', 'Offline'
    is_online            TINYINT(1)       NOT NULL DEFAULT 1,
    CONSTRAINT pk_dim_kanal PRIMARY KEY (kunci_kanal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Dimensi Kanal Penjualan';
 
-- ──────────────────────────────────────────────────────────────
-- TABEL FAKTA
-- ──────────────────────────────────────────────────────────────
 
-- FAKTA PENJUALAN
-- Grain: Satu baris = satu item baris (line item) dalam satu pesanan
-- Setiap baris = "Pada tanggal X, Pelanggan Y membeli Produk Z via Kanal K
--                ke Kota L, sebanyak qty unit, dengan harga P, diskon D"
CREATE TABLE fakta_penjualan (
    -- KUNCI ASING ke DIMENSI
    kunci_waktu          INT UNSIGNED      NOT NULL,
    kunci_pelanggan      INT UNSIGNED      NOT NULL,
    kunci_produk         INT UNSIGNED      NOT NULL,
    kunci_lokasi         INT UNSIGNED      NOT NULL,
    kunci_kanal          TINYINT UNSIGNED  NOT NULL,
 
    -- DEGENERATE DIMENSIONS (no separate dimension table)
    id_pesanan           INT UNSIGNED      NOT NULL,   -- no. pesanan dari OLTP
    id_item_pesanan      INT UNSIGNED      NOT NULL,   -- no. baris item dari OLTP
 
    -- MEASURES
    qty                  SMALLINT UNSIGNED NOT NULL DEFAULT 1,   -- additive
    harga_satuan         DECIMAL(15,2)     NOT NULL,             -- non-additive
    diskon               DECIMAL(15,2)     NOT NULL DEFAULT 0.00,-- additive
    revenue_bersih       DECIMAL(15,2)     NOT NULL,             -- additive
    biaya_kirim          DECIMAL(15,2)     NOT NULL DEFAULT 0.00,-- additive (per item)
    hpp_satuan           DECIMAL(15,2)     NULL,                 -- non-additive (harga pokok)
    laba_kotor           DECIMAL(15,2)     NULL,                 -- additive
 
    -- PRIMARY KEY (composite)
    CONSTRAINT pk_fakta_penjualan
        PRIMARY KEY (kunci_waktu, kunci_pelanggan, kunci_produk,
                     id_pesanan, id_item_pesanan),
 
    -- FOREIGN KEYS ke dimensi
    CONSTRAINT fk_fp_waktu
        FOREIGN KEY (kunci_waktu)     REFERENCES dim_waktu (kunci_waktu),
    CONSTRAINT fk_fp_pelanggan
        FOREIGN KEY (kunci_pelanggan) REFERENCES dim_pelanggan (kunci_pelanggan),
    CONSTRAINT fk_fp_produk
        FOREIGN KEY (kunci_produk)    REFERENCES dim_produk (kunci_produk),
    CONSTRAINT fk_fp_lokasi
        FOREIGN KEY (kunci_lokasi)    REFERENCES dim_lokasi_kirim (kunci_lokasi),
    CONSTRAINT fk_fp_kanal
        FOREIGN KEY (kunci_kanal)     REFERENCES dim_kanal (kunci_kanal),
 
    -- INDEX tambahan untuk performa query umum
    INDEX idx_fp_waktu_produk (kunci_waktu, kunci_produk),
    INDEX idx_fp_waktu_pelanggan (kunci_waktu, kunci_pelanggan),
    INDEX idx_fp_pesanan (id_pesanan)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Fakta Penjualan — Grain: satu baris per item pesanan';

-- ============================================================
-- QUERY ANALITIK — Demonstrasi kekuatan star schema
-- Semua query ini berlari jauh lebih cepat daripada di OLTP
-- ============================================================
 
-- Query 1: Revenue per Kategori per Bulan (laporan paling umum)
SELECT
    w.tahun,
    w.nama_bulan_id,
    w.bulan,
    p.kategori,
    SUM(f.revenue_bersih)    AS total_revenue,
    SUM(f.qty)               AS total_unit_terjual,
    COUNT(DISTINCT f.id_pesanan) AS jumlah_pesanan
FROM fakta_penjualan f
JOIN dim_waktu    w ON f.kunci_waktu    = w.kunci_waktu
JOIN dim_produk   p ON f.kunci_produk   = p.kunci_produk
WHERE w.tahun = 2024
GROUP BY w.tahun, w.bulan, w.nama_bulan_id, p.kategori
ORDER BY w.bulan, total_revenue DESC;
 
-- ──────────────────────────────────────────────────────────────
 
-- Query 2: Analisis Year-Over-Year (YoY) Revenue per Produk
SELECT
    p.nama_produk,
    p.kategori,
    SUM(CASE WHEN w.tahun = 2023 THEN f.revenue_bersih ELSE 0 END) AS revenue_2023,
    SUM(CASE WHEN w.tahun = 2024 THEN f.revenue_bersih ELSE 0 END) AS revenue_2024,
    ROUND(
        (SUM(CASE WHEN w.tahun = 2024 THEN f.revenue_bersih ELSE 0 END)
         - SUM(CASE WHEN w.tahun = 2023 THEN f.revenue_bersih ELSE 0 END))
        / NULLIF(SUM(CASE WHEN w.tahun = 2023 THEN f.revenue_bersih ELSE 0 END), 0)
        * 100, 2
    ) AS pct_pertumbuhan_yoy
FROM fakta_penjualan f
JOIN dim_waktu  w ON f.kunci_waktu  = w.kunci_waktu
JOIN dim_produk p ON f.kunci_produk = p.kunci_produk
WHERE w.tahun IN (2023, 2024)
GROUP BY p.kunci_produk, p.nama_produk, p.kategori
HAVING revenue_2023 > 0
ORDER BY pct_pertumbuhan_yoy DESC
LIMIT 20;
 
-- ──────────────────────────────────────────────────────────────
 
-- Query 3: Segmentasi Pelanggan — RFM Sederhana (Recency, Frequency, Monetary)
SELECT
    pel.segmen_pelanggan,
    COUNT(DISTINCT f.kunci_pelanggan)  AS jumlah_pelanggan,
    ROUND(AVG(rev_per_pelanggan.total_revenue), 0) AS avg_revenue_per_pelanggan,
    ROUND(AVG(freq_per_pelanggan.jumlah_pesanan), 1) AS avg_frekuensi_pesanan
FROM dim_pelanggan pel
JOIN (
    SELECT kunci_pelanggan, SUM(revenue_bersih) AS total_revenue
    FROM fakta_penjualan
    GROUP BY kunci_pelanggan
) rev_per_pelanggan ON pel.kunci_pelanggan = rev_per_pelanggan.kunci_pelanggan
JOIN (
    SELECT kunci_pelanggan, COUNT(DISTINCT id_pesanan) AS jumlah_pesanan
    FROM fakta_penjualan
    GROUP BY kunci_pelanggan
) freq_per_pelanggan ON pel.kunci_pelanggan = freq_per_pelanggan.kunci_pelanggan
JOIN fakta_penjualan f ON pel.kunci_pelanggan = f.kunci_pelanggan
WHERE pel.is_current = 1
GROUP BY pel.segmen_pelanggan
ORDER BY avg_revenue_per_pelanggan DESC;
 
-- ──────────────────────────────────────────────────────────────
 
-- Query 4: Analisis Pola Waktu — Weekday vs Weekend dan Hari Libur
SELECT
    CASE
        WHEN w.is_hari_libur_nas = 1 THEN 'Hari Libur Nasional'
        WHEN w.is_weekend        = 1 THEN 'Weekend'
        ELSE 'Hari Kerja'
    END AS tipe_hari,
    COUNT(DISTINCT f.id_pesanan)    AS jumlah_pesanan,
    SUM(f.revenue_bersih)           AS total_revenue,
    ROUND(AVG(f.revenue_bersih), 0) AS avg_revenue_per_item,
    ROUND(SUM(f.revenue_bersih) / COUNT(DISTINCT f.id_pesanan), 0)
                                    AS avg_revenue_per_pesanan
FROM fakta_penjualan f
JOIN dim_waktu w ON f.kunci_waktu = w.kunci_waktu
WHERE w.tahun = 2024
GROUP BY tipe_hari
ORDER BY total_revenue DESC;
 
-- ──────────────────────────────────────────────────────────────
 
-- Query 5: Top 10 Kota Pengiriman berdasarkan Revenue (Drill-down ke Provinsi)
SELECT
    l.provinsi_kirim,
    l.kota_kirim,
    COUNT(DISTINCT f.kunci_pelanggan) AS jumlah_pelanggan_unik,
    SUM(f.revenue_bersih)             AS total_revenue,
    SUM(f.qty)                        AS total_unit,
    ROUND(SUM(f.revenue_bersih) / COUNT(DISTINCT f.kunci_pelanggan), 0) AS revenue_per_pelanggan
FROM fakta_penjualan f
JOIN dim_lokasi_kirim l ON f.kunci_lokasi = l.kunci_lokasi
JOIN dim_waktu        w ON f.kunci_waktu  = w.kunci_waktu
WHERE w.tahun = 2024
GROUP BY l.provinsi_kirim, l.kota_kirim
ORDER BY total_revenue DESC
LIMIT 10;

ANTI-PATTERN 1: Grain yang Tidak Konsisten
  ❌ Salah: Satu tabel fakta berisi baris dengan grain berbeda
     (sebagian per pesanan, sebagian per item)
  ✓ Benar: Satu grain yang jelas dan konsisten di seluruh tabel

ANTI-PATTERN 2: Memasukkan Data Deskriptif ke Tabel Fakta
  ❌ Salah: fakta_penjualan berisi nama_pelanggan, nama_produk, kota
  ✓ Benar: Data deskriptif masuk ke dimensi, fakta hanya FK dan measures

ANTI-PATTERN 3: Normalisasi Dimensi (Snowflake tanpa tujuan)
  ❌ Salah: dim_produk punya FK ke dim_kategori punya FK ke dim_kategori_induk
  ✓ Benar: dim_produk memiliki kolom subkategori, kategori, kelompok_kategori
    (semua di-flatten dalam satu tabel)

ANTI-PATTERN 4: Lupa Menyertakan Natural Key di Dimensi
  ❌ Salah: dim_pelanggan hanya punya kunci_pelanggan (surrogate), tanpa id asli
  ✓ Benar: dim_pelanggan punya kunci_pelanggan (PK) DAN id_pelanggan_src

ANTI-PATTERN 5: Grain Terlalu Tinggi / Pre-Aggregated Grain
  ❌ Salah: "Satu baris per bulan per produk" (sudah aggregate)
  ✓ Benar: "Satu baris per transaksi" — agregasi dilakukan oleh query,
    bukan oleh desain grain

┌─────────────────────────────────────────────────────────────┐
│  KIMBALL FOUR-STEP DIMENSIONAL DESIGN PROCESS              │
│                                                             │
│  LANGKAH 1: PILIH PROSES BISNIS                            │
│  ─────────────────────────────────────────────             │
│  □ Tentukan satu proses bisnis yang jelas                  │
│  □ Proses = aktivitas yang menghasilkan data terukur       │
│  □ Hindari kata abstrak: "analisis", "monitoring"          │
│  □ Lebih baik: "penjualan", "peminjaman", "kunjungan"      │
│                                                             │
│  LANGKAH 2: DEKLARASIKAN GRAIN                             │
│  ─────────────────────────────────────────────             │
│  □ Tulis grain dalam SATU KALIMAT                          │
│  □ Pastikan menyebut level paling atomik                   │
│  □ Contoh valid: "Satu baris per item baris (line item)    │
│    dalam satu pesanan pelanggan pada satu tanggal"         │
│  □ Semua dimensi dan measures harus sesuai grain           │
│                                                             │
│  LANGKAH 3: IDENTIFIKASI DIMENSI                           │
│  ─────────────────────────────────────────────             │
│  □ Jawab: Siapa? Apa? Kapan? Di mana? Bagaimana?           │
│  □ dim_waktu SELALU ada                                     │
│  □ Setiap dimensi harus konsisten dengan grain             │
│  □ Gunakan surrogate key sebagai PK                        │
│  □ Simpan natural key sebagai kolom terpisah               │
│  □ Denormalize — flatten semua hierarki!                   │
│  □ Tambahkan atribut derivatif (kelompok usia, rentang     │
│    harga, dll.)                                            │
│                                                             │
│  LANGKAH 4: IDENTIFIKASI MEASURES                          │
│  ─────────────────────────────────────────────             │
│  □ Hanya ukuran numerik pada level grain yang ditetapkan   │
│  □ Klasifikasikan: additive / semi-additive / non-additive │
│  □ Prioritaskan measures yang additive                     │
│  □ Identifikasi degenerate dimensions                      │
│  □ Verifikasi: setiap measure ada di SETIAP baris?         │
└─────────────────────────────────────────────────────────────┘

SEBELUM FINALISASI DESAIN, PERIKSA SEMUA ITEM BERIKUT:

GRAIN:
  □ Grain dideklarasikan dalam satu kalimat yang jelas
  □ Grain adalah tingkat paling atomik yang masuk akal bisnis
  □ Semua dimensi yang dipilih konsisten dengan grain
  □ Semua measures ada pada tingkat grain yang sama

DIMENSI:
  □ Minimal satu dimensi waktu (dim_waktu) ada
  □ Setiap dimensi punya surrogate key (INT AUTO_INCREMENT) sebagai PK
  □ Natural key dari sumber disimpan sebagai kolom terpisah (bukan PK)
  □ Dimensi di-denormalize (tidak ada FK dari dimensi ke tabel lain)
  □ Setiap dimensi punya minimal 5–8 atribut deskriptif
  □ Atribut derivatif (kelompok usia, rentang harga, dll.) sudah ditambahkan
  □ Kolom SCD disiapkan (berlaku_dari, berlaku_sampai, is_current)
    untuk dimensi yang nilainya bisa berubah

TABEL FAKTA:
  □ Berisi FK ke semua dimensi yang relevan
  □ Berisi degenerate dimensions yang diperlukan
  □ Measures sudah teridentifikasi dan diklasifikasikan (add/semi/non-add)
  □ Tidak ada data deskriptif (teks yang seharusnya ada di dimensi)
  □ PK adalah composite dari FK-FK yang ada
  □ Index tambahan pada FK yang sering digunakan untuk filter

IMPLEMENTASI SQL:
  □ DDL dimensi dibuat SEBELUM DDL fakta
  □ FK constraint ke dimensi didefinisikan dengan benar
  □ Data dummy cukup representatif (minimal 10 baris per dimensi)
  □ Data dummy di fakta mencakup berbagai kombinasi dimensi
  □ Query analitik berjalan tanpa error

REVIEW AKHIR:
  □ Diagram konsisten dengan DDL
  □ Naming convention konsisten (kunci_xxx untuk PK/FK, dst.)
  □ Komentar SQL menjelaskan tujuan setiap tabel dan kolom kritis

-- ============================================================
-- TEMPLATE STAR SCHEMA GENERIK
-- Ganti [NAMA_DOMAIN] dengan nama domain kalian
-- Ganti [PROSES_BISNIS] dengan proses bisnis yang dimodelkan
-- ============================================================
 
-- 1. Buat database warehouse
CREATE DATABASE IF NOT EXISTS dw_[nama_domain]
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;
 
USE dw_[nama_domain];
 
-- 2. Dimensi Waktu (copy dari template P12 — selalu sama)
--    [lihat DDL dim_waktu di bagian C.7.2]
 
-- 3. Dimensi Utama 1 — ganti sesuai domain
CREATE TABLE dim_[nama_entitas_1] (
    kunci_[entitas_1]    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    id_[entitas_1]_src   INT UNSIGNED  NOT NULL,  -- natural key
    -- atribut deskriptif minimal 6 kolom:
    [atribut_1]          VARCHAR(?)    NOT NULL,
    [atribut_2]          VARCHAR(?)    NOT NULL,
    -- ... tambahkan atribut deskriptif yang relevan
    -- kolom SCD (untuk dimensi yang bisa berubah):
    berlaku_dari         DATE          NOT NULL,
    berlaku_sampai       DATE          NOT NULL DEFAULT '9999-12-31',
    is_current           TINYINT(1)    NOT NULL DEFAULT 1,
    CONSTRAINT pk_dim_[entitas_1] PRIMARY KEY (kunci_[entitas_1]),
    INDEX idx_nk_[entitas_1] (id_[entitas_1]_src)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 4. [Buat dimensi lain dengan pola yang sama]
 
-- 5. Tabel Fakta (buat TERAKHIR setelah semua dimensi ada)
-- Grain: [tuliskan grain di sini sebagai komentar!]
CREATE TABLE fakta_[proses_bisnis] (
    -- FK ke semua dimensi:
    kunci_waktu          INT UNSIGNED  NOT NULL,
    kunci_[entitas_1]    INT UNSIGNED  NOT NULL,
    kunci_[entitas_2]    INT UNSIGNED  NOT NULL,
    -- degenerate dimensions (jika ada):
    id_[dokumen]         INT UNSIGNED  NOT NULL,
    -- measures:
    [measure_1]          DECIMAL(15,2) NOT NULL,   -- additive
    [measure_2]          DECIMAL(15,2) NOT NULL,   -- additive
    [measure_3]          DECIMAL(15,2) NULL,        -- non-additive — AVG saja
    -- PK composite:
    CONSTRAINT pk_fakta_[proses_bisnis]
        PRIMARY KEY (kunci_waktu, kunci_[entitas_1], kunci_[entitas_2], id_[dokumen]),
    -- FK constraints:
    CONSTRAINT fk_[proses]_waktu
        FOREIGN KEY (kunci_waktu)       REFERENCES dim_waktu (kunci_waktu),
    CONSTRAINT fk_[proses]_[entitas_1]
        FOREIGN KEY (kunci_[entitas_1]) REFERENCES dim_[entitas_1] (kunci_[entitas_1])
    -- [tambahkan FK untuk setiap dimensi]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Fakta [Proses Bisnis] — Grain: [DEKLARASIKAN GRAIN DI SINI]';

LEMBAR KERJA DESAIN STAR SCHEMA KELOMPOK
Kelompok : _____ | Domain   : ___________________
Anggota  : _______________________________________
Tanggal  : ___________________

LANGKAH 1 — PROSES BISNIS:
Proses yang dimodelkan:
_______________________________________________

LANGKAH 2 — GRAIN:
Grain (satu kalimat):
"Satu baris mewakili ___________________________
 _____________________________________________"

Mengapa grain ini tepat?
_______________________________________________

LANGKAH 3 — DIMENSI:

Dimensi 1: dim_____________
  Surrogate Key: _______________________
  Natural Key  : _______________________
  Atribut:
  1. _____________ (tipe: _______)  Deskripsi: _______
  2. _____________ (tipe: _______)  Deskripsi: _______
  3. _____________ (tipe: _______)  Deskripsi: _______
  4. _____________ (tipe: _______)  Deskripsi: _______
  5. _____________ (tipe: _______)  Deskripsi: _______
  6. _____________ (tipe: _______)  Deskripsi: _______

Dimensi 2: dim_____________
  [isi dengan format yang sama]

Dimensi 3: dim_____________
  [isi dengan format yang sama]

Dimensi 4: dim_____________
  [isi dengan format yang sama]

dim_waktu: [selalu ada — gunakan template standar]

LANGKAH 4 — TABEL FAKTA:

Nama tabel: fakta_____________

Foreign Keys:
  □ kunci_waktu → dim_waktu
  □ kunci________ → dim_______
  □ kunci________ → dim_______
  □ kunci________ → dim_______

Degenerate Dimensions:
  □ id_______________ (tidak punya tabel dimensi sendiri karena: _______)

Measures:
┌─────────────────┬──────────────┬──────────────────────────────┐
│ Nama Kolom      │ Tipe         │ Alasan                       │
├─────────────────┼──────────────┼──────────────────────────────┤
│                 │ □ Additive   │                              │
│                 │ □ Semi-add   │                              │
│                 │ □ Non-add    │                              │
├─────────────────┼──────────────┼──────────────────────────────┤
│                 │ □ Additive   │                              │
│                 │ □ Semi-add   │                              │
│                 │ □ Non-add    │                              │
├─────────────────┼──────────────┼──────────────────────────────┤
│                 │ □ Additive   │                              │
│                 │ □ Semi-add   │                              │
│                 │ □ Non-add    │                              │
└─────────────────┴──────────────┴──────────────────────────────┘

PERTANYAAN ANALITIK yang bisa dijawab schema ini:
1. _____________________________________________
2. _____________________________________________