How can I schedule the loading of an Oracle table on a daily basis.

[ck7939]

We have reports that depend on data that changes daily. We want to be able to schedule the loading of a table from an Oracle table on a daily basis and have it replace the previously loaded table of the same name, therefore updating the reports based on this data. Is there any way to schedule such a data load?

[Kurt_Bremser]

How do you load the table at the moment?

[ck7939]

We currently have the table loaded manually using the GUI to import an oracle table. When there is new data, I import it again and remove the old table. Then, in the report, it prompts me to replace the data with a new table, so I choose the updated table.

I would like to set a schedule, to import from the table once per day, and rather than having to delete the table and be prompted to replace it, I would like the report to simply pick up the new data (presumably having the same name as the old table) and change the visualizations respectively.

[Kurt_Bremser]

This becomes a matter of the method you use for the import. If you connect to Oracle from Enterprise Guide (ie via ODBC), and EG then transfers the data to the SAS, where the SAS workspace server runs, that will be quite hard to automate.

Please post some more information:

- where is the workspace server located (remote or locally, if remote which OS is used on the server)

- how do you connect to Oracle (locally via ODBC, locally via Oracle client, from the workspace server with SAS/ACCESS to Oracle)

[ck7939]

The workspace server is located remotely. It runs Centos 6.6. We connect to oracle via Oracle client running on the workspace server.

[Kurt_Bremser]

Then it is best to take your import from oracle code (which utilizes SAS/ACCESS to Oracle), write it to the CentOS server as a .sas file (Files section in Enterprise Guide), and then use the crontab to run it from batch (with /....path_to_SASFoundation.../sas $HOME/yourjob.sas -log $HOME/yourjob.log)

You may need to do some tweaking so that the SAS batch process runs with the necessary configuration parameters that you have in the workspace server.

[jakarman]

Review: How to Auto Load Data to Memory in SAS Visual Analytics 7.1 - YouTube  There is a dedicated approach for that.

Interesting point at 4:10 as it show the OS level files to interact for some settings
VA is different as with the in memory approach as the more common  Appserver services (WS SP PWS Batch Connect) it also using his own dedicated proc not to old common known ones.