Veri ambarları: yıldız şema, olgular ve boyutlar

OLTP ve OLAP farkı, olgu ve boyut tabloları, tanecik (grain), vekil anahtarlar, yıldız ve kar tanesi şemaları, yavaş değişen boyutlar (SCD 1 ve 2), ETL/ELT ve sütunlu ambarlar; alıştırma veritabanı SQLite'ta bir yıldıza dönüştürülüyor.

  • Üniversite
  • 25 dk
  • 12 soru
  • Ders 28 / 28

Bu derste öğreneceklerin

  • OLTP ve OLAP sistemlerinin amacını ve şemalarının neden farklı olduğunu açıklamak
  • Taneciği seçerek olgu ve boyut tablolarından bir yıldız şema kurmak ve sorgulamak
  • Vekil anahtarları, tarih boyutunu ve toplanamayan ölçüleri doğru kullanmak
  • Değişen boyutları tip 1 ve tip 2 olarak saklamak, yıldızı kar tanesiyle karşılaştırmak

Bir mağaza veritabanı saniyede yüzlerce küçük işlem için kurulur: sipariş yaz, stoğu azalt, ödemeyi kaydet. Şimdi yönetim soruyor: “Son üç yılda gelir çeyreklere ve ülkelere göre nasıl değişti?” Böyle bir sorgu milyonlarca satır okur, beş tabloyu birleştirir ve çalışan mağazayı yavaşlatır. Üstelik eski veriler ya silinmiş ya da üzerine yazılmıştır. Bu yüzden analiz için ayrı bir veritabanı, bir veri ambarı (data warehouse) kurulur ve başka bir ilkeyle tasarlanır. «Veritabanı tasarımı ve normalizasyon» dersinin sonunda yıldız şemanın adı geçmişti; bu derste onu adım adım kuruyoruz.

OLTP ve OLAP: iki farklı iş

OLTPOLAP
Amaçgünlük işlemleranaliz ve raporlar
Tipik sorgubir siparişi oku ya da değiştirmilyonlarca satır üzerinde toplamlar
Yazmasürekli, küçük parçalarladönemsel yükleme (örneğin gece)
Şemanormalleştirilmiş (3NF)bilerek normalleştirilmemiş yıldız
Geçmişgüncel durumyılların geçmişi
Örnek sistemlerPostgreSQL, MySQL, SQL ServerClickHouse, BigQuery, Snowflake, Redshift

Bir ambar, veriyi birkaç kaynaktan (web sitesi, kasa, CRM) alır, temizler ve raporlar için elverişli bir biçimde saklar. Ana fikir: yazmayı değil okumayı iyileştirmek. Bunun için uygulamada en çok kullanılan model, Ralph Kimball'ın yaygınlaştırdığı boyutsal modellemedir (dimensional modeling).

  • ETL (extract, transform, load): veri kaynaktan alınır, ayrı bir araçta temizlenip yıldıza dönüştürülür, sonra ambara yüklenir; klasik yol budur.
  • ELT: ham veri önce ambara yüklenir, dönüştürme orada SQL ile yapılır (örneğin dbt aracıyla). Güçlü bulut ambarlarıyla bu, standart hâline geldi.
  • Sütunlu saklama: her sütun ayrı saklanır. SUM(revenue) yalnızca tek bir sütunu okur, tekrarlanan değerler (kategori, ülke) iyi sıkıştırılır; ClickHouse, BigQuery, Snowflake, Redshift ve kendi bilgisayarında DuckDB böyle çalışır.

Okumaya devam etmek için ücretsiz kaydol

Dersin geri kalan bölümleri, test ve notlar Educora hesabıyla açılır. Kayıt bir dakika sürer; ilerlemen tüm cihazlarında saklanır.

Olgular, boyutlar ve tanecik

Olgu tablosuTanım

İş olaylarının sayısal ölçülerini (miktar, gelir) ve boyutlara giden yabancı anahtarları saklayan uzun, dar tablo. Satırların çoğu buradadır.

Boyut tablosuTanım

Olayın bağlamını anlatan geniş tablo: kim (müşteri), ne (ürün), ne zaman (tarih), nerede (mağaza). Raporlar onun sütunlarına göre gruplar ve filtreler.

