Yıldız ve Kar Tanesi Şeması

Önceki ders boyut bağıntılarını düzleştirilmiş kurdu: abonenin bölgesi, bölge adının kendisi olarak 200 satırın her birinde yazıldı. Bölge kümesi beş elemanlıdır, yani aynı beş metin boyut bağıntısında kırk kez tekrarlanıyor. Olgudan çıkarılan tekrar boyutun içinde yeniden ortaya çıktı, yalnızca on bir kat daha küçük ölçekte.

Aynı işlemi bir kez daha uygulamak mümkündür: bölgeyi ayrı bir tabloya çıkarıp abone boyutunda yalnızca bir başvuru bırakmak. Bu, olgu ile bölge adı arasına ikinci bir halka koyar ve şemanın biçimini değiştirir. Olgunun çevresinde tek katmanlı boyutlar duran düzen yıldız şeması (star schema), boyutların kendi alt tablolarına dallandığı düzen kar tanesi şeması (snowflake schema) adını alır. Bu ders iki şemayı aynı veriyle kurar ve normalleştirme derecesinin boyut tarafında kaç satır, sorgu tarafında kaç birleştirme ettiğini sayar.

• AM9. Kaynak, hat ve depo kurgudur; gerçek bir ambar, göl, dosya biçimi ya da sorgu motoru çağrılmaz. Tohum 20260218, ölçü koşumdan bağımsızdır.
• AM10. Normalleştirmenin kuramı, işlevsel bağımlılık ve normal biçim yordamı Veri Modelleme ve İlişkisel Kuram kursunda ölçüldü ve tekrarlanmaz. Buradaki soru yalnızca derecenin boyut satırına ve birleştirme sayısına ne ödettiğidir.
• AM11. İki şema aynı olgu bağıntısını paylaşır; değişen tek şey boyutların düzenidir. Olgunun taneciği abone ile dönemin çiftidir ve 2200 satırdır.
• AM12. Bölge tablosunun anahtarı bölge dizinidir. Anahtar bir hücredir; genişliği ölçülmez.
• AM13. Birleştirme sayısı sorgunun boyut bağıntılarına toplam kaç kez baktığıdır. Kar tanesinde her olgu satırı için önce abone boyutuna, sonra bölge tablosuna bakılır.
• AM14. Maliyet dosya x 120 + hücre biçiminde okunur. Dosya sabiti 120 bir seçimdir ve ayrı bir boyut bağıntısı en az bir dosya demektir.
• AM15. Taban çizgisi yıldız şemasıdır: iki boyut bağıntısı, tek katman, dallanma yok.
• AM16. Çözünürlük: satır oranının adımı 0,0005. Toplamlar oran olarak değil, değerin kendisi olarak yazılır.

İki Şemanın Kurulması

İlk blok ortak tanımı kurar. yildiz işlevi önceki dersteki düzeni verir: olgu üç alan taşır, abone boyutu bölge adını ve tipi taşır. kar_tanesi aynı veriden başlar, abone boyutundaki bölge adını bölge dizinine çevirir ve beş satırlık bir bölge tablosu açar. sorgu_bolge_toplam iki düzende de aynı soruyu yanıtlar ve yol boyunca kaç kez boyuta baktığını sayar; kar tanesinde her satır için bir bakış fazladan yapılır, çünkü abone boyutundan çıkan dizin bir ad değildir.

# KURGUDUR. Sayac okumalari, hat ve depo ders icinde tanimlanir; gercek bir
# ambar, gol, dosya bicimi ya da sorgu motoru cagrilmaz.
TOHUM, M32 = 20260218, 0xFFFFFFFF


def uretec(t):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def sonraki():
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return sonraki


BOLGE = ["dogu", "bati", "kuzey", "guney", "merkez"]
TIP = ["mesken", "isyeri"]
DONEM = [f"2026-{a:02d}" for a in range(1, 13)]
ABONE = {}
for i in range(200):
    r = uretec(TOHUM + 37 * i)
    ABONE[f"A{i + 1:03d}"] = {"bolge": BOLGE[int(r() * 5)], "tip": TIP[int(r() * 2)],
                              "baslangic": 10000 + int(r() * 60000)}


