Summa

In this blog post I will provide an overview of considerations when integrating with a specific SaaS provider: Salesforce.com (SFDC). Salesforce.com is one of the most popular SaaS applications in the market and it is one the leading SFA (Sales Force Automation) solutions available to date. Salesforce.com provides very comprehensive integration capabilities. To narrow the scope of this post, I will focus on integrating through their Enterprise Web Service API. The discussion will also be focused on the technical aspects of the integration. There are several business and data modeling decisions that will not be covered here, but are covered at a high-level on an earlier post of this series.

What is Saleforce.com?

Salesforce.com is a popular SaaS (Software as a Service) application that provides two core sets of business functionality:

• Sales Force Automation (SFA). Provides accounts, contacts and opportunity management, as well as sales analysis and forecasting.
• Customer Relationship Management (CRM). Provides call center, knowledge base and e-mail-to-case integration, among other CRM capabilities.

Salesforce.com Data Model

SalesForce.com provides a built-in data model. This data model includes entities used in the SFA domain, such as accounts, contacts and opportunities. This data model can be extended in 2 ways: by adding custom columns or by adding custom entities. Additional relationships can be defined. All data entities include an 18 character unique identifier, as well as standard audit fields (e.g. CreatedDate, CreatedById, etc.).

The SOQL (Salesforce.com Object Query Language)

SOQL is a SQL like query language with some key differences:

• Only SELECT statements are supported.
• WHERE clauses are supported, but JOIN constructs are not; at least not on the same way.
• All joins are of inner type, there must be an existing relationship between the joined entities.
• All joins are implicit, they must not be declared explicitly, just by merely referencing the entity name followed by a dot and the field name.
• Aggregate functions (count, average, sum, etc.) are supported.
• Subqueries can also be used, as well as GROUP BY clauses.
• IN lists can also be used in the WHERE clause, however there is a 10,000 character limit for the overall query string.

This is the basic SOQL syntax:  

SELECT fieldList FROM objectType[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY fieldGroupByList] | [GROUP BY ROLLUP|CUBE (fieldSubtotalGroupByList)]
[HAVING havingConditionExpression]
[ORDER BY fieldOrderByList ASC | DESC ? NULLS FIRST | LAST ?]
[LIMIT ?]

The following is a very simple SOQL query which retrieves contacts that were created after a certain date:

SELECT id, FirstName, LastName, RecordType.Name FROM Contact WHERE CreatedDate > 2009-08-01T12:00:00-05:00

There is no support for wildcard queries (*). Fields must be enumerated explicitly separated by comma characters in the fieldList above. The query above looks very similar to a typical SQL query. One of the differences is that it is implicitly joining to the RecordType entity to retrieve the RecordType’s Name field. For this to work there must be a relationship created between the Contact and RecordType entities. 

The Force.com Enterprise API

The Enterprise API is a SOAP based API that provides web service operations to query and modify data. It is targeted for real-time integration. Here is a summary of the most commonly used operations:

Logging in

In order to call any web service operation, the application must first log into Salesforce.com by invoking the login operation. This operation requires providing a username and password (concatenated with a security token). The response will include a sessionId and URL to be used for subsequent calls. It is a good practice to cache these values as they need to be sent in the SOAP header in the successive web service calls.

Querying data

As mentioned above querying data is done through the use of SOQL. The SOQL query is a string provided in the queryString element of the query operation. If there are more than 500 records that match the query, the queryMore operation must be invoked multiple times to get the additional records until the total is reached. To invoke queryMore a queryLocator must be provided, which is returned in the first queryResponse and each subsequent queryMoreResponse.

The default batch size can be overridden to a maximum of 2,000 records in the batchSize element of the QueryOptions included in the SOAP Header. However this batch size is merely a suggestion. There are certain cases in which the batch size will not be used. One example is when two or more fields of type long text are included in the query; in this case the maximum records returned will be 200.

These limits help Salesforce.com prevent one particular query from using more resources than others and thus impacting other users’ performance. However these constraints create complexity in the logic to query data because unless the expected number of results is less than 200, the query must be invoked once and queryMore multiple times. It is a good practice to aggregate the results in memory by a SaaS integration layer to abstract complexity from consumers of this information.

Modifying data

There is no support for data modification operations in SOQL. All data modifications are performed through either insert, update, delete or upsert web service operations by sending the records to modify within the SOAP payload. The upsert operation inserts a new record if it does not exist, or updates it if it exists already.

A key distinction between upsert and the other write operations (insert, update, delete) is that upsert is the only operation that can update records using an external key. All other write operations require the salesforce.com unique ID (18 character ID, e.g. 001S000000AreVyIAJ) to perform any updates or deletions.

A maximum of 200 records can be sent in any of these operations. The response operation contains a record level result, i.e. success or error. Additionally the upsertResponse will contain a created boolean field to indicate that the record was created if true, or updated if false. Unfortunately the response will not contain the identifier sent in the request, so the only way to correlate between the input row and the result is by matching the index. Here is a sample upsert request:

<NS3:upsert xmlns:NS3="urn:enterprise.soap.sforce.com">
<NS3:externalIDFieldName>External_Id__c</NS3:externalIDFieldName>
<NS3:sObjects NS4:type="Contact" xmlns:NS4="http://www.w3.org/2001/XMLSchema-instance">
     <NS6:Birthdate>1980-01-01</NS6:Birthdate>        
     <NS6:LastName>Smith</NS6:LastName>
     <NS6:MailingCity>Pittsburgh</NS6:MailingCity>
     <NS6:MailingState>PA</NS6:MailingState>
     <NS6:MailingStreet>100 5th Ave</NS6:MailingStreet>
     <NS6:MailingPostalCode>15219</NS6:MailingPostalCode>
<NS6:External_ID__c>138131</NS6:External_ID__c>
</NS3:sObjects>
</NS3:upsert> 

Additional Integration considerations

• Every write operation (insert, update, delete) is committed automatically. This means that there will not be any automatic rollbacks, any insertions or updates will have to be recovered by deleting. Recovering from a delete is harder, unless a temporary copy is stored elsewhere.
• The underlying database is completely hidden from external access. This means that any integration will need to occur through one of the Force.com APIs.
• Saleforce.com’s support policy is to provide support for any given API version for a minimum of 3 years.
• Salesforce.com has periodic planned outages for maintenance or upgrades. These planned outages typically occur during Saturday evenings and can last up to 6 hours. This means that any integration occurring during this time will have to plan for and expect such unavailability.
• Salesforce.com has a proven availability of 99.9% uptime. However there will be unexpected service disruptions. One recent instance occurred on March 11^th, 2010 with an outage of approximately 10 minutes around 9AM EST. Salesforce.com is very transparent about their up-time through the http://trust.salesforce.com website. They provide up to date availability status as well as outage information for the last 30 days.

Because of all of the above considerations it is strongly recommended to create a SaaS Integration layer to hide specific Salesforce.com API details from internal applications. Additionally, it is often desirable to create a common (canonical) schema to communicate between the integration layer and internal applications without having to use the Salesforce.com API schema.