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
-
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 -
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"?>
<Graph created="Wed May 02 10:25:31 CEST 2007" guiVersion="1.6" licenseType="Unlicensed clover.GUI" modified="Wed May 02 18:11:49 CEST 2007" name="SCDDataIntersection" revision="1.51">
<Global>
<Metadata id="Metadata0">
<Record name="Product" recordSize="-1" type="delimited">
<Field delimiter=";" name="product_id" nullable="true" shift="0" type="integer"/>
<Field delimiter=";" name="brand_name" nullable="true" shift="0" type="string"/>
<Field delimiter="\r\n" name="product_name" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Connection dbConfig="connectionMySql.cfg" id="DBConnection1" type="JDBC"/>
</Global>
<Phase number="0">
<Node enabled="enabled" guiHeight="0" guiName="Data Intersection" guiWidth="0" guiX="370" guiY="20" id="DATA_INTERSECTION0" joinKey="product_id" type="DATA_INTERSECTION">
<attr name="transform">${out.0.product_id} = ${in.0.product_id};
${out.0.brand_name} = ${in.0.brand_name};
${out.0.product_name} = ${in.0.product_name};
</attr>
</Node>
<Node dbConnection="DBConnection1" enabled="enabled" guiHeight="0" guiName="Dimension" guiWidth="0" guiX="20" guiY="281" id="DB_INPUT_TABLE0" sqlQuery="select * from product_dimension" type="DB_INPUT_TABLE"/>
<Node dbConnection="DBConnection1" enabled="enabled" guiHeight="0" guiName="Source" guiWidth="0" guiX="20" guiY="20" id="DB_INPUT_TABLE1" sqlQuery="select * from product_source" type="DB_INPUT_TABLE"/>
<Node dbConnection="DBConnection1" dbTable="product_dimension" enabled="enabled" fieldMap="product_id=product_id;brand_name=brand_name;product_name=product_name" guiHeight="0" guiName="Insert" guiWidth="0" guiX="545" guiY="20" id="DB_OUTPUT_TABLE0" sqlQuery="insert into product_dimension (product_id,brand_name,product_name) values (?,?,?)" type="DB_OUTPUT_TABLE"/>
<Node cloverFields="brand_name;product_name;product_id" dbConnection="DBConnection1" dbTable="product_dimension" enabled="enabled" guiHeight="0" guiName="Update" guiWidth="0" guiX="545" guiY="107" id="DB_OUTPUT_TABLE2" sqlQuery="update product_dimension set brand_name=?,product_name=? where product_id=?" type="DB_OUTPUT_TABLE"/>
<Node cloverFields="product_id" dbConnection="DBConnection1" enabled="enabled" guiHeight="0" guiName="Delete" guiWidth="0" guiX="545" guiY="194" id="DB_OUTPUT_TABLE3" sqlQuery="DELETE FROM product_dimension WHERE product_id=?" type="DB_OUTPUT_TABLE"/>
<Node enabled="enabled" guiHeight="0" guiName="Sort" guiWidth="0" guiX="195" guiY="20" id="SORT0" sortKey="product_id" type="SORT"/>
<Node enabled="enabled" guiHeight="0" guiName="Sort" guiWidth="0" guiX="195" guiY="281" id="SORT1" sortKey="product_id" type="SORT"/>
<Edge fromNode="DATA_INTERSECTION0:0" guiBendpoints="" id="Edge9" inPort="0 (Insert)" metadata="Metadata0" outPort="0 (Data Intersection)" toNode="DB_OUTPUT_TABLE0:0"/>
<Edge fromNode="DATA_INTERSECTION0:1" guiBendpoints="" id="Edge10" inPort="0 (Update)" metadata="Metadata0" outPort="1 (Data Intersection)" toNode="DB_OUTPUT_TABLE2:0"/>
<Edge fromNode="DATA_INTERSECTION0:2" guiBendpoints="" id="Edge11" inPort="0 (Delete)" metadata="Metadata0" outPort="2 (Data Intersection)" toNode="DB_OUTPUT_TABLE3:0"/>
<Edge fromNode="DB_INPUT_TABLE0:0" guiBendpoints="" id="Edge0" inPort="0 (Sort)" metadata="Metadata0" outPort="0 (Dimension)" toNode="SORT1:0"/>
<Edge fromNode="DB_INPUT_TABLE1:0" guiBendpoints="" id="Edge2" inPort="0 (Sort)" metadata="Metadata0" outPort="0 (Source)" toNode="SORT0:0"/>
<Edge fromNode="SORT0:0" guiBendpoints="" id="Edge1" inPort="0 (Data Intersection)" metadata="Metadata0" outPort="0 (Sort)" toNode="DATA_INTERSECTION0:0"/>
<Edge fromNode="SORT1:0" guiBendpoints="" id="Edge8" inPort="1 (Data Intersection)" metadata="Metadata0" outPort="0 (Sort)" toNode="DATA_INTERSECTION0:1"/>
</Phase>
</Graph> -
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();
}
Please sign in to leave a comment.
Comments 3