-

SSIS- Sql Server Intergration Services (Sql Server Entegrasyon Servisleri) Uygulama Örnekleri

Microsoft’un sunduğu İş Zekası çözümlerinde kullanılan teknolojilerin bir kısmı aşağıdaki gibidir;

  • Sql Server veri tabanı motoru (database engine), uygulama veri tabanı ve veri ambarları için kullanılır,
  • ETL işlemleri, Microsoft Sql Server Integration Services (SSIS) ile yapılır,
  • Veri temizliği, Data Quality Services (DQS) ile,
  • Veri yönetimi (Master Data Management), Master Data Services (MDS) ile,
  • Depolama ve sorgu (query) işleme, Sql Server Analysis Services (SSAS) ile,
  • Rapor yayınlama ve gönderme, Sql Server Reporting Services (SSRS) ile

yapılır.

Sql Server Entegrasyon Servisleri Uygulama Örnekleri

Entegrasyon Servisleri ETL işlemlerini gerçekleştiren servistir. Günlük veri tabanından Veri Ambarı’na geçilme aşamasında SSIS ile oluşturulan paketler çalıştırılır. Bu yazıda  verilen Entegrasyon Servisleri ile ilgili senaryo örnekleri aslında bu geçiş esnasında çalıştırılan paketlerdir. Hangi paketlerin oluşturulacağı ise şirket verisinin analiz edilmesi ve sık ihtiyaç duyulan raporlardan yola çıkılarak karar verilir.

Entegrasyon servisleri için gösterilen örneklerde veri tabanı olarak Microsoft’un ücretsiz kullanıma sunduğu veri tabanı, AdventureWorks kullanılmıştır. AdventureWorks uluslararası bisiklet ve bisiklet aksesuarları satan bir şirketin satış, ürün, kullanıcı gibi bilgilerini tutan ilişkisel veri tabanıdır

Entegrasyon Servisleri 1.Senaryo

Senaryo:  AdventureWorks  veri tabanındaki Product tablosu üzerinde bulunan ürün renklerini farklı diller ile ifade edilmesi.

Bu örnekteki amaç Veri Ambarı’nda çok dilli olma özelliğinin nasıl katılabileceğini göstermektir.

Sql Server Data Tools üzerinde spesifik işler yapmak üzere tanımlanmış task adı verilen yapılar mevcuttur. Bu senaryo için “Oledb Source” ismindeki task eklenir, bu task’a kaynak olarak AdventureWorks veri tabanı verilir.

Oledb Source task için aşağıdaki Sql komutu kullanılır:

Select ProductID, Name, Color, Size, and ListPrice from Production.Product

Bu senaryo için renkleri farklı dillerde ifade etme işlemi için farklı dillerdeki renk bilgilerini tutmak üzere bir lookup tablosuna ihtiyaç duyulur. ColorTranslation ismindeki aşağıda gösterilen bilgileri içeren tablo oluşturulur.

ID İngilizceRenk TürkçeRenk  İspanyolcaRenk
1 Red Kırmızı Roje
2 Black Siyah Negro
3 Blue Mavi Azul
4 White Beyaz Blanco
5 Null Renksiz No Color

Renkleri eşleştirme ve ürün rengini farklı renklerde gösterme işlemini yapmak üzere araçlar içerisinden “Lookup” ismindeki task eklenir. Product ve ColorTranslation tablolarını bağlamak üzere iki tabloda bulunan, ortak bir kolona ihtiyaç duyulur. Bu kolon aşağıdaki şekilde görüldüğü gibi EnglishColor dır.

kolon

Aşağıdaki şekilde verinin akış diyagramındaki tek yönlü oklar bir task’dan diğerine bilgi taşımaktadır. Eşleşen ve eşleşmeyen renkleri göstermek üzere araçlar içerisinden iki adet “Audit” ismindeki task eklenir. 504 üründen 414 tanesinin rengi lookup tablosundaki renklerle eşleşirken 90 adet ürün renginin tablodaki renklerden farklı renkte olduğu anlaşılmaktadır.

diagram

Aşağıdaki şekildeki senaryo çıktısında görülebileceği gibi İngilizce renklere karşılık gelen Türkçe ve İspanyolca ürün renkleri eklenmiştir.

result

Entegrasyon Servisleri 2.Senaryo

Senaryo: AdventureWorks  veri tabanındaki Product tablosundaki ürünleri mavi ve kırmızı renk ve pahalı ürünler olarak ayrıştırma işleminin gerçekleştirilmesi.

