MERGE Type 2 Slowly Changing Dimension (SCD) Kullanımı

Daha önceki yazılarımda MERGE komutunun SQL Server‘da kullanılma seçenekleri ve komut yapısını ele almıştım. Ayrıca MERGE komutu kullanılarak nasıl bir Type 1 Slowly Changing Dimension (SCD) oluşturulabileceğini örnekler üzerinde göstermiştim. Bunlar ile ilgili yazılarıma aşağıdaki linklerden erişebilir ve konunun devamına geçmeden kısaca göz atmak isteyebilirsiniz:

  • SQL Server MERGE Komutu Kullanımı ve Performans Önerileri

http://www.abdullahaltintas.com/index.php/sql-server-merge-komutu-kullanimi-ve-performans-onerileri/

  • MERGE Type 1 Slowly Changing Dimension (SCD) Kullanımı

http://www.abdullahaltintas.com/index.php/merge-type-1-slowly-changing-dimension-scd-kullanimi/

Bu yazımızda ise SQL Server’da MERGE komutu kullanılarak Slowly Changing Dimension (SCD) Type 2 çözümünün nasıl uygulanabileceğini ele alacağız. Örneğimize geçmeden önce kısaca Type 2 SCD‘nin ne olduğunu da açıklayalım. Type1 SCD ile kaynaktan gelen yeni veriler hedef tabloya insert edilmekte ve kaynakta değişikliğe uğramış olan ve hedef tabloya daha önce aktarılmış olan veriler hedefte daha önce bulundukları için update ile overwrite edilerek var olan kayıt üzerinde ilgili değişiklik yansıtılmaktaydı. Type 2 SCD ile ise hedef tabloda olmayan kaynaktan yeni gelen veriler insert edilecek, hedefte bulunan ancak kaynakta değişikliğe uğramış olan kayıtlar overwrite ile üzerine (var olan satır üzerinde) güncelleme yapılmak yerine history tutulabilmesi için eski kayıt pasife çekilecek ve güncel değeri tutan yeni bir kayıt tabloya eklenecektir. Bu nedenle Type 2 SCD ile verinin historical yani tarihsel geçmişinin de tutulabilmesine olanak sağlanmaktadır.

Bu kadar teorik bilgiden sonra örneğimizi gerçekleştirerek demo üzerinde sonuçları inceleyelim. Daha önceki MERGE yazılarımızda kullandığımıza çok benzer bir yapıyı bu örnekte de kullanıyor olacağız. Sadece farklı olarak ilgili satırın güncel kayıt olup olmadığını tutmak için bir flag kolonu (IsActive) ekleyeceğiz. Aynı zamanda kaydın eklenme ve güncellenme tarihlerini elde edebilmemiz için başlangıç ve bitiş tarihlerini tutabileceğimiz 2 kolon (StartDate ve EndDate) daha ekleyeceğiz. Örneğimiz için tabloların oluşturulma scriptleri aşağıdaki gibi olacaktır:

create table EmployeeSource
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit
)
GO


create table EmployeeTarget
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit,
StartDate datetime,
EndDate datetime
)
GO

Kaynak ve hedef tablolarımızı oluşturduktan sonra bunlara demomuz için biraz veri girişi yapalım:

insert into dbo.EmployeeSource
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 1)
GO

insert into dbo.EmployeeTarget 
values
(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1, '20120721', '99991231'),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1, '20090101', '99991231'),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1, '20140103', '99991231'),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1, '20150618', '99991231')
GO

Örneğimizde yeni eklenen IsActive kolonuna şu an örnek verilerin hepsi için 1 yani Aktif kayıt olduğunu girdik. Aynı zamanda kaydın başlangıç tarihi kolonu olan StartDate için RecruitmentDate kolonundaki işe giriş tarihini girdik. Kaydın bitiş tarihi için ise henüz kayıtlar aktif kayıt olduğu için 31.12.9999 tarihini girdik. Burada başka kullanımlar da mevcut olup bunların detaylarına bu makalede girmeyeceğiz. Ancak başka bir yazımızda bu kullanımları da ele alacağız.

Verileri insert ettikten sonra tablomuzu sorgulayalım ve gelen sonuçları ekranda inceleyelim:

select * from dbo.EmployeeSource
select * from dbo.EmployeeTarget

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaş’ın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.

scd2sorgusonucu

