In the first part of the article series titled ‘Integrating with Force.com platform using DbAmp’, we got introduced to the DbAmp tool and its benefits. We also went through the fundamentals and learned how it works and components of the DbAmp. In this article, we’ll look at some basics of the DbAmp as this is very essential in developing the DbAmp Jobs to integrate with Salesforce. The official DbAmp documentation is an excellent source of information to learn about the basics and this article series will touch upon few topics that you can find in the documentation, but will not go into those topics in detail, as they have been already covered in that documentation.
When writing SQL queries to perform any CRUD operations against Salesforce, there are two ways a developer can choose to write them.
The first method is to write the queries directly against the Salesforce. In this method, the developers use specific type of syntax to access the Salesforce objects and the queries are executed against the Salesforce in real-time. The format is described as follows:
For e.g. to select all records from the Account object, you simply execute the following command (assuming your linked server name is SFORCE)
This has few drawbacks; Firstly, the performance takes a big hit as it has to execute directly against the Salesforce. Secondly, this will have a big impact on the API call limit. The reason is if you use INSERT or UPDATE in this manner, every record that gets inserted or updated count against the API call.
With local access method, you first replicate the required objects and store it in your local SQL server database and you write your SQL query against this table for SELECT queries. For CUD queries, you create a load table (as explained in part-1). This method has couple of advantages: Firstly, the performance is much improved, because your SELECT queries run against local database and not against the Salesforce. Secondly, as a developer, you have the option of using bulk API. With bulk API, upto 10,000 records can be sent in one single call. Also, when using the SF_BULKOPS (without the bulk api option), DbAmp batches upto 200 records in one single call.
DbAmp supports both child to parent relationship queries and parent to child queries. When replicating the Salesforce objects from Force.com platform to the local SQL server database, DbAmp uses a special mechanism to flatten the Salesforce objects in the local SQL server database. Here is a list of things that happens when an object is replicated:
The DbAmp creates every table with a column titled ‘Id’ which is mapped to the Object Id. Note that Salesforce never displays the Id field in the object definition. You can check my earlier article on Object Id’s to learn more about it. DbAmp also makes this field as primary key.
For lookup fields, the columns are created with the same name and suffixed by ‘Id’. For e.g. the ‘CreatedBy’ field becomes ‘CreatedById’ column in the local table and DbAmp stores the record id of the record it is pointing to. For e.g when replicating Case object, the field ‘CreatedBy’ is created as ‘CreatedById’ column in the local Case object and if the case is created by the user ‘John Adams’ and if the ‘Id’ of the User object for this user is ‘005014A7CA434AC’, then the ‘CreatedById’ column in the local Case object will have the value of ‘005014A7CA434AC’.
When a parent object in a lookup or master-detail relationship is replicated, there will be one record for each of the record in the related list of the parent object. For e.g. If an Account object has 3 contacts, then there will be totally three account records. If there are multiple related related lists, then there will be one record for each of the record in each of the related list. For e.g. if an Account object has 2 contacts with 5 cases, then there will be totally seven account records.
The value for the datetime types are always represented as UTC in Salesforce. When the user sees a value for the DateTime field, the value is represented in the user’s timezone (configured in the user’s profile). When the DateTime fields in Salesforce are manipulated using DbAmp, it is converted into local timezone. This is handled by DbAmp automatically and users need not convert back and forth from UTC to local and local to UTC. This is a very important point to remember when dealing with DateTime fields.
Using Column Subset Views
The Salesforce API has a limit on 10,000 characters for the SELECT queries; hence objects with huge number of columns cannot be replicated or refreshed who cross this limit. The DbAmp documentation mentions that if field count is above 325, the API may return error. In my experience, we got this error when the field count went above 400. Anyways, DbAmp suggest that the object can be split and then queries and this is called as Column Subset Views. In this method, the developer writes a SELECT query with a specific form of syntax to split the objects. The syntax is as follows:
For e.g. to split the Case object into two tables, the following query can be issued.
Note that the Case object is enclosed by square brackets. This is because, ‘Case’ is reserved keyword and any reserved keyword in the table/column names should be enclosed by square brackets. The above query will get all the records from the Case table and will create two separate tables, named ‘Case_SubsetAM’ which will contain all the columns which start with A to M, both inclusive and another table named ‘Case_SubsetNZ’ which will contain all the columns that starts with N to Z, both inclusive. The column subset view can be used with SF_REPLICATE stored procedure as well, as follows:
In order to work with Salesforce data, every tool in the market has to use the Salesforce API. There is no other shortcut or preferred treatment for certain vendors. To ensure fair usage, Salesforce restricts the resource usage by putting a limit on the API calls. The following list provides high level limits on the API:
SELECT/SF_REPLICATE/SF_REFRESH – 1000 records => 1 API call
UPDATE & INSERT – 1 record => 1 API call
SF_BULKOPS without bulkapi switch 1 API call for each batch of 200 records
SF_BULKOPS with bulkapi switch => 1 API call for each batch of 10,000 records
Understanding the fundamentals of DbAmp helps developer to write better queries against Salesforce. In this article we covered such fundamentals and in the next article we will explore how records can be retrieved from the Salesforce. For list of the articles in this article series, please check the first part of this article series.