topic Re: How can I schedule the loading of an Oracle table on a daily basis. in SAS Visual Analytics

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

ck7939 — Wed, 22 Apr 2015 15:43:03 GMT

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?

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

Kurt_Bremser — Thu, 23 Apr 2015 06:27:38 GMT

How do you load the table at the moment?

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

ck7939 — Thu, 23 Apr 2015 13:07:19 GMT

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.

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

Kurt_Bremser — Fri, 24 Apr 2015 07:17:11 GMT

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)

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

ck7939 — Fri, 24 Apr 2015 13:23:15 GMT

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

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

Kurt_Bremser — Sat, 25 Apr 2015 12:15:22 GMT

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.

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

jakarman — Sat, 25 Apr 2015 19:27:27 GMT

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.