topic LOAD All SQL Server Database Tables to LASR in SAS Visual Analytics

LOAD All SQL Server Database Tables to LASR

Achraf_Ougdal — Tue, 16 May 2023 14:00:11 GMT

Hello Everyone,

We have an SQL Server Database that we would like to load to LASR.

I have some simple questions :

Can we use the ODBC to do so ?
If we use the OLEDB, I understand that I need to manually create the query code necessary to load tables to LASR (one table at the time) am I right ?
In both cases, are we going to find the same Star Schema in the Database or I should Create a new LASR Star Schema using the tables ?

PS : The SAS/ACCESS Interface to SQL Server is not available.

Thank you.

Re: LOAD All SQL Server Database Tables to LASR

SASKiwi — Wed, 17 May 2023 02:08:44 GMT

There's no problem with continuing to use OLEDB. If you simply want to copy an SQL Server table into LASR you can automate it like this:

libname MySQLSvr OLEDB <SQLServer connection details>;

%macro Copy_and_Load_LASR ( SQLSrvr_Table = );

* Copy SQL Server table;
proc datasets library = WORK nolist;
  delete &SQLSrvr_Table;
run;
  copy in = MySQLSvr = out = WORK;
  select &SQLSrvr_Table;
run;
quit;

* If table already loaded then delete first.;
proc datasets library = LASRLIB nolist;
  delete &SQLSrvr_Table;
run;
quit;

* Now reload latest table.;
data LASRLIB.&SQLSrvr_Table ( &Table_Options. );
  set WORK.&SQLSrvr_Table;
run;

* Register or update LASR table in metadata. ;
 proc metalib;
   omr (library = "LASRLIB");
   folder       = "/Shared Data/SAS Visual Analytics/Public/LASR";
   select       = ("&SQLSrvr_Table");
   update_rule = (delete);
 run;

%mend Copy_and_Load_LASR;

%Copy_and_Load_LASR (SQLSrvr_Table = MySQLSrvrTable1);
%Copy_and_Load_LASR (SQLSrvr_Table = MySQLSrvrTable2);

If you need to select certain columns then you can easily enhance this macro to select them - no SQL required.

Re: LOAD All SQL Server Database Tables to LASR

Achraf_Ougdal — Thu, 25 May 2023 12:04:56 GMT

It worked, Thank you so much !