Tanecik (grain)Tanım

Olgu tablosunun bir satırının neyi temsil ettiğinin kesin tanımı; örneğin “bir sipariş kalemi” ya da “bir ürünün bir günlük stoğu”. Tanecik, boyutlardan ve ölçülerden önce seçilir.

1N1NN1dim_datedate_keyfull_datequarteris_weekenddim_productproduct_keyproduct_idnamecategoryfact_salesdate_keyproduct_keycustomer_keyΣ quantityΣ revenuedim_customercustomer_keycustomer_idcitycountryolgu tablosuboyut tablosu
Yıldız: her boyut olgu tablosuna bir JOIN uzaklığındadır.

Ölçüler üç türlüdür. Toplanabilir ölçüler (miktar, gelir) her boyut boyunca toplanabilir. Yarı toplanabilir bir ölçü (eldeki stok, hesap bakiyesi) ürünler boyunca toplanır ama günler boyunca toplanmaz: pazartesi 10 ve salı 10 adet stok “20 adet” demek değildir. Toplanamayan ölçüler (birim fiyat, yüzde) hiç toplanmaz; toplanabilir parçalardan hesaplanır:

average_price = Σ revenue / Σ quantityaverage_price = Σ revenue / Σ quantity

Burada

Σ revenue
seçilen satırların gelir toplamı
Σ quantity
aynı satırlarda satılan adetlerin toplamı

Ortalama fiyat ağırlıklı hesaplanır; AVG(unit_price) her satıra aynı ağırlığı verir ve yanlıştır.

Ortalama fiyat: iki yol

Problem

Olgu tablosunda iki satır var: 100'den 1 adet ve 60'tan 3 adet. Bir adedin ortalama fiyatı nedir?

Çözümü gösterÇözümü gizle

Çözüm

1) Gelirler: 1 · 100 = 100 ve 3 · 60 = 180; Σ revenue = 280.
2) Σ quantity = 1 + 3 = 4.
3) Ortalama fiyat = 280 / 4 = 70.
4) AVG(unit_price) (100 + 60) / 2 = 80 verirdi; ucuz üründen daha çok satıldığı için bu yanlıştır.

Vekil anahtarlar ve tarih boyutu

Vekil anahtarTanım

Ambarın kendisinin verdiği, anlamı olmayan bir tam sayı anahtar (product_key). Kaynaktaki iş anahtarı (products.id) ayrı bir sütunda tutulur. Nedenler: kaynaklar id değerlerini değiştirebilir ya da yeniden kullanabilir, birkaç kaynak birleştirilir ve SCD tip 2'de bir ürünün birkaç sürümü olur.

Raporların yarısı “ne zaman” sorusudur: çeyrek, ay, hafta sonu, bayram. Bunları her sorguda strftime ile hesaplamak yerine her gün için bir satırı olan bir tarih boyutu kurulur. Anahtar olarak okunabilir bir tam sayı seçilir:

date_key = year · 10000 + month · 100 + day

Burada

year
yıl, örneğin 2025
month
ay, 1–12
day
ayın günü, 1–31

Böyle bir anahtar tarih gibi sıralanır ve gözle okunur.

Anahtar ve çeyrek

Problem

1) 2025-04-09 tarihinin date_key değeri nedir? 2) Çeyrek = (ay + 2) / 3 (tam bölme) ise bu tarih hangi çeyrektedir?

Çözümü gösterÇözümü gizle

Çözüm

1) 2025 · 10000 + 4 · 100 + 9 = 20.250.000 + 400 + 9 = 20250409.
2) (4 + 2) / 3 = 6 / 3 = 2, yani ikinci çeyrek. Kontrol: mart için (3 + 2) / 3 = 5 / 3 = 1, nisan için 2.

Özyinelemeli bir CTE (bkz. «CTE: WITH ve özyinelemeli sorgular») 2025 yılının her günü için bir satır üretir:

SQL
CREATE TABLE dim_date AS
WITH RECURSIVE d(day) AS (
  SELECT '2025-01-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2025-12-31'
)
SELECT CAST(strftime('%Y%m%d', day) AS INTEGER)        AS date_key,
       day                                             AS full_date,
       CAST(strftime('%Y', day) AS INTEGER)            AS year,
       (CAST(strftime('%m', day) AS INTEGER) + 2) / 3  AS quarter,
       CAST(strftime('%m', day) AS INTEGER)            AS month,
       strftime('%w', day) IN ('0', '6')               AS is_weekend
