Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

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

Reply
Occasional Contributor
Posts: 5

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

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?

Esteemed Advisor
Posts: 6,646

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

How do you load the table at the moment?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

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

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.

Esteemed Advisor
Posts: 6,646

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

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

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

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

Esteemed Advisor
Posts: 6,646

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,206

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

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.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 6 replies
  • 587 views
  • 0 likes
  • 3 in conversation