Tablodaki verileri belirlenen özelliklere göre ayırmak mümkündür. Bu senaryo için ayırma işlemini yapmak üzere “Conditional Split” ismindeki task kullanılır. Bu işlemden önce boş (null) alan temizliğinin yapılması gerekir. Bu işlem için “Derived Column” ismindeki task eklenir. Bu taskta yapılması gereken işlem için REPLACENULL(Color,”No Color”) fonksiyonu kullanılır, Color kolonu için Null görünen yerleri “No Color ” olarak güncellemiş olur.

Şartlı  ayrıştırma (conditional split) işlemi için şart faktörleri aşağıdaki gibidir:

•          Pahalı Ürünler: ListPrice > 1500,

•          Kırmızı Ürünler: Color == “Red” ,

•          Mavi Ürünler: Color == “Blue”

Aşağıdaki diyagramdan görülebileceği üzere, veri kaynağı devamında null temizliği ve şartlı ayırma işlemi yapılır. Şartlı ayırma işlemi sonrasında ürünler kırmızı, mavi, pahalı ürünler ve bunlar dışında kalan ürünler olarak ayrıştırılmış olur.

conditionalSplit

Aşağıdaki şekilde kırmızı renkli ürünlerin çıktısını göstermek üzere eklenmiştir.

kırmızı

 Kaynaklar

  1. Bt Akademi’den aldığım Sql Server 2012 ile İş Zekası Uygulamları Eğitimi

İŞ ZEKASI BİLEŞENLERİ

Birçok İş Zekası çözümü Veri kaynağı, ETL, Veri Ambarı, Analitik Veri Modeli (Analytical Data Model), Raporlama ve Analiz gibi temel İş Zekası bileşenlerini barındırır. Bütün platformlar için uygun olan ve bileşenler arasındaki ilişkiyi gösteren aşağıdaki şekil bu bileşenleri özetler niteliktedir. İş Zekası çözümü bir veri kaynağı ile başlar, veri işlenir ve Veri Ambarı ya da veri modeli oluşturulur. Sonuç olarak son kullanıcıya anlamlı rapor ve analizler sunulur.

İŞzekasıbileşenleri

Veri Kaynağı

Veri kaynağı İş Zekası çözümlerine temel oluşturan bileşendir. Şirketlerin birçoğu operasyon işlemlerinde Enterprise Resource Planning (ERP), Supply Chain Management (SCM), satış odaklı işlemler için Customer Relationship Management (CRM) ya da perakende satış işlemleri için Point-of-Sale (POS) gibi yazılımlar kullanırlar. Bu sistemler İş Zekası çözümlerinde başlangıç noktası olacak veri kaynağını üretirler. Uygulama veri tabanı genellikle Sql Server, Oracle ya da Access gibi ilişkisel veri tabanı yönetimi sistemleri (relational database management systems) olarak tasarlanır. İş Zekası çözümlerinde kullanılan veri kaynağı bulut tabanlı veya web servis örneğinde olduğu gibi harici ya da uygulama veri tabanı örneğinde olduğu gibi dahili olabilir.

ETL

ETL, üç ayrı işlemin tek bir işlem gibi yapıldığı bileşendir. ETL sistemleri genel anlamda verinin operasyonel veri tabanlarından veri ambarlarına aktarma işlemini gerçekleştirir. Verinin kaynaktan Veri Ambarı’na kopyalanmasından çok daha fazlasıdır. ETL veriyi kaynak sistemden çıkarıp veri içeriğini Veri Ambarı şemasına iletir ve Veri Ambarı’na yükler.

ETL İş Zekası çözümünün kalbi olarak düşünülebilir. ETL sistemleri veriyi sürekli olarak pompalayarak Veri Ambarı’nın güncel olmasını ve veri entegrasyonunu sağlayarak İş Zekası çözümünün bir bütün olarak devam etmesini sağlar. Etkili bir ETL tasarımı daha etkili bir İş Zekası çözümü elde edilebilmesine katkıda bulunur.

Veri Ambarı (Data Warehouse)

Veri Ambarı bütün raporlama ve analizlere temel oluşturan merkezi veri depolama birimi olduğundan İş Zekası çözümünün beyni olarak düşünülebilir. Günlük veri tabanlarında işlemler halen devam ediyor olduğundan rapor ya da analiz yapmak için uygun değildirler. Operasyonel veri tabanlarında select sorgusu çalıştırmak uzun süre alır. Select sorgusu üzerine yoğunlaşılmaktadır çünkü raporlar veya analizler genel olarak karmaşık select komut setlerinin çalıştırılması ile alınır. Bu zamanı kısaltmak için veri tabanının kopyasını almak yeterli olmaz. Günlük veri tabanlarının yükünü azaltacak bir yapıya ihtiyaç vardır. Bu ihtiyaçlar Veri Ambarı yapısının kullanılması ile giderilmiş olur.  Günlük veri tabanları ETL operasyonlarından geçirilerek verinin daha rahat analiz edilebileceği ve kolaylıkla rapor alınabileceği veri ambarları oluşturulur.

