Serinin bu ikinci yazısında, ilk yazıda teorik olarak oluşturduğumuz dimensional(boyutsal) modeli fiziksel olarak oluşturacağız. İlk yazıda da belirttiğim gibi, bunu yaparken bir PostgreSQL veritabanı kullanacağız. Elbette başka alternatifler de mevcut ve isteyen istediği teknolojide bunu gerçekleştirebilir. Belirli bir teknolojiye bağlı kalmak durumunda değiliz.
Serinin diğer yazılarını okumak için:
Fiziksel modeli oluştururken, yine elimizde çok farklı metodlar mevcut. Örneğin bu işlem bir ETL aracı ile, herhangi bir programlama dili ile, ya da direkt olarak SQL kullanılarak gerçekleştirilebilir. Piyasada birçok open source(açık kaynak) ETL araçları olduğu gibi, açık kaynak olmayanlar da mevcut. İhtiyaca göre bunlar arasından çeşitli seçimler yapılabilir.
Biz bu yazıda fiziksel modeli oluştururken direkt olarak SQL’den faydalanacağız. Bunun başlıca nedenleri:
- SQL’in evrensel olması ve neredeyse her teknolojide birbirine çok yakın şekillerde kullanılmasıdır. T-SQL, PL-SQL, HiveQL veya Impala bile yazıyor olsanız, yazdığınız dil çoğunlukla diğerlerine benzer olacaktır. Dolayısıyla bunlardan herhangi biri ile tecrübesi olan bir kişi, diğerlerini de gördüğünde yabancı kalmayacaktır. Fakat bu durum ETL araçları için bu kadar geçerli değildir. Örneğin SSIS kullanan birisi için ODI farklı görünebilir(her ne kadar temelde çok benzer işler yapıyor olsalar da), veya Informatica kullanan biri adına AB Initio için aynı şeyin geçerli olmaması muhtemeldir. Dolayısıyla SQL’deki evrenselliğe burada sahip değiliz, bu da bizim için SQL kullanmanın mantıklı olacağını gösteriyor.
- Ayrıca, SQL gerçekten de bu tarz işler için yoğun olarak kullanılmakta, dolayısıyla bu da bir diğer neden.
Başlamadan önce, biraz da PostgreSQL’dan bahsedelim.
PostgreSQL nedir?
Öncelikle, PostgreSQL bir açık kaynak teknolojidir, dolayısıyla isteyen herkes rahatlıkla kurup faydalanabilir ve aynı zamanda katkıda da bulunabilir. Kendisi bir ilişkisel veritabanıdır ve kullandığı dil de temel olarak SQL’dir. Fakat üstte bahsettiğimiz farklı SQL versiyonları gibi, PostgreSQL’de kendine has bir SQL versiyonuna sahiptir dolayısıyla bazı ufak değişiklikler vardır. Fakat çoğunlukla, SQL kullanıcıları tarafından rahatlıkla anlaşılabilecektir. Bu tarz değişiklikler elbette ki sadece kullanılan dilde değil, başka noktalarda da bulunmaktadır. Örneğin veri tipleri, performans, güvenlik gibi başka meselelerde de kendine has özellikleri ve farkları bulunmaktadır. Daha detaylı bilgi almak isterseniz, PostgreSQL’in websitesinin linkini de kaynaklara bırakacağım.
Bir diğer nokta ise, bizim bu modelimize sürekli veri akışı olmayacağıdır. Dolayısıyla, tek seferlik bir model geliştireceğimiz için bazı noktalar klasik ETL geliştirmelerinden farklı olacaktır. Bizim buradaki amacımız modelin nasıl geliştirildiğine ve final aşamada nasıl görüneceğine dair fikir vermek. Aksi takdirde, gerçek bir senaryodaki gibi ETL geliştirmesi yapmaya kalksaydık, bu çok uzun sürebilir ve ufak bir yazı serisinde bitmesi imkansız hale gelirdi. Çünkü düzenli bir veri akışı olan dimensional modeli geliştirmeye kalktığınızda, işin içine düşünmeniz gereken birçok senaryo girecek. Biz bu makalelerde dimensional modele ve o modeli zenginleştirilmiş veriyle besleyecek olan ETL akışına ve bu akışın nasıl geliştirileceğine dair fikir vermek istiyoruz. Bu da bizi diğer detaylardan uzak tutuyor.
Boyutsal (Dimensional) modelin geliştirilmesi
Üstteki konulardan da kısaca bahsettikten sonra, geliştirmelere başlayabiliriz. Bu modeli oluşturmaya başlamadan önce, “Northwind_DW” adlı bir veritabanı oluşturarak geliştirmelere orada başladım. Dimensional modeli normal “Northwind” veritabanından ayrı bir yerde tutmak adına, siz de aynı yolu izleyebilirsiniz. Serinin ilk yazısında ilerlediğimiz sıra ile burada da ilerleyelim.
1. Date Dimension
Date dimension tablosu, ilk yazıda da bahsettiğim gibi diğerlerinden farklıdır. Bu tablo için internette çeşitli şekillerde hazır kodlar bulabilirsiniz. Uzun bir kod bloğu olduğundan, sıfırdan burada yazmaya da gerek olmayacaktır. İnternetteki örnek kodlardan birini alıp direkt olarak kendi ihtiyaçlarımıza göre güncelleyebiliriz.
Öncelikle tablo yaratma kodu ile başlayalım:
CREATE TABLE public.DimDate
(
TarihSK INT PRIMARY KEY NOT NULL,
Tarih Date NOT NULL,
Gün VARCHAR(70) NOT NULL,
Ay VARCHAR(70) NOT NULL,
Ceyrek INT NOT NULL,
Yil INT NOT NULL,
HaftaninGunu INT NOT NULL,
AyinGunu INT NOT NULL,
YilinGunu INT NOT NULL,
HaftaNumarasi INT NOT NULL,
AyNumarasi INT NOT NULL,
IsGunuMu VARCHAR(10) NOT NULL,
TatilGunuMu VARCHAR(10) NOT NULL
);
Tabloda ilk yazıda bahsettiğimiz alanları ekledik ve ticari takvim kısımlarını da şimdilik geçtik. Örnek vermek ve göstermek açısından, bu alanlar yeterli olacaktır. TarihSK alanının primary key olduğuna ve alanların hiçbirinin NULL girilebilir olmadığına da dikkat çekmek istiyorum.
İkinci olarak, dummy satır olarak görev yapacak ve karşılığı olmayan satırlar ile eşleşecek olan ilk satırımızı ekleyelim.
INSERT INTO public.DimDate
VALUES (-1, '2999-01-01', 'Unknown', 'Unknown', -1, -1, -1, -1, -1, -1, -1, 'Unknown', 'Unknown')
“-1” PK anahtar değerini barındıran alan, karşılığı olmayan satırlar ile eşleşecek şekilde tasarlandı ve diğer özellikler de buna uygun değerler ile donatıldı. “INT” alanlar için -1, CHAR alanlar için ise “Unknown” ifadesini kullandık. Burada şirketin tercihlerine göre farklı değerler de elbette kullanılabilir.
Son olarak, tabloyu dolduracak kodu ekleyelim:
CREATE INDEX dimdate_idx_tarih
ON public.DimDate(tarih);
INSERT INTO public.DimDate
SELECT TO_CHAR(gun, 'yyyymmdd')::INT AS TarihSK,
gun AS Tarih,
TO_CHAR(gun, 'Day') AS Gun,
TO_CHAR(gun, 'Month') AS Ay,
EXTRACT(QUARTER FROM gun) AS Ceyrek,
EXTRACT(ISOYEAR FROM gun) AS Yil,
EXTRACT(ISODOW FROM gun) AS HaftaninGunu,
EXTRACT(DAY FROM gun) AS AyinGunu,
EXTRACT(DOY FROM gun) AS YilinGunu,
EXTRACT(WEEK FROM gun) AS HaftaNumarasi,
EXTRACT(MONTH FROM gun) AS AyNumarasi,
CASE
WHEN EXTRACT(ISODOW FROM gun) IN (6, 7)
THEN 'HAYIR'
ELSE 'EVET'
END AS IsGunuMu,
CASE
WHEN EXTRACT(ISODOW FROM gun) IN (6, 7)
THEN 'EVET'
ELSE 'HAYIR'
END AS TatilGunuMu
FROM (SELECT '1996-07-04'::DATE + SEQUENCE.DAY AS gun
FROM GENERATE_SERIES(0, 1095) AS SEQUENCE (DAY)
GROUP BY SEQUENCE.DAY) DQ
ORDER BY 1 ASC;
Evet, kod bloğunu kısaca açıklayalım.
- İlk yaptığımız işlem, “tarih” alanı üzerinde bir index oluşturmak. Bunun nedeni, filtreleme işlemlerinin en çok bu alandan yapılacak olması. Farklı gereksinimlere göre, farklı alanlara da indexler atanabilir. Kod bloğunun devamında, ilk yazıda tablo halinde gösterdiğimiz kolonları teker teker ekliyoruz.
- “FROM” bloğunda, 1996 yılı 4 temmuz tarihinden itibaren 3 yıllık ve günlük bazda bir seri oluşturuyoruz ve üstteki “SELECT” bloğuna bu seriyi veriyoruz. Burada “04-07-1996” tarihi, “Northwind” veritabanındaki ilk sipariş tarihi olduğu için bu tarihten başlattım. Veritabanında 2 küsür yıl boyunca siparişler gözüküyor, ben garanti olması açısından 3 yıllık veri ekledim. Normalde elbette ki bir date dimension tablosu oluştururken geleceğe dönük en az 20 yıllık bir veri eklersiniz, fakat biz burada sadece örnek olması açısından bu miktarı kısıtlı tuttuk.
- “SELECT” bloğunun devamında, istediğimiz alanları PostgreSQL’in date fonksiyonlarını kullanarak oluşturduk ve “INSERT” ifadesi ile tabloya ekledik.
Evet, tablonun son halini inceleyelim:
SELECT * FROM public.DimDate;
Görüleceği üzere date dimension tablosunun verileri, bizim ilk yazıda teorik olarak belirlediğimiz gibi.
2. Product(Ürün) Dimension
Ayni şekilde, ürün dimension tablosunda da CREATE ifadesi ile başlayıp dummy satırı tabloya ekleyelim:
CREATE TABLE public.DimProduct
(
UrunSK INT PRIMARY KEY NOT NULL,
UrunID INT NOT NULL,
UrunAdi VARCHAR(250) NOT NULL,
Tedarikci VARCHAR(250) NOT NULL,
Kategori VARCHAR(100) NOT NULL,
BirimBasinaMiktar VARCHAR(100) NOT NULL,
BirimFiyat REAL NOT NULL,
StoktakiBirim INT NOT NULL,
SiparistekiBirim INT NOT NULL,
YenidenSiparisSeviyesi INT NOT NULL,
UretimdeMi VARCHAR(50) NOT NULL
);
INSERT INTO public.DimProduct
VALUES (-1, -1, 'Unknown', 'Unknown', 'Unknown', 'Unknown', -1, -1, -1, -1, 'Unknown');
Tablo alanları ilk yazıda da bahsettiğimiz gibi. Gerekli veri tiplerini de ayarladık ve PK alanını da “UrunSK” olarak seçtik. Dummy satırı da INSERT ettikten sonra, tablonun doldurulmasına geçelim:
CREATE EXTENSION dblink SCHEMA public;
INSERT INTO public.DimProduct
SELECT products.*
FROM public.dblink('dbname=Northwind',
'SELECT ROW_NUMBER() OVER(PARTITION BY 1) AS UrunSK
,product_id AS UrunID
,product_name AS UrunAdi
,COALESCE(s.company_name, ''Unknown'') AS Tedarikci
,COALESCE(c.category_name, ''Unknown'') AS Kategori
,quantity_per_unit AS BirimBasinaMiktar
,unit_price AS BirimFiyat
,units_in_stock AS StoktakiBirim
,units_on_order AS SiparistekiBirim
,reorder_level AS YenidenSiparisSeviyesi
,CASE
WHEN discontinued = 1 THEN ''Evet''
ELSE ''Hayir''
END AS UretimdeMi
FROM public.products p
LEFT OUTER JOIN public.suppliers s
ON p.supplier_id = s.supplier_id
LEFT OUTER JOIN public.categories c
ON p.category_id = c.category_id
;')
AS products(UrunSK INT
,UrunID INT
,UrunAdi VARCHAR
,Tedarikci VARCHAR
,Kategori VARCHAR
,BirimBasinaMiktar VARCHAR
,unit_price REAL
,units_in_stock INT
,units_on_order INT
,reorder_level INT
,UretimdeMi VARCHAR);
Bu noktada kodu biraz açıklamak gerekecek.
- Biz “Northwind” veritabanının modelini farklı bir veritabanı üzerinde yapıyoruz ve gerekli veriyi almak için “Northwind” veritabanına bağlanmamız gerekiyor. PostgreSQL bu konuda diğer veritabanları gibi bir çözüme izin vermediğinden dolayı, bunu yapmak için alternatif bir yol izliyoruz. Örneğin SQL server’daki gibi [veritabanı].[tabloadı].[kolonadı] syntax’i burada çalışmadığı için, dışarıdan “dblink” adlı bir EXTENSION kuruyoruz. Bunu kurmak için, “CREATE EXTENSION” ifadesinin eklenmesi yeterli çünkü bu zaten default olarak PostgreSQL’ı yüklediğinizde geliyor.
- Sonrasında, “dblink” fonksiyonunu kullanarak adını verdiğimiz veritabanına(Northwind) bağlanıyoruz ve ikinci argümanda verdiğimiz sorgunun sonucunu bir “relation” olarak alıyoruz. Burada biz bu relation’ın adını da “products” olarak seçtik. “Relation” ifadesini bir tablo gibi düşünebilirsiniz. Bunu seçmekle beraber, bu sorgudan gelecek olan alanların adını ve veri tiplerini de interpreter’a bildiriyoruz. Daha sonra buradan “*” ifadesini kullanarak tüm alanları çekip DimProduct tablomuza “INSERT” işlemini başlatıyoruz.
- “SELECT” komutundaki alanları zaten baktığınızda kolayca anlayabilirsiniz, bunlar ilk yazıda bahsettiğimiz alanlar. Ek olarak, burada bir INDEX yaratma ihtiyacı duymadık çünkü veri zaten hayli az(77 satır). Aslında date dimension tablosunda da yaratmamız gerekmezdi(1095 satır) fakat göstermek amacıyla yaratarak ilerledik. Normal şartlarda bu indexler tabloların tamamında oluşturulurdu. Bir diğer değinmek istediğim nokta ise, SK alanını da 1’den başlayacak şekilde bir seri olarak manual oluşturmuş olmamız. Normal şartlarda burada “AUTO INCREMENT” bir kolon tanımlayarak da rahatlıkla ilerlenebilirdi.
Sonucu inceleyecek olursak;
SELECT * FROM public.DimProduct;
Evet, tablo içerisindeki veriler de istediğimiz şekilde oluşmuş durumda. Kolonların neden bu şekilde oluşturulduğunu ilk yazıda anlattığımız için, bu yazıda buraya değinmiyorum.
3. Customer(Müşteri) Dimension
Müşteri dimension tablosu için de CREATE ifadesini yazarak geliştirmeye başlayalım:
CREATE TABLE public.DimCustomer
(
MusteriSK INT PRIMARY KEY NOT NULL,
MusteriID INT NOT NULL,
MusteriAdi VARCHAR(100) NOT NULL,
BaglantiAdi VARCHAR(100) NOT NULL,
BaglantiUnvani VARCHAR(50) NOT NULL,
Adres VARCHAR(250) NOT NULL,
Sehir VARCHAR(50) NOT NULL,
Bolge VARCHAR(50) NOT NULL,
PostaKodu VARCHAR(30) NOT NULL,
Ulke VARCHAR(50) NOT NULL,
Telefon VARCHAR(50) NOT NULL,
Faks VARCHAR(50) NOT NULL
);
INSERT INTO public.DimCustomer
VALUES (-1, -1, 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown');
Evet diğer dimension tablolarında yaptığımız gibi, burada da tabloyu yarattıktan sonra dummy satırımızı da ekledik. Devamında, tabloyu ilk yazıda belirttiğimiz standartlara göre oluşuracağız.
Geliştirmeye başlayalım:
INSERT INTO public.DimCustomer
SELECT customers.*
FROM public.dblink('dbname=Northwind',
'SELECT ROW_NUMBER () OVER (PARTITION BY 1) AS MusteriSK
,customer_id AS MusteriID
,company_name AS MusteriAdi
,contact_name AS BaglantiAdi
,contact_title AS BaglantiUnvanı
,address AS Adres
,city AS Sehir
,CASE region
WHEN ''AK'' THEN ''Alaska''
WHEN ''CA'' THEN ''California''
WHEN ''ID'' THEN ''Idaho''
WHEN ''MT'' THEN ''Montana''
WHEN ''NM'' THEN ''New Mexico''
WHEN ''OR'' THEN ''Oregon''
WHEN ''WA'' THEN ''Washington''
WHEN ''WY'' THEN ''Wyoming''
ELSE ''Unknown''
END AS Bolge
,postal_code AS PostaKodu
,COALESCE(country, ''Unknown'') AS Ulke
,COALESCE(phone, ''Unknown'') AS Telefon
,COALESCE(fax, ''Unknown'') AS Faks
FROM public.customers')
AS customers(MusteriSK INT
,MusteriID VARCHAR
,MusteriAdi VARCHAR
,BaglantiAdi VARCHAR
,BaglantiUnvanı VARCHAR
,Adres VARCHAR
,Sehir VARCHAR
,Bolge VARCHAR
,PostaKodu VARCHAR
,Ulke VARCHAR
,Telefon VARCHAR
,Faks VARCHAR);
Kod bloğu bir önceki dimension tablosundakiyle iskelet olarak aynı şekilde. Bu kez “customers” tablosuna gidip oradaki alanları ihtiyaçlarımıza göre alıyoruz. Index atma işlemini yine gerçekleştirmiyoruz ve “dblink” extension’ını da bir önceki dimension tablosunu yaratırken yüklediğimiz için, bir kez daha yüklememize gerek yok. Direkt olarak kullanabiliriz.
Bahsetmek istediğim bir nokta, bölge kısaltmalarını kodlarken sadece birkaçını yapıp, tamamını yapmaya gerek görmedim. Sadece birkaçını kodlamak bizim için görsel açıdan yeterli. Elbette normal bir projede, tamamının düzgün bir içimde kodlanması gerekirdi.
Sonuca bakacak olursak:
SELECT * FROM public.DimCustomer;
Görüleceği üzere, müşteri dimension tablosunun satırları da istediğimiz şekilde doldurulmuş durumda. Bir sonraki tablo ile devam edelim.
4. Employee(Çalışan) Dimension
Her zamanki gibi, CREATE ifadesi ile tabloyu oluşturalım ve dummy satırı ekleyelim:
CREATE TABLE public.DimEmployee
(
CalisanSK INT PRIMARY KEY NOT NULL,
CalisanID INT NOT NULL,
Soyadi VARCHAR(50) NOT NULL,
Unvani VARCHAR(50) NOT NULL,
NezaketUnvani VARCHAR(10) NOT NULL,
DogumTarihi DATE NOT NULL,
IseAlimTarihi DATE NOT NULL,
Adres VARCHAR(250) NOT NULL,
Sehir VARCHAR(50) NOT NULL,
Bolge VARCHAR(50) NOT NULL,
PostaKodu VARCHAR(50) NOT NULL,
Ulke VARCHAR(50) NOT NULL,
EvTelefonu VARCHAR(50) NOT NULL,
Uzanti VARCHAR(10) NOT NULL,
Yonetici VARCHAR(50) NOT NULL
);
INSERT INTO public.DimEmployee
VALUES (-1, -1, 'Unknown', 'Unknown', 'Unknown', '2099-01-01', '2099-01-01', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown');
CREATE TABLE public.DimEmpTerritory
(
CalisanID INT NOT NULL,
AlanAciklamasi VARCHAR(50) NOT NULL,
BolgeAciklamasi VARCHAR(50) NOT NULL,
PRIMARY KEY(CalisanID, AlanAciklamasi)
);
INSERT INTO public.DimEmpTerritory
VALUES (-1, 'Unknown', 'Unknown');
Evet, tablo yaratma ifadelerini hem çalışan dimension tablosu için hem de ilk yazıda bahsettiğimiz çalışanlar ve bölgeler arasında köprü görevi görecek olan tablo için yazdık.
Şimdi bu tabloların içini dolduralım:
INSERT INTO public.DimEmployee
SELECT employees.*
FROM public.dblink('dbname=Northwind',
'SELECT ROW_NUMBER () OVER (PARTITION BY 1) AS CalisanSK
,e1.employee_id AS CalisanID
,e1.last_name AS Soyadi
,e1.first_name AS Adi
,e1.title AS Unvani
,e1.title_of_courtesy AS NezaketUnvani
,e1.birth_date AS DogumTarihi
,e1.hire_date AS IseAlimTarihi
,e1.address AS Adres
,e1.city AS Sehir
,CASE e1.region
WHEN ''AK'' THEN ''Alaska''
WHEN ''CA'' THEN ''California''
WHEN ''ID'' THEN ''Idaho''
WHEN ''MT'' THEN ''Montana''
WHEN ''NM'' THEN ''New Mexico''
WHEN ''OR'' THEN ''Oregon''
WHEN ''WA'' THEN ''Washington''
WHEN ''WY'' THEN ''Wyoming''
ELSE ''Unknown''
END AS Bolge
,e1.postal_code AS PostaKodu
,e1.country AS Ulke
,e1.home_phone AS EvTelefonu
,e1.extension AS Uzanti
,COALESCE(CONCAT(e2.first_name, '' '', e2.last_name), ''-'') AS Yonetici
FROM public.employees e1
LEFT OUTER JOIN public.employees e2
ON e1.reports_to = e2.employee_id
;')
AS employees(CalisanSK INT,
CalisanID INT,
Soyadi VARCHAR,
Adi VARCHAR,
Unvani VARCHAR,
NezaketUnvani VARCHAR,
DogumTarihi DATE,
IseAlimTarihi DATE,
Adres VARCHAR,
Sehir VARCHAR,
Bolge VARCHAR,
PostaKodu VARCHAR,
Ulke VARCHAR,
EvTelefonu VARCHAR,
Uzanti VARCHAR,
Yonetici VARCHAR)
;
INSERT INTO public.dimEmpTerritory
SELECT empTerritory.*
FROM public.dblink('dbname=Northwind',
'SELECT DISTINCT et.employee_id AS CalisanID
,t.territory_description AS AlanAciklamasi
,r.region_description AS BolgeAciklamasi
FROM employee_territories et
LEFT OUTER JOIN territories t
ON et.territory_id = t.territory_id
LEFT OUTER JOIN region r
ON t.region_id = r.region_id
ORDER BY 1 ASC
;')
AS empTerritory(CalisanID INT,
AlanAciklamasi VARCHAR,
BolgeAciklamasi VARCHAR);
- Evet, bu kod bloğu içerisinde iki tabloyu da doldurduk.
- “SELECT” bloğundaki alanları ilk yazıda belirlediğimiz kurallar doğrultusunda transforme ettik ve INSERT işlemini gerçekleştirdik.
- İleride bu iki tablo birbirlerine “CalisanID” alanı üzerinden bağlanarak raporlara katkıda bulunabilir. “Bolge” alanı için bir önceki kural setini uyguladığımızı ve “Yönetici” alanı için ise tabloya bir self-join gerçekleştirdiğimize dikkat edelim.
SELECT * FROM public.DimEmployee;
SELECT * FROM public.dimEmpTerritory;
Sonuçları görelim:
Tablolar istediğimiz şekilde doldurulmuş durumda.
5. Shipper(Kargo Şirketi) Dimension
Kargo şirketi dimension tablosu ile devam edelim. Diğerlerinde olduğu gibi CREATE ifadesi ile başlıyoruz:
CREATE TABLE public.dimShipper
(
KargoSirketiSK INT PRIMARY KEY NOT NULL,
KargoSirketiID INT NOT NULL,
SirketAdi VARCHAR(100) NOT NULL,
Telefon VARCHAR(50) NOT NULL
);
INSERT INTO public.dimShipper
VALUES (-1, -1, 'Unknown', 'Unknown')
Ufak bir tablo olduğundan dolayı, oluşturulma ve dummy satır eklenme scripti de gayet küçük oldu.
Tabloyu dolduralım:
INSERT INTO public.DimShipper
SELECT shippers.*
FROM public.dblink('dbname=Northwind',
'SELECT ROW_NUMBER () OVER (PARTITION BY 1) AS KargoSirketiSK
,shipper_id AS KargoSirketiID
,company_name AS SirketAdi
,phone AS Telefon
FROM public.shippers;')
AS shippers( KargoSirketiSK INT
,KargoSirketiID INT
,SirketAdi VARCHAR
,Telefon VARCHAR);
Evet kod bloğunu açıklamaya gerek yok, diğerleriyle benzer bir yapıda ve oldukça sade.
Tabloya bakacak olursak:
SELECT * FROM public.dimShipper;
Veriler istediğimiz gibi, dolayısıyla son dimension tablosu ile devam edebiliriz.
6. ShipGeography(Teslimat Bölgesi) Dimension
Evet, sipariş coğrafya bilgilerini tutacak olan dimension tablosu için CREATE statement ile başlayalım:
CREATE TABLE public.dimShipGeography
(
TeslimatBolgesiSK INT PRIMARY KEY NOT NULL,
TeslimatAdi VARCHAR(100) NOT NULL,
TeslimatAdresi VARCHAR(250) NOT NULL,
TeslimatSehri VARCHAR(100) NOT NULL,
TeslimatBolgesi VARCHAR(50) NOT NULL,
TeslimatPostaKodu VARCHAR(50) NOT NULL,
TeslimatUlkesi VARCHAR(75) NOT NULL
);
INSERT INTO public.dimShipGeography
VALUES (-1, 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown');
Bu aşamada, tablo oluşturulması ve bu tablonun dummy satır ile doldurulmasını tamamladık.
İkinci aşama olan tabloyu doldurmaya geçelim:
INSERT INTO public.dimShipGeography
SELECT ShipGeography.*
FROM public.dblink('dbname=Northwind',
'SELECT ROW_NUMBER() OVER(PARTITION BY 1) AS TeslimatBolgesiSK
,sg.*
FROM
(
SELECT DISTINCT COALESCE(ship_name, ''Unknown'') AS TeslimatAdi
,COALESCE(ship_address, ''Unknown'') AS TeslimatAdresi
,COALESCE(ship_city, ''Unknown'') AS TeslimatSehri
,CASE ship_region
WHEN ''AK'' THEN ''Alaska''
WHEN ''CA'' THEN ''California''
WHEN ''ID'' THEN ''Idaho''
WHEN ''MT'' THEN ''Montana''
WHEN ''NM'' THEN ''New Mexico''
WHEN ''OR'' THEN ''Oregon''
WHEN ''WA'' THEN ''Washington''
WHEN ''WY'' THEN ''Wyoming''
ELSE ''Unknown''
END AS TeslimatBolgesi
,COALESCE(ship_postal_code, ''Unknown'') AS TeslimatPostaKodu
,COALESCE(ship_country, ''Unknown'') AS TeslimatUlkesi
FROM public.orders
) sg;')
AS ShipGeography(TeslimatBolgesiSK INT
,TeslimatAdi VARCHAR
,TeslimatAdresi VARCHAR
,TeslimatSehri VARCHAR
,TeslimatBolgesi VARCHAR
,TeslimatPostaKodu VARCHAR
,TeslimatUlkesi VARCHAR);
“Orders” tablosunda karşımıza çıkan coğrafik bölge bilgileri için dimension tablomuzu doldurduk. Sorgu yine bir öncekiler ile benzer olduğundan, detaya girmeden devam ediyorum.
Tabloyu inceleyecek olursak;
Evet tablodaki veriler istediğimiz gibi gözüküyor. Bu dimension tablosunu da burada noktalayarak, son tablomuz olan “fact” tablosuna geçebiliriz.
7. Fact Tablosu
Fact tablosu CREATE scripti ile başlayalım:
CREATE TABLE public.FactSales
(
FactSK INT PRIMARY KEY NOT NULL,
MusteriID INT NOT NULL,
CalisanID INT NOT NULL,
KargoSirketiID INT NOT NULL,
SiparisID INT NOT NULL,
UrunID INT NOT NULL,
TeslimatBolgesiID INT NOT NULL,
BirimFiyat REAL,
Adet INT,
IndirimOrani REAL,
TotalGetiri REAL,
KargoUcreti REAL,
SiparisTarihi INT NOT NULL,
TeslimatTarihi DATE,
ZorunluTeslimatTarihi DATE
);
Burada dikkat edilmesi gereken bir nokta, “FK(Foreign Key)” alanları dışında NULL değerlere izin vermemiz. Bunun sebebini ilk yazıda açıklamıştık.
Tabloyu doldurarak devam edelim:
CREATE TEMPORARY TABLE temp_dimCustomer AS
SELECT dimCustomer.*
FROM public.dblink('dbname=Northwind_DW',
'SELECT MusteriSK
,MusteriID
FROM public.dimcustomer;')
AS dimCustomer(MusteriSK INT, MusteriID VARCHAR);
CREATE TEMPORARY TABLE temp_dimEmployee AS
SELECT dimEmployee.*
FROM public.dblink('dbname=Northwind_DW',
'SELECT CalisanSK
,CalisanID
FROM public.dimEmployee;')
AS dimEmployee(CalisanSK INT, CalisanID INT);
CREATE TEMPORARY TABLE temp_dimShipper AS
SELECT dimShipper.*
FROM public.dblink('dbname=Northwind_DW',
'SELECT KargoSirketiSK
,KargoSirketiID
FROM public.dimShipper;')
AS dimShipper(KargoSirketiSK INT, KargoSirketiID INT);
CREATE TEMPORARY TABLE temp_dimProduct AS
SELECT dimProduct.*
FROM public.dblink('dbname=Northwind_DW',
'SELECT UrunSK
,UrunID
FROM public.dimProduct;')
AS dimProduct(UrunSK INT, UrunID INT);
CREATE TEMPORARY TABLE temp_dimShipGeography AS
SELECT dimShipGeography.*
FROM public.dblink('dbname=Northwind_DW',
'SELECT TeslimatBolgesiSK
,TeslimatAdi
,TeslimatAdresi
FROM public.dimShipGeography;')
AS dimShipGeography(TeslimatBolgesiSK INT, TeslimatAdi VARCHAR,
TeslimatAdresi VARCHAR);
CREATE TEMPORARY TABLE temp_dimDate AS
SELECT dimDate.*
FROM public.dblink('dbname=Northwind_DW',
'SELECT TarihSK
,Tarih
FROM public.dimDate;')
AS dimDate(TarihSK INT, Tarih DATE);
CREATE TABLE fact_temp AS
SELECT ROW_NUMBER() OVER(PARTITION BY 1) AS FactSK
,c.MusteriSK AS MusteriID
,e.CalisanSK AS CalisanID
,s.KargoSirketiSK AS KargoSirketiID
,o.Order_id AS SiparisID
,p.UrunSK AS UrunID
,sg.TeslimatBolgesiSK AS TeslimatBolgesiID
,od.Unit_price AS BirimFiyat
,od.Quantity AS Adet
,od.Discount AS IndirimOrani
,ROUND(CAST((od.Unit_price * od.Quantity) * (1 - od.Discount) AS Numeric), 2) AS TotalGetiri
,ROUND(CAST(o.Freight / COUNT(*) OVER (PARTITION BY o.order_id) AS Numeric), 2) AS KargoUcreti
,d.TarihSK AS SiparisTarihi
,o.Shipped_date AS TeslimatTarihi
,o.Required_date AS ZorunluTeslimatTarihi
FROM public.order_details od
LEFT OUTER JOIN public.orders o
ON od.order_id = o.order_id
LEFT OUTER JOIN temp_dimCustomer c
ON COALESCE(o.customer_id, 'Unknown') = c.MusteriID
LEFT OUTER JOIN temp_dimEmployee e
ON COALESCE(o.employee_id, -1) = e.CalisanID
LEFT OUTER JOIN temp_dimShipper s
ON COALESCE(o.ship_via, -1) = s.KargoSirketiID
LEFT OUTER JOIN temp_dimProduct p
ON COALESCE(od.product_id, -1) = p.UrunID
LEFT OUTER JOIN temp_dimShipGeography sg
ON COALESCE(o.ship_name, 'Unknown') = sg.TeslimatAdi
AND COALESCE(o.ship_address, 'Unknown') = sg.TeslimatAdresi
LEFT OUTER JOIN temp_dimDate d
ON COALESCE(o.order_date, '2099-01-01') = d.Tarih
;
INSERT INTO public.factsales
SELECT factsales.*
FROM public.dblink('dbname=Northwind',
'SELECT *
FROM public.fact_temp;')
AS factsales(FactSK INT, MusteriID INT, CalisanID INT
,KargoSirketiID INT, SiparisID INT, UrunID INT
,TeslimatBolgesiID INT, BirimFiyat REAL, Adet INT
,IndirimOranı REAL, TotalGetiri REAL, KargoUcreti REAL
,SiparisTarihi INT, TeslimatTarihi DATE, ZorunluTeslimatTarihi DATE);
DROP TABLE fact_temp;
Evet, fact tablosunu doldurmak adına yazdığımız sorguyu biraz açıklayalım.
- Öncelikle, şu ana kadar oluşturmuş olduğumuz dimension tablolarından “temp” tablolar yaratıyoruz. Bunun sebebi, fact tablosunun dolarken bu dimension tablolarından yararlanacak olması. Direkt olarak o tablolara sorgu atamadığımızdan, temp tablolar vasıtasıyla onlardaki veriyi geçici olarak buraya alıyoruz.
- İkinci adımda, modelimizdeki asıl fact tablosunu doldururken faydalanılmak üzere, “Northwind” veri tabanında bir fiziksel tablo yaratıp, içerisine veriyi dolduruyoruz. Veriyi doldururken kullandığımız sorguya bakacak olursak, tüm dimension tablolarına joinlendiğimizi ve gerekli SK alanlarını aldığımızı, sonrasında ise fact alanların türetilmesi ve kargo ücretinin de indirgenmesi işlemlerini görüyoruz.
- En son adımda ise, gerçek fact tablomuzu bu yaratmış olduğumuz geçici tablodan doldurup, o geçici tabloyu da DROP metodu ile veritabanından düşürüyoruz. Şuna dikkat etmek gerekir ki, 85. satıra kadar olan kodları “Northwind” veritabanında, 85-96. satırlar arasını “Northwind_DW” veritabanında, 96. satırı ise tekrar “Northwind” veritabanında çalıştırdık.
Şimdi, fact tablosunun içeriğine göz atalım:
SELECT * FROM public.factsales;
Görüleceği üzere, fact tablosunun verileri de istediğimiz şekilde doldurulmuş durumda.
Evet, ilk yazıda logical(teorik) halini oluşturmuş olduğumuz dimensional modelin fiziksel halini de bu yazıda oluşturduk. İlk yazıda nedenleriyle beraber detaylıca anlattığımız model prensiplerini, fiziksel modeli oluştururken birebir uyguladık.
Serinin bir sonraki ve son yazısında, bu modelin raporlamada nasıl kullanılacağına örnek raporlar oluşturarak bakacağız.
Notlar
- Normal bir modelleme/veri ambarı projesinde, eğer ETL SQL kullanılarak yapılıyorsa, kod blokları çok daha düzenli yazılır. Örneğin, yapılacak her iş için bir SP(Stored Procedure) yazılarak, bunlar vasıtasıyla ETL akışı gerçekleştirilebilir. Bunun sebebi, kod bloklarını daha yönetilebilir ve düzenli kılmak, aynı zamanda hataları da izole ederek sorun çözümlerini kolaylaştırmaktır. Biz burada fikir vermek ve ana konuyu işlemek adına çok fazla detaya girmediğimiz için, bu yola gitmedik.
- Modelin geliştirmesi yapılırken, daha farklı(ya da daha efektif) metodlar da kullanılabilirdi, zira bu konuda çeşitli çözümler ve alternatif yollar mevcut. Fakat biz burada yazıyı da fazla uzatmamak adına, en basit ve anlaşılabilir metodlar ile ilerledik.