Örneğimizdeki amacımız kaynak EmployeeSource tablomuza yeni eklenen ve henüz hedef tablomuz EmployeeTarget‘a eklenmemiş olan kayıtların INSERT edilmesini sağlamak ve daha önce aktarımı yapıldıktan sonra kaynak EmployeeSource tablomuzda güncellenmiş kayıtların güncel hallerinin EmployeeTarget tablosuna aktarılmasını sağlamak olacaktır. Yalnız burada Type 1 SCD yapısını kullanarak var olan kaydın üzerinde overwrite ederek güncelleme yapmak yerine, Type 2 SCD kullanarak personellerin maaşlarında yapılan değişikliklerin geçmişini (historical data) de tutmak istemekteyiz. Bu nedenle Salary kolonu üzerinde Type 2 SCD uygulayacağız.

Bu isteklerimizi karşılayabilmek için MERGE komutu ile aşağıdaki Type 2 SCD kodunu oluşturup çalıştıracağız:

INSERT INTO dbo.EmployeeTarget
SELECT EmployeeID,
		FirstName,
		LastName,
		Title,
		RecruitmentDate,
		Salary,
		IsActive,
		StartDate,
		EndDate		
FROM
	(
	MERGE INTO dbo.EmployeeTarget as t
	USING dbo.EmployeeSource as s
	ON t.EmployeeID = s.EmployeeID	
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, StartDate, EndDate)
		VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, 1, GETDATE(), '99991231')
	WHEN MATCHED AND t.IsActive = 1 AND t.Salary <> s.Salary  THEN
		UPDATE SET  t.IsActive = 0, t.EndDate = GETDATE()
	OUTPUT $Action ActionOut, 
			s.EmployeeID, 
			s.FirstName, 
			s.LastName, 
			s.Title, 
			s.RecruitmentDate, 
			s.Salary, 
			1 as IsActive,
			GETDATE() as StartDate, 
			'99991231' as EndDate			
	) as MergeTbl
WHERE MergeTbl.ActionOut = 'UPDATE';

Yukarıda yazmış olduğumuz MERGE komutunu çalıştırdığımızda istemiş olduğumuz sonucu elde etmiş olacağız. Kaynaktan yeni gelen 5 no’lu id’ye sahip Şeydanur Sandıkçı hedef tablomuza insert edilmiş olacak. Aynı zamanda Salary kolonu üzerinde Type 2 Slowly Changing Dimesnion (SCD) yapısını oluşturduğumuz için Abdullah Altıntaş’a ait maaş bilgisi değiştiği için hedefte daha önce 1000 değerine sahip olan satır historical amaçlı korunmuş ve IsActive kolonu 0 olarak update edilmiştir. Yine benzer şekilde bu kaydın başlangıç zamanı işe giriş tarihi olarak korunmuş ancak bitiş tarihi kolonu olan EndDate güncellemenin yapıldığı tarihi alarak bu kaydın eski kayıt olduğu set edilmiştir. Bunlarla birlikte Abdullah Altıntaş‘a ait yeni bir kayıt eklenmiş ve güncel maaş bilgisi olan 2000 değeri buraya eklenmiştir. Bu satırın başlangıç tarihi olan StartDate kolonu ilgili değişikliğin yapıldığı update tarihini almış durumdadır. EndDate kolonu ise kayıt hala aktif olduğu için 31.12.9999 tarihini göstermektedir.

Son durumda verilerimizi kontrol etmek için tekrar tablolarımızı sorguladığımızda aşağıdaki gibi sonuçlarla karşılaşmaktayız:

scd2sonucseti

