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

Importing data into SAS VA periodically

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Importing data into SAS VA periodically

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


Accepted Solutions
Solution
‎01-14-2015 04:57 AM
Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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.

View solution in original post


All Replies
Super User
Posts: 1,230

Re: Importing data into SAS VA periodically

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

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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!

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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...

Super User
Posts: 1,230

Re: Importing data into SAS VA periodically

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

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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

Thanks!

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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...

Super User
Posts: 1,230

Re: Importing data into SAS VA periodically

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.

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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...

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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/

Super User
Posts: 1,230

Re: Importing data into SAS VA periodically

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...

Contributor
Posts: 23

Re: Importing data into SAS VA periodically

I'm obviously not as smart as you think... :-)

I'll keep you posted!

Solution
‎01-14-2015 04:57 AM
Contributor
Posts: 23

Re: Importing data into SAS VA periodically

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 2189 views
  • 6 likes
  • 2 in conversation