Şirketlerin odaklandıkları belli başlı iş süreçleri vardır ve bu iş süreçlerinin performansı şirketlerin alacakları kararlarda belirleyici niteliğe sahiptir. Perakende sektörü iş süreci de, diğer birçok iş süreci gibi önemli sayısal metrikler üretir ve bunların düzenli ve temiz bir şekilde belirli aralıklarla raporlanmasını talep eder. Bu yazı serisinin ana içeriği, elimizde OLTP sistemde bulunan bir perakende iş süreci verisini Kimball prensiplerine göre modellemek ve daha sonra bunun geliştirmesini yapmak olacak.

Bu yazı serisinin üç bölümden oluşmasını planlıyorum, serinin sonunda OLTP sistemden alınan perakende verisini, OLAP niteliklerine uygun düzenli bir dimensional (boyutsal) modele dönüştürecek ve bunu önce teorik sonra da pratik olarak geliştireceğiz. Buradaki asıl amaç, bir data warehouse içerisindeki birden çok dimensional modelin aslında parça parça nasıl oluşturulduğunu görmek olacak. Bu modellerden şimdilik sadece bir tanesine odaklanmak, bizim büyük resmi kavramamıza yardımcı olacaktır.

Serinin diğer yazılarını okumak için:

Serinin bu ilk yazısında perakende iş sürecinin logical modelini oluşturacağız. “Logical” kelimesinden kastımız, teorik modelin oluşturulması. Daha sonra bu teorik modelin üzerine gerçek fiziksel modelimizi ikinci yazıda bina edeceğiz. Üçüncü yazıda ise, bina edilen modelden ne tür raporlar oluşturulabilir ve bunlar karar alım süreçlerinde nasıl kullanılabilir bu noktalara değineceğiz.

Bu ilk yazıda, çok bilinen “Northwind” veritabanından yararlanmayı planlıyorum ve sonraki yazılarda modelimizi de bir PostrgeSQL veritabanında oluşturacağız. Sonrasında bu veritabanından bazı raporlar oluşturmak için yine SQL kullanacağız.

Öncelikle “Northwind” veritabanını tanıyarak başlayalım. Bu veritabanı “Northwind” adlı kurgusal bir yiyecek ithalat/ihracat firmasının satış verilerini içerir. ER diyagramına göz atacak olursak:

  • Görüleceği üzere siparişlerin toplandığı ana tablo “Orders” tablosu. Bu siparişlerin daha detaylı hali ise “Order Details” tablosunda toplanmış. “Orders” tablosu için parent (ebeveyn), “Order Details” için ise child (çocuk) tablo sıfatlarını kullanabiliriz. “Order Details” tablosunda granülarite tek bir ürün seviyesine kadar düşerken, “Orders” tablosu direkt olarak sipariş seviyesinde bir tablo.
  • Bunlara ek olarak, “Products” ve “Categories” tabloları ürünleri ve bunların hangi kategorilere ait oldukları bilgilerini tutuyor. Dolayısıyla kategoriler ve ürünler arasında bir hiyerarşi var.
  • “Customers” tablosu müşteri detaylarını tutarken, “CustomerDemographics” tablosu olası tüm müşteri demografik bilgilerini barındırıyor. Burada iki tablo arasında köprü görevi gören ve müşteriler ile demografik bilgiler arasındaki çoka-çok (many to many) ilişkiyi saklayan bir de “CustomerCustomerDemo” tablosu var.
  • Aynı ilişki “Employees”, “Territories” ve “Regions” tabloları arasında da var. “Territories” ve “Regions” arasında bulunan bire-çok(one to many) ilişkiden dolayı bir hiyerarşi bulunuyor.
  • Daha sonra “EmployeeTerritories” tablosu çalışanlar ve bunların bölgeleri arasındaki köprü görevini görürken, “Employees” tablosu ise çalışan bilgilerinin tutulduğu tablo.
  • “Shippers” tablosu ise kargo şirketi bilgilerini içeren tablo olarak karşımıza çıkıyor.
  • Son olarak, “Suppliers” tablosu da tedarikçi şirketi bilgilerini içeriyor. Dikkat edilmesi gereken bir nokta, customer demographics ile alakalı tabloların boş olduğu. Dolayısıyla bunlardan gelebilecek herhangi bir bilgi gözükmüyor.

Evet “Northwind” veritabanın genel yapısı bu şekilde. Karşımızda bir perakende sektörü şirketinin veritabanı bulunuyor ve biz şirketin bu sektördeki başarısını ölçmek adına bu yapıyı bir dimensional modele çevirerek raporlamaya hazır ve kolay raporlanabilir bir hale getirmek istiyoruz. Her ne kadar veri ambarı için dimension modeller oluşturmada karşımıza farklı yaklaşımlar çıksa da, biz bu yazı serisinde Kimball prensiplerini kullanacağız. İleride farklı yaklaşımlar ile alakalı yazılar da yazabiliriz elbette fakat şu anlık bu kulvarda ilerleyeceğiz. Hangi yaklaşımın veya yöntemin bu konuda daha iyi sonuçlar vereceği bambaşka bir tartışma konusu ve bu yazının ana konusunun dışında kalıyor.

Normal şartlarda, bir veri ambarı projesi başlarken, şirketin aktif faaliyet gösterdiği iş süreçleri önemlerine ve hızlıca geliştirilebilirliklerine göre bir sıralamaya tabi tutulduktan sonra hangisine öncelikle başlanacağı tartışılır. Bizim burada modelleyeceğimiz iş süreci direkt olarak perakende satışlar olacağından, böyle bir listeye gerek yok ve “Northwind” şirketinin ürün satışları üzerine odaklanıp o süreci modelleyebiliriz.

Bu modelleme süreci bizim için 4 adımla başlayacak:

  1. İş Sürecinin Belirlenmesi
  2. Granülaritenin Belirlenmesi
  3. Dimensionların (boyut) Belirlenmesi
  4. Factlarin (olgu) Belirlenmesi

1. Adım: İş Sürecinin Belirlenmesi

İlk aşamayı zaten üstte de tamamladık, bu yazıda modelleyeceğimiz iş süreci “Northwind” şirketinin ürün siparişleri ve bunların satışı olacak. Dolayısıyla ikinci adıma geçerek devam edebiliriz.

2. Adım: Granülaritenin Belirlenmesi

Granülarite, verinin elde edildiği en düşük detay olarak tanımlanabilir. “Northwind” senaryosunda düşünecek olursak, elimizdeki en ince detay veri “Order Details” tablosunda bulunmaktadır ve her siparişteki bir ürün başına bir satır olarak tanımlanabilir. Tabii burada şöyle bir varsayım var ki bir siparişte aynı ürün birden çok kez satılmışsa, o ürün tek satırda ifade edilip “Quantity” alanına satılan miktar girilmeli. Yani, örneğin 3 ürün satıldıysa 3 satır değil de tek satırda 3 adet ürün satıldığı belirtilmeli.

Granülerite, bizim için çok kritik bir önem arz etmektedir. Raporlamanın çok daha kapsamlı ve detaylı bir şekilde yapılabilmesi için, mümkün olan en düşük detaydaki granüleritenin seçilmesi gerekir. Aksi takdirde granülerite detayı yükseldikçe raporlarda görülmesi mümkün olan ayrıntılar da düşecektir. Veriyi detay seviyeden özet seviyeye(daha yüksek granüleriteye) çektikçe dimension (boyut) kaybı yaşarız. Bu da raporlamadaki detay taleplerin karşılanması konusunda karşımıza engel çıkaracaktır.

3. Adım: Dimension’ların (boyut) Belirlenmesi

Bu bölümde, modelimizde yer alacak olan dimensionları belirleyeceğiz ve detaylandıracağız. Her dimension için olası özellikler (dimension kolonları) neler olabilir ve var olan kolonlardan neler türetebiliriz bunları konuşacağız. Unutmamak gerekir ki özellikleri türetirken aklımızda olması gereken nokta raporların nasıl oluşacağıdır. Dimension kolonları aslında finalde raporlarda göreceğimiz kolonlar olacak ve o kolonlardaki değerler de raporlarda göreceğimiz değerlere dönüşecek. Dimension kolonları genelde raporlarda gruplamada ve filtrelemede kullanılır. Ayrıca bu kolonlar direkt rapor kolonları olarak da yansıyabilir. O yüzden dimension kolonlarını belirlerken ve oluştururken aklımızda sürekli bunların raporlarda nasıl kullanılacağı yer almalı ve ileriyi görerek tasarlamalıyız.

Kullanacağımız dimension tablolarını şu şekilde sıralayabiliriz;

  1. date (tarih),
  2. product (ürün),
  3. customer (müşteri),
  4. employee (çalışan),
  5. shipper (kargo şirketi) ve
  6. ShipGeography (teslimat bölgesi).

İlk dimension tablomuz ile başlayalım.

3.1. Date (Tarih) Dimension

Date dimension neredeyse tüm dimensional modellerde bulunan bir dimension tablosudur. Çünkü neredeyse her ölçülen metrik zamanla alakalıdır ve zaman boyutuna göre analiz edilmeyi talep eder. Dolayısıyla zaman boyutunu da bir tablo halinde modelimizde barındırmak bizim için çoğunlukla bir zorunluluktur. Bu dimension tablosunun diğer dimensionlardan bir farkı da, önceden doldurulabilir olmasıdır. Takvimdeki tarihler zaten belli olduğundan, verinin yenilenmesini veya herhangi bir adımı beklemeden date dimension tablosunu önceden doldurabiliriz.

Bu tablodaki granülarite tercihi genellikle gün bazında olmaktadır, yani her satır takvimdeki bir günü ifade etmektedir. Bu noktada şöyle bir soru sorulabilir; gün bazında bir date dimension tablosu bizim için yeterli olacak mıdır? Çünkü fact tablolarındaki metrikler daha düşük granülaritelerde (saat, dakika, saniye vb.) raporlanmak istenebilir. Bu takdirde, date dimension’ının granülaritesini bu seviyelere çekmek yerine, siparişin gerçekleştiği “timestamp” bilgisini direkt olarak fact tablo üzerinde tutmak ve SQL’in sunduğu Date fonksiyonları ile analize katmak veya ayrı bir “timestamp” dimension tablosu oluşturup bu tablonun detay seviyesini ihtiyaca göre belirlemek daha mantıklı olabilir.

Örneğin dakikalık analizler isteniyorsa, bu tablo bir günün her dakikasını tek satırla temsil eden 60×24 = 1,440 satırlık bir tablo olabilir. Aynı şekilde, saniyelik talepler olduğu takdirde 60x60x24 = 86,400 satırlık bir tablo işimizi görecektir. Bu detay seviyesini asıl date dimension tablomuza entegre etmek isteseydik, bir yıllık veriyi karşılamak için dahi 86,400×365 = 31,536,000 satırlık bir tablo gerekecekti ki çok daha uzun vadelerde bu sayı inanılmaz büyük bir tabloya doğru yol alacaktı. Dolayısıyla mantıklı bir çözüm olmazdı. Ayrı “timestamp” dimension tablosu oluşturulduktan sonra, onun da “Surrogate key” alanı normal date tablosunun “Surrogate key” alanı gibi fact tablosunda yerini alıp raporlamalara dahil olabilir.

Peki, date dimension tablosundaki özellikler (alanlar veya kolonlar) ne olmalıdır?
Bu noktada da yine yaratıcı düşünce ve gelecekte oluşturulacak olan raporların taleplerini ön görme konusu devreye giriyor. Örneğin, raporlarda zaman konusunda hangi filtreler veya alanlar görülmek istenir? İlk akla gelenler gün, hafta, ay ve yıl bilgisi, haftanın kaçıncı günü olduğu, ayın kaçıncı günü olduğu, hangi çeyrekte olduğu, iş günü olup olmadığı, eğer ticari takvim kullanılıyorsa aynı bilgilerin ticari takvim versiyonu gibi birçok bilgi aklımıza gelir. Biz bu yazının kapsamını da kısa tutmak adına, aşağıdaki gibi bazılarını ekleyip diğerlerini de ucu açık şekilde bırakalım:

