After adding a Salesforce data source, you can access your Salesforce account's data in a relational manner. However, the Salesforce data model itself has a few limitations which do not exist in regular relational databases.
For example, Salesforce does not support JOINs between arbitrary tables. Tables involed in a JOIN must be in a parent-to-child or child-to-parent relationship. Otherwise, you will get the following error message:
ERROR at Row:1:Column:3071
Didn't understand relationship 'custom_table__cs' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
There are two options to circumvent this error:
- Disable JOIN pushdown: By default, all JOINs are pushed down to Salesforce. You can disable this behaviour to let DataVirtuality calculate the JOIN in memory instead. This can be set by editingthe Salesforce data source and setting the Translator parameters to SupportsOuterJoins=false,SupportsFullOuterJoins=false,SupportsInnerJoins=false.
- Materialize tables: The JOIN will only be pushed down to Salesforce, when both tables involved will be retrieved from Salesforce. If one of them is materialized, DataVirtuality will retrieve only the non-materialized table from Salesforce and perform the JOIN in memory. Since this needs to be considered for every possible JOIN, option 1 is usually the preferred way.
