Team, I have created a "Oracle View" just to get the counts from the table and added report_date as sysdate, when I run the Staged Data--> Snapshot manually it is refreshing the data and I see the latest count and the current datetime on the reportdate column,. but when I try to use the Export and use the Xnapshot to load the target table, it is not refreshing the snapshot it is using the old result to load the target. so I created the process just to refresh the snapshot and created a job to refresh automatically.
Is there any other work around to achieve the automatic refresh on snapshots?
As Nick says, whenever you need a snapshot to refresh automatically you do this using a job that includes an enabled snapshot task, or by setting the process execution preferences of a process, assuming you just want to click the Run button on the process and refresh the source data every time.
Thanks Mike and Nick for your prompt help!! Here is what I am doing...
Staged Data: Snapshot (using SQL option) called "sn_2_table_SQL_Join"
SUM (a.rowcount_i - b.rowcount_i) A_B_ROWCOUNT_DIFF
FROM TABLE_A A, TABLE_B B
WHERE A.OBJECT_N = B.OBJECT_N
GROUP BY A.OBJECT_N , A.ROWCOUNT_I, B.ROWCOUNT_I
ORDER BY A.OBJECT_N
Created a new process called "p_Test"
My simple intention is to run the job which will execute the process and refresh the snapshot.
I ran the "j_Test" and i checked it in Task area it ran successfully, i went and opened the Process (p_Test) and clicked the Source (Reader) to see the data under the Results Browser, it shows the old data set, I also went and checked the snapshot (sn_2_table_SQL_Join) and checked the data on the Result Browser, it still shows the old set data. i confirmed it by checking the Report Date column which is SYSDATE.
If i go and right click on Staged Data (sn_2_table_SQL_Join) snapshot, it is refreshing fine.
I would like to know how to automate the refresh....
Please let me know if you have any questions.
Above, you have created a job that does not refresh the snapshot.
When you go into the process execution preferences, you need to enable the *snapshot* task, not the process task. Running the process task means running it on the currently populated staged data. Running the snapshot task (then the process task - which you can't disable in process execution preferences) means refreshing the data when you do so.
You can then save the process execution preferences, meaning every time you click on the run button on the process you refresh the snapshot.
Alternatively, create a job that runs the snapshot task as well as the process task.
Note that the 'Save as Job' option in process execution preferences has a known issue in this circumstance where it does not link the snapshot and process correctly, resulting in the error 'Job is attempting to read and write to an object in the same phase, please check configuration' when attempting to run the saved job. You can work around this by deleting and re-adding the snapshot task from the saved job, or just create the job manually.