Evet, yukarıdaki alanlar bizim örnek date dimension tablomuzu oluşturabilir. İlk alan dimension tablosunun “Surrogate Key” alanı, aynı zamanda “Primary Key” görevini de üstleniyor. Bu anahtar, diğer dimension tablolarının aksine genelde akıllı anahtar şeklinde tabir edilen ve tarih bilgisinin integer’a çevrilmiş halini barındıran bir anahtar alandır. Örneğin, 1 Şubat 2021 için “01022021” şeklinde bir değer içerir ve aslında bu değerin veri tipi integer’dır.

Date dimension tablosunun kapsamı artırılabilir ve daha detaya da girilebilir, fakat yazının aşırı uzun hale gelmemesi açısından bu bölümü biraz daha kısa tutuyorum. Bu alanların içerisindeki değerlerin nasıl olacağına, serinin 2. yazısında tabloları gerçekten fiziksel olarak oluştururken değineceğim.

3.2. Product (Ürün) Dimension

Ürün dimension tablosu, elimizdeki ürünler hakkında tüm bilgileri tutacak olan tablo. Bu tabloda ürünleri mümkün olduğunca kullanışlı ve anlaşılır özellikler ile donatıp raporlarda kullanılabilir hale getireceğiz. Özellikler(kolonlar) ne kadar iyi ve kaliteli olursa, onlardan faydalanan raporlar da o derecede kaliteli olacaktır. Ek olarak, ürünler konusunda elimizde herhangi bir hiyerarşi varsa, bunu da ürün tablosunda düzleştirerek bulunduracağız. Örneğin, şuan elimizde ürünler ve bunların bağlı olduğu kategoriler var. Bu da aslında bizim için ürünler ve kategoriler arasında bir hiyerarşi oluşturuyor. “Northwind” veritabanının elimizdeki OLTP versiyonunda, tablolar normalleştirilmiş durumda ve dolayısıyla kategoriler için ayrı bir lookup tablosu oluşturulup ürünler tablosuna “Foreign Key” alanı ile bağlanmış. Biz bu normalizasyonun derecesini düşürüp kategori lookup tablosunu ortadan kaldıracağız ve kategori bilgisini de direkt olarak ürün tablosunda tutacağız. Böylece ekstra joinleri ve anlaşılması zor yapıyı devre dışı bırakarak dimensional(boyutsal) modellemenin iki ana ilkesi olan “kolay anlaşılabilirlik” ve “hızlı sorgu performansı” koşullarına öncelik vereceğiz.

Ürün dimension tabloları oluşturulurken genelde IT tarafından sağlanan bir ana “ürün” dosyası baz alınır. Fakat elimizde böyle bir dosya olmadığından, direkt olarak “product” tablosunu kullanacağız. “Product” tablosundaki alanlara bir göz atalım:

Evet “Product” tablosunda bulunan alanlar görsellerdeki gibi. Bunları şu şekilde sıralayabiliriz; Ürün ID’si, ürün adı, tedarikçi ID’si, kategori ID’si, birim başına miktar, birim fiyat, stoktaki miktar, siparişteki miktar, yeniden sipariş adedi, üretimde mi değil mi. Üstte de bahsettiğimiz gibi kategori ve tedarikçi ID’lerini normalleştirmek yerine burada direkt olarak tutacağız. Kategoriler ve ürünler bir hiyerarşi oluştururken, ürünler ve tedarikçiler oluşturmuyor. Dolayısıyla bu da bize aslında bir hiyerarşiye mensup olan veya olmayan tüm özelliklerin bir tabloda sorunsuz bir şekilde bulunabileceğini gösteriyor.

Diğer dimension tablolarında yaptığımız gibi burada da tabii ki ürünün kendi ID’sini değil bizim atayacağımız bir “Surrogate Key(SK)” alanını “Primary Key” olarak kullanacağız. Fakat ürün ID’sine de elbette “Natural Key (NK)” olarak yer vereceğiz. Bunlar dışında, tablodaki diğer alanlar da dimension tablomuzda yer alacak. “Discontinued” alanı bir “flag” alan olduğundan dolayı, içerisindeki değerleri 1 ve 0 değil de “Evet” veya “Hayır” olarak değiştirelim. Bunu yapmamızın sebebi, bu alan kullanılarak bir rapor oluşturulduğunda, 1 veya 0 değerleri yerine çok daha anlaşılır ve net değerlerin yer almasını istememiz. Kullanıcılar için “Evet” ve “Hayır” değerleri 1 ve 0 değerlerine göre çok daha mantıklı ve kullanılabilir değerlerdir, o yüzden dimension tablomuzda bunlara yer vermek daha sağlıklı raporlar için mantıklı bir adım olacaktır. “Northwind” veritabanındaki “Product” tablosunun alanları gerçekten kısıtlı olduğu için, şu aşamada daha fazla alan eklemeden devam edebiliriz.

Final product tablomuz aşağıdaki gibi olacak:

Ürün dimension tablosunda birçok sayısal değer olduğu da dikkat çekebilir. Sayısal değerler daha çok fact tablolarında bulunmaya yatkındır, fakat bir sayısal değerin hangi tabloda bulunacağına karar vermek için basit bir soru sorulabilir. Bu sayısal değer, analitik fonksiyonlar içerisinde kullanılan bir değer mi yoksa daha çok gruplamalarda ve filtrelemelerde kullanılan bir değer mi. Eğer cevap birinciyse, o sayısal değer büyük ihtimalle fact tablosuna ait olmalıdır. Eğer ikinciyse, dimension tablosu için daha uygundur. Burada birim fiyat, stoktaki birim, siparişteki birim gibi değerler şirketin performansını ölçerken oluşturulan raporlarda kullanılan ve analitik işlemlere maruz kalan sayısal metriklerden çok daha farklı. Bunlar ürüne özel metrikler ve analitik kapabiliteleri çok düşük. Zaten dikkatli bakıldığında bunların “additive(toplanabilir)” metrikler kategorisine değil de daha çok “semi-additive(yarı toplanabilir)” kategorisine uyduğunu görüyoruz. Dolayısıyla ürün dimension tablomuzun bu metrikler için en uygun yer olduğunda karar kılabiliriz.

