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

Microsoft SQL Server‘da MERGE komutunun kullanımı ve Slowly Changing Dimension çözümlerinin MERGE komutu ile nasıl yapılabileceğini önceki makalelerimde yazmıştım. Özellikle Type 1 Slowly Changing Dimension (SCD) ve Type 2 Slowly Changing Dimension (SCD) yapılarını ayrı ayrı nasıl çözümleyebileceğimizi daha önce ele almıştık. İhtiyacınız olan tüm SCD yapılarını öğrenmek ve bunların MERGE ile nasıl yapılabildiğini öğrenmek ise yeni konuya başlamadan önce bu makalelere bir göz atmak isteyebilirsiniz. Eğer sadece Type 3 SCD ile ilgileniyorsanız bu durumda direkt olarak makalemize devam edebilirsiniz.

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

Bu makalemizde Slowly Changing Dimension Type 3 çözümümün ne olduğu ve SQL Server’da MERGE komutu ile nasıl ele alınabileceğini göstereceğiz. Diğer makalelerimizde olduğu gibi yine bu durumunu bir örnek demo üzerinde gerçekleştireceğiz. İsterseniz ilk olarak Type 3 SCD‘nin ne olduğunu ve Type 1 SCD ile Type 2 SCD‘den nasıl ayrıştığını bahsedelim.

Type 1 SCD ile kaynak tablomuzdan hedef tablomuza daha önce aktarımı yapılmış olan kayıtlar için gelen güncellemeler overwrite edilerek hedefte daha önce var olan kaydın üzerinde güncelleme yapılmaktadır. Dolayısıyla update işlemi ile var olan kayıt üzerinde güncelleme çalışmaktadır.

Type 2 SCD ile kaynak tablomuzdan hedef tablomuza daha önce aktarımı yapılmış olan kayıtlar için gelen güncellemeler verinin historical yani tarihsel geçmişini tutabilmek için versiyonlu bir şekilde tutulmaktadır. Bunu gerçekleştirmek için var olan kayıt pasif olarak işaretlenecek ve yeni gelen güncel bilgiyi içeren yeni bir satır hedef tabloya aktif olacak şekilde eklenecektir. Bu şekilde Type 2 SCD ile verinin tarihsel geçmişini de satır bazlı normalize bir yapıda tutulmaktadır.

Type 3 SCD ile ise kaynak tablomuzdan hedef tablomuza daha önce aktarımı yapılmış olan kayıtlar için gelen güncellemeler verinin yine benzer şekilde historical yani tarihsel geçmişini tutabilmek için kullanılabilir. Burada Type 3 SCD’nin Type 2 SCD’den farklı olan tarafı yeni gelen güncel bilgiyi tutmak için yeni bir satırda tutmak yerine denormalize bir şekilde yeni bir kolonda bu bilgiyi tutmaktadır. Dolayısıyla Type 3 SCD ile verinin tarihsel olarak geçmiş bilgisine tasarım anında eklenen versiyon adedi kadar olan ek kolonlar üzerinden erişilebilmektedir.

Type 3 Slowly Changing Dimension‘ın ne olduğundan kısaca bahsettik. Type 2 SCD’ye çok benzediğini ancak verinin historical geçmişini satır bazlı olarak değil sütun bazlı olarak tuttuğunu belirttik. Tabi ki bu şekilde kolon bazlı geçmişi tutmak sadece tablo tasarımına başlarken verinin kaç versiyon geriye yönelik tutulacağının belirlenmesi gerekliliği ve normalizasyon kurallarının uygulanmaması nedeniyle çoğu durumda pek tercih edilmemektedir. Genellikle Type 2 SCD ile verinin tarihsel geçmişini tutmak çok daha verimli ve performanslı olacaktır.

Bu kadar teorik bilgiden sonra hızlıca örneğimize geçelim. Diğer SCD type’larında kullandığımız tablo ve verileri gerektiği kadar değiştirerek bu örnekte de kullanacağız. Bunun için ilk olarak demomuzda kullanacağımız tablolarımızı create edelim:

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,
Salary_Previous_1 decimal,
Salary_Previous_1_ValidTo datetime,
Salary_Previous_2 decimal,
Salary_Previous_2_ValidTo datetime
)
GO

Burada daha önceki örneklerden farklı olarak hedef tablomuz olan EmployeeTarget tablosuna 4 adet yeni kolon ekledik. Biz örneğimiz için maaş bilgisi üzerinde yapılan değişikliklerin sadece son 2 versiyonunu tutmak istediğimiz için Salary_Previous_1 kolonu ile maaşın değişmeden önceki son halini yani birinci tarihsel geçmişini tutuyoruz. Aynı şekilde Salary_Previous_2 kolonunda da maaşın daha önceki hali olan ikinci tarihsel geçmiş versiyonunu tutuyoruz. Burada güncelleme yapıldığı tarihleri de Salary_Previous_1_ValidTo ve Salary_Previous_2_ValidTo kolonlarında tutacağız.