İstenilen verinin tarihsel geçmişini tutabileceğimiz yapıyı MERGE Type 2 SCD ile tasarlamış olduk ve sonuçları da yukarıdaki gibi listeledik. Şimdi yazılan MERGE komutunun karışıklığı nedeniyle kısaca parça parça üzerinden geçelim:

  • Kodun ilk kısmında INSERT INTO … SELECT … yapısı kullanılarak select ifadesi içinden dönecek olan verileri hedef tabloya insert etmekteyiz.
  • FROM ifadesinden sonra parantezler içerisine aldığımız MergeTbl ismini verdiğimiz derived table içinde asıl ihtiyacımız olan MERGE komutunu kullanmaktayız.
    • Bu kısımda ilk olarak hedef tablomuzu veriyoruz. Ardından USING ifadesi ile kaynak tablomuzu belirtiyoruz. ON koşulunda da ilgili satırın hedef ve kaynakta bulunup bulunmadığını kontrol etmek için unique kolonlar üzerinden eşleştirme yapıyoruz.
    • İkinci kısımda WHEN NOT MATCHED BY TARGET ifadesi ile daha önce olmayan yeni eklenmesi gereken kayıtlar için INSERT komutunu belirtiyoruz.
    • Üçüncü kısımda WHEN MATCHED ifadesi ile kaynaktan hedefe daha önce aktarılmış kayıtları buluyoruz ve IsActive = 1 koşulu ile aktif olan kayıtları filtreliyoruz. Aynı zamanda Salary kolonu üzerinde Type 2 SCD yapmak istediğimiz için bu kayıtların Salary değerleri eşleşmeyenleri yani değişikliğe uğrayanlarını elde edip güncelliyoruz. Burada historical olarak eski kaydı temsil edebilmesi için ilgili kayıtların IsActive değerini 0 olarak, EndDate değerini de sistemde işlemin yapıldığı tarih olarak güncelleyip kapatıyoruz.
    • Dördüncü kısım olan OUTPUT bölümünde ise değişikliğe uğramış kaydın güncel yeni halini INSERT edebilmek için gerekli olan kolonları ve hesaplamaları OUTPUT‘a gönderiyoruz. $Action ile daha önceki makalelerimizde belirttiğimiz gibi MERGE içinde yapılan işlemi (INSERT, UPDATE, DELETE) elde edebiliyoruz. Burada dikkat etmemiz gereken son nokta ise yeni kayıt güncel kayıt olacağı için IsActive değerini 1 olarak, StartDate kolonunu işlemin yapıldığı tarih olarak, EndDate kolonunu ise 31.12.9999 olarak set etmiş olmamız.
  • En son kısım olan WHERE ifadesinde ise MERGE komutunun yer aldığı derived table içinden sadece UPDATE ile güncelleme yapılmış kayıtları elde edecek şekilde filreliyoruz. Dolayısıyla değişikliğe uğramış olan kayıtların yeni halleri MERGE içinde yazılan OUTPUT ile buraya gönderilecek ve ilk ifade olan INSERT INTO … SELECT … komutu sayesinde güncel kayıt hedef tablomuza ayrı bir satır olarak eklenecektir.

Bu yazımızda daha önce genel hatlarını ve Type 1 SCD yapısını ele aldığımız MERGE komutu ile Type 2 Slowly Changing Dimension (SCD) yapısını nasıl gerçekleştirebileceğimizi ele aldık. Veritabanlarında ilgili kayıt update edildiğinde var olan kayıt üzerine overwrite ederek değil de ilgili satırın hem eski halini hem de güncel halini tutmamıza olanak sağlayan historical veri tutma mekanizmasını MERGE komutu ile nasıl yapabileceğimizi bir örnek ile göstermiş olduk. Bir sonraki yazımızda ise MERGE komutu ile Type 3 SCD işleminin nasıl yapılabileceğini ele alacağız.

Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

Microsoft Açık Akademi Yaz Okulu – Microsoft Azure Stream Analytics Sunumu

Her yıl Microsoft Türkiye lokasyonunda düzenlenen ve Türkiye’nin çeşitli üniversitelerinden öğrencilerin katılımıyla gerçekleştirilen Microsoft Açık Akademi Yaz Okulu etkinliğinde bu yıl da sunumlarımızla katılımcılara güncel teknolojiler hakkında bilgi aktardık. Microsoft’un bulut çözümleri platformu olan Microsoft Azure üzerinde veri çözümlerinin neler olduğu ile ilgili katılımcılara sunumlar gerçekleştirdik.

acikakademi foto.jpg-large

Geçtiğimiz hafta gerçekleştirdiğimiz sunumda öncelikle Data Platform MVP’lerinden Koray Kocabaş hocamız Big Data kavramlarından bahsetti ve Big Data konsepti içerisinde neler olduğunu katılımcılara çok güzel örneklerle aktardı. Devamında bana devrettiği sunum kısmında katılımcılara Big Data ve Data Analytics kapsamında önemli bir yer tutan stream olarak gelen verinin ele alınması konusunda Microsoft’un çözümleri olan Azure Event Hubs ve Azure Stream Analytics servisleri ile ilgili katılımcılara bilgiler verdik.

Sunum esnasında 2 farklı senaryo ele aldık ve bunlarla ilgili demo gerçekleştirdik. İlk olarak stream olarak akan verinin analizinin yapılmasının ardından anlık olarak dashboard’larda gerçek zamanlı olarak nasıl raporlanabileceği Microsoft Power BI kullanarak gerçekleştirildi. İkinci case altında ise analizi yapılan verinin Azure SQL Database hizmetinde nasıl depolanabileceği gösterildi. Günün devamında yine Koray Kocabaş hocamız bu depolanan verinin Microsoft Big Data çözümleri ile nasıl yönetilebileceğini gösteren sunumlar ve örneklerle günü tamamladı.

