SAS Visual Analytics Data Load from Oracle

[avinashaecwb]

Hi

I have just started working on SAS. At my organization all data is on Oracle DB and I have to create analytics on that. The tables have around 9 million rows and 50 columns each. Mostly I have to use 7 such tables. Before any reporting, I was trying to pull the data into SAS LASR metadata. My SAS VA installation is on single server. From what I know, I followed below steps:

1) Load the tables once through SAS VA Data Import functionality.

2) Created autoload scripts to unload and reload the tables using unix cronjob. Two .sas scripts for unload and then reload is executed nightly through cronjob. 

This thing works.

However:

The tables get updated every minute. For any reporting, loading the entire table again and again in SAS takes time and memory. I was looking for help/suggestions to make it more effective. I have thought of below option. Could you please guide on this and/or suggest any other better solution:

Create PL/SQL procedure having all the required joins and store the result in a seperate table on Oracle DB itself. The script will also update the table based on modified date. only records updated would get modified and entire table need not be re-populated. Then pull this final table data into SAS LASR. Again, in this method too, the final table would contain huge volume of data. Is there any way to NOT purge the existing table on SAS LASR but only insert new entries ? Would Proc Merge help ? Is there any way to identify updated rows and then update it in SAS LASR?

[JuanS_OCS]

Hello,

I understand your dilema. As SAS Data Builder generates automatic code, this option would not be probably your best option, only for the initial load of the big table.

For incresing performance during the updates (inserts, as you mention), I would recommend an external process. Some options I can think about:

1. If you have license for Enterprise Guide, or an additional SAS Server (as SAS Data Integration), I would use SAS code for the updates of the tables.
2. If you create a PL/SQL procedure for the initial load, and you generate a table (big), you could place that table or csv on the autoload folder. And in this scenario, you could also create a second PL/SQL procedure, for the generation of the (little) table/csv for updates/inserts only. That little table you can place it on the Autoload folder, on the Append sub-folder. 
3. I am not aware of an option to put on the SAS Data Builder Query an option to load everything on the initial load and just update the tables when needed, not load/generate the full table again. On this point, maybe other members of the community can help you.

I hope this may help you a bit.

Best regards,

Juan

[SASKiwi · Accepted solution]

I suggest that the way to do updates and appends in LASR memory is with some custom SAS code. PROC IMSTAT is designed to do this:

http://support.sas.com/documentation/cdl/en/inmsref/67953/HTML/default/viewer.htm#p10dosb1fybvpzn1hw...

I suspect you would need to figure out in advance in Oracle what rows have been updated and which rows have been appended. You can then load separate update and append tables into LASR memory and use IMSTAT to apply these to your main table.