Implementing scd-I with intersection

CloverDX Platform
1

I am trying 2 implement the SCD Type-I using the intersection component.Though i am able 2 separate out the changed data into different streams i dont know how 2 update the dimension tables in the mysql DW please send a detailed suggestion as 2 how 2 do this .Thank u

2

hai Tomas Erben thanx 4 the reply that solved my problem.could u please let me know how 2 implement type-II as well as III u dont have to give a detailed description but let me know how to implent them using the available transformers.please help me .

also i have another thing in mind how & why shud i keep track of changing columns on dimensions.is there no other way?

thanks in advance

3

See graph below, where TypeI solved for product dimension (product_id,brand_name, product_name). Chech node id=“DB_OUTPUT_TABLE2” especially, where updates from data intersection are made.

Tomik

<?xml version="1.0" encoding="UTF-8"?> ${out.0.product\_id} = ${in.0.product\_id}; ${out.0.brand\_name} = ${in.0.brand\_name}; ${out.0.product\_name} = ${in.0.product\_name};
4

Hi, one of way how to implement SCD TypeII is usage of join component with left outer join type where your source is master and dimension is slave (assuming that we process only records with key existing in both tables - output from data intersection). Join key and slave key has to include all fields which need to be compared (f.e. drive - product_id_source;brand_name_source;product_name_source slave - product_id_dim;brand_name_dim;product_name_dim). Output metadata contain all fields from master and also indicator indicating if record exists in master and slave or in master only (possible code written in transformation language bellow). Filtering records existing only in master are new records which has to be inserted into dimension with new key (could be generated in Clover using sequences or in database). Type III could use the same principle only update substitute insert and dimension structure is different.

Tomas

//TL

int indicator;

function TestRecord (){
if (isnull($ProductDim.product_id_dim)==true){indicator=1;}else{indicator=2;}
return indicator;
}

function transform(){

$ProductAndNullIndication.product_id_source := $ProductSource.product_id_source;
$ProductAndNullIndication.brand_name_source := $ProductSource.brand_name_source;
$ProductAndNullIndication.product_name_source := $ProductSource.product_name_source;
$ProductAndNullIndication.product_id_dim := TestRecord();
}