SaaS Data access API headache across SQL, SOQL, ROQL, SOAP, REST

With the proliferation of data silos in SaaS applications, a lot of data driven organizations and application developers are getting headaches.

Meet Jeff


Jeff Leinbach is a Senior Software Engineer for DataDirect Cloud. He has been with Progress DataDirect for 9 years and worked on relational database drivers including Teradata, MySQL, DB2, Informix, Sybase and SQL Server. Then the clouds rolled in and he transitioned to building connectivity to SaaS data sources such as:

• Salesforce Platform
• Oracle RightNow
• Microsoft Dynamics CRM
• Progress RollBase
• Eloqua
• Hubspot

He is a father, husband and big fan of the NC State Wolfpack.

Jeff gets a lot of headaches


Building standards based data connectivity to multiple SaaS sources requires many development hours since each SaaS data source is very different. Each has a proprietary web service API across SOAP, REST, or both. For example, Salesforce.com uses a SOAP API for fetching data and a REST like API for bulk operations.

Jeff is part of an army of highly skilled and talented engineers focused on standards based data connectivity for SaaS sources and each exhibits different types of aches.

To focus on Jeff, below are some things that contribute to his headache when building new sources:



1. Does the SaaS source have a query language?

Some SaaS sources have a query language that is typically a subset of SQL or “SQL like”. For example, Salesforce Platform has SOQL (often pronounced “soakel” or “sockle”) while Oracle RightNow has ROQL (pronounced “rockwell”).

2. Great, a query language – now what?

Even if the source has a query language, each has its own rules for accessing data. For example, some object models give information about relationships, but not all query languages support leveraging those relationships and even have different query syntax to associate data between multiple objects. In some cases, the query language can handle a particular query; and in others cases it cannot which means Jeff and team take care of it.

3. No query language?

For some SaaS vendors, it may not make sense to have a query language. In those cases, building standard’s based connectivity can be equally challenging since each object is exposed with a different API with each having unique rules for invoking, filtering, searching, etc.

4. Let’s assume you can get the data back from the SaaS API

Each SaaS API formats results in a different way (JSON,XML,CSV,etc) making it hard to build a generic solution. For example, each API has different governors or limits for fetching, aggregating and sorting results. For example, some sources return a max of 10,000 results at a time, and then the data access code has to manually page each set and reorder the entire thing. And not all objects are exposed in the same way. For example, each API and corresponding filter rules can be different.

5. CRUD operations for SaaS Applications?

Updates, Deletes and Inserts with SaaS APIs are very different than relational databases. For example, the API may require an ID be fetched for each target object to be updated. This might be inefficient for updating multiple objects which may need to be chained to optimize network packets or use a different BULK API depending on what is available. For more specific details, here is a write-up on how ODBC/JDBC drivers make UPDATES in Salesforce easy.

6. Handling Date Formats in the cloud

All SaaS dates are exchanged in UTC. Not all clients expect dates in UTC; and it’s up to the data access code to localize the date appropriately, and ensure the round trip is successful for writes. Then, a framework had to be developed to provide a standard way for consumers to format those dates and extract things like DAY, WEEK, MONTH, etc.

7. User Authentication

Each SaaS application has different challenges in authenticating users. Jeff had too big of a headache to even expand further on this.

8. Is Metadata Static or Dynamic?

SaaS applications support either static or dynamic object models. With dynamic object models, the schema needs to be discovered by the data access code. Some detect changes to the schema, and others do not. I asked Jeff if they’re all different, and he replied emphatically, ”YES”.

9. Predicting performance between SaaS sources

Once you get the hang of building SaaS data connectivity, you are guaranteed to completely build connectivity and then run into a source that just won’t perform well under a certain workload sending you back to the drawing board to revisit all available APIs, architectures, and advil (aka the three A’s).

10. Maintaining matrix of changing SaaS APIs

SaaS applications are great since they make updates in the cloud seamlessly to the users. Or at least, it appears seamless to end users; but to those building data connectivity, this can be the opposite of seamless (seamful?). For example, Salesforce.com changes their API once a quarter which means your code needs to be recompiled each time. And the metadata will likely change all over again (see above). Further code changes are typical for new or modified interfaces. Finally, Jeff and team need to certify each update before sending it out the door to users.

Don’t be Jeff


Visit www.datadirectcloud.com

Don’t tell Jeff, but his team’s hard work lets YOU focus on building really cool and amazing data driven applications INSTEAD of writing data access code that comes with the above headaches.

Continue reading...