Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] iWay adapter for Salesforce: is Custom SOQL possible?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] iWay adapter for Salesforce: is Custom SOQL possible?
 Login/Join
 
Member
posted
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>,
 
Posts: 3 | Registered: July 14, 2015Report This Post
Guru
posted Hide Post
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


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
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.
 
Posts: 3 | Registered: July 14, 2015Report This Post
Guru
posted Hide Post
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 


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Member
posted Hide Post
PERFECT - thanks!
 
Posts: 3 | Registered: July 14, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] iWay adapter for Salesforce: is Custom SOQL possible?

Copyright © 1996-2020 Information Builders