def kaynak_uret():
    kayit = []
    for i, (ab, a) in enumerate(ABONE.items()):
        e = a["baslangic"]
        for d in DONEM:
            r = uretec(TOHUM + 101 * i + 7 * DONEM.index(d))
            e += 4 + int(r() * 46)
            kayit.append({"abone": ab, "donem": d, "bolge": a["bolge"], "tip": a["tip"],
                          "endeks": e, "birim": "m3"})
    return kayit


def a1_ayikla(kayit):
    ZOR = ("abone", "donem", "bolge", "endeks", "birim")
    tut = [k for k in kayit if all(a in k for a in ZOR)]
    return tut, len(kayit) - len(tut)


def a2_tekille(kayit):
    gor, tut = set(), []
    for k in kayit:
        ad = (k["abone"], k["donem"])
        if ad in gor:
            continue
        gor.add(ad)
        tut.append(k)
    return tut, len(kayit) - len(tut)


def a3_tuketim(kayit):
    tablo = {(k["abone"], k["donem"]): k for k in kayit}
    cikti, dusen = [], 0
    for k in kayit:
        i = DONEM.index(k["donem"])
        if i == 0:
            continue
        onceki = tablo.get((k["abone"], DONEM[i - 1]))
        if onceki is None:
            dusen += 1
            continue
        try:
            f = int(k["endeks"]) - int(onceki["endeks"])
        except (TypeError, ValueError):
            dusen += 1
            continue
        cikti.append(dict(k, m3=f))
    return cikti, dusen


SUTUN = ("abone", "donem", "bolge", "tip", "endeks", "birim", "m3")
DOSYA_SABIT = 120          # her dosyaya dokunmanin sabit bedeli , hucre cinsinden


def satirlar():
    v, _ = a1_ayikla(kaynak_uret())
    v, _ = a2_tekille(v)
    v, _ = a3_tuketim(v)
    return v


def yaz(kayitlar, bolumleme=()):
    duzen = {}
    for k in kayitlar:
        anahtar = tuple(k[a] for a in bolumleme)
        duzen.setdefault(anahtar, []).append(k)
    return duzen


def sorgu(duzen, bolumleme, secilen, suzgec=None, sutunlu=True):
    suzgec = {} if suzgec is None else suzgec
    budayan = {a: v for a, v in suzgec.items() if a in bolumleme}
    kalan = {a: v for a, v in suzgec.items() if a not in bolumleme}
    dosya, hucre, satir = 0, 0, 0
    okunan_sutun = len(secilen) if sutunlu else len(SUTUN)
    for anahtar, kayit in duzen.items():
        if budayan:
            eslesme = dict(zip(bolumleme, anahtar))
            if any(eslesme[a] != v for a, v in budayan.items()):
                continue
        dosya += 1
        hucre += len(kayit) * okunan_sutun
        satir += sum(1 for k in kayit if all(k[a] == v for a, v in kalan.items()))
    return {"dosya": dosya, "hucre": hucre, "eslesen_satir": satir,
            "maliyet": dosya * DOSYA_SABIT + hucre}


def yildiz(kayitlar):
    """Duzlestirilmis boyutlar: bolge adi abone boyutunun icinde durur."""
    olgu = [{"abone": k["abone"], "donem": k["donem"], "m3": k["m3"]} for k in kayitlar]
    d_abone = {k["abone"]: {"bolge": k["bolge"], "tip": k["tip"]} for k in kayitlar}
    d_donem = {d: {"yil": d[:4], "ay": d[5:]} for d in DONEM}
    return olgu, {"abone": d_abone, "donem": d_donem}


def kar_tanesi(kayitlar):
    """Ayni veri , bolge ayri bir tabloya cikarilmis."""
    olgu, boy = yildiz(kayitlar)
    d_abone = {a: {"bolge_no": BOLGE.index(v["bolge"]), "tip": v["tip"]}
               for a, v in boy["abone"].items()}
    d_bolge = {i: {"ad": b} for i, b in enumerate(BOLGE)}
    return olgu, {"abone": d_abone, "bolge": d_bolge, "donem": boy["donem"]}


def sorgu_bolge_toplam(olgu, boy, kar=False):
    """Bolge basina toplam. Kar tanesinde bir birlestirme fazla."""
    birlestirme, top = 0, {}
    for o in olgu:
        a = boy["abone"][o["abone"]]
        birlestirme += 1
        if kar:
            b = boy["bolge"][a["bolge_no"]]["ad"]
            birlestirme += 1
        else:
            b = a["bolge"]
        top[b] = top.get(b, 0) + o["m3"]
    return top, birlestirme

Kazanılan Satır, Ödenen Birleştirme

İkinci blok iki şemayı kurar, boyut tarafını sayar ve aynı sorguyu ikisine de sorar.

SATIR = satirlar()
Y_OLGU, Y_BOYUT = yildiz(SATIR)
K_OLGU, K_BOYUT = kar_tanesi(SATIR)
y_top, y_bir = sorgu_bolge_toplam(Y_OLGU, Y_BOYUT)
k_top, k_bir = sorgu_bolge_toplam(K_OLGU, K_BOYUT, kar=True)
y_satir = len(Y_BOYUT["abone"]) + len(Y_BOYUT["donem"])
k_satir = len(K_BOYUT["abone"]) + len(K_BOYUT["bolge"]) + len(K_BOYUT["donem"])
y_hucre = len(Y_BOYUT["abone"]) * 3 + len(Y_BOYUT["donem"]) * 3
k_hucre = len(K_BOYUT["abone"]) * 3 + len(K_BOYUT["bolge"]) * 2 + len(K_BOYUT["donem"]) * 3
print(f"{'sema':<12}{'boyut baginti':>15}{'boyut satiri':>14}{'boyut hucresi':>15}"
      f"{'birlestirme':>13}")
print(f"{'yildiz':<12}{2:>15}{y_satir:>14}{y_hucre:>15}{y_bir:>13}")
print(f"{'kar tanesi':<12}{3:>15}{k_satir:>14}{k_hucre:>15}{k_bir:>13}")
print("sonuc birebir ayni:", y_top == k_top)
print("sonuc:", y_top)

sema          boyut baginti  boyut satiri  boyut hucresi  birlestirme
yildiz                    2           212            636         2200
kar tanesi                3           217            646         4400
sonuc birebir ayni: True
sonuc: {'merkez': 14536, 'kuzey': 13338, 'dogu': 9695, 'bati': 9164, 'guney': 11721}

Tablo dersin tamamını taşır. Boyut satırı 212’den 217’ye çıkıyor: beş satır, bölge tablosunun kendisi. Boyut hücresi 636’dan 646’ya çıkıyor. Birleştirme ise 2200’den 4400’e, yani iki katına yükseliyor. Sonuç satırı ikisinin de aynı sözlüğü ürettiğini söyler.

Buradaki asıl bulgu, beklenenin tersi olmasıdır. Normalleştirme tekrarı kaldırdığı için yer kazandırmalıydı; bu ölçümde kazandırmıyor. Abone boyutunda bölge adının yerini bölge dizini alıyor, ama sütun sayısı değişmiyor: 200 satır hâlâ üç hücre taşıyor. Kazanılan tek şey adın metin yerine dizin olmasıdır ve bu ölçüde bir hücre bir hücredir. Buna karşılık beş satırlık bölge tablosu ile onun on hücresi baştan eklenmiş oluyor. Beş satır ve on hücre eksi yönde, birleştirme iki katına çıkacak biçimde artı yönde.

Sonucun birebir aynı kalması, bu takasın yalnızca bedel tarafında olduğunu gösterir. Kar tanesi şeması yıldız şemasının veremediği bir cevabı vermiyor, aynı cevabı iki kat birleştirmeyle veriyor. Kursun kuralı bu satırı okur: düzenin sayısı sakladığı satır değil, bir sorgunun ona dokunmak için ödediğidir; bu sorgu için ödenen 4400 birleştirmedir ve hangi sorgu olduğu yazılmadan bu sayının anlamı yoktur.

Bu ölçüm aynı zamanda kursun ikinci iddiasının bu derse düşen biçimidir: düzen kararı işi kötüleştirebilir. Tek nitelik taşıyan bir bölge tablosuyla kurulan kar tanesi on hücre kaybettiriyor, birleştirmeyi ikiye katlıyor ve karşılığında hiçbir yeni cevap vermiyor. Kötüleşmenin fark edilmemesinin nedeni de aynı satırdadır: sonuç kımıldamıyor.

Maliyet, Güncelleme ve Ödemenin Başladığı Nokta

Üçüncü blok üç şeyi arka arkaya ölçer: aynı sorgunun iki şemada dokunduğu dosya ve ödediği maliyet, bir bölge adı düzeltildiğinde güncellenen satır sayısı, ve bölge tablosunun taşıdığı nitelik sayısı arttıkça hücre dengesinin nereye gittiği.

y_ab = [{"abone": a, **v} for a, v in Y_BOYUT["abone"].items()]
k_ab = [{"abone": a, **v} for a, v in K_BOYUT["abone"].items()]
k_bl = [{"bolge_no": i, **v} for i, v in K_BOYUT["bolge"].items()]
q_olgu = sorgu(yaz(Y_OLGU, ()), (), ("abone", "m3"))
q_yab = sorgu(yaz(y_ab, ()), (), ("abone", "bolge"))
q_kab = sorgu(yaz(k_ab, ()), (), ("abone", "bolge_no"))
q_kbl = sorgu(yaz(k_bl, ()), (), ("bolge_no", "ad"))
y = [q_olgu, q_yab]
k = [q_olgu, q_kab, q_kbl]
print(f"{'sema':<12}{'dosya':>7}{'hucre':>7}{'maliyet':>9}")
for ad, q in (("yildiz", y), ("kar tanesi", k)):
    print(f"{ad:<12}{sum(x['dosya'] for x in q):>7}{sum(x['hucre'] for x in q):>7}"
          f"{sum(x['maliyet'] for x in q):>9}")
print("fark:", sum(x["maliyet"] for x in k) - sum(x["maliyet"] for x in y),
      " bunun", DOSYA_SABIT, "kadari dosya sabiti")
print()
n_ab = sum(1 for v in Y_BOYUT["abone"].values() if v["bolge"] == "kuzey")
print("bir bolgenin adi degisiyor: yildiz", n_ab, "satir , kar tanesi 1 satir")
print()
print(f"{'bolge niteligi':>15}{'yildiz hucre':>14}{'kar tanesi hucre':>18}{'fark':>7}")
for n in range(1, 7):
    y_h = len(Y_BOYUT["abone"]) * (2 + n)
    k_h = len(K_BOYUT["abone"]) * 3 + len(K_BOYUT["bolge"]) * (1 + n)
    print(f"{n:>15}{y_h:>14}{k_h:>18}{y_h - k_h:>7}")

sema          dosya  hucre  maliyet
yildiz            2   4800     5040
kar tanesi        3   4810     5170
fark: 130  bunun 120 kadari dosya sabiti

bir bolgenin adi degisiyor: yildiz 46 satir , kar tanesi 1 satir

 bolge niteligi  yildiz hucre  kar tanesi hucre   fark
              1           600               610    -10
              2           800               615    185
              3          1000               620    380
              4          1200               625    575
              5          1400               630    770
              6          1600               635    965

Maliyet satırı beklenenden küçük bir fark verir: 5040’a karşı 5170, aradaki 130. Bunun 120’si üçüncü dosyaya dokunmanın sabit bedelidir, kalan 10 bölge tablosunun on hücresi. Okunan hücre neredeyse hiç artmıyor, çünkü bölge tablosu beş satırlıktır. Dosya sabiti 120 seçildiği için ek tablonun bedeli bu kadar; sabit daha büyük seçilseydi kar tanesinin okuma tarafındaki bedeli neredeyse tamamen sabitten gelirdi. Birleştirmenin iki katına çıkması bu ölçüde görünmez, çünkü birleştirme dosyaya dokunmaz; sayılan iki şey ayrı bedellerdir ve ikisi de yazılmadan şema ölçülmemiş sayılır.

