SSIS’te Lookup yapılırken cache mode seçeneklerinin çalışma performansını nasıl etkilediğinden bahsetmiştik. Özellikle Full Cache mod kullanıldığında, data flow başlamadan tüm referans tablosunun RAM’e alındığını ve eşleşme aramalarının RAM üzerinde yapıldığını biliyoruz. Bu da doğal olarak sorgulama yapmak için veritabanına gidilmesi gereğini ortadan kaldırarak Lookup işleminin hızlı bir biçimde tamamlanmasını sağlar. Fakat bu hızın da RAM’e bir yük getirdiğini ve RAM kullanımını artırdığını söylemiştik. Eğer Lookup sayısı fazla ise ve tüm bu Lookup komponentleri Full Cache modda çalıştırılacaksa, bunun RAM üzerindeki etkisini söylemeye şüphesiz gerek yok. Bir de referans tablolarınız büyükse, data flow başlamadan önce tüm bu referans tablolarının RAM’e alınması ve Lookup işlemlerinin RAM üzerinde gerçekleşmesi performansın son derece zorlanmasına neden olacaktır.
Bu problemi aşmak için SSIS bize “Cache Transform” komponentini sunuyor. Bu komponent ne işe yarar ve nasıl kullanılır bu yazıda bunu göstereceğim. Eğer büyük referans tablolarına birden çok yerde Full Cache mod kullanarak Lookup işlemi gerçekleştiriliyorsa, Cache Transform komponenti kullanmak performans açısından çok etkili olacaktır. Düşünün ki 1 milyondan fazla satırı olan bir referans tablosuna birden çok yerde Full Cache modda Lookup yapıyorsunuz ve her Lookup komponenti için bu tablo flow başlamadan önce tamamen RAM’e alınıyor. Bu büyük bir problem. İşte tam burada Cache Transform komponenti devreye giriyor. Cache Transform herhangi bir referans tablosunu alır ve bir dosyaya yazar. Bu dosyaya “Cache Dosyası” denir ve uzantısı “.caw” olarak geçer. Bu dosyadaki veri sürekli RAM üzerinde tutulur. Böylece, bahsettiğimiz dosyaya yazılan referans tablosuna Lookup yapan komponentlerin tamamı gerektiği şekilde ayarlanarak, her seferinde tabloyu RAM’e almak yerine, her zaman RAM üzerinde olan bu dosyaya Lookup gerçekleştirilebilir. Sonuç olarak birden fazla Lookup direk RAM üzerinde bulunan bu dosyaya yönelir ve tüm Lookup’lar eşleşme aramak için aynı veri kaynağına bakmış olur. Bu durumda bir dosya birden çok Lookup komponentini besler. Cache Transform meselesini anlamanın en iyi yolu uygulamasını yapmak. Yeni bir paket oluşturup bu işlemin nasıl gerçekleştirileceğini görelim.
Öncelikle, Lookup yapacağımız tabloyu tanıyalım. Tabloyu hepimizin bildiği “AdventureworksDW2016CTP3” veritabanından yani AdventureWorks veritabanının veri ambarına dönüştürülmüş halinden seçtim. Oradaki DimCustomer tablosunu referans alan iki Lookup komponenti yapacağız ve bu Lookup komponentleri Full Cache modda çalışacak. Biz de Cache Transform komponentini kullanarak bu tabloyu cache dosyasına yazdıracağız ve bu Lookupların cache dosyasını referans almasını sağlayacağız. Böylece RAM üzerinde ayrı ayrı iki tane DimCustomer tablosu bulunması yerine, her zaman RAM’de saklanacak olan bir “caw” dosyası ile iki Lookup komponentine tek hedef atayacağız. DimCustomer tablosu şu şekilde:
Buradaki CustomerKey kolonu bu tablonun Surrogate Key alanı olduğu için, Customer bilgisini içeren Fact tablolarında yer alacağını biliyoruz. Biz ise bu söz konusu Fact tabloları üzerinden DimCustomer’a tekrar Lookup yaparak, Müşteri ad ve soyad bilgilerini getireceğiz. Buradaki amaç, Cache Transform kullanarak cache dosyasına yazdırma işleminin detaylarını ve bunun Lookup ile nasıl ilişkilendirileceğini göstermek. Peki hangi iki tablodan DimCustomer’a gideceğiz? Aşağıdaki FactInternetSales ve FactSurveyResponse tablolarından.
Görüleceği gibi üstteki iki Fact tablosu da CustomerKey alanını barındırıyor. Bizim Lookup esnasında kullanacağımız kolon da CustomerKey kolonu. Yeni bir paket açıp gerekli komponentleri yerleştirmeden önce, birkaç bilgi vermek istiyorum.
Connection Manager mantığını ve nasıl oluşturulacağını önceki yazılarımda anlatmıştım. Veritabanlarına ulaşmak için, ilgili connection manager’ları oluşturmamız gerektiğini biliyorsunuz. Aynı şekilde, Cache Transform komponentinin oluşturacağı “.caw” dosyasına erişebilmek için de bir connection manager’a ihtiyacımız var. Buna “Cache Connection Manager” deniyor. Bahsettiğimiz bu Cache Connection Manager’in Lookup komponentlerinde kullanılabilmesi için ise bir şart var. İlgili Lookup komponenti Full Cache modda çalışıyor olmalı. Full Cache dışında hiçbir mod esnasında Cache Connection Manager kullanılamaz. Bu yüzden Lookup komponentlerini Full Cache modda çalışmak üzere ayarlayacağız.
Şimdi paketi açıp ilk komponentlerimizi ekleyelim.
Paketin adını CacheTransform koydum ve Data Flow Task’ı ekledim. Task’ın ismini DFT_CacheFile olarak düzenledim. İçerisine girip gerekli ayarlamaları yapmadan önce, bu işlemi nasıl gerçekleştireceğimize dair birkaç teknik bilgi vereyim. Öncelikle, referans tablosunun Cache File üzerine yazılma işlemi ile, ona referans yapacak olan Lookup işlemlerinin ayrı data flow’lar ya da ayrı paketler içerisinde olması gerek. Bu sayede Lookup komponenti çalışıp işlem başlamadan önce, referans tablosu Cache File üzerine yazılmış olur ve böylece Lookup komponenti ona referans verebilir. Eğer Cache File üzerine yazılma işlemini ve Lookup komponentini aynı paket içerisinde fakat ayrı Data Flow Task’lar içinde tutacaksak, ilgili Data Flow Task’lar birbirlerine “Precedence Constraint” ler ile bağlanmalıdır. Yani birinin sonucuna göre diğeri çalışmalıdır. Öncelikle Cache File üzerine yazan Data Flow Task çalışmalı, onun başarılı olarak çalışmasından sonra Lookup komponentlerini barındıracak olan Data Flow Task çalışmalıdır. Böylece Cache Transform komponenti Lookup’tan önce çalışacaktır. Biz de işlemimizi bu şekilde gerçekleştireceğiz.
DimCustomer tablomu getirecek olan OLE DB Source Komponentimi ekledim ve içini aşağıdaki gibi düzenledim:
Dikkat ederseniz AdventureWorks DW üzerine erişebilmek için Connection Manager’imi oluşturdum ve yine data flow performansını gereksiz yere yormamak adına sadece ihtiyacım olan kolonları SQL sorgumda kullandım. Bu aşamadan sonra Cache Transform komponentini kullanarak Cache File üzerine yazdırma işlemine geçiyoruz.
Cache Transform komponentini ekledik. içini aşağıdaki gibi düzenleyelim:
Komponentin içerisine girdiğimizde karşımıza çıkan ilk sayfa bu. Bu noktada bize dosyaya erişebilmek için bir Cache Connection Manager soruyor. Biz yeni oluşturacağımız için New butonuna basarak ilerliyoruz.
New butonuna tıkladıktan sonra karşımıza gelen tablo bu şekilde. Burada da oluşturduğumuz CCM(Cache Connection Manager) için isim ve tanım isteniyor bizden. CCM_DimCustomer olarak seçtim ve description kısmını boş bıraktım. Siz isterseniz ekleyebilirsiniz. Ok işareti ile gösterdiğim “Use file cache” kısmını işaretlediğimizde, tablomuzu bir Cache File üzerine yazdırmak istediğimizi söylüyoruz. Tikledikten sonra, “Browse” butonuna basarak Cache dosyasının nerede ve hangi isim ile oluşmasını istediğimizi belirliyoruz.
Üst kısımda Cache dosyasının hangi dosya yolunda tutulacağını görüyorsunuz. Alt kısımda kutucuk içine aldığım noktada dosya ismini DimCustomerCacheFile olarak belirledim. Sağ tarafta dosyanın uzantısı “Cache Files (*.caw)” olarak gözükmekte ki yukarıda bundan bahsetmiştim. Open dedikten sonra devam ediyorum.
Bir önceki sayfa artık bu şekilde gözüküyor. Dosya yolu File name kısmına yazılmış durumda. Refresh Metadata kısmı, Cache File dosyasının içeriğinin değişmesi durumunda metadata bilgisini güncellemeye yarar. Bu noktadan sonra üst taraftaki “Columns” sekmesine geçip orada da gerekli düzenlemeleri yapmamız gerekiyor. Aksi takdirde CCM’i oluşturamayız. Columns sekmesine geçtiğimde karşıma gelen ekran şu şekilde:
Burada CCM içerisindeki kolonların özelliklerini belirleyeceğiz. Veri tipi, uzunluk, ondalık kısım ve code page gibi özellikler yer alıyor. Bu özelliklerin detaylarına burada değinmeyeceğim çünkü bu yazının asıl amacı CCM’i ve detaylarını anlatmak olduğundan, bizi burada asıl ilgilendiren özellik ok işareti ile gösterdiğim kolon olan Index Position kolonu. Index Position özelliği, Lookup esnasında aralarında ilişki kurulacak olan kolonlarda kullanılacak. Bir başka deyişle, anahtar alanlarda. Buradaki index position değeri pozitif ve bir seri şeklinde ardışık olarak artan bir numaradır. Yani bir kolonun index position değeri 1 ise, bir başka kolona 2 değerini vermeden diğer kolonlara 3 değerini veremeyiz.
Peki bu sayı neyi gösterir? Bu sayı, Lookup komponentinin referans tablosundaki satırları input tablosundaki satırlarla hangi sıra üzerinden karşılaştıracağını gösterir. Bizim senaryomuzda tek indekslenmiş kolon CustomerKey kolonu olduğu için, sadece bu kolon üzerinden karşılaştırma yapacak. Fakat bir başka kolon da 2 değerine sahip olsaydı, CustomerKey kolonundan sonra o da karşılaştırmaya dahil olacaktı. Bu bilgiye dayanarak şunu söyleyebiliriz, en fazla unique(tekil) satıra sahip olan kolon, en düşük index position değerine sahip olmalıdır. Devam edelim;
Index Position değerlerini ayarladıktan sonra OK butonuna bastık ve karşımıza bu sayfa geldi. Gördüğünüz gibi CCM artık hazır. Altta sarı kutucuk içersinde bir uyarı görüyoruz. Tüm hedef kolonların, input kolonları ile eşlenmiş olması gerektiğini söylüyor. Sol taraftan “Mappings” sekmesine bakalım.
Evet kolonlar arasında eşlenmeyen yok. Dikkat ederseniz, CustomerKey kolonunun yanından bir büyüteç işareti var. Bu, o kolon üzerinde index olduğuna dair bir işaret. CustomerKey kolonundan başka herhangi bir kolonda da index yok ve CustomerKey kolonunun Index Position değeri de 1. Data Flow’umuzun son hali artık aşağıdaki gibi:
Hatırlarsanız Cache Transform komponentinin, kendisine referans yapılacak olan Lookuplardan farklı pakette veya en azından farklı Data Flow Task içerisinde olması gerektiğini söylemiştik. Şimdi bu DFT içerisinde referans tablomuzu Cache File üzerine yazdırdığımıza göre, bir başka DFT ekleyip gerekli Lookup komponentleri içerisinde burada oluşturmuş olduğumuz Cache Connection Manager üzerinden Cache dosyasına bağlanacağız. Bir DFT daha ekleyerek komponentleri içerisine koyalım.
İkinci DFT’mi ekledim ve ismini de DFT_InputTables koydum. İçerisine girdiğimde öncelikle aşağıdaki gibi input tablolarımı getirecek olan iki tane OLE DB Source komponenti eklemem gerekiyor.
Komponentlerin içlerini de aşağıdaki şekilde dizayn ediyorum:
Input tablolarım olan FactInternetSales ve FactSurveyResponse tablolarını komponentler içerisinde SQL command aracılığı ile çektim. Şimdi CCM’ye referans yapacak olan Lookup komponentlerini ekliyorum.
LKP_CustomerKey_1 ve LKP_CustomerKey_2 olarak isimlendirdiğim Lookup komponentlerini ekledim. Asıl önemli olan nokta ise bunların içlerini nasıl dizayn edeceğim.
Birinci Lookup komponentinin içini açtığımda karşıma gelen ilk sayfa bu. Dikkat ederseniz üstte Cache mode kısmında Full cache seçili. Zaten sadece bu modda CCM’ye bağlanabileceğimizi söylemiştim. Connection type kısmında OLE DB connection manager değil, Cache connection manager seçili. En alt kısımda ise eşleşmeyen satırlar ile alakalı nasıl bir önlem alacağımız soruluyor. Buradaki seçenekleri kısaca açıklayayım. Ignore failure seçerseniz, Lookup komponenti çalışırken eşleşmeyen(referans tablosunda karşılığını bulamadığı) satırlarla karşılaşırsa, herhangi bir şey yapmadan o satırları göz ardı ederek çalışmasına devam eder. Redirect rows to error output seçeneği seçili olursa, Lookup komponenti eşleşmeyen satırları “Error bacağına” yönlendirir. Yani Lookup komponentinden çıkan iki bacaktan birisinde eşleşen satırlar, diğerinde eşleşmeyen satırlar olur ve siz her ikisini de farklı hedeflere yönlendirebilirsiniz. Fail component seçeneği, herhangi bir eşleşmeyen satırla karşılaşması halinde Lookup komponentinin hata vermesine ve paketin çalışmasının durmasına sebep olur. Redirect rows to no match output seçilirse de eşleşmeyen satırlar aynı eşleşen satırlar gibi başka bir hedefe yönlendirilir. Redirect rows to error output seçeneğinden farklı olarak hata gibi algılanmazlar. Eğer bu seçeneği seçtiğiniz halde eşleşmeyen satırlar için gidebilecekleri bir hedef belirlemezseniz, satırlar göz ardı edilecektir. Fakat bu durumda Lookup komponenti size bir uyarı vererek eşleşmeyen satırların başka bir hedefe yönlendirilemediğini söyler. Ben burada Ignore Failure seçeneğini işaretledim. Bu da şu an konumuz farklı olduğu için yaptığım bir seçim. Devam edelim.
İkinci sayfada connection bölümü var. İlk sayfada Cache Connection Manager seçtiğimiz için, burada karşımıza CCM seçenekleri geliyor. Elimizde zaten bir tane var fakat birden fazla olduğunda burada tamamı listelenecektir. CCM_DimCustomer’ı seçerek devam ediyorum.
Geldik üçüncü sayfaya. Burada hangi kolonları anahtar olarak kullanacağımız ve üzerlerinden eşleştirme yapacağımız soruluyor. Bizim anahtar kolonumuz CustomerKey kolonu olduğu için, input tablosundaki ve referans tablosundaki CustomerKey alanlarını eşledim. Zaten sağ taraftaki referans tablosu kolonlarına bakarsanız, üzerinde index olan kolonun yanında aynı Cache Transform komponentinde olduğu gibi büyüteç işareti olduğunu görürsünüz. Alt tarafta kutu içine aldığım kısımda ise referans tablosundan gelecek olan kolonların input tablosuna geldiklerinde isimlerinin nasıl olması gerektiğini ayarladım. Bunu daha önce de yapmıştık.
Bu noktada vermem gereken birkaç önemli bilgi var. Daha önce de söylediğimiz gibi Cache Transform kullanarak bir Cache File üzerine yazılmış veri her zaman RAM(memory) üzerinde tutulur. Ayrıca CCM’in desteklemediği birkaç veri tipi vardır. Bunlar; DT_TEXT, DT_NTEXT, DT_IMAGE ve BLOB(Binary Large Object) veri tipleridir. Bir başka önemli bilgi ise, Cache Transform komponenti, Cache Connection Manager üzerine sadece unique(tekil) satırları yazar. Bir tabloda çiftleyen kayıtlar böylece tekilleştirilerek yazılmış olur.
Üstte kolon eşlemesinden bahsettik. Burada da çok kritik bir nokta var. Herhangi bir Lookup komponenti CCM kullanmak üzere ayarlandığında, referans tablosunda sadece üzerinde indeks olan kolonlar input tablosundaki kolonlar ile eşlenebilir. Ayrıca, üzerinde indeks olan tüm kolonlar, input tablosundaki bir kolonla eşlenmelidir. Devam edecek olursak, Lookup komponentinin diğer sayfalarına girmemize gerek yok. Komponenti istediğimiz şekilde ayarladık. İkinci komponent için de aynı işlemleri yapacağımdan, burada bunları göstermeye gerek yok. Sonuç olarak, bir paket içerisinde iki farklı Data Flow Task oluşturduk ve birinde referans tablosunu CCM’e ve dolayısıyla Cache File üzerine yazarken, diğerinde de ona referans yapacak olan Lookup komponentlerini yerleştirdik. Tabi burada eşleşen kayıtların gideceği hedef tablolar da oluşturarak onların da komponentlerini eklemeliyiz. İkinci DFT’nin final hali şu şekilde olacak:
İki tane OLE DB Destination komponenti ekleyerek, isimlerini koydum ve ikisinden de yeni tablo oluşturdum. Bunlar FactInternetSales_Extended ve FactSurveyResponse_Extended tabloları. Üstte kutucuk içerisinde ise “Match Output” yani eşleşen satırları hedeflere bağladığımı belirttim. Teknik tasarım kısmı bittiğine göre, paketi artık çalıştırabiliriz.
Control Flow içerisindeki Data Flow Task’lar başarı ile tamamlandı. İkisinin de içerisine bakacak olursak:
Burada CCM’e yazılma işlemi tamamlandı.
Burada da Lookup işlemleri yapıldı. Paketin doğru çalışıp çalışmadığının sağlamasını yapmadan önce, son bir bilgi daha vermek istiyorum. Bir paket içerisinde, aynı Cache Connection Manager üzerine sadece bir tane Cache Transform komponenti veri yazabilir. Eğer paket içerisinde birden fazla Cache Transform varsa, çalışma esnasında çağrılan ilk Cache Transform CCM’e veriyi yazar. Bundan sonraki Cache Transform’ların yazma operasyonları başarısız olur. Şimdi oluşturduğumuz hedef tabloların da içlerini kontrol ederek işlemimizin sağlamasını yapalım.
Kutucuk içerisine aldığım CustomerFirstName ve CustomerLastName kolonları gösteriyor ki paket doğru bir biçimde çalışmış. Dolayısıyla iki Lookup komponenti üzerinden bir Cache dosyasına referans vererek, Full Cache modda çalıştık. İki tablo RAM üzerinde ayrı ayrı yer kaplayacağına, bir dosya RAM üzerinde yer kapladı ve iki Lookup ona referans yaptı. Burada belki iki komponent üzerinden senaryoyu anlatmak istemiş olabilirim fakat normal bir projede, büyük bir tabloya referans vererek Lookup yapmak isteyen belki onlarca komponent olabilir ve Cache Transform’un asıl faydası da o şekilde ortaya çıkar.