Connect SAS (Statistical Analysis Software) to Salesforce database - Answers - Salesforce Trailblazer Community
Ask Search:
Brian PedersenBrian Pedersen 

Connect SAS (Statistical Analysis Software) to Salesforce database

We currently use a VERY poor workaround to obtain our data and replicate it to a datacenter owned by my company.  There are 3 handshakes that occur and often (almost daily) break and require intervention.  Adding new fields to the nightly replication takes over 40 hours for 1 field.  I found an article that uses ODBC to connect, but I am more curious about three things.  I want this to be as minimally owned/supported by IT as possible and be business owned.

1.) Has anyone connected SAS to their Salesforce instance
2.) if so, what method did you use to extract data into SAS
3.) How long did it take for implementation?
Ron KoppelmannRon Koppelmann
Brian, not an easy question to answer as integration is highly dependent on knowing a great detail about your current architecture. Having said that I'll take a poke at the three questions below: 

1.) Has anyone connected SAS to their Salesforce instance 
         We connected SAP and SF but I have not personally done SAS. However, most large companies like SAP have "connectors" that allow you to connect to Salesforce.com. Have you come across this in your research:
http://support.sas.com/software/products/surveyor/index.html
2.) if so, what method did you use to extract data into SAS
           Not having connected SAS I can't say but if you don't have a middleware/ETL tool "in house" for doing this already (think CastIron or Informatica) I'd suggest starting with the connector SAS appears to offer. Why? It's usually "free" in the sense that you may not have to spend more than you're already spending with SAS and if you have SAS expertise in house they may be able to get it implemented faster and cheaper.
3.) How long did it take for implementation?
           I've seen integrations vary widely based on the demands placed by the business for how frequently the data needs to be updated (real time vs nightly) and how much it has to be transformed to be usable in the host database.
Brian PedersenBrian Pedersen
Ron,

Thank you for the response.  I have done some reading into it, but the integration part isn't fully necessary.  We integrate to be able to communicate up and back, but it is to store on our datacenter.  This is the document that I have read to connect SAS to Salesforce.  If I don't get a connection from SF to SAS, then I will definitely be seeking people out at Dreamforce.

http://books.google.com/books?id=12I5HZErWXMC&pg=PA24&lpg=PA24&dq=sas+to+salesforce+odbc&source=bl&ots=bj6nALIoVt&sig=v6s8tmoRS6dbV64B5-OhHb37mSw&hl=en&sa=X&ei=sIJqU9HPFNDtoATdo4GQDg&ved=0CI4BEOgBMAg#v=onepage&q&f=false

Thanks again! :)
Brian
Ron KoppelmannRon Koppelmann
My pleasure, if you have integration squared away and need more of an export only tool/solution, have you looked at Jitterbit on the AppExcchange? See link below:

https://appexchange.salesforce.com/listingDetail?listingId=a0N300000016ZoVEAU
Brian PedersenBrian Pedersen
We have that as well.  We use it for case creations and simple data pulls.  Unfortunately, since we have over 1 million cases and 400 fields, it takes over 30 hours to pull the data down, which by that point has become old data.
Ron KoppelmannRon Koppelmann
Are you recreating the entire table with all fields every day? Have you tried to reduce the "payload" of this job by only updating the changes made since the last time the data extraction ran? I have used that strategy in the past to make jobs like this run faster. 
Michael McCarthyMichael McCarthy
ODBC Drives exist and can read\write
Here are some Bread Crumbs from my Circus...
BASE SAS 9.4 M5 and DEVART ODBC WORKS - SEE EXAMPLE BELOW - SEE DEVART ON APPS STORE
HURRAY! Running tests on this driver with BASE SAS 9.4 M5 desktop and it can read\write. I wrote this comment for other SAS users who may need to do this...
Here is working SAS CODE AND SYNTAX:
/*ODBC TO SALESFORCE SANDBOX - FOLLOW INSTRUCTIONS FROM DOWNLOAD*/
libname SANDBOX odbc
datasrc='SANDBOX'
user=’SALESFORCE USER NAME'
password='PASSWORD'
schema=ALL
preserve_tab_names=yes
preserve_col_names=yes;
/*UPDATE TEXT FIELD USING ID*/
PROC SQL;
update SANDBOX.YOUROBJECT__c
set YOURFIELD__c = “I WAS HERE WITH SAS”
where ID = "YOURIDSTRINGHERE";
quit;
/*INSERT EXAMPLE*/
PROC SQL;
insert into SANDBOX.YOUROBJECT__c
set YOURPARENTRECORD__c = 'YOURSTRINGHSERE', NAME = "WHATEVER", YOURFIELD__c = "I WAS HERE WITH SAS";
quit;
 
Updates are a few tenths of second while inserts are 2-3 seconds...Good solution if you just need to update a few thousand records
Pinak DuttaPinak Dutta
But Devart drivers comes with a cost. I think there should be more viable options avaliable in the market