Question:
Does the iWay adapter for Salesforce support custom SOQL queries?
In searching for 'SOQL' on the IB site the only result was a mention on slide #44 on this powerpoint deck:
http://www.informationbuilders...aMigratorUpdate.pptx

Background:
I'm the admin for a Salesforce instance - we use Talend to do all integration, however one of our internal systems is using iWay to collect data from Salesforce and we don't want them to download everything.

Salesforce.com has a query language known as SOQL (like SQL) which is great for aggregating and joining data at the Salesforce.com level before it is downloaded - it makes for efficient use of resources and only provides the information you ask for. It also reduces the amount of API hits for which every Salesforce instance has a cap.

We have a situation where iWay needs to download aggregated information from 3 separate Salesforce objects (for the Salesforce admins: these objects all have lookup fields joining each other) but without using SOQL, iWay would need to download every record from all three objects before doing the filtering, aggregation and and joins. Architecturally speaking this is very inefficient as it takes awhile to download and increases the hits on our API limits.

For reference, this is how Talend allows custom SOQL in their Salesforce connector:


Thanks

This message has been edited. Last edited by: <Kathryn Henning>,

From your reference to the DataMigrator update presentation I assume that's what you are using. The support for SOQL (Salesforce Object Query Language) added at that time was that DataMigrator would generate a SOQL query. However you are correct that currently it doesn't generate aggregates.

We later added support for use of DB_EXPR to directly pass through some SOQL constructs but that's mostly useful for SFDC date constants.

Have you opened a hottrack case to request a new feature for either
(a) support for SQL aggregates such as SUM() to be passed through to SOQL
(b) support for Direct Pass Through to allow use of arbitrary SOQL

quote:

From your reference to the DataMigrator update presentation I assume that's what you are using. The support for SOQL (Salesforce Object Query Language) added at that time was that DataMigrator would generate a SOQL query. However you are correct that currently it doesn't generate aggregates.

Thank you for the explanation. We are using DataMigrator - For us, generating aggregate queries is not as important as generating joins.. or in SOQL speak, relationship queries. Ideally the generated SOQL would use relationship queries to get related fields on joined objects, as opposed to downloading all records and then performing the joins.
For example this is the most efficient way to get a Contact's Name, email address and the name of their company:
SELECT Name, Email, Account.Name from Contact

What we don't want is DataMigrator performing the following steps:
1. Download all Contact records (Select Name, Email from Contact)
2. Download all Account records (Select Name from Account)
3. Perform the joins and output result

In Talend this is solved by allowing custom SOQL to input relationship queries, however if DM generates the relationship queries automatically that would be a choice feature.

Joins we do.

Start by creating synonyms for Account and Contact.

Then in the DMC create a "cluster" join of the two objects.
1. Use New, Synonym via Synonym Editor to create a new empty synonym.
2. Click on the modeling view tab.
3. Right click and select Insert, Reference to existing synonym [as root]. Select Contact.
4. Right click on CONTACT and select Insert, Reference to existing synonym [as child]. Select Account.
5. Click the save button and give it a name, contact_account, and click Save.

Now you can create a data flow that generates a SQL statement like this:

SELECT 
   T1.CONTACT.NAME  AS NAME ,  
   T1.EMAIL ,  
   T1.ACCOUNT.NAME  AS ACCOUNT_NAME  
FROM 
   contact_account T1  

That does what you want, but don't take my word for it. Run the query, then right click on the server name and select "Server Session Log" and when it appears click Ctrl-F and search for "salesforce soql" and you'll find there's just one SQOL query made.

salesforce soql Request: SELECT AccountId,Name,Email,Contact.Account.Name FROM Contact

You could also enable the tracing salesforce with the component:

SFDC/1/CLIENT 

PERFECT - thanks!