FROM d;

SELECT COUNT(*) AS days, SUM(is_weekend) AS weekend_days,
       MIN(date_key) AS first_key, MAX(date_key) AS last_key
FROM dim_date;
Beklenen çıktı
days | weekend_days | first_key | last_key
365 | 104 | 20250101 | 20251231
365 gün, bunların 104'ü cumartesi ya da pazar.

Yıldızı kurmak ve sorgulamak

  1. Tanecik

    Bir olgu satırı = bir sipariş kalemi (orders tablosunun bir satırı).

  2. Boyutlar

    dim_product, dim_customer, dim_date; vekil anahtarlarla.

  3. Anahtar arama

    Yükleme sırasında her sipariş iş anahtarıyla boyuta birleştirilir ve vekil anahtarı alır.

  4. Ölçüler

    quantity ve önceden hesaplanmış revenue = quantity · price; toplanabilir oldukları için.

SQL
CREATE TABLE dim_product (
  product_key INTEGER PRIMARY KEY,   -- surrogate key
  product_id  INTEGER NOT NULL,      -- business key from the shop
  name TEXT, category TEXT
);
INSERT INTO dim_product (product_id, name, category)
SELECT id, name, category FROM products ORDER BY category, name;

CREATE TABLE fact_sales (
  date_key     INTEGER NOT NULL,
  product_key  INTEGER NOT NULL REFERENCES dim_product,
  customer_key INTEGER NOT NULL,
  quantity     INTEGER,
  revenue      REAL
);
INSERT INTO fact_sales
SELECT CAST(strftime('%Y%m%d', o.order_date) AS INTEGER),
       dp.product_key, o.customer_id, o.quantity, o.quantity * p.price
FROM orders AS o
JOIN products    AS p  ON p.id = o.product_id
JOIN dim_product AS dp ON dp.product_id = o.product_id;

SELECT f.date_key, f.product_key, dp.product_id, dp.name, f.quantity, f.revenue
FROM fact_sales AS f JOIN dim_product AS dp USING (product_key)
ORDER BY f.date_key, f.product_key LIMIT 4;
Beklenen çıktı
date_key | product_key | product_id | name | quantity | revenue
20250115 | 3 | 3 | Headphones | 2 | 241
20250115 | 4 | 1 | Laptop | 1 | 1450
20250202 | 9 | 4 | Notebook | 20 | 64
20250210 | 6 | 2 | Smartphone | 1 | 899.99
Laptop ürününün iş anahtarı 1'dir ama ambarda product_key 4 aldı; anahtarlar birbirinden bağımsızdır.

Bir yıldız sorgusunun biçimi hep aynıdır: olgu tablosu + gereken boyutlara JOIN + boyut özniteliklerine göre GROUP BY + ölçülerin SUM değeri. Sitedeki her örnek temiz bir veritabanıyla başladığı için aşağıdaki blok önce yıldızı kısa bir biçimde yeniden kurar; gerçek bir ambarda tablolar zaten vardır ve yalnızca son SELECT yazılır.

