4 Replies Latest reply on Oct 14, 2013 11:09 AM by Mike-Matthews-Oracle

    How to refresh oedq - snapshots automatically

    Bala Govi

      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?




        • 1. Re: How to refresh oedq - snapshots automatically
          Nick Gorman-Oracle

          To ensure that you are always using the latest data, you can either:

          1) Include the snapshot in all relevant jobs


          2) Use an 'external data' lookup reference data to query the database directly




          • 2. Re: How to refresh oedq - snapshots automatically

            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.

            • 3. Re: How to refresh oedq - snapshots automatically
              Bala Govi

              Thanks Mike and Nick for your prompt help!! Here is what I am doing...


              Step 1:

              Staged Data: Snapshot (using SQL option)  called "sn_2_table_SQL_Join"




                       SYSDATE REPORT_DT,

                       A.rowcount_i TAB_A_ROWCOUNT,

                       B.rowcount_i TAB_B_ROWCOUNT,

                       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


              ORDER BY A.OBJECT_N


              Step 2:


              Created a new process called "p_Test"

              1. Reader as sn_2_table_SQL_Join
              2. Clicked the "Run Preferences" and enabled the checkbox for the Process, it shows with yellow lighting symbol on the preview area.
              3. Clicked "Save as Job" and name it as "j_Test"


              Step 3:


              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.



              • 4. Re: How to refresh oedq - snapshots automatically

                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.