Importing data into SAS VA periodically

[xangadix]

Hi all,

I've been building SAS VA reports quite some time now. Until now, it was not a problem to import tables and views from a SQL database individually, but now I need a method to import these tables and views automatically every week. Does anyone know where I can find information about that? I googled, but could not find anything about it...

Thanks in advancein

[MichelleHomes]

Hi,

Information on loading data into SAS Visual Analytics can be found in the SAS Visual Analytics Administration Guide, which can be found at the SAS Support - SAS Visual Analytics page. Refer to the version of SAS Visual Analytics you are using and note you will need a userid and password from your local SAS support office to access it.

Are these tables and views you want to import new tables or existing registered metadata tables and you are overwriting them or updating them? There are many ways to load data into SAS Visual Analytics depending on what your requirements are. You may want to check out this "How To" note Selerity has put together that succinctly outlines the many ways to load data in SAS Visual Analytics as described in the administration documentation - How To: Loading Data in SAS Visual Analytics - Overview

Kind Regards,

Michelle

[xangadix]

Hi Michelle,

Thanks for your quick reply! I'll explain some more about my situation: I have a Microsoft SQL Server with about 15 views that I import manually everytime the views are changed (File > Import Data). I do not use the query builder, I just import them into LASR tables directly. In the future, I want these views to be automatically imported and overwriting the old LASR tables weekly.

I found out that the "How To: Loading Data in SAS Visual Analytics - Unloading, Reloading or Deleting a Table" way is what I need, but I need to schedule that task weekly. I could not find a way to get that done... Do you know if that's possible?

Thanks!

[xangadix]

I just found that it doesn't work that way either... I can't make a connection to the SQL Server, like I can when I import manually...

[MichelleHomes]

What version of SAS Visual Analytics are you using? Have you had a look at the SAS Visual Analytics Administration Guide - the default settings on the server may need to be changed for the type of scheduling/autoload you are trying to do.

Also you may want to have a look at a similar discussion at

Kind Regards,

Michelle

[xangadix]

I'm using version 7.1. I'll check the link you just sent.

Thanks!

[xangadix]

Hmm, the discussion you mentioned offers no solutions... And I can't reach the SAS Visual Analytics Administration Guide, because I need a username and a password to access it, for some reason...

[MichelleHomes]

As I mentioned above, a userid and password is required to access the SAS Visual Analytics Administration documentation, and I suggested for you to contact your local SAS Support office who can supply you with the credentials.

The discussion thread I referred to is about creating a data query that is based on importing data into the SAS LASR Analytics Server and as you are using SAS Visual Analytics 7.1 there is the ability to schedule a data query. The reference was more for your information rather than a specific answer to your situation.

[xangadix]

Oops, I just realized I missed the password part in your message, sorry...

Scheduling a Data Query is possible, absolutely, but I can't find a way to create a data query with an ODBC source...

[xangadix]

I think I'm going to check out if this is the answer. It sure looks like it can be...

http://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/

[MichelleHomes]

Yes it would seem that it will!

I had assumed you had an ODBC SAS metadata library already assigned. But of course, you wouldn't if you are only using the File -> Import feature as the registration and loading of the table is being done behind the scenes. Let us know how you go...

[xangadix]

I'm obviously not as smart as you think... 🙂

I'll keep you posted!

[xangadix · Accepted solution]

It took me some serious effort to create a library that shows all the tables in the Microsoft SQL Server database. I created the library in the Management Console and registered the tables there. After that, I was able to unload and reload them all at the same time in SAS VA. Saving the database user and password isn't possible, at least I couldn't find any option for it.

The periodically reloading of the data must be done by scripting, which is something I will work on later this week.