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ş
| OLTP | OLAP | |
|---|---|---|
| Amaç | günlük işlemler | analiz ve raporlar |
| Tipik sorgu | bir siparişi oku ya da değiştir | milyonlarca satır üzerinde toplamlar |
| Yazma | sürekli, küçük parçalarla | dönemsel yükleme (örneğin gece) |
| Şema | normalleştirilmiş (3NF) | bilerek normalleştirilmemiş yıldız |
| Geçmiş | güncel durum | yılların geçmişi |
| Örnek sistemler | PostgreSQL, MySQL, SQL Server | ClickHouse, 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.
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.
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:
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
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:
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
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:
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
Yıldızı kurmak ve sorgulamak
Tanecik
Bir olgu satırı = bir sipariş kalemi (
orderstablosunun bir satırı).Boyutlar
dim_product,dim_customer,dim_date; vekil anahtarlarla.Anahtar arama
Yükleme sırasında her sipariş iş anahtarıyla boyuta birleştirilir ve vekil anahtarı alır.
Ölçüler
quantityve önceden hesaplanmışrevenue = quantity · price; toplanabilir oldukları için.
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
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.
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
| Yıldız | Kar tanesi | |
|---|---|---|
| Boyutlar | tek geniş tablo (kategori dim_product içinde) | normalleştirilmiş: dim_product → dim_category |
| Sorgu | az JOIN, sade | her düzey için ek bir JOIN |
| Tekrar | kategori adı tekrarlanır | tekrar 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:
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ı
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
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.
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 hereBeklenen çı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.
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 orderBeklenen çı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_toile yeni bir satır ekler. - Kar tanesi boyutları normalleştirir (ek
JOIN); ELT ve sütunlu ambarlar bugünün standardıdır.