Veri Ambarı şirketin hafızasıdır. Bilgiyi detaylı ve geçmişini de içerecek şekilde tutar. Operasyonel veri tabanlarında verinin güncellendikten sonraki son haline ulaşmak mümkündür. Veri Ambarı’nda ise verinin güncellenmeden önce ve sonraki bütün versiyonları tutulur. Örneğin veri tabanında çalışan bilgilerinin tutulduğu bir tablo olduğunu varsayalım. Çalışanlardan birinin 2011 yılında 2 çocuğu olsun ve çalışanın 2014 yılında bir çocuğu daha doğuyor olsun. Günlük veri tabanında çalışanın 3 çocuğu olduğu bilgisi tutulurken Veri Ambarı’nda ise çalışanın 2011 yılında 2 çocuğu, 2014 yılında 3 çocuğu olduğu bilgileri yer alır. Anlaşılabileceği üzere Veri Ambarı’ndan çalışanla ilgili geçmiş bilgilere de ulaşılabilmektedir. Bunun bir sonucu olarak Veri Ambarı günlük veri tabanına göre çok daha fazla yer kaplamaktadır.

Günlük veri tabanında aynı kelime farklı şekillerde ifade edilebildiğinden veri kirliliği vardır. Veri Ambarı’nda ise aynı kelimeye denk gelen bütün kelimeler orijinal versiyonu ile ifade edilecek şekilde veri temizliği yapılır böylece veri kirliliği azaltılmış olur.

Veri Ambarı oluşturulurken denormalizasyon, tabloları birleştirme ve sadeleştirme, işlemi uygulanır, bunun sonucunda tablo sayısı azalır, tablo içerisindeki veri miktarı artar, birden fazla tablo tek bir tablo olarak ifade edilmiş olur.

Veri ambarları güncel veriyi tutmaz. Günlük veri tabanından ETL işlemlerini gerçekleştirmek üzere tanımlanan paketler, belirli saatlerde çalışarak Veri Ambarı’nı günceller, genellikle bu paketler 6 saatlik periyotlarla çalıştırılır.

Veri Ambarı oluşturulurken tablo ve sütun isimleri anlamlı olmalıdır. Böylece rapor alırken her defasında isimlendirmektense raporda kullanılacak şekilde isimlendirme yapmak zamandan tasarruf edilmesini sağlayacaktır.

Analitik Veri Modeli

Şirketler analiz ve raporlama işlemlerini direkt olarak Veri Ambarı üzerinden yapmak isteyebilirler. Diğer bir yöntem ise analitik veri modeli üzerinden rapor çekip analiz üretmektir. Analitik veri modeli için kaynak olarak genellikle Veri Ambarı kullanılır. Analitik veri modellerinin amacı işleyiş için Veri Ambarı’ndan çekilen veriye değer katarak gerekli olan anlamlı bilgiyi üretmektir. Analitik veri modeli Veri Ambarı’nda olmayan KPI (Key Performance Indicator), önceden hesaplanmış ölçümler (calculated measures),  kullanıcı tanımlı hiyerarşiler (user-defined hierarchies) gibi ek faydalar sağlar. Analitik veri modelleri küp (cube) isminde çok boyutlu yapılar oluşturur, küpler özellikle eklenmiş hesaplamaların bulunduğu yapılardır.

 Raporlama

İş Zekası çözümlerinin sağladığı temel faydalardan bir tanesi kurumların iş performansını arttırması ve işleyişi takip edebilmesi amacıyla rapor ve analizler üretmektir. Rapor; gelecek yıllarda hangi aksiyonların alınması gerektiği amacı ile oluşturulan, iş performansı ve aktivitelerin bir özetidir.

İş Zekası çözümlerinin çoğu standart raporların alındığı raporlama elementlerini barındırır. Raporlar doğrudan günlük veri tabanından, Veri Ambarı’ndan ya da analitik veri modelleri kullanılarak alınabilir, bu ihtiyaca ve senaryoya göre değişir.

Kaynaklar

[1]   Microsoft Corporation, 2012, Designing Business Intelligence Solutions with Microsoft Sql Server 2012, Microsoft Corporation, Product Number: 20467B.

[2]  Veri Ambarı ve İş Zekası Yapısal Teorisi, http://www.cevizbilgi.com.tr/veri-ambari-ve-zekasi-yapisal-teorisi/, [Ziyaret tarihi:20 Şubat 2015].

[3]  Loshin, D., 2003, Getting Onboard with Emerging IT, Business Intelligence: The Savvy Manager’s Guide, Morgan Kaufmann Publishers, ISBN-10: 1558609164, SanFrancisco, 100-120.