{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "81b63b3e",
   "metadata": {},
   "source": [
    "# Lab Minggu 3 — Data Warehouse, Star Schema & Kubus OLAP\n",
    "\n",
    "**Tujuan lab:**\n",
    "1. Proses ETL: mengubah tabel penjualan datar (OLTP export) menjadi **star schema**.\n",
    "2. Memuat star schema ke SQLite (sebagai warehouse mini) dan menjalankan kueri analitik.\n",
    "3. Mempraktikkan operasi OLAP: roll-up, drill-down, slice, dice, pivot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e622edeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.insert(0, \"..\")\n",
    "\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from common.datasets import penjualan_flat\n",
    "\n",
    "df = penjualan_flat()\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c383a428",
   "metadata": {},
   "source": [
    "## 1. Extract — lihat redundansi di tabel datar\n",
    "\n",
    "Perhatikan `kategori`, `harga_satuan`, `kota`, `wilayah` diulang di setiap baris → boros\n",
    "dan rawan inkonsistensi. Star schema memisahkannya ke tabel dimensi."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8250d7dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Baris:\", len(df))\n",
    "print(\"Produk unik:\", df.nama_produk.nunique(), \"| Toko unik:\", df.nama_toko.nunique(),\n",
    "      \"| Tanggal unik:\", df.tanggal.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c6bb0fc",
   "metadata": {},
   "source": [
    "## 2. Transform — bangun tabel dimensi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef5d87db",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_produk = (df[[\"nama_produk\", \"kategori\", \"harga_satuan\"]].drop_duplicates()\n",
    "              .sort_values(\"nama_produk\").reset_index(drop=True))\n",
    "dim_produk.insert(0, \"id_produk\", range(1, len(dim_produk) + 1))\n",
    "dim_produk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c831e25",
   "metadata": {},
   "outputs": [],
   "source": [
    "dim_toko = (df[[\"nama_toko\", \"kota\", \"wilayah\"]].drop_duplicates()\n",
    "            .sort_values(\"nama_toko\").reset_index(drop=True))\n",
    "dim_toko.insert(0, \"id_toko\", range(1, len(dim_toko) + 1))\n",
    "dim_toko"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34aa3de2",
   "metadata": {},
   "outputs": [],
   "source": [
    "tgl = pd.Series(df[\"tanggal\"].drop_duplicates().sort_values().values)\n",
    "dim_waktu = pd.DataFrame({\n",
    "    \"id_waktu\": tgl.dt.strftime(\"%Y%m%d\").astype(int),\n",
    "    \"tanggal\": tgl,\n",
    "    \"bulan\": tgl.dt.month,\n",
    "    \"kuartal\": tgl.dt.quarter,\n",
    "    \"tahun\": tgl.dt.year,\n",
    "    \"nama_hari\": tgl.dt.day_name(),\n",
    "})\n",
    "dim_waktu.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "786b6d63",
   "metadata": {},
   "source": [
    "## 3. Tabel fakta (grain: satu item per transaksi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f1e3e7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "fakta = (df.merge(dim_produk[[\"id_produk\", \"nama_produk\"]], on=\"nama_produk\")\n",
    "           .merge(dim_toko[[\"id_toko\", \"nama_toko\"]], on=\"nama_toko\")\n",
    "           .merge(dim_waktu[[\"id_waktu\", \"tanggal\"]], on=\"tanggal\")\n",
    "           [[\"id_transaksi\", \"id_waktu\", \"id_produk\", \"id_toko\", \"jumlah\", \"total\"]])\n",
    "print(fakta.shape)\n",
    "fakta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99687472",
   "metadata": {},
   "source": [
    "Diagram star schema yang kita buat:\n",
    "\n",
    "```\n",
    "                 dim_waktu (id_waktu, tanggal, bulan, kuartal, tahun, nama_hari)\n",
    "                       │\n",
    "dim_produk ──── fakta_penjualan ──── dim_toko\n",
    "(id_produk, nama,   (id_transaksi, id_waktu,   (id_toko, nama_toko,\n",
    " kategori, harga)    id_produk, id_toko,         kota, wilayah)\n",
    "                     jumlah, total)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b805e61",
   "metadata": {},
   "source": [
    "## 4. Load — ke SQLite (warehouse mini)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6cc4af9",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\":memory:\")\n",
    "fakta.to_sql(\"fakta\", con, index=False)\n",
    "dim_produk.to_sql(\"dim_produk\", con, index=False)\n",
    "dim_toko.to_sql(\"dim_toko\", con, index=False)\n",
    "dim_waktu.assign(tanggal=dim_waktu.tanggal.astype(str)).to_sql(\"dim_waktu\", con, index=False)\n",
    "pd.read_sql(\"SELECT name FROM sqlite_master WHERE type='table'\", con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32442bdc",
   "metadata": {},
   "source": [
    "## 5. Kueri OLAP dengan SQL\n",
    "\n",
    "### Roll-up: total penjualan per kuartal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c26fb211",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "SELECT w.kuartal, SUM(f.total) AS total\n",
    "FROM fakta f JOIN dim_waktu w ON f.id_waktu = w.id_waktu\n",
    "GROUP BY w.kuartal ORDER BY w.kuartal\n",
    "\"\"\"\n",
    "pd.read_sql(q, con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ffa8e54",
   "metadata": {},
   "source": [
    "### Drill-down: kuartal → bulan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a771aeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "SELECT w.kuartal, w.bulan, SUM(f.total) AS total\n",
    "FROM fakta f JOIN dim_waktu w ON f.id_waktu = w.id_waktu\n",
    "GROUP BY w.kuartal, w.bulan ORDER BY w.kuartal, w.bulan\n",
    "\"\"\"\n",
    "pd.read_sql(q, con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fcd0f11",
   "metadata": {},
   "source": [
    "### Slice: hanya kategori Elektronik, total per wilayah"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f45da30",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "SELECT t.wilayah, SUM(f.total) AS total\n",
    "FROM fakta f\n",
    "JOIN dim_produk p ON f.id_produk = p.id_produk\n",
    "JOIN dim_toko t   ON f.id_toko = t.id_toko\n",
    "WHERE p.kategori = 'Elektronik'\n",
    "GROUP BY t.wilayah\n",
    "\"\"\"\n",
    "pd.read_sql(q, con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6396e26",
   "metadata": {},
   "source": [
    "### Dice: Fashion & Makanan, di Jakarta & Bandung, kuartal 1–2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ec10a5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "SELECT p.kategori, t.kota, w.kuartal, SUM(f.jumlah) AS unit, SUM(f.total) AS total\n",
    "FROM fakta f\n",
    "JOIN dim_produk p ON f.id_produk = p.id_produk\n",
    "JOIN dim_toko t   ON f.id_toko = t.id_toko\n",
    "JOIN dim_waktu w  ON f.id_waktu = w.id_waktu\n",
    "WHERE p.kategori IN ('Fashion', 'Makanan')\n",
    "  AND t.kota IN ('Jakarta', 'Bandung')\n",
    "  AND w.kuartal IN (1, 2)\n",
    "GROUP BY p.kategori, t.kota, w.kuartal\n",
    "\"\"\"\n",
    "pd.read_sql(q, con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05d81be7",
   "metadata": {},
   "source": [
    "## 6. Operasi OLAP dengan Pandas (pivot / kubus)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb5bede0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"kuartal\"] = df[\"tanggal\"].dt.quarter\n",
    "kubus = df.pivot_table(index=\"kategori\", columns=\"kuartal\", values=\"total\",\n",
    "                       aggfunc=\"sum\", fill_value=0, margins=True, margins_name=\"TOTAL\")\n",
    "(kubus / 1e6).round(1)   # dalam juta rupiah"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb3fcb09",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot (rotasi): tukar sumbu\n",
    "(df.pivot_table(index=\"kuartal\", columns=\"kategori\", values=\"total\", aggfunc=\"sum\") / 1e6).round(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b483b150",
   "metadata": {},
   "outputs": [],
   "source": [
    "(df.pivot_table(index=\"kuartal\", columns=\"kategori\", values=\"total\", aggfunc=\"sum\") / 1e6).plot(\n",
    "    kind=\"bar\", figsize=(8, 4), title=\"Penjualan per kuartal per kategori (juta Rp)\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2613f014",
   "metadata": {},
   "source": [
    "## 7. Semua cuboid sekaligus (GROUP BY CUBE secara manual)\n",
    "\n",
    "Dengan 3 dimensi ada 2³ = 8 cuboid."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4da15c36",
   "metadata": {},
   "outputs": [],
   "source": [
    "from itertools import combinations\n",
    "\n",
    "dims = [\"kategori\", \"wilayah\", \"kuartal\"]\n",
    "for r in range(len(dims) + 1):\n",
    "    for combo in combinations(dims, r):\n",
    "        if combo:\n",
    "            n = df.groupby(list(combo))[\"total\"].sum().shape[0]\n",
    "        else:\n",
    "            n = 1\n",
    "        print(f\"cuboid {str(combo):40s} -> {n} sel\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da18263d",
   "metadata": {},
   "source": [
    "## 8. Jebakan measure aljabar: AVG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d12c6acb",
   "metadata": {},
   "outputs": [],
   "source": [
    "per_toko = df.groupby(\"nama_toko\")[\"total\"].agg([\"mean\", \"count\"])\n",
    "salah = per_toko[\"mean\"].mean()\n",
    "benar = (per_toko[\"mean\"] * per_toko[\"count\"]).sum() / per_toko[\"count\"].sum()\n",
    "print(f\"Rata-rata dari rata-rata (SALAH): {salah:,.0f}\")\n",
    "print(f\"Rata-rata tertimbang (BENAR)    : {benar:,.0f}\")\n",
    "print(f\"Cek langsung                    : {df['total'].mean():,.0f}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ff21a9b",
   "metadata": {},
   "source": [
    "## ✍️ Coba sendiri\n",
    "\n",
    "1. Ubah dimensi produk menjadi **snowflake**: pisahkan `dim_kategori(id_kategori, nama_kategori)`.\n",
    "   Tulis kueri SQL total per kategori dengan 2 JOIN.\n",
    "2. Tulis kueri SQL: 3 produk terlaris (berdasarkan unit) di wilayah Barat pada kuartal 4.\n",
    "3. Buat pivot: baris = nama_hari, kolom = kategori, nilai = jumlah transaksi. Hari apa paling ramai?\n",
    "4. Gambar diagram star schema versimu sendiri untuk bisnis lain (mis. bioskop) di sel markdown."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71c236ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aecc787f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eca7c70f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "045d3d32",
   "metadata": {},
   "source": [
    "**Diagram no. 4:**\n",
    "\n",
    "```\n",
    "...\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec361d3c",
   "metadata": {},
   "source": [
    "## Selanjutnya\n",
    "Kerjakan kuis dan `exercise.py`, lalu jalankan `python check.py 3` dari folder utama."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}