Bu tablonun alanları içerisindeki değerleri de tabloyu fiziksel olarak inşa ettiğimizde göreceğiz.

3.3. Customer (Müşteri) Dimension

Müşteri dimension tablosu, müşteriler ile alakalı raporlamada kullanılabilecek her türlü bilgiyi saklayacak olan tablomuz olacak. Yine müşteriler ile alakalı özellikleri kaliteli, açık ve net olarak bu tabloda yansıtmaya çalışacağız. Ekleyeceğimiz veya çıkaracağımız her özellik, bizim raporlardaki kalitemizi etkileyeceğinden dolayı, bu tablodaki kolonlar son derece verimli olmalı. Elbette bu tüm dimension tabloları için geçerli. “Northwind” şirketinin müşterileri kurumsal firmalar olduğundan dolayı, bizim tablomuz da bu müşterilerin detaylarını içerecek.

Customer tablosunun alanlarına bir göz atalım:

Evet kolonlar sırasıyla müşteri ID’si, müşteri adı, iletişim kurulan kişi, iletişim kurulan kişinin ünvanı, adres, şehir, bölge, posta kodu, ülke, telefon ve fax. Her zamanki gibi, tabloya kendi SK alanımızı atayıp PK olarak seçeceğiz ve buradaki müşteri ID’sini de NK olarak tutmaya devam edeceğiz. Bu alanın NK olarak var olmaya devam etmesi, tabloyu kaynak sistem ile tekrar bağlamak istediğimiz senaryolarda bize çok yardımcı olacaktır. Ayrıca bir ürünün birden fazla versiyonunu tutuyorsak, bunu da ayırd etmemizi sağlayacaktır. Eğer aynı NK değerinin birden fazla kaynaktan gelme olasılığı var ise, NK değerlerinin önüne kaynaklarını belirten bir kısa ön ek koyulabilir. Fakat bizim senaryomuzda böyle bir durum olmadığı için gerek yok.

Bir diğer nokta, “Region” alanının kısaltmalardan oluşuyor olması. Örneğin, “AK” -> Alaska, “AL” -> “Alabama” vb. Bu durumda, kriptik bölge kodlarını dimension tablosunda bırakmak yerine, bunları daha açıklayıcı bölge isimleriyle değiştirmek daha mantıklı olacaktır. Böylece raporlar kısaltmalara bakmaktansa, direkt olarak bölge adlarından faydalanabilir ve bunları gösterebilir. Bu da son kullanıcı için kolaylık sağlar.

Tabloya baktığımızda bir başka dikkat çeken nokta, kolonlardaki “NULL” değerler. Dimension kolonları üstte de belirttiğimiz gibi raporlarda filtrelemelerde veya gruplamalarda kullanıldığından dolayı, kolonlar içerisinde NULL değerler bulunması rapora da yansıyacaktır. Örneğin, raporda “Region” alanına göre bir gruplama yapıldığını düşünelim. NULL değerler de raporda görülecek ve bunların sayıları da belirtilecektir. NULL değer son kullanıcı için hiçbir şey ifade etmeyen, kafa karıştıran bir değer olabilir. Ayrıca raporun kötü görünmesini de sağlayacaktır. Ek olarak, farklı raporlama araçları NULL’ların kullanımını farklı uyguluyor olabilir, bu da aynı rapor farklı araçlardan çalıştırıldığında farklı sonuçların doğmasına, dolayısıyla da kafa karışıklığına sebep olabilir. Dolayısıyla dimension tablolarının kolonlarında NULL değerler olduğunda, bunları daha anlamlı bir ifade ile, örneğin “Unknown(bilinmiyor)” ya da “Not Applicable(uygulanabilir değil)” gibi ifadelerle değiştirmek daha mantıklı olacaktır.

Son olarak, NULL değerler karşımıza iki farklı şekilde daha çıkabilir. Birincisi, dimension veya fact tablolarında anahtar kolonlarda NULL’lar bulunabilir, ki bu “Referential Integrity” denilen yani tüm anahtarların bütünlüğünü bozan bir durumdur. Dolayısıyla joinlerde aksamalara sebep olacaktır. Bu gibi durumlarda, dimension tablolarında NULL anahtar değerlerini karşılamak için bir “dummy(kukla)” satır bulundurulur. NULL olan anahtarlar yerine bu kukla satırın SK değeri yerleştirilir, dolayısıyla bütünlük bozulmamış olur. İkinci durum ise, NULL’ların fact tablolarındaki sayısal metriklerde ortaya çıkmasıdır. Bu konuda problem yoktur, NULL’lar olduğu gibi bırakılabilir çünkü SQL’deki sayısal özet fonksiyonları(SUM, MAX, MIN, AVG vb.), NULL değerler ile ilgili doğru yaklaşımları otomatik olarak uygulayacaktır.

Customer dimension tablosu ile ilgili bahsetmek istediğim son nokta, “postal_code” alanıdır. Bu alan bir posta kodu(zipcode) alanıdır ve aslında bu kod içerisinde bulunan harf ve rakamlar coğrafi bölgeleri temsil etmektedir. Dimension tablolarında bu tarz alanlar olduğunda, yapılacak en mantıklı uygulamalardan biri bu kodun içerisindeki anlamlı parçaları ayrıştırıp, koda ek olarak ayrı ayrı alanlarda sunmaktır. Böylece raporda filtreleme ya da gruplama yapılmak istendiğinde, zipcode alanının ilk, orta veya son harflerini kullanmaya çalışmak yerine, direkt olarak ayrılmış parçaların kendi alanlarını kullanmak son kullanıcıya inanılmaz bir kolaylık ve anlaşılabilirlik sağlayacaktır. Üstte de bahsettiğimiz gibi, dimension alanları ne kadar açıklayıcı, detaylı ve geniş olursa, finalde oluşacak rapolar da aynı oranda açıklayıcı ve kolay anlaşılabilir olur. Bizim senaryomuzda ise, zipcode alanının sağladığı coğrafik bilgiler zaten ayrı kolonlar olarak tabloda bulunuyor. Dolayısıyla, bu alanları oldukları gibi bırakmamız yeterli olacaktır.

