DWH’da Veri Bütünlüğü Veri Temizliği

 

DWH’da Veri Bütünlüğü Veri Temizliği

Veri bütünlüğü tüm ortamlarda olduğu gibi DWH’da da en önemli konulardan bir tanesi.Veri bütünlüğünü şöyle özetleyebiliriz müşteri tablosunda olmayan bir müşteriye ait satış olmamalıdır.Biz genelde dimensional modellemede(kimball) inmon modelleme kadar özen göstermeyebiliyoruz veri bütünlüğüne ama bizde de elbette sorunlara yol açabiliyor.

Normalde kaynak sistemlerimizin normalize edilmesini ve bütünlüğün FK’larla sağlanmasını bekliyoruz.Fakat bu her zaman istenildiği ölçüde başarılı olmayabiliyor.Özellikle eski sistemlerde bu çok başımıza gelebiliyor.Bu durumda yapmamız gereken ilk şey kaynak sistemde bu sorunları düzeltmek olmalı.Çünkü software ve data engineer olarak ayrılsakda bu bir bütün meselesi ve söz konusu olan iş dağılımı değil şirketin bütünlüğü.Dolayısıyla testlerinizde böyle bir örnek ile karşılaştınız veya iş biriminiz fark ettiğinde,sorunu kendi tarafınızda çözmeye çalışmaktansa kaynak sistemdeki arkadaşlarla görüşüp datayı orada düzenletirseniz sizin süreciniz dışındada bu datayla bir şekilde ilgilenen olursa onlar için de bu sorunu düzeltmiş olursunuz.

Fakat diyelim ki dokunulamıyor kaynak sisteme bu durumda ne yapacağız?Bu durumda sorunun sizin pipeline’lardan kaynaklanmadığını göstermek için “Tanımsız Kayıt” veya “Kaynakda Bulunamadı” gibi bir tanımlama ile bu sorunlu kayıtları göstermeniz lazım.Bunun içinde yapmanız gereken şey aslında çok basit.Fact’inizi doldururken ilgili FK’ları dolduracağınız pipeline’da BI katmanında joinleneceği dimension ile pipeline’da joinleyip varlık yokluk kontrolüne koyduktan sonra yok ise -1 var ise ilgili id’yi basabilirsiniz.Peki bu -1 neyi ifade edecek?Bu -1,dimension tablosuna öncesinde -1 idli,”Tanımsız Kayıt” açıklamalı eklediğiniz satır ile eşleşecek ve BI tarafında kullanıcılar Tanımsız Kayıt görecekler.Böylelikle sorunlu kayıtları hemen fark edebilecekler.Kaynak sistemde kayıtları düzeltebiliyorsanız dahi böyle bir mekanizma kurmanız hatalı kayıtları tespitini çok kolaylaştıracaktır.Tek sorun pipeline süresi uzayacaktır bence varsın uzasın veri bütünlüğü çok önemli bir konu çünkü.

Bir diğer önemli konu asla ama asla veri filtrelemeyin!.Bu çok canınızı sıkacak bir konudur.Örneğin bir log tablonuz var sizden xxxxx tipindeki işlemler istendi sadece,sizde ODS’e bu şekilde aldınız haliyle fact tablonuzada direkt ODS’den çıktınız.Fakat sonrasında yyyyy tipindeki işlemlerde istendi.Bu durumda ODS’e bunu aldınız fakat fact’iniz bu sefer başka tipdeki işlemleri göstermeye başladı.O yüzden muhakkak ODS’e full almaya çalışın veya bunu kullanan factlerinizde yarını düşünerek uygun filtrenizi verin.Bir diğer konu ODS full aldınız ama fact’e sadece belirli bir kısmı aldınız.Bu durumda diğerlerini istediğinizde tekrar çalışmanız gerekir.Peki son kullanıcı sadece durumu xxx olanları istiyor bu durumda ne yapmalıyız?Siz full çıkın kullanıcı için filtre koyun filtrelisiniz kendisi.Bunu yapamayız C level için yaptık,bu durumda yine fact’i full çıkın rapor veya dashboardda filtreleyin ama factiniz hala full olsun.

DWH’da en önemli konulardan bir tanesi granularity.Yani en dip seviyede veri vermeye çalışmalıyız.Çünkü yine aynı sebepten kesinlikle yarın detay seviyesi sorulacak ve size ek çalışma masrafı çıkacak.O yüzden en dip seviyede verin zaten BI toolunda aggregate olacak.Performans sorunu yaşarsanız en dip seviyedeki fact’inizden aggregate fact tablo çıkabilirsiniz.

 

Peki veri temizliği konularında ne yapabiliriz?

Burada o kadar fazla case karşınıza çıkacak ki herbiri birbirinden farklı olabilir.Fakat şunu söyleyebiliriz burada yine kaynakda işi çözdürmeye özen gösterin bu sizin için olduğu kadar onlar içinde çok değerli olacaktır.

Örnek verelim hemen.Mesela diyelim ki CRM uygulamanızın bir tablosu var müşteriye yapılan işlemleri tutuyor.Sizden işlem tipi bazında her gün kaç işlem yapıldı kaç farklı müşteriye yapıldı gibi bir rapor isteniyor.Fakat tablonuzda işlem tipi yok 😊İşlem tipi açıklama sütununda belirli bir prefix ile free text tutulmuş.Aşağıdaki örneklerde olduğu gibi.

Limit Değişikliği:Müşteri istedi değiştirdik.

İlan Hakkı Değişikliği:Müşteri ek istedi verdik.

Böyle bir tabloda belirli bir prefix ve sonrası free text verilmiş ya da daha kötüsü hiç prefix yok direkt açıklama içinde kuralsız yazılmış.Bu durumda açıklama alanını parçalayıp kendiniz bir işlem tipi alanı yaratabilirsiniz ama yarın kurala uymayan bir tip gelebilir ve sizin kuralınız işlemeyebilir.Bu yüzden kesinlikle kaynak sistemde böyle bir alan açılmasını istemelisiniz.

Free text bir alandan içinde xxxx veya yyyy gibi değerler aratacak şeklinde bir kural işleteceksiniz, öncesinde muhakkak lower veya upper fonksiyonları ile tüm yazıyı düzenleyin case sensitiveden gol yiyebilirsiniz.

 Diyelimki bu açıklama alanından işlemtipini ayırabildiniz bu bir fact tablo olduğu için kesinlikle text bir alan bırakmamalısınız.Bu durumda öncesinde bir lookup tablo yaratmanız gerekir.Lookup tablolar kaynakdan gelmeyen sizin oluşturduğunuz id ve açıklama barındıran tablolardır.Hangi işlem tipine hangi id’nin geleceği belli olduktan sonra artık fact tablonuzdaki işlemtipi_key sütununa bu lookup tablosundaki id’i basmanız gerekir.

Bunu yapmamızın çok önemli iki sebebi var.Birincisi text alanlar fact üzerinde çok ciddi yer kaplıyorlar.İkincisi İşlemtipini lookupdan almazsanız fact üzerinden almak zorunda kalırsınız bu da sadece işlemi olan işlemtiplerini getirmenizi sağlar.Fakat son kullanıcı tüm işlem tiplerini görmek ister yani işlemi yok ise xxx işlemtipinden 0 adet işlem var görmek ister.Ayrıca BI toolunuzdaki filtrelenizdede factten veri getirdiğinizde çok yavaş açılacaktır.

Örnekler çoğaltılabilir.Bir diğer konu artık JSON’ların hayatımızda çok fazla yer alması.JSON’lardan verileri çıkarmak istediğinizde ona bir text gibi davranıp text fonksiyonları ile örneğin SUBSTRING,LEFT vb kullanıp kesinlikle istediğiniz değeri dışarı almayın.JSON sütunlar tercih edilmesinin sebebi yeni bir sütunun hemen eklenebilmesidir.2 hafta doğru çalışan kodunuz bir günde çalışamaz olabilir çünkü siz statik bir kod yazmış oldunuz.JSON fonksiyonlar kullanarak key value ilişkisinden scalar değeri döndürün muhakkak.Ayrıca kesinlikle tüm sütunları çıkarmaya çalışmayın lazım oldukça alırsınız çoğunu hiç bir zaman kullanmayacaksanız.JSON için yazdığım bir yazım vardı blog ve linkedin profilimden görebilirsiniz.

Yine bir örnek daha verelim, telefon veya tc no sütunu free text girilmiş.Bu sütunlar belirli bir pattern’i takip ettiği için düzenlememiz hala bir nebze kolay.Öncelikle tüm verilerimizi aynı formatta vermemiz çok önemli o yüzden şu şekil bir telefon formatı vermemlisiniz son kullanıcıya.

+905555555555

05555555555

5555555555

0555 555 55 55

Çünkü son kullanıcı böyle bir bilgi alacaksa muhakkak sonrasında bir işlem yapacaktır ve bu durumda onun otomatize etmeye çalıştığı yapıya zarar verecektir.Bu yüzden iş birimleri ile el sıkıştığınız bir formata karar verip tüm gelen datalar için bu formata uyacak şekilde düzenlemeniz gerekir.Elbette söylemeye gerek yok kaynakta düzenletebilirseniz ne mutlu 😊

 

Açıkçası ne kadar örnek versek az kalacak veri temizliği özelinde, o yüzden veriye bunun kaynakda mı düzenlemesi lazım, benim tarafımda mı diye baktıktan sonra bu işlemleri yapmanız en doğrusu olacaktır.

 

Umarım faydalı olmuştur,sonraki yazılarda görüşmek üzere😊

 

 

 

 

Azure Synapse ve SQL Pools

 

Azure Synapse ve SQL Pools

Azure Synapse Analytics,Microsoftun big data ve klasik DWH yapısını bir araya getirdiği bir servisi.Bunun içerisinde Sparkda var,SQL’de var,ADF’de var.Bir lake üzerine kurulu bir yapı olduğundan diğer iş birimlerininde bağlanıp datalar üzerinde işlem yapması veya machine learning üzerine bir servisin buradaki datalara erişimi son derece kolay.

 

 

 