Oluşturduğumuz tablolarımıza örnek olması açısından bir kaç adet 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, NULL, NULL, NULL, NULL),
(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1, NULL, NULL, NULL, NULL),
(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1, NULL, NULL, NULL, NULL),
(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1, NULL, NULL, NULL, NULL)
GO

Burada hedef tablomuzda henüz verinin geçmiş bilgisi olmadığını simule etmek için son eklenen dört kolondaki değerleri NULL olarak bıraktık. Tablomuzu sorgulayalım ve işleme başlamadan önceki halini görelim:

sorgu_baslamadan_once

Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız hedef tablomuzEmployeeTarget 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.

Örneğimizdeki amacımız olan maaş bilgisinin tutulduğu Salary kolonu üzerinde yapılacak olan değişiklikleri Type 3 Slowly Changing Dimension (SCD) ile ele almak olduğu için MERGE komutumuzu bu senaryoyu karşılayacak şekilde yazacağız. Böylece Salary kolonu üzerinde güncelleme yapıldığında yeni bir satır eklenmeyecek, onun yerine güncel maaş bilgisi Salary kolonuna yazılacak, geçmiş maaş bilgisi de Salary_Previous_1 kolonuna aktarılacaktır.

İstediğimiz senaryoyu karşılayabilmek için gerekli olan MERGE komutu aşağıda bulunmaktadır:

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, s.IsActive)
WHEN MATCHED AND t.Salary <> s.Salary  THEN
	UPDATE SET  t.Salary = s.Salary,
				t.Salary_Previous_1 = t.Salary,
				t.Salary_Previous_1_ValidTo = GETDATE(),
				t.Salary_Previous_2 = t.Salary_Previous_1,
				t.Salary_Previous_2_ValidTo = t.Salary_Previous_1_ValidTo;

Yukarıdaki MERGE komutunu çalıştırdığımızda ve tablolarımız tekrar sorguladığımızda aşağıdaki gibi bir sonuç karşımıza gelecektir:

scd_type_3_sonra

Sonuçlardan görüleceği üzere Abdullah Altıntaş‘a ait maaş bilgisi değişikliğe uğradığı için ve bu durumu Type 3 SCD ile yönetmek istediğimiz için var olan kaydın Salary kolonu yeni güncel değeri almış ve eski maaş bilgisi Salary_Previous_1 kolonuna aktarılmıştır. Aynı zamanda işlemin gerçekleştiği zaman bilgisi de geçerlilik tarihini tutmak için Salary_Previous_1_ValidTo kolonuna yazılmıştır. Bu arada 5 no’lu id’ye sahip olan Şeydanur Sandıkçı da yeni gelen bir kayıt olduğu için son dört kolon NULL olacak şekilde EmployeeTarget tablosuna eklenmiştir.

Peki ilerleyen zamanlarda ilgili kişiye ait tekrar maaş bilgisi değiştiğinde durum ne olacak? Bunu test etmek için Abdullah Altıntaş‘ın kaynak tablomuz olan EmployeeSource tablosundaki maaş bilgisini 3000 olarak güncelleyelim:

update dbo.EmployeeSource
set Salary = 3000
where EmployeeID = 1

Ardından MERGE komutunu tekrar çalıştırıp neler olduğuna bakalım:

scd_type_3_sonra_2

Yukarıdaki sonuçlarda görüleceği üzere Abdullah Altıntaş‘a ait Salary kolonu en güncel versiyon olan 3000 değerini tutmaktadır. Değişen Salary bilgisi olan 2000 değeri Salary_Previous_1 kolonunda tutulurken, daha önceki maaş olan 1000 değeri ise Salary_Previous_2 kolonunda tutulmaktadır. Aynı zamanda değişikliklerin yapıldığı tarih bilgileri de Salary_Previous_1_ValidTo ve Salary_Previous_2_ValidTo kolonlarında tutulmaktadır. Biz örneğimizde Salary kolonuna ait değişikliklerin sadece son 2 versiyonunu tutacak şekilde tasarladık ancak istenilen durumlarda bu değer ihtiyaca göre tasarım anında değiştirilerek gereklilikler karşılanabilir.

Bu yazımızda Slowly Changing Dimension Type 3 işlemini MERGE komutu ile nasıl tasarlayabileceğimizi ele aldık. Aynı zamanda Type 1 SCD, Type 2 SCD ve Type 3 SCD karşılaştırması yaparak çalışma mekanizmalarının nasıl farklı olduğunu gösterdik. Yapmış olduğumuz örnek ile de Type 3 SCD‘ye bir senaryo üzerinden yaklaşarak sonuçlarını değerlendirdik. Bir sonraki yazımızda MERGE serisinin devamı olarak Type 4 SCD işlemini MERGE komutu ile nasıl yapabileceğimizi ele alacağız.

Umarım faydalı olur. Keyifli okumalar…

Yazar: Abdullah ALTINTAŞ

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

Yorum Yaz