In warehouse it is a best practice to keep a job control table for tracking all your activities like Dimension load time, fact load time, Stage load time like that and status of each load.
In ODI we implemented the process through some procedures and variables and adding that into load plan for inserting values properly.
1) Create a batch control table to generate Row_Id for each load. You can insert a number and a time stamp to the table, and this row_id is used as a row_id for the main table.
Variable Code :select nvl(max(BATCH_ID),0)+1 from ETL_BATCH_CTRL
Procedure Code:insert into ETL_BATCH_CTRL (BATCH_ID,LOAD_DATE) values (#v_BATCH_ID,CURRENT_DATE)
2)Create a Job control table and insert initial flag values as N for each load and start time as current timestamp.
Code:
INSERT INTO JOB_CNTRL_TBL
(ROW_ID,STG_LOAD_FLAG,DIM_LOAD_FLAG ,FACT_LOAD_FLAG ,PROC_LOAD_FLAG,STG_LOAD_STRT_TIME,LOAD_DATE)
VALUES ('#v_BATCH_ID','N','N','N','N',CURRENT_TIMESTAMP,
TO_NUMBER(TO_CHAR(CURRENT_DATE,'YYYYMMDD')))
Update step after stage load:
UPDATE JOB_CNTRL_TBL SET STG_LOAD_FLAG='Y', STG_LOAD_END_TIME=CURRENT_TIMESTAMP,
DIM_LOAD_STRT_TIME=CURRENT_TIMESTAMP WHERE ROW_ID=#v_BATCH_ID
like this after each step we update the flags. and finally all flags will set to Y by that way we can make sure the process is completed.
