SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS and Salesforce - Your input needed

Accepted Solution Solved
Reply
Highlighted
SAS Employee
Posts: 244
Accepted Solution

SAS and Salesforce - Your input needed

[ Edited ]

Hi,

 

If you are using SAS with Salesforce we could use your help. I am looking for advice. Feel free to contact me directly!

 

Here are a sample of the questions we need help with:

1/ Do you use custom objects in Salesforce?

2/ What is the typical size of a large collection of objects in Salesforce? How many records? Size per record?

3/ Do you write data to Salesforce?

4/ Can you provide same queries that you run from SAS?

 

Thanks in advance for your input.

 

Best wishes,

Jeff


Accepted Solutions
Solution
‎10-11-2017 03:13 PM
SAS Employee
Posts: 244

Re: SAS and Salesforce - Your input needed

I asked a couple of people this question via e-mail. Here is what I was told...

 

Here are a sample of the questions we need help with:
1) Do you use custom objects in Salesforce?

 

We access numerous custom objects (both objects and custom fields) in the SFDC implementation.  From the perspective of the driver, there really is no difference between built in and custom.


2) What is the typical size of a large collection of objects in Salesforce?

 

I’m not clear on what you are looking for here.  What are you defining as a collection of objects?  How many records? We regularly pull tens of thousands to hundreds of thousands of record out in a matter of minutes.  Size per record?  Varies by object – many of the objects are quite wide, up to hundreds of fields.  I would have to calculate size, not immediately available.


3) Do you write data to Salesforce? 

 

Yes.  We can write directly via the ODBC driver with SQL INSERT and UPDATE statements.  We can also write via calls to the SFDC web services API.


4) Can you provide same queries that you run from SAS? 

 

As I said above, we don’t run queries directly from SAS.  We utilize Data Management Studio and the SFDC ODBC driver for data pulls from SFDC.  Most of the queries are quite simple, such as

 

SELECT *

   FROM SFORCE.ACCOUNT

  WHERE SYS_LASTMODIFIEDDATE > '%%FILTER_LAST_UPDATE_DTTM%%';

View solution in original post


All Replies
Solution
‎10-11-2017 03:13 PM
SAS Employee
Posts: 244

Re: SAS and Salesforce - Your input needed

I asked a couple of people this question via e-mail. Here is what I was told...

 

Here are a sample of the questions we need help with:
1) Do you use custom objects in Salesforce?

 

We access numerous custom objects (both objects and custom fields) in the SFDC implementation.  From the perspective of the driver, there really is no difference between built in and custom.


2) What is the typical size of a large collection of objects in Salesforce?

 

I’m not clear on what you are looking for here.  What are you defining as a collection of objects?  How many records? We regularly pull tens of thousands to hundreds of thousands of record out in a matter of minutes.  Size per record?  Varies by object – many of the objects are quite wide, up to hundreds of fields.  I would have to calculate size, not immediately available.


3) Do you write data to Salesforce? 

 

Yes.  We can write directly via the ODBC driver with SQL INSERT and UPDATE statements.  We can also write via calls to the SFDC web services API.


4) Can you provide same queries that you run from SAS? 

 

As I said above, we don’t run queries directly from SAS.  We utilize Data Management Studio and the SFDC ODBC driver for data pulls from SFDC.  Most of the queries are quite simple, such as

 

SELECT *

   FROM SFORCE.ACCOUNT

  WHERE SYS_LASTMODIFIEDDATE > '%%FILTER_LAST_UPDATE_DTTM%%';

Frequent Contributor
Posts: 129

Re: SAS and Salesforce - Your input needed

Hi JBailey,

Could you please share the syntax used to establish the connection to SharePoint using ODBC?

I am currently trying to find a way connecting our SAS base reports on SalesForce (in order to make use of the data available in the account and contact objects).

Thanks a lot in advance for the support!

Kind regards,
Florent
SAS Employee
Posts: 244

Re: SAS and Salesforce - Your input needed

Hi @Florent,

 

Do you really mean Sharepoint? Either way, it is probably best to open a new topic because it will help people find the answer.

 

Best wishes,

Jeff

Frequent Contributor
Posts: 129

Re: SAS and Salesforce - Your input needed

Hi @JBailey,

 

No indeed, I meant SalesForce Smiley Wink

 

Regards,

Florent

SAS Employee
Posts: 244

Re: SAS and Salesforce - Your input needed

Hi @Florent,

 

Can you start another topic? I have an answer for you but I don't want to overload this topic.

 

Best wishes,

Jeff

Occasional Contributor
Posts: 10

Re: SAS and Salesforce - Your input needed

 

SAS Scripts to Connect SAS to Salesforce Smiley Happy

 

BASE SAS 9.4 M5 and DEVART ODBC WORKS - SEE EXAMPLE BELOW - SEE DEVART ON SALESFORCE APPS STORE

 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 (for me anyway)...Good solution if you just need to update a few thousand records

 

- Bread Crumbs and Circuses for all

Bread Crumbs and Circuses for All
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 559 views
  • 4 likes
  • 3 in conversation