Ölçeklenebilir olması bu kadar farklı servisin ayakta olması ve senkronize bir şekilde bir biri ile çalışmasıyla sizin uğraşmamanız bu servisi seçmeniz için yeterli gerçekten.

Çok büyük datalarda Spark dönüşümleri ve sourcedan data aktarımları için ADF gerçekten iyi.DB tarafında ise nodelar üzerinde bir mimari ve columnstore bir DB olması performansı çok arttırıyor.Elbette bunun bir dezavantajı da var klasik columnstore DB’lerde olduğu gibi update-delete komutları uzun sürebiliyor ama ben kendim çok ciddi sorunlar yaşamadığımı söylemeliyim.Ayrıca tablo seçimleri ve data skew konuları komplexleri arttıran konular klasik DWH’lara göre.Artık detaya inebiliriz sanırım.

 

Azure Synapse Analytics içerisinde iki tip SQL pool seçeneği bulunmaktadır.Biz her ne kadar Dedicated üzerine konuşacak olsakda serverlessda yerine göre kullanılabiliyor.

 

Aşağıdaki resim üzerine konuşacağız ama ilk bilmeniz gereken Dedicated SQL pool 7/24 sizin için ayrılmış başkasının kullanamadığı bir kaynak.Yani sanki clouddda bir makine satın almışsınız gibi düşünebilirsiniz.

Serverless ise siz ne zaman sorgu gönderirseniz o zaman ihtiyacınız kadar olan kaynağı genel havuzdan çekip sizin işinizi bitirip sonra başkasının işlerini yapmaya devam eden kaynaklar olarak düşünebilirsiniz.

Resmi incelemeye başlayalım çünkü bize genel mimariyi anlatacak aslında.Gördüğümüz ilk şey her iki mimaride de Node’lar üzerinde bir yapı kurulduğu.Yani biz sorguyu gönderdiğimizde Control Node uygun Compute Node’ları seçip işi dağıtıyor.

Serverless üzerinde konuşup hemen asıl konumuza dönelim şimdi.Control Node sizin sorgu isteğinizi aldıktan sonra sorgunuzun kullanacağı kaynak miktarını ölçüp ona göre node sayısını ayarlar ve sorgunuzu döndürür.Bunu genelde dedicated SQL pool seçimi yapmamış datayı Lake veya Storage üzerinde file formatta tutan firmalar kullanabiliyor.File’ı sanki tabloymuş gibi from kısmına koyup sonra SQL yazdığınızı düşünün😊Bunun içinde OPENROWSET fonksiyonu ile ve değişmekle beraber genelde parquet formatındaki dosyalar üzerinde işlem yaparlar.

Bunun dezavantajı şudur çok büyük kaynak isteyen sorgu gönderirseniz çok büyük bir maliyet ile karşılaşabilirsiniz.Burada kullandıkça öde mantığı olduğundan mümkün olduğunca az sorgu göndermek istersiniz.Hatta power bi veya başka bir bi toolu bağlanacaksa import mode yapmalısınızki her seferinde gelip sorguyu çalıştırmasın maliyet çıkarmasın.

Şunu unutmayın Serverless SQL Pool her Synapse içinde hazır kurulu gelir,sizin kurulum yapmanıza gerek yoktur.Ayrıca kullanmadığınız sürece hiç bir maliyet çıkarmaz açıp kapamaya çalışmayın diğer servisler gibi.

Ayrıca serverless üzerinde hiç bir data saklayamacağınızı unutmayın sadece metada saklayabilirsiniz.Yani tablo veya materilazed view yaratamazsınız ama external table veya view yaratabilirsiniz çünkü bunlar datayı lakede tutmaya devam ederken metadayı serverless üzerinde tutabilir.

Serverless üzerine çok bile konuştuk bence artık Dedicated SQL Pool zamanı😊

 

 

Dedicated SQL Pool kurulumu yapacağınız zaman size bunun kaç node olması gerektiği sorulacaktır.Bunu seçmeden önce şunu bilmemiz lazım.MPP mimarisinde Control Node ve Compute Node’lar var.Control Node tek bir tane ve bu sorguları uygun Compute Node’lara dağıtıp döndürmekle ilgileniyor kendi üzerinde hiç bir işlem gerçekleşmiyor.Compute Node ise işin olduğu Node’lar aslında sorgularınız bunlar üzerinde hesaplanıp sonuç dönüyor.Haliyle ne kadar çok Compute Node olursa o kadar fazla paralel çalışıp yük dağıtılabilir oluyor.Buda performansı arttıryor.Fakat fazla Compute Node için daha yüksek servis seviyesi seçiyorsunuz bu da maliyet demek oluyor.

DW100C ile DW30000C arasında servis seviyesi seçebiliyorsunuz bu seçimler arttıkça Node sayısı ve Ram artıyor.Burada bilmemiz gereken bir kavram daha ortaya çıkıyor o da Distribution.

Synapse siz hangi servis seviyesinde çalışırsanız çalışın dataları 60 farklı distribution üzerinde tutuyor aynı zamanda sorgularıda 60 farklı distribution üzerinde çalıştırıyor.60 farklı parallelin sonucu birleştirilip döndürülüyor.Haliyle sorgu süresi en uzun distribution’ın çalışma süresi+birleştirme süresi kadar oluyor.Bu da data skew konusu buna değineceğiz.

Servis leveliniz arttıkça Compute Node 1’den 2’e,3’e max 60’a kadar yükselecektir.Örneğin 2 compute node için her bir node üzerinde 30 distribution olacaktır.Çünkü toplamda her zaman 60 distrbution var.60 Node seçtiğinizde ise her bir node’a 1 distribution düşecek ve hızı tahmin edebilirsiniz ve maliyeti de elbette 😊

Daha iyi anlamak isterseniz Node’ları fiziksel bilgisayarlar distrbutionları mantıksal bölmeler gibi düşünebilirsiniz.Dolayısıyla örneğin siz DWH100C de 1 bilgisayarda 60 işlemi aynı anda yapıp birleştirip döndürmeye çalışırken DWH30000C’de 1 bilgisayar 1 işlem yapar.Ayrıca dönüşüm işlemleri ciddi RAM harcadığından aynı anda çok fazla sorgu talebi gelirse hak verirsiniz sıraya almak zorunda kalacaktır.Örneğin DW100C için u sayı 4 sorgu ve en az %25 RAM boşta olmalı iken DW1000C için 32 sorgu %3 RAM seviyelerine düşebiliyor.

DWH200C’den DWH300C’ye geçişde bile performansımız ciddi artmıştı.Maliyet şirketinizin gücüne göre değişecektir elbette.Hesaplama aracından hesaplayabilrisiniz.

Burada şunu söylemekte lazım.Bazı firmalar SQL Pool kullanılmayan saatlerde bunu kapatıp açan mekanizmalar kuruyor çünkü Sql pool full performans kullanının veya kullanmayın aynı parayı kesiyor.

 

 

 

 Konuyu çok uzattım sanırım hadi SQL Pool tablo yapılarına girelim😊

Node ve Distribution’dan bahsettik.Buna göre tablolarımızın tiplerini seçmemiz gerekiyor.

·       Round Robin

·       Hash

·       Replicated

Round Robin

Genelde ODS tabloları için kullanılan bir tiptir.Çünkü özelliği olabildiğince hızlı bir şekilde datanın tabloya yazılmasıdır.Mümkün olabildiğince eşit bir şekilde tüm distributionlara datayı dağıtır.Ama datalar hızlı yüklenirken rastgele dağıldığından join işlemleri veya select işlemleri bu tablolar üzerinde çok daha yavaş olur bunlar asla nihai tablo gibi kullanılmamalıdır.ODS ve staging için sadece unutmayın.

Replicated

Tüm distributionlarda 1-1 aynı datayı yaratır tabloyu bölmez.Bu yüzden başka distributiondan veri getirmekle vakit kaybetmez kendi üzerindeki ile işini görür o yüzden çok hızlıdır.Ama aynı data 60 kere yazıldığından sadece küçük tablolar için kullanılmaldıır.Bunlar max 2-3 milyonluk dimension tablolar olabilir 100milyonluk dimensionlar için bu methodu kullanmayın!.

 

 

 

 

Hash

Büyük tablolar için en iyi saklama methoduur.Fact ve büyük dimensionlar bu tipte bir tabloda saklanır her zaman.Çünkü select ve join işlemleri çok hızlıdır.Her bir satır bir distributionda saklanır bu methodda ama burada şöyle bir soru ortaya çıkıyor hangisini nerede olacak nasıl biliyor?Bir Hash  fonksyion aracılığı ile bunu yapıyor.Örneğin siz FirmaID verdiniz buna göre hashle dediniz FirmaId 1 hep 1 nolu distrubitona gidiyor FirmaID 55 40 nolu distrbıutiona gidiyor.Fakat burada şöyle bir sorun var sizin datanızın %75’i firmaId 1 diyelim.Bu durumda %75’i 1 nolu distirbutiona gidiyor ve diğer distributionlar boş yatıyor.İşte buda data skew dediğimiz kavramı ortaya çıkarıyor.Yani Distrbutionlar arası data farkı.Bu tip durumlarda ekstra sütunlar vererek ya da daha normal dağılım gösteren tek bir sütun seçerek hash değerine ulaşmanız lazım.Açıkçası burada biraz deneme yanılma yapacaksınız en iyi performansı bulmak için.Şuda var ki mümkün olduğunca joinlerde kullacağınız veya aggregationda group by da gelecek sütunlar üzerinden hash değeri oluşturmak isteyeceksiniz ki performans artılarınız olsun.Genelde primarykey gibi sütunları seçmemeye dikkat edin hepsi unique olması değil tablonun ilgili sütun bazında 60’a en yakın eşit dağılımını istiyoruz çünkü.

Data skew değerine ulaşmak için var olan hazır sorgular inceleyebilirsiniz buraya yazıp yer kaplatmayayım.Aynı zamanda bir sorgunun hangi distributionda ne kadar veri işlediğinide görebilirsiniz.

 

Synapse’de işlem yaparken en önemli şeylerden biri data_skew olayı buna çok özen gösterin ve ne yaparsanız yapın DWH100C ile çokda efektif bir DWH yaratamazsınız biraz daha yüksek bir servis seçebilirsiniz

 

 

 

 

 

SCD(Slowly Changing Dimension) Nedir?Nasıl Uygulanır?

 

SCD(Slowly Changing Dimension) Nedir?Nasıl Uygulanır?

Bu tablo tipi hemen hemen tüm DWH yapılarında yer alır çünkü bizim DWH yapma sebeplerimizden biri de geçmişi saklamaktır.Daha doğrusu geçmişi geçmişin verileri ile yorumlamaktır.

 

SCD’ye neden ihtiyaç peki?Diyelim ki sizin bir ürün tablonuz var ve ürünlerinizin birim fiyatları zaman zaman değişebiliyor ve diyelim ki siz tutarı bulmak için satışlar tablosundaki ürün adedi ile birim fiyatını çarparak toplam tutarı bulmak istiyorsunuz.Bu durumda ürün tablosu her zaman son değeri tuttuğundan sanki 5 yıl öncede aynı fiyata satmışsınız gibi görürsünüz.Halbuki her yıl-ay farklı fiyatlandırma söz konusudur.İşte SCD ile bu karışıklığı çözebiliyoruz.

Sadece buda değil elbette iyice oturması için örnekleri arttırabiliriz diyelim ki sizin çalışanlarınız departman değiştiriyor bu durumda bunu tutmak isteyebilirsiniz veya servis güzergahları bir kişi değiştiriyor ise eskisini tutmak isteyebilirsiniz vb.

Burada dikkat ederseniz hep koca bir tablodaki tek bir sütundan bahsediyorum çünkü bizim için kritik bilgi o sütun-lar.Ürün tablosunda bizim için birim fiyat kritikti ama çalışan tablosundan departman oldu.Elbette tek sütun değil sizin için birden çok sütun kritik olabilir bu önemli değildir sadece kritik sütunları belirlemek burada önemlidir.Kritik sütundan kastım elbette geçmişini saklamak istediğiniz sütunlardır.

Farkındaysanız bu örnek verdiğimiz 3 tabloda aslında bir dimension tablo yani ölçüm değil ölçümün içeriği hakkında veri tutan tablolar.Fakat zamanla yavaş yavaş içerisindeki veri değişiyor.Aslında Slowly Changing dediğimiz kavramın bir karşılığı oluyor ve aynı zaman bir dimension tablo.Peki her dimension tablo değişiyor aslında neyin SCD olacağına nasıl karar veriyoruz diye sorarsak cevap geçmişi sizin için değerli olan tablolar olacaktır.

Peki bu geçmişi nasıl tutacağız ve sonrasında buna nasıl erişeceğiz?

Öncelikle SCD’nin bir çok tipi var şuanlık 6 😊

Ama bizim için en önemlisi her zaman Type 2 olacaktır bunu unutmayın.Type 2 hemen hemen her zaman işinizi görecektir.Açıkçası diğerlerini hiç yapma ihtiyacı hissetmedim ben ama sizin öyle bir caseniz olurki type 6 mantıklı olur o zaman o şekilde yapabilirsiniz.

 

Type 2 da dimension tablolarınıza 3 sütun eklersiniz.

·        Start Date

·        End Date

·        Is Current

Start Date:Bir önceki kaydın bitiş tarihi +1(etl çalıştığı gün)

End Date :Sonraki kaydın başlangıç tarihi-1(Etl çalıştığı gün-1)

Is Current:Her zaman son kayıtta 1 önceki kayıtlarda 0

Burada önemli nokta şudur her ürünün bir önceki veya sonraki kaydı olmak zorunda değil özellikle ilk yaptığınız anda hepsinin aslında tek bir kaydı vardır tabloda.Bu durumda Start Date 1900-01-01,End Date 2999-12-31 şeklinde girilir genelde!

Ve her zaman son eklenen kaydın bitiş tarihi 2999-12-31 olarak girilir çünkü zaten yeni bir kayıt geldiğinde buradaki değeri ezecek ve hangi gün geldiyse o günün değeri basılacaktır endişe etmeyin.

 

Hemen örnek bir tablo yaratalım diyelim Ürün tablosu olsun.

SK	ProductId	ProductName	ListPrice	StartDate	EndDate	IsCurrent
1	1	A Ürünü	10	1.01.1900	31.12.2999	1
2	2	B Ürünü	10	1.01.1900	14.08.2024	0
3	2	B Ürünü	20	15.08.2024	31.12.2999	1
4	3	C Ürünü	30	1.01.1900	31.12.2999	1
5	4	D Ürünü	50	1.01.1900	31.12.2999	1
6	5	E Ürünü	60	1.01.1900	31.12.2999	1
7	6	F Ürünü	70	1.01.1900	14.08.2024	0
8	6	F Ürünü	60	15.08.2024	20.08.2024	0
9	6	F Ürünü	80	21.08.2024	31.12.2999	1
10	7	G Ürünü	20	1.01.1900	31.12.2999	1

 

Bu tabloyu inceleyelim şimdi bakın A Ürünü tek bir kayıt var fiyatı hiç değişmemiş bu yüzden başlangıç bitiş tarihleri maximum değerde ve iscurrent 1.

