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"
- Reader as sn_2_table_SQL_Join
- Clicked the "Run Preferences" and enabled the checkbox for the Process, it shows with yellow lighting symbol on the preview area.
- Clicked "Save as Job" and name it as "j_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.