İlk iki yazıda “Northwind” veri tabanından dimensional modeli önce teorik sonra ise fiziksel olarak oluşturmuştuk. Serinin son yazısında ise bu modelin raporlamada nasıl kullanılacağını göstermek adına, birkaç örnek rapor çekeceğiz.
Serinin diğer yazılarını okumak için:
Dimensional modelin en belirgin iki özelliği, kolay anlaşılır ve performanslı (hızlı sonuç döndürebilir) olmasıdır.
1. Kolay anlaşılır olmasının sebebi, modellemenin basit olması ve iş sürecinin içeriğini basitçe anlatabilir olmasıdır. Bu modelden yararlanacak olan bir son kullanıcı, hiçbir teknik tecrübesi veya arka planı olmasa dahi, modele baktığında modeli rahatça anlayabilir ve onu kafasındaki iş modeli ile özdeşleştirebilir. Çünkü modelin boyutlar halindeki şeması, son kullanıcının da olaya nasıl baktığı ile çok benzerdir.
2. Performanslı olmasının sebebi ise, de-normalizasyondan dolayı daha az “join” ile sorgu sonuçlarının daha efektif olarak dönmesidir. Sorgu optimizasyonunu yapan araç için, dimensional bir modele atılmış bir sorguyu optimize etmek kolaydır, dolayısıyla kompleks analitik sorgular daha efektif olarak döndürülebilir. Bunun karşılığında de-normalizasyondan doğan yer kaplama problemi ise, elde edilen bu performans faydasının karşısında çoğunlukla önemsenmeyecek kadar küçüktür.
Evet, örnek raporlar almaya başlayalım.
Örnek Rapor 1: Şirkete en çok gelir sağlayan üç müşteri hangileridir?
Raporun sorgusunu aşağıdaki gibi yazabiliriz:
SELECT dc.musteriadi
,SUM(fs.totalgetiri) AS Getiri
FROM public.factsales fs
LEFT OUTER JOIN public.dimcustomer dc
ON fs.musteriid = musterisk
GROUP BY dc.musteriadi
ORDER BY 2 DESC;
Görüleceği üzere, basit bir join ile fact tablosundan müşteri dimension tablosuna bağlandık ve müşteri adı üzerinden gruplayarak veriyi sıraladık. Total getiri kolonunu önceden ETL sürecinde hesaplamış olmamız ise, sorguyu daha da basitleştirmemize olanak veriyor. Sonuca bakacak olursak:
Evet, gelir getirmede ilk 3 şirket “QUICK-Stop”, “Ernst Handel” ve “Save-a-lot Markets” olarak görünüyor.
Örnek Rapor 2: Şirketin en çok satış yapan çalışanı adet bazında en fazla hangi bölgeye satış yapmıştır?
Raporun sorgusunu yazalim:
SELECT ds.teslimatbolgesi
,SUM(fs.adet) AS SatisAdedi
FROM public.factsales fs
LEFT OUTER JOIN public.dimshipgeography ds
ON fs.teslimatbolgesiid = ds.teslimatbolgesisk
WHERE fs.calisanid = (
SELECT t2.calisanid
FROM
(
SELECT t1.calisanid
,ROW_NUMBER() OVER(ORDER BY t1.SatisAdedi DESC) AS Sira
FROM
(
SELECT fs.calisanid
,SUM(fs.adet) AS SatisAdedi
FROM public.factsales fs
GROUP BY fs.calisanid
) t1
) t2
WHERE t2.Sira = 1
)
AND ds.teslimatbolgesi <> 'Unknown'
GROUP BY ds.teslimatbolgesi
ORDER BY 2 DESC;
Sorguyu inceleyecek olursak, ana sorgu gövdesinin WHERE filtresinde iki koşul görüyoruz. Bunlardan ilki, en çok satış yapmış çalışanın id’sini buluyor ve fact tablosunu buna göre filtreliyor. İkinci koşul ise “Unknown” olan bölgeleri eliyor. Sonrasında teslimat bölgesi dimension tablosuna joinlenildiği için oradaki teslimat bölgesi kolonuna göre veriyi gruplayıp toplam satış adetlerine göre sıralıyor. En fazla satış yapan çalışanı bulurken, “ROW_NUMBER” windowing(pencere) fonksiyonunu kullanıyor.
Sonuca bakacak olursak:
Görüleceği üzere, bu çalışan en çok “Idaho” bölgesine satış gerçekleştirmiş. Unutmayalım ki bunu gelir bazında değil, adet bazında belirledik. Bu çalışanın kim olduğunu öğrenmek için ise, çalışan dimension tablosuna joinlenmek gerekecektir. Fakat bunu yapmadan ben söyleyeyim, kendisi Margaret Peacock.
Örnek Rapor 3: 1997 yılı iş günlerinde Wyoming bölgesinde ürünleri en çok satılan tedarikçi hangisidir?
Sorguyu yazalım:
SELECT t2.Tedarikci
,t2.SatisAdedi
FROM
(
SELECT t1.Tedarikci
,t1.SatisAdedi
,ROW_NUMBER() OVER(ORDER BY t1.SatisAdedi DESC) AS Sira
FROM
(
SELECT dp.Tedarikci
,SUM(fs.adet) AS SatisAdedi
FROM public.factsales fs
LEFT OUTER JOIN public.dimdate dd
ON fs.siparistarihi = dd.tarihsk
LEFT OUTER JOIN public.dimshipgeography ds
ON fs.teslimatbolgesiid = ds.teslimatbolgesisk
LEFT OUTER JOIN public.dimproduct dp
ON fs.urunid = dp.urunsk
WHERE dd.isgunumu = 'EVET'
AND dd.yil = 1997
AND ds.teslimatbolgesi = 'Wyoming'
GROUP BY dp.tedarikci
) t1
) t2
WHERE t2.Sira = 1;
Sorguda tarih, teslimat bölgesi ve ürün dimension tablolarına joinlendikten sonra istenilen filtreleri uyguladık. Tarih tablosundan iş günü-yıl bilgisini ve bölge tablosundan da bölge bilgisini filtreledikten sonra tedarikçi bazında ürün satış adetlerini alarak bunları sıralamaya tabi tuttuk.
Sonuç aşağıdaki gibi:
Elbette sadece birinci tedarikçiyi görmek istediğimizden sonucu kısıtlı tuttuk. Fakat burada diğer tedarikçilerin de satış adetlerini görmek de mantıklı olabilirdi. 1997 yılı iş günlerinde Wyoming bölgesinde “Ma Maison” adlı tedarikçinin getirdiği ürünler toplamda 40 adet satılmış ve ürünleri en çok satılan tedarikçi olmuş.
Örnek Rapor 4: Üretimi devam eden ürünlerde: satılan ürün çeşitliliği bakımından şirketin en başarılı iki çalışanı kimdir, bu çalışanlar ürünleri hangi ülkelerdeki müşterilere satmıştır ve bu satışların yüzdesel dağılımı nedir?
Öncelikle belirtelim ki ürün çeşitliliği derken kastettiğimiz nokta, kaç farklı ürün satıldığı. İki kişinin ürün çeşitliliğinin aynı olması durumunda, ikisini de alacağız. Satışların yüzdesel dağılımında ise, getiriyi baz alacağız. Sorguyu yazmaya başlayalım:
SELECT f.Calisan
,f.Ulke
,ROUND(CAST(f.Getiri AS NUMERIC), 2) AS Getiri
,CONCAT(CAST(ROUND(CAST((f.Getiri/(SUM(f.Getiri) OVER (PARTITION BY f.Calisan))) * 100 AS NUMERIC), 2) AS TEXT), '%') AS Yuzde
FROM
(
SELECT CONCAT(de.adi, ' ', de.soyadi) AS Calisan
,dc.ulke
,SUM(fs.totalgetiri) AS Getiri
FROM public.factsales fs
LEFT OUTER JOIN public.dimemployee de
ON fs.calisanid = de.calisansk
LEFT OUTER JOIN public.dimcustomer dc
ON fs.musteriid = dc.musterisk
WHERE fs.calisanid IN (
SELECT t2.calisanid
FROM
(
SELECT t1.calisanid
,DENSE_RANK() OVER(ORDER BY t1.UrunCesitliligi DESC) AS Sira
FROM
(
SELECT fs.calisanid
,COUNT(DISTINCT fs.urunid) AS UrunCesitliligi
FROM public.factsales fs
LEFT OUTER JOIN public.dimproduct dp
ON fs.urunid = dp.urunsk
WHERE dp.uretimdemi = 'Evet'
GROUP BY fs.calisanid
) t1
) t2
WHERE t2.Sira <= 2
)
GROUP BY CONCAT(de.adi, ' ', de.soyadi)
,dc.ulke
) f
Ana sorgu gövdesinde çalışan ve müşteri dimension tablolarına joinlendikten sonra, WHERE filtesinde bir sub-query kullanarak ürün çeşitliliği en yüksek olan üç çalışanı filtreledik. Bu sistem, önceki sorgularda kullandıklarımız ile benzer. Tek fark, DENSE_RANK fonksiyonu ile eşit sayıdaki satırların tamamını yakalıyor olmamız. Sonrasında, çalışan ve müşteri ülkesi bazında gruplayarak toplam satış getirisini hesapladık. En üst sorguda ise, bu getirilerin ülkeler bazında yüzdesel dağılımını hesapladık ve son çıktıyı daha okunabilir bir formata getirdik.
Sonuca bakacak olursak:
Evet sonuçta da görüldüğü gibi, çalışanların satış getirileri ve bunların ülkelere göre yüzdesel dağılımı ortada. Çalışanın ad soyad bilgisini de ekleyerek kim olduğunu raporda belirttik. Çıktı biraz büyük olduğundan, konsola sığmadı ve bir kısmının görüntüsünü ekledim.
Örnek Rapor 5: Sipariş teslim hızı konusunda, ülkelerdeki en iyi kargo firmaları hangileridir?
Evet sorguyu yazmaya başlayalım, burada elbette sipariş hızı konusundaki belirleyici etken, siparişlerin alındıktan ortalama ne kadar süre sonra teslim edildiği olacak.
SELECT ds.teslimatulkesi
,dsh.sirketadi
,ROUND(CAST(AVG(DATE_PART('day', fs.teslimattarihi::timestamp - dd.tarih::timestamp)) AS NUMERIC), 2) AS GunBazindaTeslimSuresi
FROM public.factsales fs
LEFT OUTER JOIN public.dimshipgeography ds
ON fs.teslimatbolgesiid = ds.teslimatbolgesisk
LEFT OUTER JOIN public.dimdate dd
ON fs.siparistarihi = dd.tarihsk
LEFT OUTER JOIN public.dimshipper dsh
ON fs.kargosirketiid = dsh.kargosirketisk
GROUP BY ds.teslimatulkesi, dsh.sirketadi
ORDER BY 1 ASC, 3 ASC;
Sorgu oldukça sade, gerekli dimension tablolarına join işlemini gerçekleştirdikten sonra ülke ve kargo şirketi bazında veriyi gruplayarak sipariş ve teslimat tarihleri arasındaki ortalama gün farkını bulduk.
Sonuç:
Sorgu sonucunun tamamını incelediğimizde, “Federal Shipping” adlı kargo şirketinin tüm ülkelerde iyi bir hız başarısı yakaladığını görüyoruz.
Örnek Rapor 6: En hızlı kargo şirketlerinin uyguladığı ortalama kargo ücreti nedir? Hızlı kargo şirketleri daha mı çok ücret almaktadır?
Her zamanki gibi, sorguya başlayalım:
SELECT f.sirketadi
,ROUND(CAST(AVG(f.kargoucreti) AS NUMERIC), 2) AS OrtalamaUcret
FROM
(
SELECT dsh.sirketadi
,fs.siparisid
,SUM(fs.kargoucreti) AS KargoUcreti
FROM public.factsales fs
LEFT OUTER JOIN public.dimshipper dsh
ON fs.kargosirketiid = dsh.kargosirketisk
WHERE fs.kargosirketiid IN (
SELECT t1.kargosirketiid
FROM
(
SELECT fs.kargosirketiid
,ROUND(CAST(AVG(DATE_PART('day', fs.teslimattarihi::timestamp - dd.tarih::timestamp)) AS NUMERIC), 2) AS TeslimSuresi
FROM public.factsales fs
LEFT OUTER JOIN public.dimdate dd
ON fs.siparistarihi = dd.tarihsk
GROUP BY fs.kargosirketiid
ORDER BY 2 ASC
) t1
LIMIT 2
)
GROUP BY dsh.sirketadi
,fs.siparisid
) f
GROUP BY f.sirketadi
;
Evet, sorguyu kısaca inceleyelim. Öncelikle WHERE filtresi içerisinde, en hızlı 2 kargo şirketini tespit ettik. Bunu yaparken, önceki sorguların aksine bu kez ORDER BY ve LIMIT ikilisini kullandık. Bu da WINDOWING fonksiyonların kullanımına bir alternatif olarak karşımıza çıkmaktadır. Daha sonra, fact tablosunu sadece bu kargo şirketlerini içerecek şekilde filtreledik ve şirket-sipariş bazında gruplayarak kargo ücretlerini topladık. Bunu yapmamızın sebebi, hatırlarsanız kargo ücretlerini siparişteki her satır bazına indirgeyebilmek adına, satılan ürün sayısına bölerek her satıra eşit dağılmasını sağlamıştık. Bu yüzden öncelikle kargo şirketlerinin sipariş bazında aldıkları asıl kargo ücretini bulmak adına bu satırları topladık. Son aşamada ise, sadece kargo şirketi bazında gruplayarak ortalama kargo ücretini bulduk.
Sonucu inceleyelim:
Baktığımızda, “Federal Shipping” en hızlı kargo şirketi ve “Speedy Ekspress” firmasından daha fazla ücret alıyor. Burada bir doğru orantı olabilir, fakat tüm firmaların ücretlerini değerlendirmek gerekir.
Evet, hazırlamış olduğumuz dimensional modelden 6 tane örnek rapor hazırladık ve bunların hem sonuçlarını, hem de sorgularını inceledik. Bu raporlar, dimension ve fact tablolarının aktif olarak model içerisinde nasıl kullanıldığına dair örnekler oluşturabilir. Elbette bunlar daha da çoğaltılabilirdi, fakat yazıyı fazla uzatmamak adına bu kadarını yeterli gördüm.
Bu üçüncü ve son yazı ile beraber, perakende sektörüne ait bir veritabanının modellenme ve bu model üzerinde çalışmalar yapılma serisini bitirmiş oluyoruz. Elbette bu yazı serisi sadece bir örnek teşkil etmek içindi, normal projelerde bu süreç çok daha kapsamlı ve detaylı olacaktır.
Notlar
Sorgular daha çeşitli ve optimize yollar ile de yazılabilirdi, fakat veritabanının küçüklüğü ve örnek teşkil etme amacıyla yazılmasından dolayı, bu yöntemler tercih edildi. Farklı alternatifler ile de aynı sonuçlar elde edilebilir.
Kaynakça
PostgreSQL: Documentation: 10: PostgreSQL 10.16 Documentation