Böyle bir organizasyonda yer almak ve öğrencilere sunum yapmak benim açımdan çok keyifliydi. Öncelikle bu organizasyonun düzenlenmesinde emeği geçenler olmak üzere sunuma katılan ve bir şeyler öğrenmek için can atan bütün katılımcılara çok teşekkür ederim. Benzer etkinliklerde tekrar görüşmek dileğiyle…

acikakademisinif

Yazar: Abdullah ALTINTAŞ

Stream Analytics ile Analiz Edilen Verinin Azure SQL Database’de Depolanması (Demo)

Advanced Data Analytics konu başlığı altında daha önce ele aldığımız makalelerde stream olarak gelen, akan verilerin Microsoft Azure Event Hubs servisi ile elde edilebileceğini ve Microsoft Azure Stream Analytics servisi ile bu verilerin analiz edilebileceğini ele almıştık. Stream Analytics’ten çıkan bu verilerin de Microsoft Power BI ürünü ile bağdaştırılarak gerçek zamanlı olarak raporlanabileceğini göstermiştik. Bu konuda daha önce yazmış olduğumuz makalelere aşağıdaki linklerden erişebilirsiniz:

http://www.abdullahaltintas.com/index.php/real-time-click-stream-analizi-icin-microsoft-azure-cozumleri-serisi/

Stream olarak akan verinin analizi yapıldıktan sonra gerçek zamanlı olarak raporlanması ihtiyacının dışında, daha sonra tekrar ele alınabilmesi için bir veri tabanında depolanması ihtiyacı da karşımıza çıkabilir. Bu makalemizde stream olarak akan verinin Event Hubs ile elde edilmesi ve Stream Analytics ile analiz edilmesinin ardından Azure SQL Database‘de nasıl depolanabileceğini ele alacağız.

Serinin önceki yazılarında kullanmış olduğumuz uygulamayı kullanarak aynı şekilde click stream verisini üreteceğiz ve daha önceki makalemizde ele aldığımız üzere Azure Event Hubs ile bu veriyi elde edeceğiz.

Yine serinin önceki yazılarında ele aldığımız üzere Event Hubs ile elde edilen veriyi Stream Analytics ile analiz edeceğiz. Burada sadece farklı olarak Stream Analytics job’ının output kısmında Power BI yerine Azure SQL Database tercihini yapacağız. Böylelikle analiz edilen veriyi Azure SQL Database içinde oluşturulan tabloda depolayabileceğiz.

Verilerin Azure SQL Database hizmetinde tutulabilmesi için bu aşamada ya var olan hazır bir Azure SQL Database servisini kullanarak ya da yeni bir Azure SQL Database oluşturarak işlemlerimizi tamamlıyoruz. Yeni bir Azure SQL DB oluşturabilmek için Azure portal üzerinde alt tarafta bulunan New sekmesine tıklıyoruz ve Data Services altında bulunan SQL Database hizmetini seçiyoruz. Quick Create ile veya Custom olarak oluşturacağımız database’e bir isim veriyoruz. Örneğimizde biz “altintasdb” adını vermiş olduk. Ayrıca oluşturulma aşamasında yetkili bir kullanıcı adı ve şifreyi belirleyerek ilerliyoruz.

Azure SQL Database servisi oluşturulduktan sonra erişim için gerekli izinlerin ve firewall kurallarının oluşturulması gerekmektedir. Lokal bilgisayarımızda bulunan SQL Server Management Studio (SSMS) ile Azure SQL Database’e bağlanabilmek için kullanmakta olduğumuz IP adresine erişimi açmamız gerekmektedir. Bu işlemi Azure SQL Database sayfasında bulunan Server kısmındaki Configure tabına gelerek Ip Rule (Add rule diyerek) tanımlayabilirsiniz. Aynı zamanda Azure SQL Database’in diğer Azure servisleri tarafından kullanılabilmesi için de aynı sayfanın alt kısmında bulunan izin verme kısmında gerekli seçeneği Yes olarak işaretleyerek aktif hale getiriyoruz. İlgili değişiklikleri yaptıktan sonra sayfanın altında bulunan Save butonu ile değişiklikleri kaydediyoruz.

sqldb_configure