Fakat B Ürününe baktığımızda 14.08.2024 tarihinde bir değişiklik olmuş fiyat 10 ken 20 olmuş.İşte biz bu tarihten önceki satışlar için 10 lira ile sonrası için 20 lira ile çarpacağız tutarı bulmak için.

Veya F Ürünü 3 kez değişmiş bu n kez değişebilir hiç önemli değildir.

Tüm ETL araçlarında SCD type 2 için hazır yöntemler vardır fakat yok veya kendiniz yazmak istersenizde row by row bir loop ile değil bulk olarak bunu yapmaya dikkat edin.

PL/SQL,T-SQL ile de yapabilirsiniz bu süreci bilginiz olsun.

Peki dikkatinizi çeken bu SK sütunu nedir?

SK(Surrogate Key)’in kısaltmasıdır.Dikkat ederseniz ProductId çokluyor çünkü biz ProductID bazında geçmişi saklıyoruz.Bu durumda bize unique bir key lazım işte bu SK oluyor.Özellikle DWH ortamlarında çok sık kullanılan bir kavramdır.SCD tablolarında olmazsa olmazdır!!!

SK üretmek için auto increment sütun tanımlayabilirsiniz veya sequence’den değer getirtebilirsiniz.

Unutmadan söylemiş olalım SCD tabloları asla ama asla truncate edilemez!!

SCD tablomuzu type 2 formatta tutmaya başladık diyelim bitti mi?

SCD anlamında evet ama fact anlamında hayır.

Raporlama katmanında her tarihi o zamanki değer ile eşleştirmek için iki yönteminiz vardır.

Ya fact tablonuzda ProductID ve raporlama tarihi vardır bunları aşağıdaki joine benzer bir şekilde joinlersiniz BI Toolıunuzda.

Select * from Fact

inner join scd on fact.product_id=scd.product_id and fact.report_Date between scd.start_date and scd.end_date

Ya da direkt fact üzerine SCD’nin SK değerini basarsınız.SK unique olduğu için bir daha joinlerde bu kadar uğraşmazsanız hatta Power BI’da mecbursunuz mesela çünkü ikili joinlere izin vermiyor.

Peki fact üzerine SK değerini nasıl basacaksınız?

Aslında yukardaki joinin bir benzeri fact’inizi dolduran ETL mappinginizde yapacaksınız ve o join sonucu dönen SK değerini fact’e basacaksınız.

 

Baştan uca bir SCD işlemi böyle oluyor fakat bazen Snapshot fact tablo gibi düşünülebiliyor.Buradan raporlama yapamazsınız bunu unutmayın.Örneğin x tarihinde kaç çalışanım vardı veya ürünüm vardı bunlar SCD’nin görevi değil.Bunlar için verilerin x tarihindeki durumlarını bulamıyorsanız snapshot fact tutmalısınız bu da değişen değil tüm datayı tuttuğundan çok tercih edilmez zorunda değilseniz.

 

Geçmişe dönük SCD doldurmak işkence gibidir iş birimini ikna edebiliyorsanız bugünden başlatın.Zaten elinizde log yoksa hiçbir şey yapamazsınız ama varsa da çok zorlayabilir.

 

Umarım faydalı olmuştur,bir sonraki yazımda görüşmek üzere 😊

 

 

 

SQL Serverda JSON veri ile çalışma

 

SQL Serverda JSON veri ile çalışma

Günümüzde artık çoğu data json formatta tutulmaya başlanıyor.Çünkü developer için datayı bu şekilde göndermek çok kolay çünkü yeni bir alan eklemek ile ilgili hiçbir endişe yok ve datayı da saklıyor win-win durum söz konusu.Fakat bu analiz yapmak isteyenler için zorlayıcı bir hale gelebiliyor.

 

İşte aslında burada bu JSON fonksiyonlarını ve bunlardan istediğimiz verileri nasıl alabileceğimizi göreceğiz.Örnek olarak bir tablo oluşturdum ve uzunca bir örnek JSON data oluşturttum.Bir çok site var sizde benzer jsonlar ürettirebilirsiniz JSON Generator yazarak.Ben https://json-generator.com/ sitesini kullandım.

 

create table json_test

(

id int,

json_data nvarchar(max),

notjson_data nvarchar(max)

)

 

