Veri modelleme ve ambar: normalizasyon, yıldız şema, SCD

Neden veri modelleme?

Anadolu Market'in sipariş sistemi saniyede onlarca küçük işlem yapar: sepete ekle, stok düş, ödeme al. Bu sistemin tabloları hızlı ve tutarlı yazmak için tasarlanmıştır. Pazarlama müdürü ise şunu sorar: "2024'te bölge ve kategori bazında ciro nasıl değişti, hedefin ne kadarını tuttuk?" Bu soruyu sipariş sisteminin on tablosu üzerinden her seferinde yeniden JOIN'leyerek yanıtlamak hem yavaştır hem de her analist farklı bir tanım kullanır. Çözüm, analize uygun ikinci bir model kurmaktır: veri ambarı (data warehouse) ve onun en yaygın deseni olan yıldız şema (star schema).

Bu derste iki dünyayı ayıracak, normalizasyonu ve bilinçli denormalizasyonu, Kimball yönteminin olgu (fact) ve boyut (dimension) kavramlarını, tanecik (grain) seçimini, ölçü türlerini, tarih boyutunu, vekil anahtarı ve yavaş değişen boyutları (SCD) öğreneceksin. Örnekler Kesit Power BI'daki kesit_bi modeli ve Kesit SQL'deki anadolu_market verisiyle çalışır. SQL tarafını sql-analitik, yükleme tarafını veri-boru-hatti dersinde gördün.

OLTP ile OLAP

Normalizasyon: 1NF, 2NF, 3NF

Normalizasyon, aynı bilginin tek yerde tutulması için tabloları bölmektir. Amaç güncelleme anomalilerini (bir müşterinin şehrini üç satırda değiştirip birini unutmak) önlemektir.

• 1NF: Her hücrede tek değer. "urunler: Kulaklık, Klavye" gibi listeli hücre yok; her ürün ayrı satır.
• 2NF: Bileşik anahtarlı tabloda her sütun anahtarın tamamına bağlı. (siparis_no, urun_no) anahtarlı kalem tablosunda urun_adi yalnız urun_no'ya bağlıdır; ürün tablosuna taşınır.
• 3NF: Anahtar olmayan sütun başka bir anahtar olmayan sütuna bağlı değil. Siparişte musteri_no ve musteri_sehri birlikte duruyorsa şehir müşteriye bağlıdır; müşteri tablosuna gider.

duz = [   # tek tablo: her satırda müşteri ve ürün bilgisi tekrar ediyor
    {"siparis": 1, "musteri": 7, "sehir": "İzmir", "urun": 27, "urun_adi": "Keten Gömlek", "adet": 1},
    {"siparis": 1, "musteri": 7, "sehir": "İzmir", "urun": 9, "urun_adi": "Suç ve Ceza", "adet": 2},
    {"siparis": 2, "musteri": 7, "sehir": "İzmir", "urun": 9, "urun_adi": "Suç ve Ceza", "adet": 1},
]
musteri = {r["musteri"]: {"sehir": r["sehir"]} for r in duz}
urun = {r["urun"]: {"ad": r["urun_adi"]} for r in duz}
siparis = {r["siparis"]: {"musteri": r["musteri"]} for r in duz}
kalem = [(r["siparis"], r["urun"], r["adet"]) for r in duz]
print(musteri, urun, siparis, kalem, sep="\n")

Ambar tarafında ise bilinçli olarak denormalize ederiz: ürün boyutunda kategori adı ürünün yanında durur, kategori ayrı tabloya bölünmez. Okuma çok, yazma az olduğu için tekrar maliyeti düşük, JOIN tasarrufu büyüktür.

Kimball: olgu, boyut ve tanecik

Olgu tablosu (fact table) ölçülebilir olayları tutar: satış satırı, uçuş, banka işlemi. Sütunları boyut anahtarları ve sayısal ölçülerdir (adet, tutar). Boyut tablosu (dimension table) olayın bağlamını verir: kim (müşteri), ne (ürün), ne zaman (tarih), nerede (mağaza). Filtreler ve gruplamalar boyutlardan gelir.

Modelin ilk ve en önemli kararı taneciktir (grain): "olgu tablosunda bir satır tam olarak neyi temsil eder?" Kimball'ın sırası: iş sürecini seç, tanecik bildir, boyutları belirle, ölçüleri belirle. Tanecik net değilse sayılar ikiye katlanır ya da kaybolur.

