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

Microsoft SQL Server‘da MERGE komutu ile neler yapılabildiğini serinin daha önceki yazılarında sizlerle paylaşmıştık. Aşağıda MERGE komutunun genel kullanımı, MERGE ile Slowly Changing Dimension (SCD) Type 1 , Type 2 ve Type 3 çözümlerinin nasıl yapılabileceğini aktardığım yazılarım mevcut. Yazının devamına geçmeden bunlara göz atmak isteyebilirsiniz. Eğer ihtiyacınız sadece MERGE komutu ile SCD Type 4 işleminin nasıl yapılabildiği ise direkt olarak makalenin devamına geçebilirsiniz.

  • 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/

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

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

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

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

Bu makalemizde SQL Server’da kullanılan MERGE komutu yardımı ile Type 4 Slowly Changing Dimension (SCD) işleminin nasıl gerçekleştirilebileceğini ele alacağız. Serinin diğer yazılarında olduğu gibi isterseniz öncelikle Type 4 SCD‘nin ne olduğundan kısaca bahsedelim:

Serinin önceki yazılarında bahsettiğimiz gibi MERGE komutu ile yazdığımız SCD Type 1 yöntemi ile kaynaktan hedef  tabloya daha önce aktarımı yapılmış olan ve ardından kaynakta tekrar güncellenen kayıtlar, hedef tabloda overwrite edilerek yani var olan kaydın üzerinde güncellenerek tutulmaktaydı. Type 2 SCD ile aktarımı yapılan kayıtlarda ise daha önce aktarımı yapılmış olan kayıt pasife çekilerek güncellenmek istenen kaydın yeni halini tutan ve aktif olacak olan yeni bir kayıt tabloya eklenmekteydi. Yani bu yöntem ile verinin historical hali tarihsel versiyonu tutulabilmekteydi. Type 3 SCD kullanılarak yapılan tasarımda ise verinin yine tarihsel geçmişi tutulabilmekteydi ancak bu yöntemde satır bazlı olarak değil var olan kaydın üzerinde tasarım aşamasında eklenen kolonlarda  (denormalize bir şekilde) tarihsel geçmiş tutulabilmekteydi. Type 4 SCD yönteminde ise kaynaktan hedefe aktarımı yapılmış olan ve ardından kaynakta tekrar güncellenen kayıtlar için hedef tabloda verinin en güncel hali tutulmaktadır. Ancak verinin historical geçmişini yani tarihsel versiyonlarını tutabilmek için ayrı bir tablo oluşturulmakta ve MERGE komutu ile verinin güncellenmeden önceki eski hali bu tabloya insert edilmektedir. Yani Type 4 SCD ile verinin tarihsel geçmişi ayrı bir tabloda tutulmaktadır. Bir nevi bu tablo arşiv tablo görevi görmektedir.

Bu kadar teorik bilgiden sonra şimdi isterseniz örneğimize geçelim. Serinin önceki yazılarında olduğu gibi benzer tabloları kullanacağız. Sadece gerekli değişiklikleri yaparak kodumuzu oluşturacağız. Demomuz için gerekli olan tabloların create scriptleri aşağıdaki gibidir:

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
)
GO

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

Burada dikkat etmemiz gereken nokta var olan kaynak ve hedef tablolarımıza ilave olarak bir de tarihsel veriyi tutmak açısından EmployeeTargetArchive isminde yeni tablo ekledik. Aynı zamanda bu tabloda ilgili verinin geçerlilik tarihlerini tutabilmek için de StartDate ve EndDate kolonlarını ekledik.

Şimdi de örneğimiz için kaynak ve hedef tablomuza 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),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1)
GO

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

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

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. Aynı zamanda yeni oluşturduğumuz arşiv datayı tutacak olan EmployeeTargetArchive tablosunda henüz bir kod çalıştırmadığımız için hiç bir veri bulunmamaktadır. MERGE komutunu çalıştırdıktan sonra tarihsel geçmişi tutulacak olan veriler bu tabloya aktarılacaktır.

scdtype4baslangic

Ö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 4 SCD yapısını kullanarak aktarım yapacağımız için hedefte var olan kayıt üzerinde overwrite ile gerekli değişiklik yapılacak ve güncel bilgi hedef tablomuz olan EmployeeTarget‘ta tutulacaktır. Ayrıca güncellenen verinin eski versiyonu yani tarihsel geçmişi de EmployeeTargetArchive tablosuna insert edilecek ve dolayısıyla historical verinin ayrı bir tabloda tutulması sağlanacaktır.

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


-- Geçici olarak verilerin aktarılacağı Temp Table görevi gören Table Variable tanımlıyoruz.
DECLARE @ArchiveTemp TABLE
(
EmployeeID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Title nvarchar(100),
RecruitmentDate datetime,
Salary decimal,
IsActive bit,
MergeAction nvarchar(10)
)

-- Merge komutumuzu aşağıdaki şekilde oluşturuyoruz.
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)
		VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, 1)
	WHEN MATCHED AND 
				ISNULL(t.FirstName, '') <> ISNULL(s.FirstName, '')
				OR ISNULL(t.LastName, '') <> ISNULL(s.LastName, '')
				OR ISNULL(t.Title, '') <> ISNULL(s.Title, '')
				OR ISNULL(t.RecruitmentDate, '') <> ISNULL(s.RecruitmentDate, '')
				OR ISNULL(t.Salary, '') <> ISNULL(s.Salary, '')
				OR ISNULL(t.IsActive, '') <> ISNULL(s.IsActive, '')
		THEN UPDATE SET  
				t.FirstName = s.FirstName,
				t.LastName = s.LastName,
				t.Title = s.Title,
				t.RecruitmentDate = s.RecruitmentDate,
				t.Salary = s.Salary,
				t.IsActive = s.IsActive
	OUTPUT 	DELETED.EmployeeID, 
			DELETED.FirstName, 
			DELETED.LastName, 
			DELETED.Title, 
			DELETED.RecruitmentDate, 
			DELETED.Salary, 
			DELETED.IsActive,
			$Action as MergeAction
	INTO @ArchiveTemp (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, MergeAction);

-- Güncellenen kaydın historical halini de son olarak Arşiv tablomuza ekleyelim.
INSERT INTO dbo.EmployeeTargetArchive (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, StartDate)
SELECT EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive, 
		ISNULL((SELECT MAX(EndDate) FROM dbo.EmployeeTargetArchive
		WHERE EmployeeID = Tmp.EmployeeID), RecruitmentDate)
FROM @ArchiveTemp as Tmp
WHERE MergeAction = 'UPDATE'

-- Arşiv tablosunda verilerin aktif olup olmadığını ve güncellenme tarihlerini set edelim.
UPDATE eta
SET eta.EndDate = GETDATE()
FROM dbo.EmployeeTargetArchive as eta
INNER JOIN @ArchiveTemp as tmp
ON eta.EmployeeID = tmp.EmployeeID
WHERE eta.EndDate IS NULL

Yukarıdaki kodları çalıştırdığımızda istemiş olduğumuz sonuçları elde edeceğiz. Kaynak tablomuz EmployeeSource, hedef tablomuz EmployeeTarget ve arşiv tablomuz EmployeeTargetArchive tablolarımızın son durumunu görmek için tekrar sorgulayalım. Sonuçlar aşağıdaki gibi olacaktır:

scdtype4son

