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

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

Posted on October 28, 2013 0 Comments

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, 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, 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


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.

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.


Comments are disabled in preview mode.

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation