DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 160

Points: 1911

Post Posted: Wed Jun 13, 2018 8:12 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi All,

I have the below scenario, I have to build(insert) the address dimension as per the below scenario

1. Compare values(Addr1,City,State,Country,PostalCode) against existing customer address dimension(Addr1,City,State,Country,PostalCode), no key columns. if it is not available then insert. I can do this by using lookup stage with the help of isnull function to segeregate records.

2. Compare(Addr1,City,State,Country,PostalCode) against existing dimension, if any of the values has been changed(Meaning: if city is null for the first order, and for another order city is having values), we again need to insert(treat it as an new record) that into the customer address dimension.

Kindly let me knw your thoughts on how to achieve these scenarios, especially 2.
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42585
Location: Denver, CO
Points: 219186

Post Posted: Wed Jun 13, 2018 8:35 am Reply with quote    Back to top    

So... actually a Type 2 dimension? There's a stage for that.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Wed Jun 13, 2018 2:11 pm Reply with quote    Back to top    

more specifically use a SCD stage or Change Capture .
Rate this response:  
Not yet rated
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 160

Points: 1911

Post Posted: Thu Jun 14, 2018 1:54 am Reply with quote    Back to top    

Hi Craig,

It is not really SCD Type2. Always we will need to insert a new row to treat it as new record eventhough some columns has been updated as we are doing incremental load.

Eg1:Yesterday record: Insert the below row into Cust_Addr_Dim as it has not the below value in dimension

Customer,Order_id,AddrLine1,AddrLine2,City,State,Country,Postalcode
1,22,24 Dickson Rd,Null,Houston,TX,USA,71104

Eg2:CurrentDay's Reocrd(Incremental Data)

Customer,Order_id,AddrLine1,AddrLine2,City,State,Country,Postalcode
1,33,24 Dickson Rd,Bulevd Street,Houston,TX,USA,71104

We will need to treat this as a new record as it has different order from the same customer with the AddrLine2 has values.

I am doing CDC against Cust_Addr_DIM(as it contains only address columns, no key columns in that dimension(customer,order_id etc..).

All i have compared with existing values is (AddrLine1,AddrLine2,City,State,Country,PostalCode), from here i am struggling with Eg2 scenario.

Note: If the currentDay(Eg2)(Incremental Data) matches with the existing values(AddrLine1,AddrLine2,City,State,Country,PostalCode)), do nothing else insert.

Thanks,
S.R
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42585
Location: Denver, CO
Points: 219186

Post Posted: Thu Jun 14, 2018 6:29 am Reply with quote    Back to top    

Okay. You'd need to expand on the specifics of what issues / struggles you are having as it sounds fairly straight-forward to me. But I'm sure I'm missing something about the way your data is architected...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Fri Jun 15, 2018 12:27 pm Reply with quote    Back to top    

Its still remains as chulett says , a special case similar to SCD2, maybe you should just enable change capture stage for Updates & Inserts and run both that data[sets] as an Insert. Drop Copies ( same addresses) and Deletes ( No records in Input , but exist as reference) . MAke sure Not to compare Nulls ( Handle nulls as Empty strings Or 0 ( integer cols) before comparing ) on both ends. Your key for address should be composite of all Value columns needed for that address.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42585
Location: Denver, CO
Points: 219186

Post Posted: Wed Jun 20, 2018 10:10 am Reply with quote    Back to top    

Did you work this out?

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 160

Points: 1911

Post Posted: Thu Jun 21, 2018 3:36 am Reply with quote    Back to top    

Hi Craig / Ramesh,


Yes..I have used CDC stage to get that done. Thanks for your valuable inputs.


Note: Took a week off..and haven't used my laptop for almost a week. Very Happy




Regards,
S.R
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours