İş zekası araçlarındaki “Lookup” komponentinin ne işe yaradığını az çok hepimiz biliyoruzdur. SQL’deki join işlemine yakın bir işlem gerçekleştirir. Eğer lookup hakkında pek bilgi sahibi değilseniz, bu konuda araştırma yapmanızı tavsiye ederim. Diğerlerinde olduğu gibi SSIS’te de Lookup komponenti aslında bir önceki yazımızda da bahsettiğimiz gibi Merge Join’in yaptığı işe benzer bir işlem yapar. Arada belirgin farklar var tabii ki. Bu farklara ve lookup komponentinin genel özelliklerine bu yazımda değinmeyeceğim. Bu yazı genel bir lookup rehberi olmaktan çok, özel bir lookup çeşidi olan “Range Lookup” üzerinde yoğunlaşacak.
Range Lookup derken neyi kastettiğimizi anlatalım. Bildiğiniz üzere veri ambarlarında “Fact” tabloları, “Dimension(Dim)” tablolarından gelen birçok “Surrogate Key” alanını bünyesinde bulundurur. Bunlar aynı FK(Foreign Key) kolonlar gibi simgesel değerlerdir ve karşılık gelen dimension tablolarındaki kendi satırlarını ifade ederler. Bunlarla beraber ilgili “transaction’a(işlem)” karşılık gelen sayısal değerler(miktar, satış fiyatı, kargo fiyatı vs.) de fact tabloları içerisinde bulunur. İş zekası araçlarını kullanırken transaction’lara karşılık gelen “SK(Surrogate Key)” alanlarını Dim tablolarından Fact tablosuna getirebilmek için, Lookup komponentine ihtiyaç duyulur. SSIS üzerinde de bu işlemi gerçekleştiren komponent yine Lookup’tır. Aynı Merge Join komponentinde yaptığımız gibi anahtar alan olarak kullanacağımız alan üzerinden tabloları birbirlerine bağlayıp, görmek istediğimiz kolonu referans(Dim) tablomuzdan input(Fact) tablomuza getiririz.
Şimdi biraz daha farklı bir senaryo düşünelim. Araç satışları ile alakalı işlemleri içerisinde barındıran bir Fact tablosu düşünelim. Tablonun adı da FactCarSales. Bu tabloda araç satışlarının bilgisi tutulmakta ve aşağıdaki gibi bir içeriği var. Bir sorgu çekelim:
Yukarıda bahsettiğimiz gibi, CustomerKey, ProductKey ve SalesTerritoryKey kolonları kendi Dim tablolarından gelen birer Surrogate Key alanı. Diğer kolonlar ise sırasıyla satış fiyatı, adet, araba markası ve markanın hangi arabası olduğu. Son kolon ise aracın kilometre bilgisi. Fakat ben bu kilometre bilgisinin de kendine özgü bir Dimension tablosu olmasını, bu tabloda bu şekilde kilometre bilgisini tutmaktansa bu kilometre değerine karşılık gelen ve DimKilometer tablosundan gelecek olan Surrogate Key değerinin tutulmasını istiyorum. Bu şekilde anlatmam biraz kafa karıştırmış olabilir. Bu senaryoyu bir örnek üzerinde açıklayalım.
Yukarıda bahsettiğimiz DimKilometer tablosu şu şekilde:
………
DimKilometer tablosunu tanımak için de FactCarSales tablomuzdan bir transaction işlemini referans alalım. Örneğin, 1.satırda satılan Nissan Qashqai’lerin kilometre değeri 34567. Üstteki DimKilometer tablosuna göre bu kilometre değerinin düştüğü en dar alan 30001 – 35000 olduğu için, FactCarSales tablosunda 34567 değerinin yazması yerine, DimKilometer tablosunda bu aralığa karşılık gelen ID değeri olan 7’nin yazmasını istiyoruz. İşte bu işleme “Range Lookup” denmesinin sebebi bu. Tek bir değere karşılık gelen SK’yı Fact tablosuna yazdırmak yerine, o değerin düştüğü aralığa karşılık gelen SK’yı(ID) Fact tablosuna yazdırıyoruz. Bu işlemin uygun olduğu senaryolardan biri de kilometre senaryosu. Tabii ki başka senaryolar da olabilir, fakat bu işlemin ana fikrini ve nasıl yapıldığını bildikten sonra, hangi senaryo altında yapılacağı hiç de problem değil. Eğer bu senaryoda Range Lookup değil de normal Lookup işlemi yapmak isteseydik, ona uygun bir DimKilometer tablosu olmalıydı. Bu da şu anlama geliyor ki araçlarımızın kilometreleri 0 ile 100000 arasında değişiyorsa, bu aralıktaki her kilometre değeri için DimKilometer tablosunda bir satır olmalıydı ve bu da 100000 tane SK olması anlamına gelirdi. Bunu bu şekilde yapmak yerine, kilometreleri gruplara bölüp, kilometrenin düştüğü ilgili aralığı Range Lookup işlemiyle Fact tablosuna getirmek daha uygun olacaktır.
Şimdi yapacağımızın işin ne demek olduğunu ve ne işe yaradığını anladığımıza göre, nasıl yapılacağı kısmına geçelim. SSIS üzerinde Range Lookup gerçekleştirebilmek için, input tablomuz olan FactCarSales tablosunu Data flow’umuza getirecek olan bir OLE DB Source komponenti, DimKilometer tablosunu referans alarak Lookup işlemini gerçekleştirecek olan bir Lookup komponenti ve tabii ki işlem sonundaki tabloyu bir hedefe yazdıracak olan bir OLE DB Destination komponenti. Tabii bu komponentlerin hespinden önce bize gereken bir Data Flow Task :)) DFT(Data Flow Task) komponentini ekledim ve ismini de DFT_RangeLookup verdim:
Daha sonra Data Flow Task komponentimin içine giriyorum ve gerekli komponentleri birincisinden başlayarak ekliyorum. İlk komponentim tabiki source olarak görev yapacak olan OLE DB Source.
OLE DB Source komponentinin içini yukarıdaki gibi ayarladık. Üstteki ok ile gösterdiğim RangeLookup adlı veritabanına bağlantımı sağlayacak olan connection manager. Bunun nasıl oluşturulduğunu bir önceki yazımda göstermiştim. Söz konusu yazıma buradan ulaşabilirsiniz. Bu yüzden aynı işlemleri burada tekrar yapmanın bir gereği yok. Connection manager’i seçtikten sonra SQL command kullanarak FactCarSales tablosunu tamamen alıyorum ve OK diyorum. Daha sonra lookup komponentini ekliyorum ve içini gerektiği gibi düzenliyorum.
Komponenti ekledikten sonra içini de gerekli şekilde düzenlememiz gerekiyor. İşte işin en kritik noktası da burası. Komponentin adını LKP(Lookup)_DimKilometer olarak verdim. Şimdi içini nasıl düzenlediğimize bakalım.
Lookup komponentinin ilk sayfası yukarıdaki gibi. Cache mode kısmında 3 çeşit mod var. Yazının başında burada Lookup komponentini değil de, özel bir lookup çeşidini yazacağımı söylediğim için, bu modlardan çok fazla bahsetmeyeceğim. Fakat bu modlar performans açısından son derece kritik olduğu için, kesinlikle araştırılmasını ve detaylı olarak öğrenilmesini tavsiye ederim. Modların ne olduğundan kısaca söz edelim ve neden ikinci mod olan “Partial cache” modu seçtik onu açıklayalım. Full cache mod seçilirse, data flow başlamadan önce Lookup komponenti tüm referans tablosunu(burada DimKilometer tablosu) RAM(memory)’ye alır. Böylece flow başladığında tüm veri RAM üzerinde olduğundan komponent bir eşleşme aramak için veritabanına gitmek zorunda kalmaz ve işlem çok hızlı gerçekleşir. Fakat bu modun RAM’e bir yük bindireceğini de unutmamak gerekir. Partial cache modda ise, RAM flowun başında boş olarak başlar. Yeni bir satır geldiğinde, Lookup komponenti eşleşen değeri aramak için RAM’e bakar. Eğer RAM’de eşleşmeyi bulamazsa, veritabanını sorgular. Eşleşmeyi veritabanında bulursa, değerleri RAM’e alır(cache) ve yeni bir eşleşen satır geldiğinde veritabanını sorgulamak yerine değeri direkt RAM’de bulur. Son olarak No Cache modda ise adından da anlaşılabileceği gibi Lookup komponenti RAM’de hiçbir satırı tutmaz ve her eşleşme araması için veritabanını sorgulamak zorunda kalır. Haliyle bu da fazlasıyla uzun sürer. Peki biz burada neden Partial cache modu tercih ettik? Bu modu tercih etmemizin sebebi aslında sol tarafta gördüğümüz “Advanced” sayfasını kullanacak olmamız. Bu sayfa Lookup komponentinin ileri düzey ayarlarını kendimize göre değiştirebilmemizi sağlar ve oldukça etkilidir. Biz de “Range Lookup” işlemini gerçekleştirmek için bu sayfayı kullanacağız. Bu sayfanın şöyle bir özelliği var; sadece Partial Cache mod seçili olduğunda kullanılabilir. Bunun nedenini ise o sayfaya geçtiğimizde anlatacağım. Biz de o sayfayı kullanabilmek için Partial Cache modu seçtik. Gelelim komponentin ikincisi sayfasına:
Aslında bu sayfada çok da üzerinde konuşulacak bir şey yok. OLE DB connection manager’imi seçtim ve komponentin içine gelecek olan referans tablosunu da DimKilometer olarak ayarladım. Üçüncü sayfaya geçecek olursak:
Bu nokta aslında çok önemli. Ben burada input tablosundaki Kilometer kolonunu referans tablosundaki ID kolonuna bağladım. Fakat ChildStartPoint kolonuna da bağlasaydım ya da aynı veri tipli herhangi bir kolona da bağlasaydım bir şey değişmeyecekti. Bunun nedeni birazdan da göreceğimiz gibi “Custom Query” kullanacak olmam. Fakat eğer Custom Query kullanmasaydım ve bu normal bir Lookup işlemi olsaydı, bu bağlama işlemi çok kritikti. Çünkü hangi iki kolonu birbirine bağlarsanız, komponent o iki kolon üzerinden arama yapacak ve eşleşmeleri bulmaya çalışacaktır. Input tablomda görmek istediğim kolonları da tikledim ki gelmelerini sağlayayım. Normalde fact tablosuna gelmesi gereken kolon tabii ki sadece ID kolonudur. Fakat biz burada görsel amaçlı hangi gruba dahil olduğu bilgisini de getirdik ki sağlama yapma şansımız olsun. Bunun dışında alt tarafta Output Alias kısmında yazdığım adı da işaretledim. Bu kısım şunu ifade ediyor; ben ID kolonunu referans tablosundan getirmek istiyorum fakat fact tabloma geldiğinde kolon adı KilometerID olsun. Bir sonraki sayfaya geçiyorum.
Geldik bizim için en kritik olan sayfaya. Bu sayfanın sadece Partial Cache modda aktif olduğunu söylemiştik. Neden sadece bu modda aktif ? Çünkü üst menüde de görebileceğiniz gibi Partial caching detaylarını içermektedir. İlk kısımda 32-bit ve 64-bit olarak cache büyüklüğünü bize ayarlama şansı veriyor. Partial cache modu seçtiğimizde eşleşen satırların RAM’e alındığını ve daha sonra yapılacak eşleşme aramalarında kullanıldığını söylemiştik. İşte bu satırların RAM üzerinde ne kadar yer kaplamasını istediğmizi biz burdan Lookup komponentine söyleyebiliyoruz. Bu değer “default” olarak 25 MB. Bir alt kısımda ise tiklenebilir bir kutucuk görüyoruz. Bu kutucuk size eşleşmeyen satırların da RAM’e alınıp alınmaması gerektiğini soruyor. Altındaki yüzde ayarı ise eğer o satırların RAM’e alınmasını isterseniz, RAM’in yüzde kaçını o satırlara ayırmayı düşündüğümüzü soruyor. Bizim için şu an en kritik olan kısım ise en alttaki Custom query kısmı. Burada size yazdığınız SQL sorgusunu modifiye etme ve özelleştirme şansı veriliyor. Bu ne demek ve nasıl kullanılır detaylıca anlatayım. Üst tarafta da işaretlediğim gibi query yazmak için ayrılmış alanın sağ alt köşesinde bir Parameters butonu var. Bu butonun amacı, input tablosundan gelen bir kolonu bir parametreye atayarak, Lookup komponentinin çalışması sırasında SQL sorgusu içerisinde istediğiniz yerde dinamik olarak kullanmanızı sağlıyor. Sorgu içerisinde parametreleri ifade etmek için “?” işaretini kullanıyoruz. Bu işareti gördüğümüz yerde, oraya bir parametre geleceğini ve o parametreye de input tablosundan bir kolonun atandığını anlamamız gerek. Eğer kafa karışıklığı olduysa merak etmeyin, şimdi örnekle beraber netleştireceğim. Önce parameters butonuna tıklayarak input tablosundan gelen hangi kolonu parametreye atadığımıza bakalım.
Parameters butonuna tıkladığımda karşıma açılan pencere bu. Gördüğünüz gibi fact tablomdan gelen Kilometer kolonunu parametreye atamışım. Input Column kısmına bastığınızda size parametreye atayabileceğiniz input tablosu kolonlarını gösteriyor ve siz de oradan seçtiğiniz bir kolonu parametreye atayıp SQL sorgunuzda kullanabiliyorsunuz. Peki input tablonuzdan gelen tüm kolonları parametreye atayabilir misiniz? Cevap hayır. Sadece “Columns” sekmesinde referans tablosuyla bağlantı kurduğunuz kolonları seçebilirsiniz. Ben de sadece Kilometer kolonunu referans tablosuna bağladığım için, olası seçenekler arasında sadece o vardı ve onu seçtim. Şimdi Kilometer kolonunu parametreye atadığıma göre, SQL sorgumda “?” işaretini gördüğüm yerde anlamalıyım ki orada aslında Kilometer yazıyor. Şimdi eğer sorguma dönersem şu sonuç çıkıyor. Lookup komponentinin çalışması esnasında input(fact) tablosundan gelen her satırdaki kilometre değeri için, benim yazdığım Custom Query çalışıyor ve o değer hangi ChildStartPoint ve ChildEndPoint değerleri arasındaysa o değerlerin bulunduğu satırı getiriyor. Mesela benim fact tablomun herhangi bir satırından gelen kilometre değeri 13598. Bu değeri arasına alacak olan ChildStartPoint ve ChildEndPoint ikilisi 10001 ve 15000. Dolayısıyla 13598 değeri bu iki değer arasına düştüğü için sadece bu iki değerin bulunduğu satır gelecek ve ben Lookup komponentinin “Columns” kısmında referans tablomdan hangi kolonların gelmesini istediysem o satırdaki o kolon değerlerini getirecek. Üstte verdiğim DimKilometer tablosuna bakarsanız 13598 değerine karşılık gelmesi gereken ID’nin 3, ChilStartPoint’in 10001 ve ChildEndPoint’in 15000 olması gerektiğini görürsünüz. Biraz sonra sonuçları bir tabloya yazdırmak için OLE DB Destination komponenti ekleyeceğiz ve yazdırdıktan sonra işlemin sağlamasını yapacağız. Komponentimizi ekleyelim:
Aşağıdaki gibi destination komponentini ekledim ve adını da “OLEDB_DEST_RangeLookupTarget” koydum. İşlemin devamına geçmeden önce belirtmeliyim ki komponent isimlendirmeleri önemlidir. İsimlendirmeleri mutlaka okuduğunuzda ne iş yaptığını anlayacağınız bir şekilde dizayn edin. Devam edelim. Sonuçlarımızı yeni bir tabloya yazdırmak istiyorum bu yüzden destination komponentimin içinde yeni bir tablo yaratacağım. Bunun öncesinde Lookup’ı destination komponentime bağlıyorum:
Bağlamak istediğimde bildiğiniz gibi eşleşen değerleri mi yoksa eşleşmeyen değerleri mi o hedef tablosuna bağlamak istediğimi soruyor ve ben eşleşenleri bağlamasını istiyorum. Normal bir zamanda eşleşmeyenler için de başka bir senaryo düşünülebilir fakat şu an için bu yazıda gerek olmadığından eşleşmeyenleri görmezden geliyorum. Komponentin içi de şu şekilde:
Connection Manager’imi seçtikten sonra işaretlediğim “New” butonuna basarak bana verdiği hazır CREATE kodu ile tablomu oluşturdum ve hedef olarak onu seçtim. Şimdi paketi çalıştırıp SQL Server üzerinden sorgu yazarak doğru çalışıp çalışmadığının sağlamasını yapalım.
Paket düzgün bir biçimde çalıştı. Şimdi SQL üzerinden tablomuzu kontrol edelim.
Evet final tablom bu şekilde. Kontrol etmek için bir satırı baz alalım. Örneğin üçüncü satırda satılan BMW x5’lerin kilometresi 45678. Yana doğru gidersek düştüğü dar aralık 45001 – 50000 olarak, geniş aralık ise 40001 – 50000 olarak gelmiş. Bu değerler doğru. ID değeri ise 10 olarak gelmiş ki üstte verdiğim DimKilometer tablosuna bakacak olursanız bu aralığa karşılık gelen ID değerinin 10 olduğunu, dolayısıyla bu değerin de doğru olduğunu görebilirsiniz. Sonuç olarak paketi doğru çalışmış ve herhangi bir problem yok.
Gördüğünüz gibi Range Lookup işlemi bu tarz senaryoları başarıyla gerçekleştirebilmek için çok kullanışlı bir işlem. Lookup komponenti aslında SSIS içerisindeki en detaylı ve en çok kullanılan komponentlerden bir tanesi. Bu yüzden, hakkında detaylı araştırma yapılması ve iyice öğrenilmesi gerekir. Biz bu yazıda bu komponentin gerçekleştirebildiği özel bir Lookup çeşidi olan “Range Lookup” işlemini ele aldık ve bir örnek senaryo üzerinden açıklamaya çalıştık.