Yıldız ve kar tanesi: kesit_bi modelini okumak

Yıldız şemada olgu tablosu ortada, her boyut tek bir JOIN uzaklığındadır. Kar tanesi (snowflake) şemada boyutlar da normalize edilir (ürün → kategori → departman); daha az tekrar, daha çok JOIN. Power BI ve çoğu ambar için önerilen yıldızdır.

Kesit Power BI'daki kesit_bi modeli tipik bir yıldızdır: Satislar olgu tablosu (tanecik: sipariş satırı, SatirNo benzersiz), Urunler, Musteriler, Tarih boyutları. Hedefler ayrı bir olgu tablosudur ve taneciği farklıdır (ay × kategori). StokGorunum ise periyodik anlık görüntü (periodic snapshot) olgusudur: her ay sonu, her ürün için bir satır.

import veri

con = veri.baglan("kesit_bi", tablolar=["Satislar", "Urunler", "Musteriler", "Tarih"])
sql = """
SELECT t.Yil, u.Kategori, ROUND(SUM(s.Tutar), 2) AS ciro
FROM Satislar s
JOIN Tarih t      ON t.Tarih = s.Tarih
JOIN Urunler u    ON u.UrunNo = s.UrunNo
JOIN Musteriler m ON m.MusteriNo = s.MusteriNo
WHERE s.Durum NOT IN ('İptal', 'İade') AND t.Yil = 2024 AND m.Segment = 'Premium'
GROUP BY t.Yil, u.Kategori
ORDER BY ciro DESC
LIMIT 3
"""
for satir in con.execute(sql):
    print(satir)
# tanecik kontrolü: SatirNo benzersiz mi?
print(con.execute("SELECT COUNT(*), COUNT(DISTINCT SatirNo) FROM Satislar").fetchone())

Sorgunun şekline dikkat: tüm filtreler (yıl, segment) boyutlardan, toplanan değer olgudan gelir. Power BI'da aynı şeyi model görünümünde 1-çok (one-to-many) ilişkilerle, dilimleyicilerle (slicer) yaparsın; Kesit Power BI'daki modelin tam bu yapıda olmasının nedeni budur.

Tarih boyutu ve vekil anahtar

Tarih boyutu her ambarın ilk boyutudur: her gün için bir satır; yıl, çeyrek, ay adı, hafta sonu, tatil gibi sütunlar. Böylece "hafta sonu satışları" ya da "çeyrek bazında" sorgular tarih fonksiyonu yazmadan, basit bir filtreyle yapılır. Tarih boyutunu Python ile birkaç satırda üretebilirsin:

from datetime import date, timedelta

AYLAR = ["Oca", "Şub", "Mar", "Nis", "May", "Haz", "Tem", "Ağu", "Eyl", "Eki", "Kas", "Ara"]

def tarih_satiri(g):
    return {
        "tarih_key": g.year * 10000 + g.month * 100 + g.day,   # 20240315 gibi okunur vekil anahtar
        "tarih": g.isoformat(),
        "yil": g.year,
        "ceyrek": (g.month - 1) // 3 + 1,
        "ay_adi": AYLAR[g.month - 1],
        "hafta_sonu": g.weekday() >= 5,
    }

baslangic = date(2024, 3, 29)
for i in range(3):
    print(tarih_satiri(baslangic + timedelta(days=i)))

Vekil anahtar (surrogate key), boyut satırına ambarın verdiği anlamsız tamsayı kimliktir; kaynak sistemin doğal anahtarı (natural/business key: müşteri no, ürün kodu) ayrı sütunda durur. Neden? (1) Aynı müşterinin geçmiş sürümlerini (SCD2) ayırt etmek için her sürüme ayrı anahtar gerekir; (2) farklı kaynak sistemlerin numaraları çakışabilir; (3) tamsayı JOIN'leri hızlıdır; (4) "bilinmeyen müşteri" için -1 gibi özel bir satır tutabilirsin. Tarih boyutunda ise 20240315 biçimi istisnai olarak okunur bir vekil anahtardır.

Yavaş değişen boyutlar (SCD)

Müşteri Ankara'dan İzmir'e taşındı. Geçen yılın satışları hangi bölgede görünmeli? Bu sorunun cevabı SCD (slowly changing dimension) tipidir:

boyut = [{"key": 1, "musteri_no": 7, "sehir": "Ankara", "baslangic": "2023-01-01", "bitis": None, "guncel": True}]

def scd2(boyut, musteri_no, yeni_sehir, tarih):
    eski = next(r for r in boyut if r["musteri_no"] == musteri_no and r["guncel"])
    if eski["sehir"] == yeni_sehir:
        return                                   # değişiklik yok, yeni sürüm açma
    eski["bitis"], eski["guncel"] = tarih, False  # eski sürümü kapat
    boyut.append({"key": max(r["key"] for r in boyut) + 1, "musteri_no": musteri_no,
                  "sehir": yeni_sehir, "baslangic": tarih, "bitis": None, "guncel": True})

scd2(boyut, 7, "İzmir", "2024-06-01")
scd2(boyut, 7, "İzmir", "2024-07-01")            # tekrar: etkisiz
for r in boyut:
    print(r)

SCD2'de olgu satırı, olay tarihinde geçerli olan sürümün vekil anahtarını taşır; böylece 2023 satışı Ankara'da, 2024 Temmuz satışı İzmir'de görünür. SCD2'nin SQL ve upsert ile yüklenmesini veri-boru-hatti dersinde, dbt snapshot ile otomatikleştirilmesini dbt-elt dersinde görürsün.

anadolu_market'ten yıldız şemaya

Kesit SQL'deki anadolu_market bir OLTP modelidir: orders (başlık), order_items (kalem), customers, products. Bunu yıldıza çevirmek için: tanecik = sipariş kalemi; boyutlar = tarih, müşteri, ürün (+ kanal, durum gibi düşük kardinaliteli sütunlar olguda kalabilir); ölçüler = adet, brüt tutar, indirim, net tutar. DuckDB ile:

import duckdb
import veri

con = duckdb.connect()
for t in ["orders", "order_items", "products"]:
    con.execute(f"CREATE TABLE {t} AS SELECT * FROM read_csv('{veri.yol('anadolu_market', t)}')")

con.execute("""
CREATE TABLE dim_urun AS
SELECT ROW_NUMBER() OVER (ORDER BY product_id) AS urun_key, product_id, name AS urun, category AS kategori
FROM products
""")
con.execute("""
CREATE TABLE fact_satis AS
SELECT i.item_id,
       CAST(strftime(o.order_date, '%Y%m%d') AS INTEGER) AS tarih_key,
       d.urun_key, o.customer_id, o.channel AS kanal, o.status AS durum,
       i.quantity AS adet,
       ROUND(i.quantity * i.unit_price * (1 - i.discount), 2) AS net_tutar
FROM order_items i
JOIN orders o   ON o.order_id = i.order_id
JOIN dim_urun d ON d.product_id = i.product_id
""")
print(con.execute("SELECT COUNT(*), ROUND(SUM(net_tutar), 2) FROM fact_satis").fetchone())
print(con.execute("""
    SELECT d.kategori, ROUND(SUM(f.net_tutar), 2) FROM fact_satis f JOIN dim_urun d USING (urun_key)
    WHERE f.durum <> 'cancelled' GROUP BY 1 ORDER BY 2 DESC LIMIT 3
""").fetchall())

Olgu tablosu 1620 satır, kalem tablosuyla aynı: tanecik korundu. Toplam net tutar kesit_bi.Satislar'ın tutar toplamıyla (1.346.189,85) bire bir aynı; Kesit Power BI modeli de tam olarak bu dönüşümle üretilmiştir.

Data vault ve one-big-table

• Data vault: hub (iş anahtarları), link (ilişkiler) ve satellite (zamanla değişen öznitelikler) tablolarıyla, kaynakları değiştirmeden ve tam denetim izi bırakarak entegre eder. Çok kaynaklı, düzenlemeye tabi kurumlarda ham entegrasyon katmanı olarak kullanılır; üstüne yine yıldız şema kurulur.
• One-big-table (OBT): tüm boyut sütunlarını olguya katlayıp tek geniş tablo yapmak. Sütun tabanlı ambarlarda (BigQuery, DuckDB) basit raporlar için hızlı ve kolaydır; ama SCD, tutarlı boyut ve bakım zorlaşır. Genellikle yıldızın üstüne bir sunum katmanı olarak üretilir.