Goal
The session log file(s) has value for $$LAST_EXTRACT_DATE as 'MM/DD/YYYY 12:00:00 AM'. Why is the correct timestamp not picked from the last REFRESH_DATE of the table?How to control the value for DAC source system parameter $$LAST_EXTRACT_DATE?
Solution
1) The $$LAST_EXTRACT_DATE source system parameter had Format as "MM/DD/YYYY" so the exact hour, minutes and seconds were not pulled from REFRESH DATE (Setup--> Physical Data Sources --> Refresh Dates) for a given table rather it was defaulted to 12:00 AM.
The format of $$LAST_EXTRACT_DATE was changed from "MM/DD/YYYY" to "YYYY-MM-DD HH24:MM:SS" in order to get the date timestamp value for the parameter. Thereafter the data loads picked the correct date timestamp value while populating $$LAST_EXTRACT_DATE parameter.
2) The execution plan has "Prune Days" property that can be set to number of day(s) to be deducted from the last Refresh Date but if user want to specify few hours as prune value then the 'Prune Days' property of execution plan cannot be used.
Steps to control $$LAST_EXTRACT_DATE:
1) Create a new source system parameter "REFRESH_DATE" as timestamp.
Name: REFRESH_DATE
Date Type: Timestamp
Variable: @DAC_SOURCE_REFRESH_TIMESTAMP
Function: Custom format
Format: YYYY-MM-DD HH24:MM:SS
2) Modify $$LAST_EXTRACT_DATE source system parameter properties to:
a) Set the value to SQL
b) In SQL dialog choose DBCONNECTION_OLAP (or any other connection that points to Oracle database) and enter the following string :
to_date('@DAC_REFRESH_DATE', 'YYYY-MM-DD HH24:MI:SS') -numtodsinterval(2,'HOUR') FROM DUAL;
Note: The above SQL is designed to run against Oracle database.
c) Make it custom format YYYY-MM-DD HH24:MM:SS
This will ensure that Last extract date will be refresh date time minus 2 hours.
If you need it to be refresh date minus 3 hours, then the sql can be:
to_date('@DAC_REFRESH_DATE', 'YYYY-MM-DD HH24:MI:SS') -numtodsinterval(3,'HOUR') FROM DUAL;
Note: Apply the above change(s) in Development/Test environment and test the data load.