insert into dbo.json_test values (1,'

[

  {

    "_id": "66bb99724449934db2fa9cc5",

    "index": 0,

    "guid": "438a3a2f-114e-4afa-ab78-f9ba31dd25c9",

    "isActive": false,

    "balance": "$2,026.56",

    "picture": "http://placehold.it/32x32",

    "age": 35,

    "eyeColor": "brown",

    "name": "Miranda Horton",

    "gender": "male",

    "company": "MACRONAUT",

    "email": "mirandahorton@macronaut.com",

    "phone": "+1 (908) 500-3849",

    "address": "298 Cameron Court, Chestnut, South Carolina, 2417",

    "registered": "2023-08-01T07:10:58 -03:00",

    "latitude": -15.860973,

    "longitude": -160.799826,

    "tags": [

      "quis",

      "reprehenderit",

      "dolore",

      "mollit",

      "labore",

      "elit",

      "eu"

    ],

    "friends": [

      {

        "id": 0,

        "name": "Chris Morrison"

      },

      {

        "id": 1,

        "name": "Stein Manning"

      },

      {

        "id": 2,

        "name": "Summers Norton"

      }

    ],

    "greeting": "Hello, Miranda Horton! You have 9 unread messages.",

    "favoriteFruit": "apple"

  },

  {

    "_id": "66bb99720797a80b7dc0a591",

    "index": 1,

    "guid": "31e754af-b1b9-4906-a51c-e1f40b345850",

    "isActive": false,

    "balance": "$2,582.52",

    "picture": "http://placehold.it/32x32",

    "age": 20,

    "eyeColor": "blue",

    "name": "Baldwin Blackwell",

    "gender": "male",

    "company": "LIQUIDOC",

    "email": "baldwinblackwell@liquidoc.com",

    "phone": "+1 (888) 489-3186",

    "address": "210 Beayer Place, Finderne, Virginia, 6791",

    "registered": "2017-02-14T04:35:37 -03:00",

    "latitude": -20.942119,

    "longitude": 113.878654,

    "tags": [

      "eu",

      "laborum",

      "id",

      "et",

      "duis",

      "ut",

      "et"

    ],

    "friends": [

      {

        "id": 0,

        "name": "Brianna May"

      },

      {

        "id": 1,

        "name": "Daniel Pruitt"

      },

      {

        "id": 2,

        "name": "Clara Finch"

      }

    ],

    "greeting": "Hello, Baldwin Blackwell! You have 2 unread messages.",

    "favoriteFruit": "banana"

  }

]',

'test data'

)

 

Verimi tabloya ekledikden sonra başlayabilirim.SQL Server’ın JSON tipi için özel olarak ürettiği 7 fonksiyon var şu anda bunların üzerinden tek tek geçeceğiz.

1.     ISJSON

2.     JSON_ARRAY

3.     JSON_MODIFY

4.     JSON_OBJECT

5.     JSON_PATH_EXISTS

6.     JSON_QUERY

7.     JSON_VALUE

 

ISJSON

Bu fonksiyon çok kolay bir fonksiyon verdiğiniz sütundaki veriler Json tipinde mi değil mi size bunu dönüyor.Bunun içinde JSON ise 1 değil ise 0 dönüyor.

JSON_ARRAY

JSON oluşturmak için kullandığımız bir fonksiyondur.Biz parçalamak istiyoruz ama karşınıza böyle bir case çıkabilir bilmekte fayda var.

İster bir tablonun sütunlarını alıp yapabilirsiniz isterseniz kendi değerlerinizi verebilirsiniz.

Mesela bir tablonun belirli sütunları ile oluşturmak isterseniz bu şekilde yapabilirsiniz.

SELECT JSON_ARRAY(BusinessEntityID,PersonType,FirstName,LastName)

  FROM [AdventureWorks2022].[Person].[Person]

 

Böyle bir çıktı elde edersiniz.Sütun sayısı size kalmıştır.

İsterseniz elbette tüm satırlara default değerde ekleyebilrisiniz.

SELECT JSON_ARRAY(BusinessEntityID,PersonType,FirstName,LastName,'Tüm satırlara Hüseyin Ekle')

  FROM [AdventureWorks2022].[Person].[Person]

Bu dönen datanın nvarchar(max) tipinde olduğunu unutmayın.Yalnız bizim döndürdüğümüz bu değerler bir stringdir aslında iç içe JSON yani Json içinde bir JSON objesi döndürmek isterseniz ise bir sonraki fonksiyonumuz olan JSON_OBJECT’i kullanmanız gerekir.

SELECT JSON_ARRAY(BusinessEntityID,PersonType,JSON_OBJECT('ad_sutunu':FirstName,'soyad_sutunu':LastName))

  FROM [AdventureWorks2022].[Person].[Person]

 

Dikkat ederseniz bir iç içe JSON söz konusu ve içteki jsonın key’ini ben belirledim ama value tablodan geldi!

 

 

JSON_MODIFY

Var olan bir JSON2ı güncellemenize imkan verir bunu yine ister default bir değer ile isterseniz de tablodaki bir sütun ile yapabilirsiniz kolaylıkla.

3 tane parametre alır:

Sütun,path(yani değiştirilecek değer),yeni değer

Bizim örneğimiz üzerinden gidersek bizim örneğimizde2 tane farklı element barındıran bir JSON’dı.Örneğin ilk dizideki index değerine bakarsanız bunu 0 görürsünüz ben bunu 15 yapmak istiyorum bu durumda işte bu fonksiyonu kullanabiliriz.

select JSON_DATA,JSON_MODIFY(JSON_DATA,'$[0].index',15) from dbo.json_test

 

Dikkat ederseniz index artık 15 geliyor.

Veya

select JSON_DATA,JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin') from dbo.json_test

Yukardaki sorgu ile ilk dizideki friends dizisindeki 2. Arkadaşın ismini hüseyin yapmak istediğimizde yukarıdaki kodu yazabiliriz.

