Slowly Changing Dimension (SCD) with ODI

SCD nedir, linkteki tanımlamanın benzerini yapacağım için alıntılıyorum;

"Şimdi bir müşteri dimensionımız ve bir de gelirle ilgili bir fact tablomuz olduğunu varsayalım ve fact tablosunda tarihsel olarak hangi müşteriden ne kadar gelir elde edildiğinin tutulduğunu düşünelim. Bu senaryoda müşteri dimensionındaki medeni durumu da değişip bekardan evliye geldi diyelim. Şimdi bu adamın eski gelirleri bekarken idi ama adam şimdi evli. Problem burada başlıyor adamın bu özelliğini biz ne seviyede takip etmek istiyorsak ona göre bir yöntem izlemeliyiz.

Konuyla ilgili olarak temelde 3 farklı yaklaşım var.

1. Dimensiondaki bu değişimi hata düzeltmesi olarak algilamak yani son hali neyse hep oymuş gibi varsayip geçmiş bilgisini ezmek. Bu durumda yukardaki örnekten gidersek dimension tablosundaki müşteri kaydı aynen kalacak ve medeni durum kolonu bekar değil evli olacak. Etkisini düşünürsek, yine yukardaki senaryodan gidersek geçen yıl bekar insanlardan elde ettiğimiz gelir neydi sorusunun cevabını alamamiş olacagiz. Belirttiğimiz gibi değişimi mümkün olmayan kolonlarda bu yöntemi izlemek daha makul mesela cinsiyet, doğum yeri vs.

2. Bu yöntem tüm değişiklikler takip edilmek istendiğinde uygun olan bir yöntem: Her bir değişiklik için dimension tablosuna yeni bir surrogate key ile yeni bir kayıt eklemek. Bu durumda fact tablosuna gelen yeni kayıtlar yeni yaratılan dimension kaydına ait surrogate key ile gelecek böylece tüm değişiklikler fact tabloya da yansımış olacak.

3. Üçüncü yöntem de değişimi sadece bir defa veya birkaç defa olacak durumlar için uygun. Mesela product dimensionını düşünelim. Bir ürünün ürün grubu değişebilir. Pazarlama ekibi bir ürünü artık “ev ürünleri” değil de “teknolojik ürünler” grubu altında incelemek isterse bu yaklaşımı kullanabiliriz: Dimension tablosunda fazladan kolonlar bulundurup, önceki grubunu ve değişimi bu kolonlarda tutabiliriz. Yani tabloda “mevcut ürün grubu”, “önceki ürün grubu” ve “ürün grubu değişim tarihi” diye kolonlar bulunmalı."

Kaynak: Kurumsal Zeka

Esnek olduğu ve geçmiş bilgileri tuttuğu için SCD type 2 en çok tercih edilen yapı oluyor. Bunun ile ilgili ODI'de type 2 SCD oluşturulmasından ve geliştirip yapabileceklerimize biraz bakalım.

Oracle db örnek şemalarını yüklediğimizde gelen sh user ı altında bulunan channels tablosunu örnek olarak aldım;

CREATE TABLE CHANNELS
(
CHANNEL_ID NUMBER NOT NULL,
CHANNEL_DESC VARCHAR2(20 BYTE) NOT NULL,
CHANNEL_CLASS VARCHAR2(20 BYTE) NOT NULL,
CHANNEL_CLASS_ID NUMBER NOT NULL,
CHANNEL_TOTAL VARCHAR2(13 BYTE) NOT NULL,
CHANNEL_TOTAL_ID NUMBER NOT NULL
);

INSERT INTO SH.CHANNELS ( CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS, CHANNEL_CLASS_ID, CHANNEL_TOTAL,
CHANNEL_TOTAL_ID ) VALUES (
3, 'Direct Sales', 'Direct', 12, 'Channel total', 1);
INSERT INTO SH.CHANNELS ( CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS, CHANNEL_CLASS_ID, CHANNEL_TOTAL,
CHANNEL_TOTAL_ID ) VALUES (
9, 'Tele Sales', 'Direct', 12, 'Channel total', 1);
INSERT INTO SH.CHANNELS ( CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS, CHANNEL_CLASS_ID, CHANNEL_TOTAL,
CHANNEL_TOTAL_ID ) VALUES (
5, 'Catalog', 'Indirect', 13, 'Channel total', 1);
INSERT INTO SH.CHANNELS ( CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS, CHANNEL_CLASS_ID, CHANNEL_TOTAL,
CHANNEL_TOTAL_ID ) VALUES (
4, 'Internet', 'Indirect', 13, 'Channel total', 1);
INSERT INTO SH.CHANNELS ( CHANNEL_ID, CHANNEL_DESC, CHANNEL_CLASS, CHANNEL_CLASS_ID, CHANNEL_TOTAL,
CHANNEL_TOTAL_ID ) VALUES (
2, 'Partners', 'Others', 14, 'Channel total', 1);
COMMIT;

SCD tablosu olacak channels_hist tablosunu oluşturdum;

CREATE TABLE CHANNELS_HIST
(
CHANNEL_ID NUMBER NOT NULL,
CHANNEL_DESC VARCHAR2(20 BYTE) NOT NULL,
CHANNEL_CLASS VARCHAR2(20 BYTE) NOT NULL,
CHANNEL_CLASS_ID NUMBER NOT NULL,
CHANNEL_TOTAL VARCHAR2(13 BYTE) NOT NULL,
CHANNEL_TOTAL_ID NUMBER NOT NULL,
ETL_START_DATE DATE,
ETL_END_DATE DATE,
CURRENT_FLAG NUMBER
);

Veritabanında channels_hist tablosunu oluşturduktan sonra ODI'de model tabında channels_hist tablosunu reverse edip özelliklerinden OLAP type: Slowly changing dimension olarak seçilir.

Model yan penceresinde Channel_hist modelinin columns kısmını açıp channel_id yi seçiyorum, description alanında Slowly Changing Behavior:Natural Key seçiyorum.

CHANNEL_DESC CHANNEL_CLASS CHANNEL_CLASS_ID CHANNEL_TOTAL CHANNEL_TOTAL_ID alanları için Slowly Changing Behavior: Add row on change seçiyorum.

ETL_START_DATE alanı Slowly Changing Behavior: Starting timestamp,
ETL_END_DATE Slowly Changing Behavior: Ending timestamp,
CURRENT_FLAG Slowly Changing Behavior: Current record flag olarak değiştiriyorum.

INT_CHANNELS_HIST interface inde flow kısmında IKM Slowly Changing Dimension seçilir.

Interface execute edildiğinde etl_end_date alanında mapping de to_date('29990101', 'YYYYMMDD') yazdığım halde KM içinde kodlandığı için 01/01/2400 basılıyor.

Channels_hist tablosunun ilk durumu şu şekilde oluyor;

select * from CHANNELS_HIST

CHANNEL_ID CHANNEL_DESC CHANNEL_CLASS CHANNEL_CLASS_ID CHANNEL_TOTAL
CHANNEL_TOTAL_ID ETL_START_DATE ETL_END_DATE CURRENT_FLAG

3 Direct Sales Direct 12 Channel total 1 18.10.2011 21:53:17 1
9 Tele Sales Direct 12 Channel total 1 18.10.2011 21:53:17 1
5 Catalog Indirect 13 Channel total 1 18.10.2011 21:53:17 1
4 Internet Indirect 13 Channel total 1 18.10.2011 21:53:17 1
2 Partners Others 14 Channel total 1 18.10.2011 21:53:17 1

Bazı alanlarda datada değişiklik yapıyorum;

UPDATE CHANNELS
SET CHANNEL_DESC='Telephone'
where channel_id=9;

UPDATE CHANNELS
SET CHANNEL_DESC='Direct'
where channel_id=3;

commit;

Interface i tekrar çalıştırdığımızda yeni datayı tutan 2 yeni kayıt elde etmiş oluyoruz, eski kayıtlar current_flag=0 olarak işaretleniyor, ETL_END_DATE=18.10.2011 23:32:07 olarak değiştirildi ve yeni kayıtlarda data değişmiş olarak görüyoruz:

CHANNEL_ID CHANNEL_DESC CHANNEL_CLASS CHANNEL_CLASS_ID CHANNEL_TOTAL CHANNEL_TOTAL_ID ETL_START_DATE ETL_END_DATE CURRENT_FLAG
--Eski kayıtlar
3 Direct SalesDirect 12 Channel total 1 18.10.2011 23:11:30 18.10.2011 23:32:07 0
9 Tele Sales Direct 12 Channel total 1 18.10.2011 23:11:30 18.10.2011 23:32:07 0

--Yeni kayıtlar
3 Direct Direct 12 Channel total 1 18.10.2011 23:32:07 01.01.2400 1
9 Telephone Direct 12 Channel total 1 18.10.2011 23:32:07 01.01.2400 1

Bu yapıda channels tablosunda update veya insert tarihini tutan bir alan olsaydı, etl_start_date ve etl_end_date olarak bu alanı kullanabilirdik. Fakat kaynak tabloda tarih olmadan da SCD yapısında tarih elde etmiş olduk.

Bu durumda Channels_hist tablosunda Current_flag=1 olan datalar channels tablosunu bize verir.

select * from CHANNELS_HIST

where current_flag=1

Channels tablosuna yeni bir kayıt ekliyorum;

insert into sh.channels

values(10, 'Brochure', 'Indirect', 13, 'Channel total', 1);

commit;

Channels_hist tablosuna eklenmesi için interface i tekrar çalıştırdım. Channels_hist tablounda channel_id=10 olarak yeni bir kayıt eklenmiş oldu.

select * from channels_hist

where channel_id=10

CHANNEL_ID CHANNEL_DESC CHANNEL_CLASS CHANNEL_CLASS_ID CHANNEL_TOTAL CHANNEL_TOTAL_ID ETL_START_DATE ETL_END_DATE CURRENT_FLAG

10 Brochure Indirect 13 Channel total 1 18.10.2011 23:45:11 01.01.2400 1

Yeni eklediğim kaydı silip SCD yapısında değişikliği nasıl aldığına bakıyorum;

delete from channels

where channel_id=10;

commit;

Channels_hist tablosunda silinen kayıt için değişiklik yapılmıyor, bu durum için IKM Slowly changing dimension definition kısmında bu KM için ilgili kısıtlama yer alıyor:

- As this Knowledge Module is dedicated to Datawarehouse Projects (where data should not be removed), when using it with a journalized Source Datastore, it is not possible to synchronize deletions. Therefore, data should be manually filtered to exclude any source delete events (add a filter on JRN_FLAG = 'I').

Üye Kayıt Formu

Üye Girişi

Slowly Changing Dimension (SCD) with ODI

Bu Makaleleride Okuyabilirsiniz!

Jale Ozgur