SQL
CREATE TABLE dim_date AS
WITH RECURSIVE d(day) AS (
  SELECT '2025-01-01' UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2025-12-31')
SELECT CAST(strftime('%Y%m%d', day) AS INTEGER) AS date_key,
       (CAST(strftime('%m', day) AS INTEGER) + 2) / 3 AS quarter
FROM d;
CREATE TABLE dim_product AS
SELECT ROW_NUMBER() OVER (ORDER BY category, name) AS product_key,
       id AS product_id, name, category
FROM products;
CREATE TABLE fact_sales AS
SELECT CAST(strftime('%Y%m%d', o.order_date) AS INTEGER) AS date_key,
       dp.product_key, o.quantity, o.quantity * p.price AS revenue
FROM orders AS o
JOIN products    AS p  ON p.id = o.product_id
JOIN dim_product AS dp ON dp.product_id = o.product_id;

SELECT d.quarter, p.category, SUM(f.quantity) AS items,
       ROUND(SUM(f.revenue), 2) AS revenue
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_key = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
GROUP BY d.quarter, p.category
ORDER BY d.quarter, revenue DESC;
Beklenen çıktı
quarter | category | items | revenue
1 | Electronics | 4 | 2590.99
1 | Home | 2 | 69.98
1 | Stationery | 20 | 64
1 | Accessories | 1 | 55
2 | Electronics | 2 | 1020.49
2 | Games | 1 | 42
2 | Accessories | 3 | 36
2 | Stationery | 4 | 31.6
3 | Stationery | 10 | 32
Çeyreklere ve kategorilere göre gelir; rapor “küpünün” bir kesiti.
YıldızKar tanesi
Boyutlartek geniş tablo (kategori dim_product içinde)normalleştirilmiş: dim_product → dim_category
Sorguaz JOIN, sadeher düzey için ek bir JOIN
Tekrarkategori adı tekrarlanırtekrar azdır
Ne zamançoğu ambar ve BI aracıçok büyük, derin hiyerarşili boyutlar

Değişen boyutlar: SCD tip 1 ve 2

Bir müşterinin şehri ya da bir ürünün kategorisi zaman zaman değişir; bunlara yavaş değişen boyutlar (slowly changing dimensions, SCD) denir. Tip 1: eski değerin üzerine yazılır, geçmiş kaybolur; eski siparişler de yeni şehre aitmiş gibi görünür. Tip 2: yeni sürüm için yeni bir satır (yeni bir vekil anahtar) eklenir, eski satır bir valid_to tarihiyle “kapatılır”. (Tip 3 ayrı bir “önceki değer” sütunu tutar ama yalnızca bir adım geriye.) Lalə 1 Mayıs 2025'te Gence'den Bakü'ye taşınıyor:

SQL
CREATE TABLE dim_customer (
  customer_key INTEGER PRIMARY KEY,
  customer_id  INTEGER,
  name TEXT, city TEXT,
  valid_from TEXT, valid_to TEXT, is_current INTEGER
);
INSERT INTO dim_customer VALUES
  (1, 1, 'Anar Mustafayev', 'Bakı',  '2024-01-10', '9999-12-31', 1),
  (2, 2, 'Lalə Əhmədova',   'Gəncə', '2024-03-22', '9999-12-31', 1);

-- 2025-05-01: Lalə moves to Bakı (SCD type 2)
UPDATE dim_customer SET valid_to = '2025-04-30', is_current = 0
WHERE customer_id = 2 AND is_current = 1;
INSERT INTO dim_customer VALUES
  (3, 2, 'Lalə Əhmədova', 'Bakı', '2025-05-01', '9999-12-31', 1);

SELECT o.id AS order_id, o.order_date, d.customer_key, d.city
FROM orders AS o
JOIN dim_customer AS d
  ON d.customer_id = o.customer_id
 AND o.order_date BETWEEN d.valid_from AND d.valid_to
ORDER BY o.id;
Beklenen çıktı
order_id | order_date | customer_key | city
1 | 2025-01-15 | 1 | Bakı
2 | 2025-01-15 | 1 | Bakı
3 | 2025-02-02 | 2 | Gəncə
11 | 2025-06-30 | 3 | Bakı
12 | 2025-07-07 | 1 | Bakı
Şubat siparişi Gence'ye (anahtar 2), haziran siparişi Bakü'ye (anahtar 3) düşüyor. Gerçek bir yüklemede bu arama bir kez, olgu satırı yazılırken yapılır.

Raporda tip 1 ve tip 2

Problem

Lalə'nin iki siparişi var: şubatta 20 Notebook (tanesi 3,20) ve haziranda 1 Headphones (120,50). “Şehirlere göre gelir” raporunda Gence, Lalə'den 1. ve 2. tipte ne kadar alır?

Çözümü gösterÇözümü gizle

Çözüm

1) Gelirler: 20 · 3,20 = 64 ve 1 · 120,50 = 120,50; toplam 184,50.
2) Tip 1: şehir “Bakı” ile değiştirildi, iki sipariş de Bakü'ye yazılır; Gence 0 alır, Bakü 184,50.
3) Tip 2: şubat siparişi taşınmadan önceki sürüme (Gence) bağlıdır; Gence 64, Bakü 120,50 alır.
4) Sonuç: geçmiş önemliyse (satış ekibinin şehir bazlı hedefleri) tip 2 gerekir.

Alıştırma

Başlangıç kodu tarih boyutunu, dim_customer ve fact_sales tablolarını kuruyor. Çeyreklere ve ülkelere göre olgu satırı sayısını (lines) ve geliri (revenue, 2 ondalık) hesapla. Çeyreğe, sonra gelire göre azalan sırala.

Alıştırma · SQL
CREATE TABLE dim_date AS
WITH RECURSIVE d(day) AS (
  SELECT '2025-01-01' UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2025-12-31')
SELECT CAST(strftime('%Y%m%d', day) AS INTEGER) AS date_key,
       (CAST(strftime('%m', day) AS INTEGER) + 2) / 3 AS quarter
FROM d;
CREATE TABLE dim_customer AS
SELECT ROW_NUMBER() OVER (ORDER BY country, name) AS customer_key,
       id AS customer_id, name, city, country
FROM customers;
CREATE TABLE fact_sales AS
SELECT CAST(strftime('%Y%m%d', o.order_date) AS INTEGER) AS date_key,
       dc.customer_key, o.quantity, o.quantity * p.price AS revenue
FROM orders AS o
JOIN products     AS p  ON p.id = o.product_id
JOIN dim_customer AS dc ON dc.customer_id = o.customer_id;

-- your star query here
Beklenen çıktı
quarter | country | lines | revenue
1 | Azerbaijan | 3 | 1755
1 | Türkiye | 1 | 899.99
1 | United Kingdom | 1 | 69.98
1 | Russia | 1 | 55
2 | Azerbaijan | 3 | 1056.49
2 | Türkiye | 2 | 73.6
3 | Azerbaijan | 1 | 32

Alıştırma

SCD tip 2: başlangıç kodu dim_customer tablosunu müşterilerden dolduruyor. Lalə'nin (customer_id = 2) 2025-05-01'de Bakü'ye taşınmasını tip 2 olarak kaydet (eski satırı 2025-04-30 ile kapat, yenisini ekle). Sonra sipariş anındaki şehre göre geliri (revenue, 2 ondalık) hesapla ve gelire göre azalan sırala.

Alıştırma · SQL
CREATE TABLE dim_customer (
  customer_key INTEGER PRIMARY KEY, customer_id INTEGER, name TEXT, city TEXT,
  valid_from TEXT, valid_to TEXT, is_current INTEGER);
INSERT INTO dim_customer (customer_id, name, city, valid_from, valid_to, is_current)
SELECT id, name, city, joined_on, '9999-12-31', 1 FROM customers ORDER BY id;

-- 1) close the old version of customer 2 and add the new one

-- 2) revenue by city at the time of the order
Beklenen çıktı
city | revenue
Bakı | 2779.49
İstanbul | 941.99
London | 69.98
Gəncə | 64
Moscow | 55
Ankara | 31.6

Unutma

  • OLTP küçük işlemler için normalleştirilir; OLAP ambarı ise okuma için bilerek normalleştirilmez.
  • Yıldız = ortada olgu tablosu (ölçüler + anahtarlar), çevresinde boyut tabloları (bağlam).
  • Önce tanecik: “bir satır = …”; ölçüler toplanabilir, yarı toplanabilir ya da toplanamaz olur.
  • Vekil anahtarlar ambarı kaynaktan ayırır ve SCD 2 sürümlerini mümkün kılar; tarih boyutunun anahtarı YYYYMMDD'dir.
  • SCD 1 üzerine yazar (geçmiş yok), SCD 2 valid_from/valid_to ile yeni bir satır ekler.
  • Kar tanesi boyutları normalleştirir (ek JOIN); ELT ve sütunlu ambarlar bugünün standardıdır.
Bu derste bir hata ya da yanlışlık mı gördün?

Kendini test et

12 soru. Her doğru cevap XP kazandırır.

Soru 1 / 12

Soru 1 / 12. Yıldız şemada miktar ve gelir gibi ölçüler hangi tabloda saklanır?