Sonuçları incelediğimizde hedefte olmayan ve kaynak tabloda bulunan 5 no‘lu id’ye sahip Şeydanur Sandıkçı‘nın hedef tablomuz olan EmployeeTarget tablosuna eklendiğini görmekteyiz. Aynı zamanda daha önce kaynaktan hedef tabloya aktarımı yapılmış olan 1 no‘lu id’ye sahip Abdullah Altıntaş‘ın maaş bilgisinin tutulduğu Salary kolonundaki değerin de 2000 olarak güncellendiğini ve güncel değerin EmployeeTarget tablosu üzerine yazıldığını görmekteyiz. Son olarak Abdullah Altıntaş‘ın güncellenmeden önceki maaş bilgisini (1000) içeren eski kaydın da historical amaçlı olarak EmployeeTargetArchive tablosuna eklendiğini görebilirsiniz. Ayrıca arşiv tablosuna aktarılan bu kaydın IsActive kolonundaki değerin 0 yani pasif olduğunu görebilir ve StartDateEndDate kolonlarında da bu kaydın geçerli olduğu tarih aralığını bulabilirsiniz.

Peki aynı kayıt üzerinde yeni bir güncelleme yapıldığında sonuçlar nasıl olacak? Abdullah Altıntaş‘ın maaş bilgisi üzerinde tekrar bir güncelleme yapalım:

update EmployeeSource
set Salary = 4000
where EmployeeID =1

Ardından da Type 4 SCD ihtiyacımızı karşılayacak şekilde yazmış olduğumuz MERGE komutunu tekrar çalıştıralım ve tablolarımızı tekrar sorgulayalım:

scdtype4son2

Görmüş olduğunuz gibi en güncel veri hedef tablomuzda overwrite edilerek tutulmakta ve ilgili kaydın eski versiyonu ise arşiv tablosuna geçerlilik tarihlerini de aktaracak şekilde insert edilmektedir. Type 4 Slowly Changing Dimension yönetimi ile yapmış olduğumuz tasarım sayesinde verinin tarihsel geçmişini ayrı bir tabloda saklayabildiğimizi bu şekilde görmüş oluyoruz.

Son olarak Type 4 SCD ile yazdığımız MERGE ifadesini de içeren kod bloğunu kısaca açıklayalım:

  • İlk olarak geçici olarak verilerin tutulacağı bir tane table variable tanımlıyoruz. Burada temp table oluşturup onu da kullanabilirsiniz.
  • İkinci kod bloğunda asıl MERGE komutu işlemleri yapılıyor. İlgili kaydın hedef ve kaynak tablolarda var olup olmadığı kontrol edildikten sonra eğer ilgili kayıt hedef tablosunda yoksa WHEN NOT MATCHED BY TARGET ifadesinde ilgili kayıt hedef tablomuza insert ediliyor.
  • Ardından ilgili veri daha önce hedef tablosuna aktarıldıysa ve üzerinde değişiklik yapıldıysa ne yapılması gerektiğini belirtmek için WHEN MATCHED ifadesi ile bu kontrol gerçekleştiriliyor ve kaynaktan gelen güncel bilgiler hedef tablosunda update ediliyor.
  • Merge komutunun OUTPUT kısmında değişikliğe uğrayan kayıtların eski halleri alınarak en başta oluşturulan table variable’a insert ediliyor.
  • MERGE komutu tamamlandıktan sonra yazılan INSERT komutu ile table variable’da bulunan değişikliğe uğramış kayıtların eski versiyonları asıl arşiv tablomuza insert ediliyor.
  • Son UPDATE komutunda ise ilgili kaydın geçerlilik tarihlerini güncellemek için EndDate kolonu güncelleniyor.

Bu yazımızda SQL Server’da bulunan MERGE komutu kullanılarak Type 4 Slowly Changing Dimension (SCD) yapısının nasıl tasarlanabileceğini ele almış olduk. Yaptığımız örnek ile senaryomuzu uyguladık ve sonuçlarını gözlemledik. Aynı zamanda daha önceki yazılarımızda bahsettiğimiz Type 1, Type 2, Type 3 ve bu yazıda ele aldığımız Type 4 SCD yapılarının çalışma şekillerini karşılaştırdık.  Başka yazılarla tekrar görüşmek üzere…

Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

Not: Destekleri için Zafer Dörter‘e teşekkür ederim 🙂 Emeğine, aklına sağlık Zafer hocam 🙂

MERGE Type 4 Slowly Changing Dimension (SCD) Kullanımı” üzerine bir düşünce

Yorum Yaz