Burada dikkatini çeken [0] ve [1] kullanımları olmuştur.JSON’da diziler 0’dan başlar yani 0 aslında 1.ci elementi ifade 1 ise 2.elementi ifade eder.

Ve friends bir dizi idi bu dizinin ikinci elementine gitmek için friends[1] kullanımı yapmanız gerekir.

Burada genelde biz JSON değiştirdik mi eskisi ile update etmek isteriz bunun içinde şöyle bir kod ile rahatlıkla yapabilirsiniz.

 

update dbo.json_test set JSON_DATA=JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin') 

tabi Where koymayı unutmayın siz 😊

Birden çok alanı tek seferde güncellemek isterseniz de iç içe JSON_MODIFY kullanabilirsiniz.Örneğin:

select JSON_DATA,JSON_MODIFY(JSON_MODIFY(JSON_DATA,'$[0].friends[1].name','hüseyin'),'$[0].index',25) from dbo.json_test

JSON_OBJECT

Bir JSON objesi döndürür.JSON_ARRAY ile arasındaki fark biri dizi diğeri objedir.Dizilerde key value ilişkisi olmaz indis sırası olur.Fakat JSON objelerinde bir key value ilişkisi vardır.Aslında Pythondaki list,dictionary ile aynı gibi düşünebilirsiniz.

Hatta dikkat ederseniz JSON_ARRAY [ ] içinde veri döndürürken JSON_OBJECT { } içerisinde veri döndürür.

Dolayısıyla bir JSON_OBJECT içerisine n  tane veri gönderebilirsiniz ama key vermek zorundasınız.

 

select JSON_OBJECT('name':'Hüseyin','surname':'Albayrak')

{"name":"Hüseyin","surname":"Albayrak"}

Gördüğünüz gibi istersek bir tablodan da value’leri getirebiliriz.Keyleri siz vereceksiniz değişemez.

SELECT JSON_OBJECT('urun_Adi':Name,'liste_fiyat':ListPrice)

  FROM [AdventureWorks2022].[Production].[Product]

 

JSON_PATH_EXISTS

Verilen path JSON içerisinde var mı yok mu onu döner.Var ise 1 değil ise 0 döner.Önemsiz gibi gözükse de çok önemli bir fonksiyondur özellikle bir loop vb yapıda bu fonksiyonuz hataları çok ciddi engelleyecektir.

 

select

JSON_PATH_EXISTS(JSON_DATA,'$[0]'), --1 döndü çünkü ilk element vasr gerçektende

JSON_PATH_EXISTS(JSON_DATA,'$[156]'), --0 döndü çünkü 158 elementi yok.

JSON_PATH_EXISTS(JSON_DATA,'$[0].age'),--1 döndü age sütunu var 

JSON_PATH_EXISTS(JSON_DATA,'$[0].age[0]'),--0 döndü çünkü age bir dizi değil 0.cı indisi yok

JSON_PATH_EXISTS(JSON_DATA,'$[0].friends[2].name')--1 döndü çünkü friends dizisinin 3 elementi var bu elementler json formatında yani name alanınına gidilebilir.

from dbo.json_test

 

 

JSON_QUERY

Bir JSON içerisinden bir obje veya dizi döndürmemizi sağlar.Burada önemli nokta son değeri bu fonksiyon ile döndüremezsiniz sadece obje veya dizi döndürürsünüz bunu unutmayın!

select

JSON_QUERY(JSON_DATA,'$[0]'), 

JSON_QUERY(JSON_DATA,'$[0].age'),

JSON_QUERY(JSON_DATA,'$[0].friends[2]') ,

JSON_QUERY(JSON_DATA,'$[0].friends')

from dbo.json_test

 

Bakın 2.sütun değer döndürüyor dizi veya obje gelmediği için NULL geliyor fakat diğerleri hepsi dolu geldi.

JSON_VALUE

Ve aslında hepimizin beklediği fonksiyon belkide çünkü artık JSON’dan klasik değer döndüğünü göreceğiz.

Bakın yukarıdaki aynı sorguyu sadece JSON_VALUE ile yazdım ve sonuç tam tersi

select

JSON_VALUE(JSON_DATA,'$[0]'), 

JSON_VALUE(JSON_DATA,'$[0].age'),

JSON_VALUE(JSON_DATA,'$[0].friends[2]') ,

JSON_VALUE(JSON_DATA,'$[0].friends')

from dbo.json_test

 

 

Çünkü bu fonksiyon sadece scalar dediğimiz klasik data tiğpindekileri döner.

select

JSON_VALUE(JSON_DATA,'$[0].index'), 

JSON_VALUE(JSON_DATA,'$[0].age'),

JSON_VALUE(JSON_DATA,'$[0].friends[2].name') ,

JSON_VALUE(JSON_DATA,'$[0].tags[0]')

from dbo.json_test

 

Hepsini scalar değer verdiğimizde döndürdüğünü görüyoruz.

 

JSON_ARRAYAGG

JSON_OBJECTAGG

Azure SQL’de kullanılanılabilir sadece

OPENJSON genelde Azure üzrinde kullanılır on-premde görmedim hiç.

 

Umarım faydalı olmuştur 😊