Final tabloya geçmeden önce, eklemek istediğim bir nokta var. Customer demographics ile ilgili tabloların boş olduğuna yazının başında “Northwind” veritabanını anlatırken değinmiştik. Bu tablolar boş olmasaydı, kesinlikle bu tablolardan gelecek olan bilgileri de customer dimension tablomuza alırdık.

Final müşteri dimension tablomuz aşağıdaki olacak:

3.4. Employee (Çalışan) Dimension

Çalışan dimension tablomuz, “Northwind” şirketi içerisindeki çalışanların listesini ve özelliklerini barındıran boyut tablomuz olacak. Bu tabloda da çalışanlar ile alakalı, raporlanmaya müsait tüm değerli bilgileri modele dahil edeceğiz ve mümkün olduğu kadar da kaliteyi artıracağız. Tabloda hangi alanlar olduğuna bir göz atalım:

Tablodaki alanlar sırasıyla çalışan ID’si, soyadı, adı, unvanı, nezaket unvanı, doğum tarihi, işe alım tarihi, adres, şehir, bölge, posta kodu, ülke, ev telefonu, işyeri telefon kodu, fotoğraf, notlar, rapor verdiği çalışan ve fotoğraf klasör yolu olarak karşımıza çıkıyor.

Diğer dimension tablolarımızda geçerli olan ana kural burada da geçerli: tabloya PK görevini üstelenecek olan bir SK atayacak ve var olan PK değerini de NK olarak kullanacağız. İkinci olarak, NULL değerler ile alakalı bir önceki dimension tablomuzda bahsettiğimiz noktalar burada ve diğer tüm dimension tablolarında da geçerli olacak. Üçüncü nokta ise yine “Region” kolonundaki kısaltmaları tabloda tutmak yerine, bunları daha açıklayıcı ve net olan kendi isimleriyle değiştirmek böylece raporları daha sağlıklı ve anlaşılabilir bir noktaya çıkarmak olacak. Bu işlemi son kullanıcının kendi raporlama aracında yapması yerine, bizim arka planda yapmamız daha sağlıklı olacak ve hem son kullanıcının iş yükünü azaltacak hem de onun hata yapma olasılığını ortadan kaldıracaktır. Aynı işlem ülke bilgisi için de yapılabilir fakat bu bölge bilgisi kadar gerekli değildir çünkü ülke bilgilerinin kısaltmaları zaten gayet açık. Fakat elbette ki daha kompleks ülkelerin de işin içine girmesi durumunda bu çözme işlemi ülke bilgisi için de yapılabilir. Bizim “Northwind” senaryomuzda buna gerek şuan için yok.

Tabloda çalışanların fotoğrafları da bulunuyor, bu bilgi raporlarda kullanılamayacağı için bunu dimensional modele almayacağız. Aynı durum fotoğraf klasörünün yolu için de geçerli. İsteğe bağlı olarak bu alınabilir fakat şu koşullar altında gerek görünmüyor.

Elimizdeki en ilginç alanlardan birisi, “Notes” alanı. Bu alan, çalışanlar hakkında kısa ve önemli bilgiler içeren serbest bir “text” alanı. Dolayısıyla, içerisinde isteyen istediğini yazmakta özgür. Bu durum, bu alanın raporlamada kullanılmasını çok zorlaştırıyor. Bu alan ile ilgili bir olası senaryo, üzerinde “Data Mining” yöntemleri kullanılarak içerdiği text’in analiz edilmesi ve bundan çıkan sonuçların dimension tablosuna geri beslenmesi olacaktır. Fakat biz bu yazıda böyle bir senaryoya giriş yapmayacağımız için, bu alanı da modelden çıkararak devam etmek en sağlıklısı olacaktır. Fakat gerçek bir projede, bu alan çok değerli bilgiler içeriyor olabilir ve bahsettiğimiz analizlerden sonra modele eklenmesi gerekebilir.

Üzerinde konuşulması gereken bir diğer alan “reports_to” alanı. Bu alan, aslında kim kimin yöneticisi bilgisini içeriyor. Dolayısıyla tablo üzerinde “self-join” yapılarak(tablo kendisine joinlenerek) yöneticilerin kim oldukları ID’lerinden yakalanabilir. Elbette biz bu hiyerarşik yapıyı dimension tablosunda açacağız ve ID’leri saklamak yerine, müşterilerin ad ve soyadlarını direkt olarak “reports_to” alanında saklayacağız. Böylece fazladan bir join’i daha ortadan kaldırarak performansı iyileştirdiğimiz gibi, tabloya bakan bir son kullanıcının da yönetici ID’lerini görmek yerine, direkt olarak yöneticinin kendisini görmesini sağlayarak tablonun anlaşılabilirliğini artıracağız.

Bu dimension tablosu ile alakalı tartışacağımız bir diğer nokta, üzerindeki “date” alanları. Elimizde doğum tarihi ve işe alım tarihi olmak üzere iki tane tarih alanı bulunuyor. Bu alanlar ile alakalı iki farklı yol izlenebilir:

1. Eğer bu tarih alanları, raporlarda kullanılırken SQL’in tarih ile alakalı fonksiyonlarının sunduğu imkanlardan fazlası ile analiz edilmek isteniyorsa -ki bu aslında en başta işlediğimiz date dimension tablosunun alanlarına ihtiyaç duyulacağı anlamına geliyor- bu durumda date dimension tablosunu bu alanlar ile de ilişkilendirmemiz gerekebilir ve orada oluşturduğumuz özellikleri aslında bu tarih alanlarında da kullanılabilir hale getirmemiz gerekebilir.

2. Eğer raporlama konusunda böyle bir ihtiyaç varsa, elbette date dimension tablosunun kendisini bu alanlara bağlamak yerine, “Role-playing Dimensions” denilen ve aslında date dimension tablosunun birer kopyaları olan view’ları buradaki alanlara bağlamak çok daha mantıklı olacaktır. Hem doğum tarihi hem de işe alım tarihi alanları için iki farklı view oluşturularak bu view’ların hem kendi isimleri hem de alanlarının isimleri bağlı oldukları tarih alanına uygun olarak seçilmeli ve asıl date dimension tablosundan rahatça ayırd edilebilmelidir. Elbette ki bu view’lar da asıl date dimension tablosundan oluşturulacak olduğu için, içerisindeki veriler aynı olacak. Farklı olan nokta ise alan adları ve view adları olacak. Son aşamada ise bu view’ların SK alanı, çalışan dimension tablosundaki date alanların yerine bağlanacak(yine akıllı anahtar mantığı kullanılarak) ve rapor kullanımlarına açılmış olacak. Elbette burada dimensional modelin iki ana ilkesine biraz karşı çıkmış olsak da, ana yapıyı çok da bozmayan bir çözüm olacağı ve ihtiyacı karşılamanın da en etkili yolu olduğu için, bu çözüm burada uygulanabilir. Eğer bu tarih alanlarını raporlarda kullanmak için SQL’in bize sunduğu tarih fonksiyonları yeterli olacaksa, zaten herhangi bir view oluşturmaya gerek yok ve bu alanlar olduğu gibi bırakılabilir. Biz de kendi senaryomuzda bunları oldukları gibi bırakacağız.

Finalde, çalışan dimension tablomuz aşağıdaki gibi olacak:

Son olarak, çalışanlar ile alakalı bölge bilgilerinin başka tablolarda olması da bizi ilgilendiriyor. Bir çalışana birden fazla bölge atanmış ve bu bilgiler de bir köprü tablo vasıtasıyla tutulmuş durumda. Bu bilgileri de ana çalışan dimension tablomuza eklemeye kalktığımızda, durum biraz karışıyor ve önümüze alternatif yollar çıkıyor.

Birincisi, bu bilgiyi satır bazında eklemeye kalkarsak bu, dimension tablomuzda bir çalışan başına birden fazla satıra sahip olacağımız anlamına gelir ve bu da fact tablomuzu oluştururken hangi çalışan satırının SK değerini atayacağımız kararını vermemizi zorlaştıracaktır. Örneğin, 1 numaralı çalışanın bir satış yaptığını varsayalım. Bu durumda, çalışan dimension tablosundaki hangi 1 numaralı çalışan satırını o fact satırına bağlayacağımıza dair yeterli bilgi fact tablomuzda bulunmuyor. Dolayısıyla bu yol mantıklı gibi görünmüyor.

İkinci alternatif, bu ekstra bilgileri ek kolonlar olarak dimension tablosuna eklemek olabilir. Fakat bu da kolon patlamasına sebep olabileceği gibi, çalışan başına düşen region/territory değerleri değişkenlik gösterdiği için eklenecek kolon sayısı tahmin edilemezdi. Maksimum bir kolon sayısı belirlenip, çalışan başına mevcut olmayan kolon değerlerine NULL girilebilirdi fakat bu da tablodaki NULL oranını ciddi oranda artırırdı. Bu yüzden, bu yol ile ilerlemek de seçeneklerimiz arasında olmayacak.

Üçüncü alternatif, bir çalışan için territory ve region bilgilerini tek bir kolonda toplayıp virgül ile ayırmak olabilirdi. Örneğin, bir çalışan 3 territory ve 2 region’a dahil ise, “territory_list” ve “region_list” kolonları oluşturulabilir ve bu kolonlar sırasıyla “territory1, territory2, territory3” ve “region1, region2” değerlerini barındırabilirdi. Fakat bu yöntemin dezavantajı da raporlanmasını ve filtrelerde kullanılmasını çok zorlaştırması olurdu. Ayrıca, herhangi bir rapor esnasında bu değerlerin bir “combobox” içerisinde yer alması ve liste aşağı doğru açıldığında görülmeleri hiç hoş olmazdı.

Üstte saydığımız nedenlerden dolayı, burada ayrı bir köprü tablo bulundurmaya devam edeceğiz. Fakat bu tabloyu oldukça sadeleştirecek ve üç kolondan oluşturacağız. Bu kolonlar aşağıdaki gibi olacak:

Görüleceği üzere; çalışan-alan, alan ve bölge tablolarını birbirlerine joinleyerek tek tabloya indirdik ve gerekli tüm bilgileri bu tabloda topladık. İstenildiği takdirde, çalışan dimension tablosu bu tabloya çalışan ID’si üzerinden joinlenebilir ve gerekli alan ve bölge bilgileri elde edilerek rapora eklenebilir. Bu tablo bir çalışan için birden fazla aktif satır içerebilirken, asıl çalışan dimension tablosu bir çalışan için aktif sadece bir satır içeriyor.

3.5. Shipper (Kargo Şirketi) Dimension

Sıradaki dimension tablomuz, kargo şirketi bilgilerini içeren “Shippers Dimension” olacak. Tablonun hangi alanlara sahip olduğuna göz atalım:

Evet görüleceği üzere, “Shippers” tablosu çok küçük bir tablo ve barındırdığı üç alan var. Bunlar sırasıyla kargo şirketi ID’si, kargo şirketi adı ve telefon numarası. Dolayısıyla bu dimension tablosunu oluştururken yapacağımız işlem SK alanını atamak ve kargo şirketi ID’sini de NK alanı olarak bırakmak olacak. Diğer alanları ise oldukları gibi alacağız.

Final Shipper dimension tablomuz aşağıdaki gibi olacak:

3.6. ShipGeography (Teslimat Bölgesi) Dimension

Oluşturacağımız son dimension tablosu, teslimat bilgilerini içeren tablo olacak. Bu tablonun ana amacı, teslimat ile alakalı coğrafi sorular sorulduğunda, bu soruların cevabını vermek olacak ve raporlara bu açıdan hizmet edecek. Burada bahsedilmesi gereken bir nokta şu ki, aslında teslimat bölgesi bilgisi müşteri dimension tablosunda da bulunuyor. Fakat elbette, müşteri tablosundaki lokasyonlardan farklı bir lokasyona sipariş verilmesi ihtimali ve müşteri dimension tablosundaki iletişim ismi dışında bir isim adına kargo gönderilebilmesi seçeneklerini değerlendirdiğimizde, böyle bir teslimat bölgesi dimension tablosunun kullanışlı olacağını görebiliyoruz. Ek olarak, müşteri dimension tablosunda bulunmayan bazı ek bilgiler de bu dimension içerisinde bulunabilir.

“Orders” tablosunda teslimat bilgileri ile ilgili alanlara bakalım:

Evet, alanlar sırasıyla teslimatın kimin adına olduğu, teslimat adresi, teslimat şehri, teslimat bölgesi, teslimat posta kodu ve teslimat ülkesi. Bizim bu dimension tablomuzda faydalanmak istediğimiz alanlar da bunlar.

Diğer tablolarda yaptığımız gibi, bu tabloda da bir SK alanı atayacağız. Onun dışında, “ship_region” alanındaki değerleri de kısaltmalardan çıkararak açacağız ve tüm alanlardaki NULL değerleri de önceki tablolarda kullandığımız uygulamalar ile düzenleyeceğiz. Dikkat edilmesi gereken bir nokta, aynı teslimat adresi için farklı kişiler adına teslimat verilebilir. Dolayısıyla hangi SK’nın fact tablosuna atanacağı konusunda dikkatli olunmalıdır.

Bu dimension tablosu için final alanlarımız şu şekilde olacak:

4. Fact’lerin (Olgu) Belirlenmesi

Modelleme sürecindeki son aşamamız, fact tablosunun detaylarını belirlemek olacak. Elimizde sayısal metrikler içeren iki tablo bolunuyor. Bir tanesi parent(ebeveyn), diğeri de child(çocuk) tablo.

Bu tabloların alanlarını görelim.

Orders tablosu:

Evet bu tablodaki alanlar sırasıyla order ID’si, müşteri ID’si, çalışan ID’si, sipariş tarihi, teslim edilmesi gereken maksimum tarih, teslim tarihi, kargo şirketi ID’si, kargo ücreti. Bundan sonraki alanlar bir önceki dimension tablosunda bahsettiğimiz alanlar ve fact tablomuzda bulunmayacaklar. Dolayısıyla geçiyorum.

Order Details tablosu:

Buradaki alanlar da sırasıyla sipariş ID’si, ürün ID’si, birim fiyat, miktar ve indirim oranı.

Fact tablosunu oluştururken, granularitenin sipariş içerisindeki bir ürün olduğunu belirtmiştik. Dolayısıyla, “Order Details” tablosunu baz alarak ilerleyeceğiz. Müşteri ID, çalışan ID, kargocu ID gibi alanlar direkt olarak ebeveyn tablodan bu tabloya indirgenebilir alanlar olduğundan, bizim için bir problem teşkil etmiyor.

Tarih alanlarının nasıl işleneceği, alternatif senaryolara göre değişiklik gösterecektir. Örneğin, Bir siparişteki bir ürün bir tarihte, diğer ürün başka tarihte teslim ediliyor gibi senaryolar mevcut ise, bu durumda bu alanın detay tabloda olması gerekirdi. “Northwind” veritabanında bu bilgi parent tabloda bulunduğundan dolayı, böyle bir senaryonun olmadığını varsayıyoruz. Olmuş olsa dahi, bu bilgi elimizde bulunmadığı için, modelimize almamız mümkün değil. Dolayısıyla elimizdeki tüm tarih alanlarını direkt olarak detay tablomuza indirgeyebiliriz. Tarihlerle alakalı ikinci önemli nokta, üstte de bahsettiğimiz gibi 3 farklı tarih alanının raporlama ihtiyaçlarının ne olduğu. Eğer tamamı date dimension’daki alanlar ile analiz edilmeye ihtiyaç duyuyorsa, bu durumda 3 farklı “role-playing” view oluşturarak bu alanları besleyebiliriz. Hepsini aynı fiziksel date dimension tablosundan beslemek demek, aynı tabloya 3 farklı alandan join yapılacak olması demektir ki bu da bazı problemlere yol açabilir. Dolayısıyla view’lar ile ilerlemek daha mantıklı olacaktır. Fakat, bu ihtiyaç sadece “order date” alanı için mevcutsa, diğer tarih alanları olduğu gibi bırakılarak SQL’in date fonksiyonlarıyla analiz edilmelerine izin verilebilir. Biz de kendi modelimizde şimdilik ikinci yaklaşım ile ilerleyeceğiz. Fakat birinci yaklaşımın da rahatlıkla kullanılabileceğini unutmayalım.

Üzerinde tartışılması gereken son alan, kargo ücreti alanı. Bu alan, detay seviyede değil, sipariş seviyesinde bir alan. Siparişteki tüm ürünler için ortak bir kargo ücreti bulunuyor. Dolayısıyla, bu alanın mantıklı bir şekilde detay seviyeye indirgenerek, raporlarda kullanılabilir hale getirilmesi gerekiyor.

Birinci yaklaşım, bu sayıyı sipariş bazında satır sayısına bölerek eşit derecede her satırda bulundurmak olabilir. Örneğin, 3 ürünün satıldığı bir siparişte kargo ücreti 60 dolar ise, satır başına 20 dolar yazılabilir. Böylece sipariş başına kargo ücreti toplandığında, bize totalde doğru ücreti verecektir. Fakat bu yaklaşımın dezavantajı, satır bazında bakıldığında yanlış kargo ücretini göstermesi olacaktır. Fact tabloları üzerinde nadiren satır bazlı detaya inecek kadar raporlama yapıldığını düşünürsek, bu yaklaşım mantıklı olabilir.