Bu aşamadan sonra Azure SQL Database hizmeti kullanıma hazır hale gelecektir. İlgili database’e daha rahat erişim için SSMS üzerinden gerekli connection’ı oluşturuyoruz. Burada server name kısmına Azure portal üzerinden database sekmesinde bulunan Server Name’i ([uniqueisim].database.windows.net şeklinde olacak) yazarak bağlanıyoruz. Ayrıca Azure SQL Database’e bağlantı için Windows Authentication desteklenmediği için SQL Authentication seçerek Azure SQL Database oluştururken girilen yetkili kullanıcı adı ve şifresini yazarak bağlantı sağlıyoruz.

sqldb_connection

Bağlantıyı oluşturduktan sonra Azure SQL Database’de bulunan altintasdb‘nin altında altintas_table isminde akan verilen tutulacağı bir tablo oluşturuyoruz. Tablonun create scripti aşağıdaki gibi olacaktır:

CREATE TABLE altintas_table
(
Ad nvarchar(50),
ResimAd nvarchar(50),
Zaman nvarchar(50) primary key,
X nvarchar(50),
Y nvarchar(50)
)
GO

Bu kodu çalıştırarak ilgili verilerin tutulacağı altintas_table oluşturulduktan sonra artık Azure Stream Analytics‘in output seçeneğini ayarlayabiliriz. Bunun için Azure portal üzerinde Stream Analytics servisinin output sekmesine geliyoruz ve aşağıdaki şekilde Azure SQL Databse seçeneğini seçiyoruz.

output_sqldb

Bir sonraki gelen ekranda output alias, database name, username, password ve table name alanlarını daha önce konfigure ettiğimiz şekilde girerek işlemi aşağıdaki şekilde tamamlıyoruz.

output_sqldb2

Bu aşamadan sonra artık ilgili Stream Analytics job’ını çalıştırarak akan verilerin output olarak database’e kaydedilmesini sağlayabiliriz. Serinin daha önceki yazılarında belirtildiği gibi job’ın bulunduğu sayfanın alt kısmında bulunan Start butonuna tıklayarak ilgili job’ı başlatabilirsiniz.

Buraya kadar yapılan işlemler başarılı bir şekilde tamamlandıysa ve Stream Analytics job’ı başarılı bir şekilde başlatılabildiyse uygulamamız üzerinde gerçekleştirilen click’ler anlık olarak Event Hubs tarafından toplanacak, Stream Analytics ile analiz edilecek ve ardından tanımlanan output ile Azure SQL Database’de bulunan altintas_table ismindeki tabloya kayıt edilecektir. Tablomuzu aşağıdaki gibi sorgulayıp verilerin geldiğinden emin olalım:

sql_query_out

Görmüş olduğunuz gibi uygulama üzerinde yapılan her bir tıklamaya ait veriler oluşturulan tabloya kaydedilmektedir. Tıklamayı geçekleştiren kullanıcının adı, tıklanan remin adı, tıklamanın gerçekleştiği zaman, tıklanan remin X ve Y koordinat bilgileri ilgili tabloya satır satır kaydedilmektedir.

Bu yazımızda Azure Event Hubs, Azure Stream Analytics ve Azure SQL Database çözümlerini kullanarak akan verinin analiz edilmesi ve ardından bir veritabanında saklanması için gereken çözümleri ele almış olduk. İlerleyen günlerde Advanced Data Analytics konu başlığı altında yeni yazılarımızla yine birlikte olmak üzere…

Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

Stajokulu – SQL Server 2016 Mimarisi ve Yenilikleri Etkinliği

Geçtiğimiz hafta Realtime Click Stream Analysis with Azure Stream Analytics konu başlığı ile katıldığım etkinlikte bu hafta da yaklaşık 70 öğrencinin katılımıyla “SQL Server 2016 Mimarisi ve Yenilikleri” konu başlığında bir sunum gerçekleştirmiş oldum. Oldukça keyifli geçen sunum esnasında katılımcılara T-SQL’den başlayıp SQL Server 2016 Yenilikleri ile biten bir sunum gerçekleştirdim.

İstanbul Şehir Üniversitesi’nde gerçekleştirilen organizasyonda etkinlik boyunca sunuma katılan tüm öğrencilere ve etkinliğin düzenlenmesinde rol alan organizasyon ekibine çok teşekkür ederim. Böyle organizasyonlar ile heyecanlı ve hevesli üniversite öğrencilerine yeni teknolojileri anlatmak bizlere keyif veriyor…

Bir sonraki etkinlikte görüşmek dileğiyle…

13908981_1031077020280411_2130843920135485032_o

Yazar: Abdullah ALTINTAŞ