topic Re: What is the difference between using SAS Access and ODBC connection in SAS Data Management

What is the difference between using SAS Access and ODBC connection

Tushar — Wed, 06 Mar 2013 06:33:26 GMT

Hi All,

i have one query regarding SAS Data integration studio. While registering DBMS table what option should a user choose like suppose i want to registered oracle table and also i have oracle connectivity through SAS/ACCESS but my question is what is the preferable Choice for this senario like should i use SAS/ACCESS or ODBC Connectivity? what is the difference between this?

Please help on this..

Thanks and Regards,

Tushar

Re: What is the difference between using SAS Access and ODBC connection

LinusH — Wed, 06 Mar 2013 09:54:23 GMT

Do you ask if it'spreferabale to access to Oracle via ODBC or SQL*NET?

There are pros and cons.

If you don't predict any performance issues, you might want to go with ODBC, since this SAS/ACCES license can be used for multiple sources (other than Oracle).

Using the native Oracle access with probably give you some additional functionality and better performance.

Re: What is the difference between using SAS Access and ODBC connection

Tushar — Wed, 20 Mar 2013 08:26:38 GMT

Hi Linus,

First of all thanks for your reply and sorry for my late comment. Actually this was the question i have asked by my senior, as per the work i have done on SAS DI studio for registering NON-SAS DBMS i have used ODBC connectivity and not SAS/ACCESS, but he was asking like why your connecting through ODBC when you have SAS/ACCESS install for the same database. I mean there is no option in SAS DI to choose SAS/ACCESS, when we want to registered table wizards, and one more thing i want to ask like can we use SQL pass through facilty from SAS DI? if yes how?

Re: What is the difference between using SAS Access and ODBC connection

LinusH — Wed, 20 Mar 2013 10:27:13 GMT

If you have SAS/ACCESS to Oracle installed, licensed and configured, then there is nothing that will prevent you to use it in DI Studio. The setup in DI Studio/Management Console is pretty much the same as configuring SAS/ACCESS to ODBC data sources, just a few parameters that might differ.

If the data base server, database and schema are defined, you should be able to access it using the standard wizards.

Generally, when using SQL transformations, SAS are in many cases transforming the code to an implicit pass-thru. For at least the SQL Join transformation, you have the possibility to specify explicit pass-thru code generation. Depending on your DI Studio version, there might even more SQL transformations available for you, that can do the same.

I'm more of a implicit pass-thru kind of guy, and use explicit pass-thru only when it's really needed.

Re: What is the difference between using SAS Access and ODBC connection

DaveR_SAS — Wed, 20 Mar 2013 14:28:46 GMT

In regard to explict versus implicit SQL pass-through . . . the DI Studio user guide has these two topics which might be relevant:

http://support.sas.com/documentation/cdl/en/etlug/65807/HTML/default/viewer.htm#n1sqvkm1pyw3a1n1ik1ayutua5gu.htm

http://support.sas.com/documentation/cdl/en/etlug/65807/HTML/default/viewer.htm#n0o4rbvvosnwran10wcv2e4ewyvd.htm