Dataset containing pass-through query not updated on autoload

[pedrammobedi]

I am new to SAS and VA. I am trying to load the datasets I want in the Autoload folder so that the data is updated every 15 minutes. The autoload process is run and I see that every new sas file (.sas7bdat) that I put in the autoload folder shows up in VA. The problem is that the loaded data seems to be a snapshot of the results of the first time I created the table in SAS. 

So here is the query I wrote in my SAS file:

proc sql;
connect to odbc as mycon
(datasrc=dsPedramSample user=pedram password=XXXXXXXX);
CREATE TABLE pedramTestTable as 
select *
from connection to mycon
(select top 5 * from [SAS_Datamart].[dbo].[pedramTest]);
disconnect from mycon;
quit;

After running the above query, I copy the resulted SAS file to the autoload folder and run the autoload scheduled task again. I go to VA and it looks ok. Then I go to the pedramTest table in the database and change some of the values and then run reload again. But the information is not changed. 

What am I doing wrong?

[JuanS_OCS]

Hi @pedrammobedi,

not sure if I understand. Let me refresh here what I could understand, so far.

The initial load your procedure is:

- Run a code that creates a SAS table from a database table.

- Copy that table to SAS VA AutoLoad.

- Run AutoLoad

- Check if the table is loaded within SAS VA

The refresh/update procedure is:

- Change some values on the database

- Run the AutoLoad again

- Check if the table is loaded within SAS VA.

Is my understanding correct? If it is right, then you are missing the refresh of the SAS table, by running the code and copying the table to SAS VA.

If it is wrong, I would appreciate some additional information 🙂

[pedrammobedi]

I think you understood correct.

What I need is a way to run the script in that SAS file (the same refresh you mentioned). So I am looking for a script to refresh that table. Can you help me with that?

[JuanS_OCS]

Thanks for your feedback 🙂 Great, then we are on the right track.

You can do this on several ways. I will describe some options, but there are more and you can select the one as you prefer:

1- Trigger Autoload with a remote command.

Autoload is nothing else but another script (sh on Linux or bat on Windows), which you can execute remotely. I would ask for support and oppinion of your SAS or system administrators for this. This is an easy way, but sometimes it is not allowed by security policies.

2- Trigger Autoload by a file listener.

You can schedule an additional script on your VA server, which can wait until it finds an empty file which will act as a trigger to activate the AutoLoad.

Then you only need to ensure to generate and copy the file when you want to autoload. And clean that file when not.

3- Load the data into your "normal" LASR server, instead of your Public LASR/Autoload

If you do this, then you won;t need AutoLoad anymore, you only need to use (if you have the right license and components) the proc LASR and create the libname to your LASR server/library.

See the Administration Guide of SAS VA: http://support.sas.com/documentation/cdl/en/inmsref/67597/HTML/default/viewer.htm#p1qf09ffb3hr02n1bt...

This information might help you a lot:

Tips and Techniques for Efficiently Updating and Loading Data into SAS® Visual Analytics: http://support.sas.com/resources/papers/proceedings15/SAS1905-2015.pdf

Or this wonderful post from @Zencos / @TriciaAanderud: http://bi-notes.com/2015/10/oh-snap-upload-data-to-the-lasr-server-just-like-that/

[TriciaAanderud]

Here's one other post about the Autoload process but its on the geeky admin side.  The point of this article is that the Autoload process is checking for a timestamp change on the dataset. So if you make changes to the dataset - the Autoload process does not re-load the LASR until it sees the timestamp has changed.

Thanks for the mention @JuanS_OCS.