LOAD All SQL Server Database Tables to LASR

[Achraf_Ougdal]

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.

[SASKiwi · Accepted solution]

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.

[Achraf_Ougdal]

It worked, Thank you so much !