BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avinashaecwb
Obsidian | Level 7

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.

 

 

 

 

View solution in original post

2 REPLIES 2
JuanS_OCS
Amethyst | Level 16

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
PROC Star

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.

 

 

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2101 views
  • 0 likes
  • 3 in conversation