one time nad go foreward loading in a single job

dr46014 · Post by **dr46014** » Fri Feb 09, 2007 8:49 pm

Our requirement is like this:
For the fields CUST_ID , CURRENT_ACCOUNT_NUMBER from the source table CUST_ACCT_DIM ,nonmon information will be captured from different sources and kept in the table CUST_C360_DIM

Layout of CUST_ACCT_DIM
******************************
CUST_ACCT_DIM_KEY
CUST_ID
CURRENT_ACCOUNT_NBR
EFFECTIVE_END_DATE(date on which the record gets updated
(for a particular customer id if effective end date is null then it is considered to be the latest information(correct information))
LOAD_DATE(date on which the record gets inserted)
CLIENT_ID
Layout of CUST_C360_DIM
*************************
CUST_ID
CURRENT_ACCOUNT_NBR
NEW_ACCT_FLAG
LOAD_DATE
INSERT_DATE
CUSTOMER_ROLE
MEMBER_SEQUENCE_NUMBER
NONMON_NUMBER
NONMON_SUB_TRAN_CODE

For onetime load:Along with the nonmon attributes we load the table CUST_C360_DIM taking the columns CUST_ID and CURRENT_ACCOUNT_NBR from CUST_ACCT_DIM where EFFECTIVE_END_DATE is NULL. At the same time LOAD_DATE, INSERT_DATE and NEW_ACCT_FLAG columns will be populated with the default values SYSDATE,SYSDATE and Y respectively.
For go foreward load:We take the cust_id and Current_account_nbr from CUST_ACCT_DIM with latest LOAD_DATE and compare with the cust_id and Current_account_nbr s existing in the CUST_C360_DIM and based on that we do the updating or insertion.

I have to create one job both for onetime and go foreward load.I am using ODBC satge as source for table CUST_ACCT_DIM.I am doing a look up on CUST_C360_DIM.For one time loading its not a problem as CUST_C360_DIM will be empty and load everyting in CUST_C360_DIM as per the logic given above.But my problem is to achive the go foreward load logic within the same job.How can i use the same look up to compare the the latest records with the custid and accountnumberbr each week as this loading will be on weekly basis.

ODBC------------------------------------ODBC(insert)
|
|look up
ODBC..............................................ODBC(update)

Please help me to achieve the two functionalities in a single job.If you will provide any sample job structure it will be very helpful.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 10, 2007 4:58 am

I would rather advise you to create two separate jobs, for ease of future maintenance if for no other reason.

chulett · Post by **chulett** » Sat Feb 10, 2007 9:00 am

Create two if you need to handle the volumes differently, but your 'go forward' job design should be able to handle the 'one time' load as well.

dr46014 · Post by **dr46014** » Sat Feb 10, 2007 11:28 am

yes...handling this situation with two jobs is a good and effective idea.but as per my requirement i need to create a single job to handle the situation.please advise me

ray.wurlod · Post by **ray.wurlod** » Sat Feb 10, 2007 1:58 pm

Get a better requirement.

Seriously.

chulett · Post by **chulett** » Sat Feb 10, 2007 4:26 pm

Or just build the daily version. It should work for the initial load, just find nothing to update. And will be probably be much much slower than it would be if designed as a separate job.