-- Dari DDL P12 — kolom yang belum dijelaskan penuh:
berlaku_dari    DATE    NOT NULL,
berlaku_sampai  DATE    NOT NULL DEFAULT '9999-12-31',
is_current      TINYINT(1) NOT NULL DEFAULT 1,

APA YANG SUDAH KITA KUASAI (P12):
  ✓ Kimball Four-Step Process
  ✓ Grain — keputusan paling fundamental
  ✓ Additive / semi-additive / non-additive measures
  ✓ Tabel dimensi: wide, denormalized, surrogate key
  ✓ dim_waktu dan cara populate-nya
  ✓ Variasi dimensi khusus (conformed, role-playing, junk, degenerate)
  ✓ Star schema DDL dan query analitik

PERTANYAAN YANG BELUM TERJAWAB SEPENUHNYA:
  ✗ Apakah dimensi selalu harus di-denormalize?
    → Kapan normalisasi dimensi (snowflake) lebih masuk akal?
  ✗ Kolom berlaku_dari, berlaku_sampai, is_current — bagaimana cara kerjanya?
    → Apa yang terjadi saat atribut dimensi berubah di dunia nyata?
  ✗ Strategi apa yang tepat untuk menangani perubahan dimensi yang berbeda-beda?

→ Itulah fokus Pertemuan 13!

Seorang data entry staff salah mengetik nama pelanggan:
  Sebelum  : "Budhi Santosa"
  Sesudah  : "Budi Santoso"  (koreksi typo)

Pertanyaan:
→ Apakah kita perlu menyimpan versi "Budhi Santosa" sebagai riwayat?
→ Atau cukup ditimpa dengan yang benar?

Pelanggan "Siti Rahayu" pindah kota:
  Sebelum  : kota = "Semarang"   (berlaku Jan 2021 – Des 2023)
  Sesudah  : kota = "Jakarta"    (berlaku Jan 2024 – sekarang)

Pertanyaan:
→ Jika kita timpa saja: laporan "Revenue dari Semarang tahun 2022"
  apakah masih akurat? Atau Siti sekarang "dianggap" sudah di Jakarta di 2022?
→ Apa dampaknya terhadap laporan ekspansi kota yang sudah dibuat tahun 2022?

Produk "Batik Tulis Pekalongan Premium" berpindah kategori:
  Sebelum  : kategori = "Batik Tulis"      (2021–2023)
  Sesudah  : kategori = "Koleksi Premium"  (2024–sekarang)

Pertanyaan:
→ Jika manajer minta laporan: "Revenue dari kategori Batik Tulis tahun 2022",
  apakah produk ini seharusnya masuk ke sana?
→ Bagaimana agar analisis tren per kategori tidak menjadi misleading?

SNOWFLAKE SCHEMA adalah perluasan (extension) dari star schema
di mana tabel dimensi DINORMALISASI — hierarki di dalam dimensi
dipecah menjadi tabel-tabel sub-dimensi tersendiri.

Disebut "snowflake" karena bentuk diagramnya menyerupai kepingan
salju — satu pusat (tabel fakta) dengan cabang yang semakin
memanjang dan bercabang (dimensi → sub-dimensi → sub-sub-dimensi).

STAR SCHEMA — dim_produk denormalized:

             fakta_penjualan
                   │
                   │  FK
                   ▼
           ┌──────────────────────────────────┐
           │          dim_produk               │
           │ kunci_produk        (PK)          │
           │ id_produk_src                     │
           │ nama_produk                       │
           │ subkategori         ◄──── semua   │
           │ kategori            ◄──── dalam   │
           │ kelompok_kategori   ◄──── satu    │
           │ nama_supplier       ◄──── tabel!  │
           │ kota_supplier                     │
           │ teknik_pembuatan                  │
           └──────────────────────────────────┘

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

SNOWFLAKE SCHEMA — dim_produk dinormalisasi:

             fakta_penjualan
                   │
                   │  FK
                   ▼
           ┌────────────────┐
           │   dim_produk    │
           │ kunci_produk PK │
           │ nama_produk     │
           │ kunci_subkat FK │──►┌──────────────────┐
           │ kunci_supplier FK│   │  dim_subkategori  │
           │ teknik_pembuatan│   │ kunci_subkat    PK│
           └────────────────┘   │ nama_subkategori  │
                                 │ kunci_kategori FK │──►┌──────────────────┐
                                 └──────────────────┘   │  dim_kategori     │
                                                         │ kunci_kategori PK │
                             ┌──────────────┐            │ nama_kategori     │
                             │ dim_supplier  │            │ kunci_kelompok FK │──►┌────────────────┐
                             │ kunci_sup  PK │            └──────────────────┘   │ dim_kelompok   │
                             │ nama_supplier │                                    │ kunci_kel   PK │
                             │ kota_supplier │                                    │ nama_kelompok  │
                             └──────────────┘                                    └────────────────┘

Struktur "snowflake" terlihat jelas:
fakta → dim_produk → dim_subkategori → dim_kategori → dim_kelompok
                   → dim_supplier

-- ============================================================
-- SNOWFLAKE SCHEMA — Hierarki Kategori Produk
-- Perhatikan: setiap level hierarki jadi tabel tersendiri
-- ============================================================
 