Güncelleme satırı normalleştirmenin gerçekten aldığı karşılıktır. Bir bölgenin adı düzeltildiğinde yıldız şemasında 46 abone satırı güncellenir, kar tanesinde 1 satır. Yıldızda ad kırk altı yerde durduğu için güncellemenin kırk altı yere ulaşması gerekir; kar tanesinde adın tek bir kopyası vardır ve başka bir yerde bulunamaz. Kazanç saklamada değil, tutarsızlığın oluşabileceği yüzeyde ortaya çıkıyor.

İki tarafın ölçekle ilişkisi zıttır ve şema kararı bu zıtlıktan çıkar. Kar tanesinin ödettiği fazladan birleştirme olgu satırı başına birdir: 2200 satırda 2200 fazla, olgu iki katına çıksaydı 4400 fazla. Kazandırdığı güncelleme yüzeyi ise olgudan bağımsızdır; bölgenin adı 46 abone satırında duruyor ve olgu ne kadar büyürse büyüsün abone sayısı değişmediği sürece 46’da kalıyor. Yani veri büyüdükçe dallanmanın bedeli büyür, kazancı yerinde sayar. Karar bu yüzden boyutun kaç kez tekrarlandığına değil, olgu satırının boyut satırına oranına bakılarak verilir; bu ölçümde oran on birdir ve her birleştirme o oranla çarpılarak ödenir.

Son tablo dengenin nereye gittiğini söyler. Bölge tablosu tek nitelik taşırken normalleştirme 10 hücre kaybettiriyor. İkinci nitelikte tablo dönüyor: yıldız 800, kar tanesi 615, aradaki fark 185 hücre kar tanesi lehine. Üçüncü nitelikte fark 380’e, altıncıda 965’e çıkıyor. Eşiğin nerede olduğu tekrar sayısına bağlıdır: 200 abone satırı, 5 bölge satırı ve niteliğin abone başına bir kez yazılması. Bölgenin taşıdığı bilgi tek bir addan ibaretse normalleştirme ödemiyor; bölge birkaç nitelik taşımaya başladığı anda ödemeye başlıyor ve birleştirme sayısı buradan bağımsız olarak iki katında kalıyor.

Özet

• Yıldız şeması boyutları düzleştirilmiş tutar, kar tanesi şeması boyutu kendi alt tablosuna dallandırır; iki şema aynı olgu bağıntısını ve aynı 2200 satırı paylaşır.
• Bölge ayrı bir tabloya çıkarıldığında boyut satırı 212’den 217’ye, boyut hücresi 636’dan 646’ya çıkar ve birleştirme 2200’den 4400’e, yani iki katına yükselir.
• İki şemanın ürettiği bölge toplamı birebir aynıdır; kar tanesi yeni bir cevap vermez, aynı cevabı iki kat birleştirmeyle verir.
• Maliyet 5040’tan 5170’e çıkar ve farkın 120’si dosya sabitidir; birleştirmenin ikiye katlanması maliyette görünmez, ayrı bir bedeldir ve ayrı yazılır.
• Normalleştirmenin aldığı karşılık güncelleme yüzeyidir: bir bölge adının düzeltilmesi yıldızda 46, kar tanesinde 1 satıra dokunur.
• Hücre dengesi bölge tablosunun ikinci niteliğinde döner: tek nitelikte kar tanesi 10 hücre kaybettirir, iki nitelikte 185, altı nitelikte 965 hücre kazandırır.

Sonraki Adım

İki şema da bu derste sessiz bir varsayımla çalıştı: abonenin bölgesi ve tipi değişmez. Boyut tablosu abone başına tek satır tutuyor, o satır abonenin bilgisini veriyor ve olgunun hangi dönemine bakıldığı bu bilgiyi etkilemiyor. Gerçek boyutlar böyle davranmaz. Bir mesken işyerine dönüşür, bir abone başka bölgeye taşınır, bir sınıflandırma yeniden yapılır. Boyut satırı bir kez üzerine yazıldığında geçmiş dönemlerin olguları da yeni bilgiyle nitelenir ve sorgu bunu hiçbir yerde bildirmez. Sonraki ders bu değişimi ölçer: tarihçe tutmayan bir boyut sorguyu ne kadar yavaşlatıyor, cevabı ne kadar değiştiriyor ve tarihçe tutmanın boyut satırına bedeli ne oluyor.