İkincisi, aynı değeri her satırda barındırmak olabilir. Bu durumda sipariş bazlı kargo ücretlerine bakılırken toplama işlemi değil de ortalama/maksimum/minimum işlemlerinden biri kullanılabilir ve satır bazında bakıldığında da bize doğru rakamları gösterir. Fakat farklı siparişler için toplam veya ortalama kargo ücreti gibi sorular cevaplanırken hala doğru cevaplar bulunabiliyor olsa da işler biraz daha karmaşık bir hale gelecektir. Hangi yaklaşım seçilirse seçilsin, son kullanıcıya bilgi verilerek kargo ücreti alanını nasıl analiz etmesi gerektiği anlatılmalıdır. Aksi takdirde gelecekte problemler oluşabilir. Biz burada birinci yaklaşım ile ilerleyeceğiz.

Üstte yazdıklarımız ışığında, fact tablomuzun final hali aşağıdaki gibi olacaktır:

Tablonun alanlarına baktığımızda, dimension tablolarından gelen SK alanlarını, tarihleri ve fact’leri görüyoruz. Tüm dimension tablolarının SK alanlarına ek olarak, fact tablomuzun kendi SK alanı da bulunuyor ki bunun uzun vadede ek faydaları olacaktır. Tarih değerlerine baktığımızda, sipariş tarihini date dimension tablosunun SK alanı olarak görüyoruz, diğer date alanlar ise olduğu gibi bırakılmış durumda. Bu da üstte bahsettiğimiz noktanın fiziksel olarak uygulanmış hali. Fact alanlarına baktığımızda, elimizde hali hazırda var olan sayısal değerlere ek olarak, bir de “TotalGetiri” alanını ekledik. Bu alan, “(birim fiyat x miktar) – indirim” olarak hesaplanacak ve ürün başına siparişteki toplam getiriyi hesaplayacak. Bir veya birden çok sipariş için bu alanı hesapladığınızda, direkt olarak toplam kâr görülebilecek. Bunu son kullanıcıya bırakmayıp bizim ETL esnasında kendimiz yapma sebebimiz, böyle önemli bir metrik hesaplanırken oluşabilecek olan son kullanıcı hatalarını tamamen ortadan kaldırmak ve aynı zamanda son kullanıcının işini çok kolaylaştırmak. Bunu yaparken veri saklama alanından taviz versek de, rapordaki bir hatanın bedeli veri saklama alanımızın azalmasından çok daha tehlikeli sonuçlar doğurabileceğinden, bu yaklaşım mantıklı görünüyor.

Bahsetmek istediğim son alan ise, “Sipariş ID” alanı. Bu alan, herhangi bir dimension tablosuna bağlı değil. OLTP sistemde iken, bu alan “parent” seviyede birçok özelliği bir araya toplayan PK alan idi. Müşteri, kargo şirketi, çalışan gibi “parent” seviyede alanları görmek isteyen birisi sipariş ID’si üzerinden bu tabloya joinlendiğinde bu bilgileri elde edebiliyordu. Fakat bizim dimensional modelimizde bu “parent” seviyesindeki tüm bilgilerin zaten artık kendi dimension tabloları var, ayrıca “parent” ve “child” seviyedeki tablolar da birleşerek tek bir tabloya indirgenmiş durumda. Elimizde “sipariş ID” alanı tarafından temsil edilebilecek ekstra bir bilgi bulunmuyor ve bu alan herhangi bir dimension tablosuna da bağlanmıyor. Dolayısıyla elimizde “dejenere dimension” alanı olarak kalmış durumda. Bu alan bizim için hala yararlı çünkü tek bir siparişe dair satırları sepet halinde bir araya getirmemize yardımcı oluyor ve gerektiğinde kaynak sistemle bağlantı da kurabilir. Bu yüzden tablodan çıkarmayıp “dejenere dimension” alanı olarak tutuyoruz.

Perakende Dimensional (Boyutsal) Modelinin Final Şeması

Yazının başında bahsettiğimiz 4 adımlık modelleme sürecinin sonuna geldik. Bu noktada, oluşan veri modelimizin final haline bir göz atalım:

Dimension(boyutsal) modelimize baktığımızda ilk farkedilen nokta, “Nortwhind” veritabanının kendi ER diyagramından çok daha basit ve anlaşılabilir olmasıdır. Bu da aslında boyutsal modellemenin en önemli ilkelerinden biridir. Bir son kullanıcı, bu modele baktığında, anlamakta zorlanmayacaktır. İkinci nokta ise, bu modelin efektif sorgu performansına sahip olmasıdır. “Northwind” veritabanı zaten küçük bir veritabanı olduğundan bu fark hissedilmeyebilir fakat çok daha büyük verilerle çalışıldığında raporlama için optimize edilmiş bu dimensional yapının daha iyi sorgu performansı göstereceği muhakkaktır.

Son olarak belirtmek gerek ki, dimensional model ile OLTP sistemdeki normalize model arasındaki en büyük fark, normalizasyon derecesidir. İki veri dizaynı da içerik olarak tamamen aynı bilgiyi barındırır fakat normalizasyonun derecesinin farklı olması hem basitlik ve anlaşılabilirlik hem de performans açısından büyük farklar yaratır.

Serinin bu ilk yazısını böylece tamamlamış olduk. İkinci yazıda, bu modeli fiziksel olarak hayata geçireceğiz.

Notlar

  1. “Northwind” veritabanına veri eklenmediği ve düzenli bir ETL akışı olmayacağı için, modeli sadece 1 seferlik dizayn edeceğiz. Dolayısıyla, herhangi bir dimension değişimi olmayacağı için SCD yöntemleri gibi konulara yer vermedik. Bunlar, ileride başka yazıların konusu olacak.
  2. Söz konusu dimensional bir model oluşturmak olduğunda, elbette sadece bir metod yoktur. Bu model farklı şekillerde ve farklı prensipler kullanılarak da inşa edilebilirdi.

Kaynakça

The Datawarehouse Toolkit, 3rd edition – Kimball Group

PostgreSQL: Documentation: 10: PostgreSQL 10.16 Documentation