-- Level 3: Kelompok Kategori (paling tinggi)
CREATE TABLE dim_kelompok_kategori (
    kunci_kelompok   TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nama_kelompok    VARCHAR(100)     NOT NULL,
    deskripsi        VARCHAR(300)     NULL,
    CONSTRAINT pk_dim_kelompok PRIMARY KEY (kunci_kelompok)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- Level 2: Kategori
CREATE TABLE dim_kategori (
    kunci_kategori   SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    kunci_kelompok   TINYINT UNSIGNED  NOT NULL,            -- FK ke level atas
    nama_kategori    VARCHAR(100)      NOT NULL,
    deskripsi        VARCHAR(300)      NULL,
    CONSTRAINT pk_dim_kategori PRIMARY KEY (kunci_kategori),
    CONSTRAINT fk_kat_kelompok
        FOREIGN KEY (kunci_kelompok)
        REFERENCES dim_kelompok_kategori (kunci_kelompok)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- Level 1: Sub-kategori
CREATE TABLE dim_subkategori (
    kunci_subkategori SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    kunci_kategori    SMALLINT UNSIGNED NOT NULL,           -- FK ke level atas
    nama_subkategori  VARCHAR(100)      NOT NULL,
    CONSTRAINT pk_dim_subkat PRIMARY KEY (kunci_subkategori),
    CONSTRAINT fk_subkat_kategori
        FOREIGN KEY (kunci_kategori)
        REFERENCES dim_kategori (kunci_kategori)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- Supplier (dimensi terpisah karena bukan hierarki produk)
CREATE TABLE dim_supplier (
    kunci_supplier   SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    nama_supplier    VARCHAR(150)      NOT NULL,
    kota_supplier    VARCHAR(100)      NOT NULL,
    provinsi         VARCHAR(100)      NOT NULL,
    CONSTRAINT pk_dim_supplier PRIMARY KEY (kunci_supplier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- dim_produk (SNOWFLAKE VERSION) — lebih ramping, tapi punya banyak FK
CREATE TABLE dim_produk_snowflake (
    kunci_produk       INT UNSIGNED      NOT NULL AUTO_INCREMENT,
    id_produk_src      INT UNSIGNED      NOT NULL,
    nama_produk        VARCHAR(200)      NOT NULL,
    kunci_subkategori  SMALLINT UNSIGNED NOT NULL,  -- FK ke dim_subkategori
    kunci_supplier     SMALLINT UNSIGNED NOT NULL,  -- FK ke dim_supplier
    harga_jual         DECIMAL(15,2)     NOT NULL,
    teknik_pembuatan   VARCHAR(50)       NULL,
    bahan_utama        VARCHAR(100)      NULL,
    motif              VARCHAR(100)      NULL,
    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_sf PRIMARY KEY (kunci_produk),
    CONSTRAINT fk_prod_subkat
        FOREIGN KEY (kunci_subkategori)
        REFERENCES dim_subkategori (kunci_subkategori),
    CONSTRAINT fk_prod_supplier
        FOREIGN KEY (kunci_supplier)
        REFERENCES dim_supplier (kunci_supplier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ──────────────────────────────────────────────────────────────
-- DAMPAK KE QUERY — perhatikan perbedaan jumlah JOIN!
-- ──────────────────────────────────────────────────────────────
 
-- STAR SCHEMA (dim_produk denormalized):
-- Revenue per kategori — hanya butuh 2 JOIN
SELECT p.kategori, SUM(f.revenue_bersih) AS total_revenue
FROM fakta_penjualan f
JOIN dim_produk p ON f.kunci_produk = p.kunci_produk
JOIN dim_waktu  w ON f.kunci_waktu  = w.kunci_waktu
WHERE w.tahun = 2024
GROUP BY p.kategori;
 
-- SNOWFLAKE SCHEMA (dim_produk_snowflake):
-- Revenue per kategori — butuh 4 JOIN
SELECT k.nama_kategori, SUM(f.revenue_bersih) AS total_revenue
FROM fakta_penjualan f
JOIN dim_produk_snowflake p  ON f.kunci_produk       = p.kunci_produk
JOIN dim_subkategori      sk ON p.kunci_subkategori   = sk.kunci_subkategori
JOIN dim_kategori          k ON sk.kunci_kategori     = k.kunci_kategori
JOIN dim_waktu             w ON f.kunci_waktu         = w.kunci_waktu
WHERE w.tahun = 2024
GROUP BY k.nama_kategori;
-- → Query lebih kompleks, JOIN lebih banyak, risiko lebih tinggi

┌──────────────────────────┬──────────────────────────────┬──────────────────────────────┐
│ Dimensi Perbandingan     │ STAR SCHEMA                   │ SNOWFLAKE SCHEMA              │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ STRUKTUR DIMENSI         │ Denormalized (1 tabel per     │ Normalized (banyak tabel      │
│                          │ dimensi, semua atribut flat)  │ per hierarki dimensi)         │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ JUMLAH JOIN PER QUERY    │ Sedikit: 1 hop fact → dim    │ Banyak: fact → dim → sub-dim │
│                          │ (mudah diprediksi dan debug)  │ (bertambah setiap level)      │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ PERFORMA QUERY           │ ✓ Lebih cepat                 │ ✗ Lebih lambat (lebih JOIN)  │
│ (SELECT analitik)        │ Optimizer mudah memprediksi   │ Lebih banyak intermediate     │
│                          │ join plan                     │ result set                    │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ REDUNDANSI STORAGE       │ ✗ Lebih besar                 │ ✓ Lebih kecil                 │
│                          │ (nama kategori diulang di     │ (nama kategori 1x di tabel    │
│                          │ setiap baris dim_produk)      │ dim_kategori)                 │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ PEMELIHARAAN DIMENSI     │ ✗ Agak lebih sulit            │ ✓ Lebih mudah                 │
│ (UPDATE nama kategori)   │ Harus UPDATE banyak baris di  │ UPDATE 1 baris di dim_kategori│
│                          │ dim_produk                    │ → otomatis berlaku semua      │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ KEMUDAHAN DIPAHAMI       │ ✓ Intuitif                    │ ✗ Lebih kompleks              │
│ oleh Business User       │ Analyst/manajer mudah         │ Lebih sulit dipahami tanpa    │
│                          │ memahami struktur             │ dokumentasi skema             │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ KOMPATIBILITAS BI TOOLS  │ ✓ Sangat baik                 │ ✗ Kurang baik                 │
│ (Tableau, Power BI, dll) │ Sebagian besar BI tools       │ Banyak BI tools kesulitan     │
│                          │ dirancang untuk star schema   │ handle multi-level join       │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ KONSISTENSI NILAI        │ ✗ Risiko inkonsistensi        │ ✓ Konsistensi terjamin        │
│ DIMENSI                  │ Update nama kategori harus    │ Satu sumber kebenaran untuk   │
│                          │ ke banyak baris (error-prone) │ setiap nilai dimensi          │
├──────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ KONTEKS PENGGUNAAN       │ Default untuk hampir semua    │ Gunakan hanya jika ada alasan │
│ YANG DIREKOMENDASIKAN    │ proyek data warehouse         │ spesifik yang kuat            │
└──────────────────────────┴──────────────────────────────┴──────────────────────────────┘

PANDUAN PENGAMBILAN KEPUTUSAN:

Mulai dari pertanyaan ini:

1. Apakah storage adalah kendala serius?
   TIDAK → Pilih STAR (performa lebih penting dari storage di DW modern)
   YA    → Pertimbangkan snowflake untuk dimensi yang sangat besar

2. Apakah dimensi memiliki hierarki yang SANGAT dalam (4+ level)?
   TIDAK → Pilih STAR (flatten saja)
   YA    → Snowflake bisa dipertimbangkan untuk hierarki tersebut saja

3. Apakah nilai di level hierarki berubah sangat sering?
   TIDAK → STAR (denormalized, SCD handle perubahan)
   YA    → Snowflake memudahkan UPDATE satu baris di sub-dimensi

4. Apakah BI tools yang digunakan mendukung snowflake dengan baik?
   YA    → Snowflake bisa dipertimbangkan
   TIDAK → Wajib STAR

5. Apakah tim analyst/end-user teknis dan bisa handle JOIN kompleks?
   YA    → Snowflake lebih fleksibel
   TIDAK → STAR lebih ramah pengguna

KESIMPULAN PRAKTIS:
  → STAR adalah pilihan default yang tepat untuk 90% kasus
  → Snowflake yang PENUH (seluruh dimensi dinormalisasi)
    sangat jarang digunakan di industri nyata
  → Solusi HYBRID yang paling umum: star schema dengan sebagian
    kecil dimensi yang memiliki hierarki sangat dalam di-normalize
    (sering disebut "star with normalized sub-dimensions")

HYBRID APPROACH: Gabungkan yang terbaik dari keduanya

Prinsip: Gunakan star untuk sebagian besar dimensi, gunakan
         normalisasi parsial HANYA untuk hierarki yang memiliki
         alasan kuat (biasanya: hierarki lokasi geografis yang
         sangat dalam atau hierarki produk yang kompleks)

CONTOH NYATA — DW Toko Batik Online Hybrid:

  fakta_penjualan
       │
       ├──► dim_waktu          (FLAT/DENORM — star style)
       ├──► dim_pelanggan      (FLAT/DENORM — star style)
       ├──► dim_produk         (FLAT/DENORM — star style, include subkat & kat)
       ├──► dim_kanal          (FLAT/DENORM — star style)
       └──► dim_lokasi_kirim   (PARTIAL SNOWFLAKE)
                │
                └──► dim_wilayah_indonesia  ← tabel referensi provinsi
                                              (karena ada 38 provinsi,
                                               lebih efisien normalize)

  Catatan: dim_lokasi_kirim tetap menyimpan nama_provinsi secara flat
           untuk performa query, dim_wilayah_indonesia digunakan
           sebagai lookup master untuk validasi ETL, bukan untuk JOIN
           di setiap query analitik

SKENARIO TANPA PENANGANAN SCD:

Kondisi awal (Maret 2022):
  dim_pelanggan:
  ┌──────────────────┬──────────────┬──────────────┬───────────┐
  │ kunci_pelanggan  │ nama         │ kota         │ segmen    │
  ├──────────────────┼──────────────┼──────────────┼───────────┤
  │ 1001             │ Siti Rahayu  │ Semarang     │ Gold      │
  └──────────────────┴──────────────┴──────────────┴───────────┘

  fakta_penjualan (rekaman 2022):
  ┌────────────┬─────────────────┬─────────────────────┐
  │ kunci_waktu│ kunci_pelanggan │ revenue_bersih       │
  ├────────────┼─────────────────┼─────────────────────┤
  │ 20220315   │ 1001            │ 850.000              │  ← beli di Semarang
  │ 20220620   │ 1001            │ 1.200.000            │  ← beli di Semarang
  │ 20220918   │ 1001            │ 670.000              │  ← beli di Semarang
  └────────────┴─────────────────┴─────────────────────┘

Januari 2024: Siti pindah ke Jakarta → Tim ETL melakukan:
  UPDATE dim_pelanggan SET kota = 'Jakarta' WHERE kunci_pelanggan = 1001;

Sekarang, laporan 2022 yang lama menghasilkan:

  SELECT pel.kota, SUM(f.revenue_bersih) AS revenue_2022
  FROM fakta_penjualan f
  JOIN dim_pelanggan pel ON f.kunci_pelanggan = pel.kunci_pelanggan
  JOIN dim_waktu     w   ON f.kunci_waktu     = w.kunci_waktu
  WHERE w.tahun = 2022
  GROUP BY pel.kota;

  HASIL YANG SALAH:
  ┌──────────┬──────────────┐
  │ kota     │ revenue_2022 │
  ├──────────┼──────────────┤
  │ Jakarta  │ 2.720.000    │ ← SALAH! Siti di 2022 masih di Semarang!
  └──────────┴──────────────┘

  DAMPAK BISNIS:
  → Laporan ekspansi Semarang tahun 2022 kini menunjukkan revenue yang
    lebih rendah (underestimated)
  → Laporan Jakarta 2022 lebih tinggi dari seharusnya (overestimated)
  → Keputusan strategis (buka cabang, alokasi budget) bisa salah!
  → Jika laporan sudah di-print dan diserahkan ke direksi, kini
    angkanya berbeda → hilang kepercayaan terhadap data warehouse

"Slowly" bukan berarti lambat dalam arti waktu — melainkan merujuk
pada fakta bahwa perubahan dimensi terjadi JARANG dibandingkan
dengan frekuensi munculnya data fakta baru.

  fakta_penjualan: ribuan baris baru per hari  → FAST changing
  dim_pelanggan: beberapa baris berubah per bulan → SLOWLY changing

SCD adalah teknik untuk menangani perubahan yang "lambat" itu
agar tidak merusak integritas historis data warehouse.

VARIABEL YANG BERUBAH DI DIMENSI:
  Pelanggan: pindah kota, naik segmen, ganti nama setelah menikah
  Produk: berganti kategori, harga berubah, supplier berganti
  Karyawan: naik jabatan, pindah divisi, resign dan kembali
  Lokasi: bergabung dengan kecamatan lain, kode pos berubah

SCD TYPE 1 — OVERWRITE (Timpa)
  Strategi paling sederhana: UPDATE nilai lama dengan nilai baru.
  Riwayat lama TIDAK disimpan sama sekali.

KAPAN DIGUNAKAN:
  ✓ Koreksi kesalahan data (typo, salah input) → sejarah yang
    salah tidak perlu disimpan, justru harus dihapus
  ✓ Atribut yang tidak memiliki nilai historis bisnis
    (mis: nomor telepon — analitik jarang butuh "HP lama")
  ✓ Ketika kebijakan bisnis menyatakan "yang berlaku = yang terkini"
  ✗ JANGAN gunakan untuk atribut yang mempengaruhi analisis historis
    (mis: kota, segmen, kategori produk)

SEBELUM (Typo pada nama):
  ┌─────────────────┬───────────────┬──────────┬─────────────────┐
  │ kunci_pelanggan │ nama          │ kota     │ no_telepon      │
  ├─────────────────┼───────────────┼──────────┼─────────────────┤
  │ 1001            │ Budhi Santosa │ Semarang │ 081234567890    │
  └─────────────────┴───────────────┴──────────┴─────────────────┘

SESUDAH TYPE 1 (Koreksi typo):
  ┌─────────────────┬───────────────┬──────────┬─────────────────┐
  │ kunci_pelanggan │ nama          │ kota     │ no_telepon      │
  ├─────────────────┼───────────────┼──────────┼─────────────────┤
  │ 1001            │ Budi Santoso  │ Semarang │ 081234567890    │
  └─────────────────┴───────────────┴──────────┴─────────────────┘
  Surrogate key TETAP SAMA → tidak ada baris baru → tidak ada FK yang berubah

-- ============================================================
-- SCD TYPE 1 — Simple UPDATE
-- ============================================================
 
-- Kasus 1: Koreksi typo nama pelanggan
UPDATE dim_pelanggan
SET nama_lengkap = 'Budi Santoso'
WHERE id_pelanggan_src = 501
  AND is_current = 1;
 
-- Kasus 2: Update nomor telepon (informasi yang tidak perlu disimpan historis)
UPDATE dim_pelanggan
SET no_telepon = '082198765432'
WHERE id_pelanggan_src = 501
  AND is_current = 1;
 
-- Kasus 3: Koreksi kategori produk yang salah input
UPDATE dim_produk
SET kategori = 'Batik Cap',
    subkategori = 'Batik Cap Kontemporer'
WHERE id_produk_src = 1023
  AND is_current = 1;
 
-- Verifikasi setelah update (tidak ada versi lama yang tertinggal)
SELECT kunci_pelanggan, nama_lengkap, kota, is_current
FROM dim_pelanggan
WHERE id_pelanggan_src = 501;
-- Hasil: 1 baris saja, dengan nilai yang sudah di-update
 
-- DAMPAK KE TABEL FAKTA:
-- Tidak ada yang perlu diubah di fakta!
-- Semua FK di fakta tetap menunjuk ke kunci yang sama (1001),
-- yang sekarang punya nilai baru.
-- Konsekuensi: riwayat lama pun "berubah" ikut nilai baru.
-- Ini disengaja untuk Type 1.

SCD TYPE 2 — ADD NEW ROW (Tambah Baris Baru)
  Setiap kali ada perubahan pada atribut yang dilacak:
  1. Baris lama "DITUTUP" → berlaku_sampai diisi tanggal kemarin,
                             is_current diubah menjadi 0
  2. Baris baru "DIBUKA"  → berlaku_dari diisi tanggal hari ini,
                             berlaku_sampai = '9999-12-31',
                             is_current = 1
  3. Surrogate key BARU digenerate untuk baris baru
  4. FK di tabel fakta ke depan akan merujuk ke surrogate key BARU

KAPAN DIGUNAKAN:
  ✓ Atribut yang berubah dan perubahan itu punya makna historis
    → kota pelanggan, segmen pelanggan, kategori produk,
      jabatan karyawan, harga produk (dalam konteks tertentu)
  ✓ Ketika bisnis butuh menjawab: "Bagaimana kondisi X PADA SAAT
    kejadian ini terjadi?" (point-in-time analysis)
  ✓ Ketika laporan historis harus tetap akurat meskipun data berubah

KONDISI AWAL (Januari 2022 — Siti mendaftar):
  dim_pelanggan:
  ┌──────────┬────────────┬──────────────┬──────────┬───────────────┬───────────────┬──────────┐
  │ kunci_pel│ id_pel_src │ nama         │ kota     │ berlaku_dari  │ berlaku_sampai│ is_current│
  ├──────────┼────────────┼──────────────┼──────────┼───────────────┼───────────────┼──────────┤
  │ 1001     │ 501        │ Siti Rahayu  │ Semarang │ 2022-01-15    │ 9999-12-31    │ 1        │
  └──────────┴────────────┴──────────────┴──────────┴───────────────┴───────────────┴──────────┘

  fakta_penjualan (2022–2023): semua baris FK kunci_pelanggan = 1001

───────────────────────────────────────────────────────────────────

PERUBAHAN (2 Januari 2024 — Siti pindah ke Jakarta):

  LANGKAH 1: Tutup baris lama
  UPDATE dim_pelanggan
  SET berlaku_sampai = '2024-01-01',
      is_current     = 0
  WHERE id_pelanggan_src = 501
    AND is_current = 1;

  LANGKAH 2: Insert baris baru (surrogate key baru!)
  INSERT INTO dim_pelanggan (id_pelanggan_src, nama, kota, berlaku_dari)
  VALUES (501, 'Siti Rahayu', 'Jakarta', '2024-01-02');

  dim_pelanggan SEKARANG (dua baris!):
  ┌──────────┬────────────┬──────────────┬──────────┬───────────────┬───────────────┬──────────┐
  │ kunci_pel│ id_pel_src │ nama         │ kota     │ berlaku_dari  │ berlaku_sampai│ is_current│
  ├──────────┼────────────┼──────────────┼──────────┼───────────────┼───────────────┼──────────┤
  │ 1001     │ 501        │ Siti Rahayu  │ Semarang │ 2022-01-15    │ 2024-01-01    │ 0        │ ← tertutup
  │ 2847     │ 501        │ Siti Rahayu  │ Jakarta  │ 2024-01-02    │ 9999-12-31    │ 1        │ ← aktif
  └──────────┴────────────┴──────────────┴──────────┴───────────────┴───────────────┴──────────┘

  fakta_penjualan:
  - Baris 2022–2023: masih FK ke kunci_pelanggan = 1001 (Semarang!) ← TIDAK BERUBAH
  - Baris 2024+:     FK ke kunci_pelanggan = 2847 (Jakarta!) ← BARU via ETL

───────────────────────────────────────────────────────────────────

HASIL QUERY 2022 SEKARANG:

  SELECT pel.kota, SUM(f.revenue_bersih) AS revenue_2022
  FROM fakta_penjualan f
  JOIN dim_pelanggan pel ON f.kunci_pelanggan = pel.kunci_pelanggan
  JOIN dim_waktu     w   ON f.kunci_waktu     = w.kunci_waktu
  WHERE w.tahun = 2022
  GROUP BY pel.kota;

  HASIL YANG BENAR:
  ┌──────────┬──────────────┐
  │ kota     │ revenue_2022 │
  ├──────────┼──────────────┤
  │ Semarang │ 2.720.000    │ ← BENAR! Siti di 2022 memang di Semarang
  └──────────┴──────────────┘
  → Jakarta tidak muncul karena tidak ada transaksi 2022 dari pelanggan
    yang pada saat itu berada di Jakarta.

-- ============================================================
-- SCD TYPE 2 — Implementasi Lengkap dengan Stored Procedure
-- Domain: dim_pelanggan — Toko Batik Online DW
-- ============================================================
 
-- ──────────────────────────────────────────────────────────────
-- TAHAP 1: INSERT AWAL (saat data pertama kali masuk ke DW)
-- ──────────────────────────────────────────────────────────────
INSERT INTO dim_pelanggan (
    id_pelanggan_src,
    nama_lengkap,
    jenis_kelamin,
    kelompok_usia,
    kota,
    kabupaten_kota,
    provinsi,
    pulau,
    wilayah,
    tgl_daftar,
    tahun_daftar,
    segmen_pelanggan,
    berlaku_dari,
    berlaku_sampai,
    is_current
) VALUES (
    501,                      -- id dari OLTP
    'Siti Rahayu',
    'P',
    'Dewasa Muda',
    'Semarang',
    'Kota Semarang',
    'Jawa Tengah',
    'Jawa',
    'Jawa Tengah & DIY',
    '2022-01-15',
    2022,
    'Baru',
    '2022-01-15',             -- berlaku_dari = tanggal pertama kali masuk DW
    '9999-12-31',             -- berlaku_sampai = "tak terbatas"
    1                         -- is_current = 1 (aktif)
);
 
-- ──────────────────────────────────────────────────────────────
-- TAHAP 2: PROSES UPDATE SCD TYPE 2
-- (dijalankan setiap kali ETL mendeteksi perubahan atribut)
-- ──────────────────────────────────────────────────────────────
 
-- Step A: Tutup record lama (jangan DELETE — hanya ubah tanggal akhir)
UPDATE dim_pelanggan
SET berlaku_sampai  = DATE_SUB(CURDATE(), INTERVAL 1 DAY),  -- kemarin
    is_current      = 0
WHERE id_pelanggan_src = 501
  AND is_current = 1;
 
-- Step B: Insert record baru dengan nilai baru dan surrogate key baru
INSERT INTO dim_pelanggan (
    id_pelanggan_src,
    nama_lengkap,
    jenis_kelamin,
    kelompok_usia,
    kota,
    kabupaten_kota,
    provinsi,
    pulau,
    wilayah,
    tgl_daftar,
    tahun_daftar,
    segmen_pelanggan,
    berlaku_dari,
    berlaku_sampai,
    is_current
)
-- Ambil semua atribut dari record lama, tapi ganti kota (dan nilai terkait)
SELECT
    id_pelanggan_src,
    nama_lengkap,
    jenis_kelamin,
    kelompok_usia,
    'Jakarta',              -- ← nilai baru: kota berubah
    'Kota Jakarta Pusat',   -- ← nilai baru: kabupaten_kota berubah
    'DKI Jakarta',          -- ← nilai baru: provinsi berubah
    'Jawa',                 -- sama
    'Jabodetabek',          -- ← nilai baru: wilayah berubah
    tgl_daftar,
    tahun_daftar,
    'Gold',                 -- ← nilai baru: segmen naik (bisa SCD 2 atau 1 tergantung kebijakan)
    CURDATE(),              -- berlaku_dari = hari ini
    '9999-12-31',
    1
FROM dim_pelanggan
WHERE id_pelanggan_src = 501
  AND is_current = 0        -- ambil dari record yang baru saja ditutup
  AND berlaku_sampai = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
LIMIT 1;
 
-- ──────────────────────────────────────────────────────────────
-- STORED PROCEDURE — Wrapper untuk proses SCD Type 2
-- ──────────────────────────────────────────────────────────────
DELIMITER //
CREATE PROCEDURE upsert_pelanggan_scd2(
    IN p_id_src         INT UNSIGNED,
    IN p_nama           VARCHAR(150),
    IN p_kota           VARCHAR(100),
    IN p_kabkota        VARCHAR(100),
    IN p_provinsi       VARCHAR(100),
    IN p_pulau          VARCHAR(50),
    IN p_wilayah        VARCHAR(50),
    IN p_segmen         VARCHAR(30),
    IN p_tgl_efektif    DATE
)
BEGIN
    DECLARE v_exists INT DEFAULT 0;
    DECLARE v_changed INT DEFAULT 0;
 
    -- Cek apakah record sudah ada
    SELECT COUNT(*) INTO v_exists
    FROM dim_pelanggan
    WHERE id_pelanggan_src = p_id_src AND is_current = 1;
 
    IF v_exists = 0 THEN
        -- Pelanggan baru: INSERT langsung
        INSERT INTO dim_pelanggan (
            id_pelanggan_src, nama_lengkap, kota, kabupaten_kota,
            provinsi, pulau, wilayah, segmen_pelanggan,
            tgl_daftar, tahun_daftar, berlaku_dari, berlaku_sampai, is_current
        ) VALUES (
            p_id_src, p_nama, p_kota, p_kabkota, p_provinsi,
            p_pulau, p_wilayah, p_segmen, p_tgl_efektif,
            YEAR(p_tgl_efektif), p_tgl_efektif, '9999-12-31', 1
        );
    ELSE
        -- Cek apakah ada atribut yang berubah
        SELECT COUNT(*) INTO v_changed
        FROM dim_pelanggan
        WHERE id_pelanggan_src = p_id_src
          AND is_current = 1
          AND (kota             != p_kota    OR
               segmen_pelanggan != p_segmen  OR
               provinsi         != p_provinsi);
 
        IF v_changed > 0 THEN
            -- Ada perubahan: tutup record lama, buka record baru
            UPDATE dim_pelanggan
            SET berlaku_sampai = DATE_SUB(p_tgl_efektif, INTERVAL 1 DAY),
                is_current     = 0
            WHERE id_pelanggan_src = p_id_src AND is_current = 1;
 
            INSERT INTO dim_pelanggan (
                id_pelanggan_src, nama_lengkap, kota, kabupaten_kota,
                provinsi, pulau, wilayah, segmen_pelanggan,
                tgl_daftar, tahun_daftar, berlaku_dari, berlaku_sampai, is_current
            )
            SELECT id_pelanggan_src, p_nama, p_kota, p_kabkota,
                   p_provinsi, p_pulau, p_wilayah, p_segmen,
                   tgl_daftar, tahun_daftar, p_tgl_efektif, '9999-12-31', 1
            FROM dim_pelanggan
            WHERE id_pelanggan_src = p_id_src
              AND is_current = 0
              AND berlaku_sampai = DATE_SUB(p_tgl_efektif, INTERVAL 1 DAY)
            LIMIT 1;
        END IF;
        -- Jika tidak ada perubahan: tidak lakukan apa-apa (idempotent)
    END IF;
END //
DELIMITER ;
 
-- Contoh pemanggilan prosedur:
CALL upsert_pelanggan_scd2(501, 'Siti Rahayu', 'Jakarta', 'Kota Jakarta Pusat',
                            'DKI Jakarta', 'Jawa', 'Jabodetabek', 'Gold', '2024-01-02');

-- ============================================================
-- QUERY PATTERNS — Memanfaatkan SCD Type 2 untuk Analitik
-- ============================================================
 
-- Query 1: Dapatkan profil TERKINI semua pelanggan aktif
SELECT kunci_pelanggan, nama_lengkap, kota, segmen_pelanggan
FROM dim_pelanggan
WHERE is_current = 1
ORDER BY nama_lengkap;
 
-- Query 2: Dapatkan profil pelanggan PADA TANGGAL TERTENTU
-- "Seperti apa data pelanggan pada 1 Juli 2023?"
SELECT kunci_pelanggan, nama_lengkap, kota, segmen_pelanggan
FROM dim_pelanggan
WHERE id_pelanggan_src = 501
  AND '2023-07-01' BETWEEN berlaku_dari AND berlaku_sampai;
-- → Mengembalikan baris dengan kota = 'Semarang' (sebelum pindah 2024)
 
-- Query 3: Lihat seluruh riwayat perubahan satu pelanggan
SELECT
    kunci_pelanggan,
    nama_lengkap,
    kota,
    segmen_pelanggan,
    berlaku_dari,
    berlaku_sampai,
    is_current,
    DATEDIFF(
        IF(is_current = 1, CURDATE(), berlaku_sampai),
        berlaku_dari
    ) AS lama_berlaku_hari
FROM dim_pelanggan
WHERE id_pelanggan_src = 501
ORDER BY berlaku_dari;
 
-- Query 4: Analisis historis akurat — Revenue per kota per tahun
-- (setiap transaksi dikaitkan dengan kota pelanggan PADA SAAT transaksi)
SELECT
    pel.kota,
    w.tahun,
    COUNT(DISTINCT f.id_pesanan)  AS jumlah_pesanan,
    SUM(f.revenue_bersih)         AS total_revenue
FROM fakta_penjualan f
JOIN dim_pelanggan pel ON f.kunci_pelanggan = pel.kunci_pelanggan
JOIN dim_waktu     w   ON f.kunci_waktu     = w.kunci_waktu
WHERE w.tahun IN (2022, 2023, 2024)
GROUP BY pel.kota, w.tahun
ORDER BY w.tahun, total_revenue DESC;
-- → Akurat! Siti di 2022 masuk sebagai Semarang (FK → kunci 1001)
--            Siti di 2024 masuk sebagai Jakarta (FK → kunci 2847)
 
-- Query 5: Berapa pelanggan yang pindah kota dalam 2 tahun terakhir?
SELECT COUNT(DISTINCT id_pelanggan_src) AS pelanggan_pindah_kota
FROM dim_pelanggan
WHERE is_current = 0                                    -- sudah punya versi baru
  AND berlaku_sampai >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR);
 
-- Query 6: Analisis migrasi — dari kota mana ke mana pelanggan berpindah?
SELECT
    lama.kota      AS kota_asal,
    baru.kota      AS kota_tujuan,
    COUNT(*)       AS jumlah_pelanggan
FROM dim_pelanggan lama
JOIN dim_pelanggan baru
    ON lama.id_pelanggan_src = baru.id_pelanggan_src
   AND baru.berlaku_dari = DATE_ADD(lama.berlaku_sampai, INTERVAL 1 DAY)
WHERE lama.kota != baru.kota                            -- pastikan kota berubah
GROUP BY lama.kota, baru.kota
ORDER BY jumlah_pelanggan DESC;

SCD TYPE 3 — ADD NEW COLUMN (Tambah Kolom Baru)
  Menyimpan nilai sebelumnya (HANYA SATU TINGKAT SEBELUMNYA)
  dalam kolom baru yang ditambahkan ke tabel dimensi.

  Tidak ada baris baru — yang ada adalah kolom baru!
  Surrogate key TIDAK berubah.

KAPAN DIGUNAKAN:
  ✓ Hanya butuh melacak "nilai sebelumnya" (bukan semua riwayat)
  ✓ Dimensi punya perubahan besar tapi terprediksi (mis: restrukturisasi
    organisasi, perubahan segmentasi pelanggan nasional)
  ✓ Bisnis butuh bisa laporan dengan "skema baru" DAN
    "skema lama" secara bersamaan untuk periode transisi
  ✗ TIDAK COCOK jika perubahan bisa terjadi lebih dari 1 kali
    (kolom "sebelumnya" hanya bisa menyimpan 1 versi lama)

CONTOH — Restrukturisasi segmentasi pelanggan (hanya 1 kali besar):
  Perusahaan mengubah skema segmentasi:
  LAMA: 'Bronze', 'Silver', 'Gold', 'Platinum'
  BARU: 'Reguler', 'Setia', 'VIP', 'Super VIP'

  Dengan SCD Type 3, tambahkan kolom baru:
  ALTER TABLE dim_pelanggan
  ADD COLUMN segmen_lama    VARCHAR(30) NULL AFTER segmen_pelanggan,
  ADD COLUMN tgl_ubah_segmen DATE       NULL AFTER segmen_lama;

  SEBELUM perubahan:
  ┌──────────┬─────────────────┬──────────────┬────────────────────┐
  │ kunci_pel│ nama            │ segmen       │ segmen_lama        │
  ├──────────┼─────────────────┼──────────────┼────────────────────┤
  │ 1001     │ Siti Rahayu     │ Gold         │ NULL               │
  └──────────┴─────────────────┴──────────────┴────────────────────┘

  SESUDAH perubahan (migrasi segmentasi):
  ┌──────────┬─────────────────┬──────────────┬────────────────────┐
  │ kunci_pel│ nama            │ segmen       │ segmen_lama        │
  ├──────────┼─────────────────┼──────────────┼────────────────────┤
  │ 1001     │ Siti Rahayu     │ VIP          │ Gold               │
  └──────────┴─────────────────┴──────────────┴────────────────────┘
  → Surrogate key SAMA → tidak ada baris baru
  → Laporan bisa menggunakan segmen untuk "skema baru"
    DAN segmen_lama untuk "skema lama" dalam periode transisi

-- ============================================================
-- SCD TYPE 3 — Implementasi
-- ============================================================
 
-- Tambah kolom baru ke dimensi (dilakukan SEKALI saat kebijakan berubah)
ALTER TABLE dim_pelanggan
    ADD COLUMN segmen_sebelumnya  VARCHAR(30) NULL
        COMMENT 'Segmen pelanggan sebelum restrukturisasi Januari 2024'
        AFTER segmen_pelanggan,
    ADD COLUMN tgl_ubah_segmen    DATE        NULL
        COMMENT 'Tanggal perubahan segmentasi mulai berlaku'
        AFTER segmen_sebelumnya;
 
-- Update: simpan nilai lama ke kolom baru, isi kolom utama dengan nilai baru
UPDATE dim_pelanggan
SET segmen_sebelumnya = segmen_pelanggan,   -- simpan nilai lama
    segmen_pelanggan  = CASE               -- ganti dengan nilai baru
        WHEN segmen_pelanggan = 'Bronze'   THEN 'Reguler'
        WHEN segmen_pelanggan = 'Silver'   THEN 'Setia'
        WHEN segmen_pelanggan = 'Gold'     THEN 'VIP'
        WHEN segmen_pelanggan = 'Platinum' THEN 'Super VIP'
        ELSE segmen_pelanggan
    END,
    tgl_ubah_segmen = '2024-01-01'
WHERE is_current = 1;
 
-- Query dengan skema BARU:
SELECT segmen_pelanggan AS segmen_2024, COUNT(*) AS jumlah
FROM dim_pelanggan WHERE is_current = 1 GROUP BY segmen_pelanggan;
 
-- Query dengan skema LAMA (untuk perbandingan historis):
SELECT segmen_sebelumnya AS segmen_2023, COUNT(*) AS jumlah
FROM dim_pelanggan WHERE is_current = 1 GROUP BY segmen_sebelumnya;
 
-- Query perbandingan transisi:
SELECT segmen_sebelumnya AS segmen_lama,
       segmen_pelanggan  AS segmen_baru,
       COUNT(*)           AS jumlah_pelanggan
FROM dim_pelanggan
WHERE is_current = 1 AND segmen_sebelumnya IS NOT NULL
GROUP BY segmen_sebelumnya, segmen_pelanggan
ORDER BY jumlah_pelanggan DESC;

SCD TYPE 4 — HISTORY TABLE (Tabel Historis Terpisah)
  Pisahkan tabel aktif (current) dan tabel historis ke dua tabel berbeda.
  Tabel utama hanya berisi versi terkini, tabel historis menyimpan semua versi lama.

KAPAN DIGUNAKAN:
  ✓ Ketika dimensi berubah sangat sering (semi-volatile) — Type 2 membuat
    tabel jadi terlalu besar dan lambat
  ✓ Ketika mayoritas query hanya butuh data terkini (query ke tabel utama
    selalu cepat karena tidak ada baris historis)

STRUKTUR:
  dim_pelanggan_current    ← hanya nilai TERKINI, 1 baris per pelanggan
  dim_pelanggan_history    ← semua versi lama, bisa banyak baris per pelanggan

  Tabel current di-JOIN ke fakta untuk analisis umum (cepat).
  Tabel history di-JOIN hanya saat butuh analisis historis point-in-time.

SCD TYPE 6 — HYBRID (juga dikenal sebagai "Type 1+2+3" atau "Type 6")
  Menggabungkan tiga pendekatan sekaligus untuk mendapatkan fleksibilitas maksimum:
  
  • Type 2: Baris baru saat ada perubahan (riwayat penuh tersimpan)
  • Type 1: Semua versi diperbarui dengan nilai terkini di satu kolom khusus
  • Type 3: Kolom untuk nilai sebelumnya

STRUKTUR TABEL:
  ┌──────────┬────────────┬──────────┬─────────────┬──────────────┬────────────┬──────────┐
  │kunci_pel │ nama       │ kota     │ kota_terkini│ kota_sebelum │ berlaku_   │ is_curr  │
  │          │            │ (historis│ (Type 1 —   │ (Type 3)     │ dari/sampai│          │
  │          │            │ saat ini)│ selalu baru)│              │            │          │
  ├──────────┼────────────┼──────────┼─────────────┼──────────────┼────────────┼──────────┤
  │ 1001     │ Siti       │ Semarang │ Jakarta     │ Semarang     │ 2022-01-15 │ 0        │ ← versi lama
  │ 2847     │ Siti       │ Jakarta  │ Jakarta     │ Semarang     │ 2024-01-02 │ 1        │ ← versi baru
  └──────────┴────────────┴──────────┴─────────────┴──────────────┴────────────┴──────────┘

  Manfaat:
  → kota: "di mana pelanggan SAAT transaksi terjadi" (historis akurat)
  → kota_terkini: "di mana pelanggan SEKARANG" (bisa dari semua versi lama)
  → kota_sebelumnya: transisi perubahan terakhir

  Kelemahan: Kompleks, tabel lebih lebar, ETL lebih rumit.
  Direkomendasikan hanya jika benar-benar diperlukan.

PANDUAN MEMILIH SCD TYPE PER ATRIBUT:

Untuk setiap atribut yang bisa berubah, jawab 3 pertanyaan:

Q1: Apakah perubahan ini perlu dilacak historisnya untuk analitik?
  → TIDAK (mis: typo, data koreksi, info tidak relevan)
    → SCD TYPE 1 (timpa saja)

Q2: Apakah kamu butuh menjawab "bagaimana kondisi X saat transaksi Y terjadi?"
  → YA (mis: kota, segmen, jabatan, kategori produk)
    → SCD TYPE 2 (baris baru)

Q3: Apakah perubahan hanya terjadi 1 kali besar dan kamu butuh
    laporan versi lama DAN baru secara bersamaan?
  → YA (mis: restrukturisasi segmentasi, rebranding)
    → SCD TYPE 3 (tambah kolom)
  → TIDAK → kembali ke Type 2

TABEL KEPUTUSAN PER ATRIBUT DIMENSI — dim_pelanggan:

┌─────────────────────────┬──────────┬──────────────────────────────────────────────┐
│ Atribut                 │ SCD Type │ Alasan                                        │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ nama_lengkap            │ Type 1   │ Perubahan karena typo atau nama resmi (misal  │
│                         │          │ setelah menikah) — tidak perlu historis        │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ jenis_kelamin           │ Type 1   │ Hampir tidak berubah; jika ada: koreksi data   │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ no_telepon              │ Type 1   │ Tidak ada nilai analitik dari nomor HP lama    │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ email                   │ Type 1   │ Sama seperti no_telepon                        │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ kota                    │ Type 2   │ Kunci analisis geografis historis — "revenue   │
│                         │          │ dari kota X di periode Y" harus akurat         │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ provinsi                │ Type 2   │ Sama dengan kota (hierarki lokasi)             │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ segmen_pelanggan        │ Type 2   │ Analisis cohort segmen: "pelanggan yang naik   │
│                         │          │ ke Gold — berapa lama sampai churn?"           │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ kelompok_usia           │ Type 2   │ Bisa berubah seiring usia — relevan untuk      │
│                         │          │ analisis lifecycle pelanggan                   │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ tgl_daftar              │ Type 1   │ Tidak berubah; jika ada: koreksi data saja     │
└─────────────────────────┴──────────┴──────────────────────────────────────────────┘

TABEL KEPUTUSAN — dim_produk:

┌─────────────────────────┬──────────┬──────────────────────────────────────────────┐
│ Atribut                 │ SCD Type │ Alasan                                        │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ nama_produk             │ Type 1   │ Perubahan nama biasanya koreksi; jika         │
│                         │          │ rebranding besar → pertimbangkan Type 2        │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ kategori                │ Type 2   │ Perpindahan kategori mempengaruhi analisis     │
│                         │          │ per kategori historis — harus akurat           │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ harga_jual              │ Type 2   │ "Berapa revenue dengan harga lama vs baru?"   │
│                         │          │ — pertanyaan valid yang butuh historis         │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ rentang_harga           │ Type 2   │ Derived dari harga — ikut Type 2              │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ nama_supplier           │ Type 1   │ Perubahan nama supplier biasanya rebrand;     │
│                         │          │ analitis lebih umum per kode/id supplier       │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ teknik_pembuatan        │ Type 1   │ Jarang berubah; jika berubah: koreksi data    │
├─────────────────────────┼──────────┼──────────────────────────────────────────────┤
│ is_aktif                │ Type 1   │ Status saat ini (aktif/tidak) — not historical │
└─────────────────────────┴──────────┴──────────────────────────────────────────────┘

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
BEST PRACTICES DESAIN FAKTA
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

BP-F1: Pilih grain yang paling atomik yang masuk akal secara bisnis
  → Grain aggregate = kehilangan kemampuan drill-down permanen
  → Jika ragu, pilih grain lebih rendah — bisa agregasi, tapi tidak bisa
    mengembalikan detail yang sudah hilang

BP-F2: Semua measures harus pada grain yang sama
  → Jangan campurkan "total per pesanan" dan "total per item" dalam satu baris

BP-F3: Prioritaskan additive measures
  → Simpan komponen aditif (qty, revenue) bukan rasio atau persentase
  → Persentase bisa dihitung dari komponen: margin% = laba/revenue

BP-F4: Sertakan degenerate dimensions untuk identitas transaksi
  → id_pesanan, nomor_faktur dll. perlu ada untuk drill-through ke OLTP

BP-F5: Partisi tabel fakta berdasarkan waktu
  → Untuk tabel dengan ratusan juta baris, PARTITION BY RANGE (kunci_waktu)
    meningkatkan performa query drastis (pruning partisi)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
BEST PRACTICES DESAIN DIMENSI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

BP-D1: Selalu gunakan surrogate key sebagai PK dimensi
  → Integer auto-increment — tidak punya makna bisnis
  → Natural key tetap disimpan sebagai kolom terpisah

BP-D2: Denormalize dimensi — flatten semua hierarki
  → Pilih STAR sebagai default, bukan snowflake
  → Redundansi yang disengaja = investasi untuk performa query

BP-D3: Buat dimensi selebar mungkin
  → Tambahkan atribut derivatif: kelompok_usia, rentang_harga, nama_hari_id
  → Setiap kolom dimensi = satu kemungkinan filter/grouping analitik baru

BP-D4: dim_waktu selalu wajib ada dan pre-populated
  → Jangan simpan tanggal tanpa dim_waktu
  → Populate untuk rentang 5–10 tahun ke depan sekaligus

BP-D5: Tentukan strategi SCD per atribut sebelum implementasi
  → Atribut koreksi: Type 1
  → Atribut yang mempengaruhi analisis historis: Type 2
  → Restrukturisasi sekali besar dengan kebutuhan laporan transisi: Type 3

BP-D6: Simpan "row description" di setiap dimensi
  → Kolom teks tunggal yang menjelaskan baris: "Budi Santoso - Semarang - Gold"
  → Berguna untuk BI tools, debugging, dan laporan drill-through

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
BEST PRACTICES SKEMA DAN NAMING CONVENTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

BP-N1: Naming convention konsisten di seluruh warehouse
  → Tabel fakta: fakta_[proses_bisnis] (contoh: fakta_penjualan)
  → Tabel dimensi: dim_[entitas] (contoh: dim_pelanggan)
  → Surrogate key: kunci_[entitas] (contoh: kunci_pelanggan)
  → Natural key: id_[entitas]_src (contoh: id_pelanggan_src)
  → Tanggal SCD: berlaku_dari, berlaku_sampai, is_current

BP-N2: Beri komentar SQL yang informatif
  → Komentar tabel: apa grain-nya, apa tujuan tabel ini
  → Komentar kolom: apa maknanya, apakah ada logika derivasi khusus

BP-N3: Gunakan conformed dimensions lintas proses bisnis
  → Satu dim_waktu, satu dim_pelanggan — dipakai oleh semua tabel fakta
  → Jangan buat dim_waktu2 yang sedikit berbeda!

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
BEST PRACTICES ETL DAN PEMELIHARAAN
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

BP-E1: Load dimensi SEBELUM fakta — selalu
  → FK constraint tidak bisa dipenuhi jika dimensi belum ada

BP-E2: Buat ETL bersifat idempotent
  → Menjalankan ETL dua kali dengan data yang sama tidak menghasilkan
    duplikasi atau perubahan yang tidak diinginkan

BP-E3: Audit trail di ETL
  → Catat: kapan baris pertama kali dimasukkan (etl_load_timestamp)
  → Ini berbeda dari berlaku_dari — berlaku_dari adalah tanggal bisnis,
    etl_load_timestamp adalah tanggal teknis ETL dijalankan

BP-E4: Jangan DELETE dari warehouse — gunakan soft delete
  → Jika data sumber dihapus di OLTP, tandai di warehouse dengan
    is_aktif = 0 atau is_deleted = 1, jangan DELETE dari fakta/dimensi
  → Ini melindungi integritas historis

BP-E5: Monitoring dan alerting pada proses ETL
  → Alert jika ETL gagal atau terlambat dari jadwal
  → Dashboard kualitas data: berapa baris berhasil di-load vs ditolak

DIMENSI: dim_[nama_entitas]

┌───────────────────────┬──────────┬───────────────────────────────────────────────┐
│ Atribut               │ SCD Type │ Justifikasi                                    │
├───────────────────────┼──────────┼───────────────────────────────────────────────┤
│ [nama_atribut_1]      │ Type [?] │ [Alasan bisnis mengapa type ini dipilih]       │
├───────────────────────┼──────────┼───────────────────────────────────────────────┤
│ [nama_atribut_2]      │ Type [?] │ ...                                            │
└───────────────────────┴──────────┴───────────────────────────────────────────────┘

ATRIBUT YANG MEMERLUKAN SCD TYPE 2:
  Kolom berlaku_dari, berlaku_sampai, is_current sudah ada di DDL P12.
  Verifikasi bahwa kolom-kolom ini ada dan tipe datanya benar!

PERTANYAAN ANALITIK HISTORIS yang kini bisa dijawab (berkat SCD Type 2):
  1. "_____________________________________ di periode ______?"
  2. "_____________________________________ sebelum vs sesudah _____?"

┌─────────────┬───────────────────────────────────────────────────────────────────────────┐
│             │                                                                             │
│  SCD TYPE 1 │ SEBELUM:  kunci=1001 | nama=Siti | kota=Semarang | segmen=Gold             │
│  OVERWRITE  │                                                                             │
│             │ SESUDAH:  kunci=1001 | nama=Siti | kota=Jakarta  | segmen=Gold             │
│             │                                                                             │
│             │ → 1 baris tetap | kunci sama | riwayat HILANG                              │
│             │ → FK di fakta tidak berubah                                                 │
│             │ → Laporan historis ikut berubah                                             │
│             │                                                                             │
├─────────────┼───────────────────────────────────────────────────────────────────────────┤
│             │                                                                             │
│  SCD TYPE 2 │ SEBELUM:  kunci=1001 | nama=Siti | kota=Semarang | brl_dari=2022-01-15    │
│  ADD ROW    │           berlaku_sampai=9999-12-31 | is_current=1                         │
│             │                                                                             │
│             │ SESUDAH:  kunci=1001 | nama=Siti | kota=Semarang | brl_dari=2022-01-15    │
│             │           berlaku_sampai=2024-01-01 | is_current=0   ← DITUTUP             │
│             │                                                                             │
│             │           kunci=2847 | nama=Siti | kota=Jakarta  | brl_dari=2024-01-02    │
│             │           berlaku_sampai=9999-12-31 | is_current=1   ← BARU               │
│             │                                                                             │
│             │ → 2 baris | kunci baru | riwayat TERSIMPAN PENUH                           │
│             │ → FK lama di fakta tetap ke 1001 (akurat historis)                         │
│             │ → FK baru di fakta ke 2847 (akurat untuk transaksi baru)                  │
│             │                                                                             │
├─────────────┼───────────────────────────────────────────────────────────────────────────┤
│             │                                                                             │
│  SCD TYPE 3 │ SEBELUM:  kunci=1001 | nama=Siti | segmen=Gold | segmen_lama=NULL         │
│  ADD COLUMN │                                                                             │
│             │ SESUDAH:  kunci=1001 | nama=Siti | segmen=VIP   | segmen_lama=Gold        │
│             │                                                                             │
│             │ → 1 baris tetap | kunci sama | 1 tingkat riwayat TERSIMPAN                 │
│             │ → FK di fakta tidak berubah                                                 │
│             │ → Bisa query "skema lama" via segmen_lama, "skema baru" via segmen         │
│             │ → TIDAK BISA track lebih dari 1 perubahan!                                 │
│             │                                                                             │
├─────────────┼───────────────────────────────────────────────────────────────────────────┤
│             │                                                                             │
│  KAPAN      │ Type 1: Koreksi data, info tanpa nilai historis, field operasional         │
│  PILIH MANA │ Type 2: Perubahan yang mempengaruhi analisis historis (DEFAULT untuk DW)  │
│             │ Type 3: Restrukturisasi besar 1 kali yang butuh laporan "lama" & "baru"   │
│             │                                                                             │
└─────────────┴───────────────────────────────────────────────────────────────────────────┘

CHECKLIST: APAKAH SNOWFLAKE LAYAK DIPERTIMBANGKAN?

Jawab pertanyaan berikut. Tambahkan poin untuk setiap jawaban YA:

ALASAN MEMPERTIMBANGKAN SNOWFLAKE (+1 per YA):
  □ Storage sangat terbatas dan biaya storage adalah concern utama?      +1
  □ Dimensi memiliki hierarki 4+ level yang sangat dalam?               +1
  □ Nilai di sub-dimensi berubah sangat sering (mis: harga bahan baku)? +1
  □ Tim technical yang akan query sangat terampil dalam SQL kompleks?   +1

ALASAN TETAP PAKAI STAR (−1 per YA):
  □ BI tool utama (Tableau, Power BI, dll.) lebih optimal di star?       −1
  □ Mayoritas pengguna adalah business analyst non-teknis?               −1
  □ Kecepatan query adalah prioritas utama (bukan storage)?              −1
  □ Ini adalah proyek pertama / DW sederhana?                            −1

KEPUTUSAN:
  Skor ≤ 0 → Pilih STAR SCHEMA
  Skor 1–2 → Pertimbangkan hybrid (star dengan sebagian normalisasi)
  Skor 3+  → Snowflake layak dipertimbangkan

Catatan: Industri modern dengan cloud DW (Snowflake, BigQuery, Redshift)
  hampir selalu lebih suka star karena storage sangat murah dan
  columnar storage menghilangkan kelemahan star soal redundansi.

TABEL KEPUTUSAN SCD — [NAMA DIMENSI]
Domain Proyek  : _________________________________
Kelompok       : _________________________________

┌───────────────────────┬──────────┬───────────────────────────────────────────────┐
│ Atribut               │ SCD Type │ Justifikasi Bisnis                             │
├───────────────────────┼──────────┼───────────────────────────────────────────────┤
│                       │          │                                                │
│                       │          │                                                │
│                       │          │                                                │
│                       │          │                                                │
│                       │          │                                                │
│                       │          │                                                │
│                       │          │                                                │
│                       │          │                                                │
└───────────────────────┴──────────┴───────────────────────────────────────────────┘

ATRIBUT YANG MENGGUNAKAN SCD TYPE 2 (daftar):
  1. ________________  Alasan: _________________________
  2. ________________  Alasan: _________________________
  3. ________________  Alasan: _________________________

VERIFIKASI KOLOM SCD TYPE 2 DI DDL (centang jika sudah ada):
  □ berlaku_dari     DATE NOT NULL
  □ berlaku_sampai   DATE NOT NULL DEFAULT '9999-12-31'
  □ is_current       TINYINT(1) NOT NULL DEFAULT 1

PERTANYAAN ANALITIK HISTORIS yang kini bisa dijawab:
  1. "___________________________________________________________"
     → Dimensi yang digunakan: ________________________________
  2. "___________________________________________________________"
     → Dimensi yang digunakan: ________________________________

STAR SCHEMA (dim_produk denormalized):

     ┌────────────────────────────────────────────────────┐
     │ fakta_penjualan                                    │
     │ kunci_produk FK ──────────────────────────────────►│
     └────────────────────────────────────────────────────┘
                                                          │
                                               ┌──────────▼──────────────────────────┐
                                               │ dim_produk                           │
                                               │ kunci_produk      PK                 │
                                               │ nama_produk                          │
                                               │ subkategori   ← FLAT, semua dalam    │
                                               │ kategori      ← satu tabel           │
                                               │ kelompok_kat  ←                      │
                                               │ nama_supplier ←                      │
                                               │ kota_supplier ←                      │
                                               └─────────────────────────────────────┘
Query: 2 JOIN (fact + dim_produk)

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

SNOWFLAKE SCHEMA (dim_produk dinormalisasi):

     ┌────────────────────────────────────────────────────┐
     │ fakta_penjualan                                    │
     │ kunci_produk FK ──────────────────────────────────►│
     └────────────────────────────────────────────────────┘
                                                          │
                                               ┌──────────▼──────────┐
                                               │ dim_produk           │
                                               │ kunci_produk    PK   │
                                               │ nama_produk          │
                                               │ kunci_subkat    FK ──►┌──────────────┐
                                               │ kunci_supplier  FK ──►│ dim_subkat   │
                                               └─────────────────────┘│ kunci_subkat │
                                                                       │ nama_subkat  │
                                               ┌──────────────────────┤ kunci_kat FK ├──►┌──────────────┐
                                               │ dim_supplier          └──────────────┘   │ dim_kategori │
                                               │ kunci_supplier  PK                       │ kunci_kat  PK│
                                               │ nama_supplier                            │ nama_kat     │
                                               │ kota_supplier                            │ kunci_kel FK─►┌──────────┐
                                               └──────────────────────────────────────────┘              │dim_kelom │
Query: 4–5 JOIN (fact + produk + subkat + kat + kelompok + supplier)                                     └──────────┘

LEMBAR KERJA SCD — PROYEK KELOMPOK
Kelompok : _____ | Domain : ___________________ | Tanggal: _________

DIMENSI 1: dim_____________

  Proses bisnis utama : ________________________________
  Atribut yang dievaluasi:

  ┌───────────────────┬──────────┬────────────────────────────────────────┐
  │ Atribut           │ SCD Type │ Alasan Bisnis                           │
  ├───────────────────┼──────────┼────────────────────────────────────────┤
  │                   │ Type     │                                        │
  │                   │ Type     │                                        │
  │                   │ Type     │                                        │
  │                   │ Type     │                                        │
  └───────────────────┴──────────┴────────────────────────────────────────┘

DIMENSI 2: dim_____________

  ┌───────────────────┬──────────┬────────────────────────────────────────┐
  │ Atribut           │ SCD Type │ Alasan Bisnis                           │
  ├───────────────────┼──────────┼────────────────────────────────────────┤
  │                   │ Type     │                                        │
  │                   │ Type     │                                        │
  │                   │ Type     │                                        │
  └───────────────────┴──────────┴────────────────────────────────────────┘

PILIH 1 ATRIBUT UNTUK IMPLEMENTASI SCD TYPE 2:
  Dimensi        : dim___________
  Atribut        : _______________
  Alasan dipilih : _________________________________________

  Tulis PSEUDOCODE proses SCD Type 2 untuk atribut ini:
  
  SAAT NILAI BERUBAH:
  Step 1: UPDATE _______ SET berlaku_sampai = [kemarin], is_current = 0
          WHERE id_src = [id] AND is_current = 1
  
  Step 2: INSERT INTO _______ VALUES (
              id_src = [sama],
              [atribut_berubah] = [nilai baru],
              [atribut_lain] = [sama dengan sebelumnya],
              berlaku_dari = [hari ini],
              berlaku_sampai = '9999-12-31',
              is_current = 1
          )

  QUERY HISTORIS yang kini bisa dijawab:
  "[__________________________________________________ pada periode ______?]"
  SQL: SELECT _____ FROM _____ WHERE _____ BETWEEN _____ AND _____;

-- Tambahkan 3 kolom ini ke setiap dimensi yang menggunakan Type 2:
berlaku_dari     DATE        NOT NULL,   -- tanggal mulai berlaku versi ini
berlaku_sampai   DATE        NOT NULL,   -- '9999-12-31' untuk versi current
is_current       TINYINT(1)  NOT NULL,   -- 1 = current, 0 = historis
 
-- Contoh query untuk mendapatkan versi yang berlaku pada tanggal tertentu:
SELECT * FROM dim_pelanggan
WHERE id_pelanggan_src = 1001
  AND '2024-06-15' BETWEEN berlaku_dari AND berlaku_sampai;
 
-- Contoh query untuk mendapatkan versi current:
SELECT * FROM dim_pelanggan
WHERE id_pelanggan_src = 1001
  AND is_current = 1;