+ Start a Discussion
openaccessopenaccess 

SQL access to salesforce.com data

We at OpenAccess Software are debating the value of providing SQL compliant access to saelsforce.com and would appreciate any comments.  The objective is to provide a ODBC driver that allows the use of full SQL to perform read and write operations against any salesforce.com object. This would allow the use of MS Office, Crystal, and 100's of other applications to quickly access your data.

Thank You

adamgadamg
I think in general that is valuable - I know there are other folks looking at this area as well.

However, for complex SQL operations, like reporting/OLAP, it makes much more sense to use a DB replication tool (there are several available in the marketplace) to run against a local copy of the data.
Force2b_MikeForce2b_Mike
I would also be interested in this capability, but agree with the other person on complex queries. Also, since ODBC essentially makes the data more accessible, I suggest that the driver be ReadOnly to prevent users from accidentally changing data. Changes to the SFC data are probably best done through code and/or the Excel sForce Connector.

Mike
openaccessopenaccess

Mike,

Can you please expand on what value you would see from SQL/ODBC connectivity? Also, I'm not sure why complex queries cannot be done directly against the data source without the need to replicate. Our SQL engine is fully capable for doing JOINS, GROUPING, and all the other SQL operations people use to perform reports and queries. Also we optimize operations like JOIN processing and other operations to reduce the number of trips we have to make to the server. Many ISVs license OpenAccess for their products in order to avoid the need for replication.

As for read/write, that can be controlled during the set up. And all updates occur using the sforce API so all checking inherent at thay layer would be applied before the data is actually changed.

Thank you.

Force2b_MikeForce2b_Mike
If you're able to achieve high performance against a large data set through the SQL engine, then I don't have a problem with using it for complex queries especially if replication can be avoided. However, though the WebServices API is pretty quick I'm a bit hesitant on how fast your ODBC engine can be when working with 10k+ accounts/contacts/leads (which is still fairly small). Still, it would be great if you can join tables in real time while keeping query time down.

I warn about write access because you may get some 'power users' who may accidentally change data when attempting queries through common tools like MS Access. The SForce API is nice, but it won't prevent a user from changing items such as the name or address of a company. At the same time, it would be great to be able to write to SFC through ODBC/SQL so that special code doesn't have to be written.

Mike