0

We have tables on Salesforce which we'd like to make available to other applications usings Microsoft Common Data Service. Moreover, we'd like to keep CDS more or less up to date, even having data which was created or updated five minutes ago.

However, some of those tables have hundreds of thousands, or even millions, of records. So, refreshing all the data is inefficient and impractical.

In theory, when CDS queries for data, it should be able to know when its most recent data is from and include this data in the query for new data.

But I'm not clear how to make that part of the query that gets used in the refresh operation.

Is this possible? What do I need to do?

Brian Kessler
  • 2,187
  • 6
  • 28
  • 58

2 Answers2

0

How are you selecting the data that you are retrieving? If you have the option to use a SOQL query, you can use the fields CreatedDate and LastModifiedDate as part of your queries. For example:

SELECT Id, Name
FROM Account
WHERE CreatedDate > 2020-10-25T23:01:01Z

or

SELECT Id, Name
FROM Account
WHERE LastModifiedDate > 2020-10-25T23:01:01Z
Arepa Slayer
  • 443
  • 1
  • 7
  • 20
  • I'm just using the Common Data Service's native refresh. So far as I can tell, there is no option there to edit SOQL. By that point, Salesforce is abstracted -- I could be refreshing from anything. – Brian Kessler Nov 12 '20 at 19:49
  • Are you using this connector? https://learn.microsoft.com/en-us/connectors/salesforce/ – Arepa Slayer Nov 12 '20 at 20:00
  • Yes, probably. I just used the wizard in PowerApps to create the Table from Salesforce data and it created the connector in passing. (When I previously tried creating the connector directly, the import dialog did not seem to make use of the information.) – Brian Kessler Nov 12 '20 at 20:08
  • From that documentation, I see there is a way to retrieve records (Operation ID: GetItems) that allows you to pass a `Filter Query` parameter. Do you see the option to use that operation to retrieve records? – Arepa Slayer Nov 12 '20 at 22:16
  • No need to be rude, I'm spending my time trying to help you with YOUR issue. I don't know how much experience you have with this application or if the option to filter exists but it's hidden in a menu somewhere. – Arepa Slayer Nov 13 '20 at 16:12
  • Sorry if you felt my answer was rude. I have little experience with the application. If it is hidden in a menu somewhere, I would need to know where and how to find this. Actually, I now suspect such options are very well hidden, have something to do with Power BI, and I may need additional permissions to try doing anything with it. – Brian Kessler Nov 13 '20 at 16:16
0

There are some options but I have no idea what (if anything) is implemented in your connector. Read up a bit and maybe you'll decide to do something custom. There's also a semi-decent SF connector in Azure Data Factory 2 if that helps.

Almost every Salesforce table contains CreatedDate, LastModifiedDate and SystemModstamp columns but we don't have raw access to underlying database. There's no ODBC driver (or if there is - it's lying, pretending SF objects are "linked tables" and hiding the API implementation magic in stored procedures). I'm not affiliated, never used it personally but heard good stuff about DBAmp. It's been few years though, alternatives might have popped up. Go give that ADF connector a go.

I mean you could even rephrase it a bit, look around for backup tool that does incremental backups and works with SQL Server? kill 2 birds with 1 stone.

So... The other answer gives you query route which is OK but bit impractical if it's 100+ tables.

There's Data Replication API to get Ids (primary keys) of recently updated/deleted records. SOAP API has getUpdated call, similar in REST API. You'd still have to call it per object though. And you'd just know that these were modified, still need to query all columns (there's "retrieve" call similar to SELECT *)

Perhaps you need to change the direction. SF can raise events when data changes, subscribing apps have between 1 to 3 days to consume them. It uses cometd protocol, chances are there's app for that in .NET world. There are few types of events (could raise custom events, could raise only when certain conditions are met, from SF config or code; and other way around - subscribing app could specify a query it's interested in and get notified whenever query's results would change). But if you just want everything - search for "Change Data Capture". Could be nice near-realtime solution

eyescream
  • 18,088
  • 2
  • 34
  • 46
  • I'm looking for a low-code solution to give the data out of Salesforce and into Microsoft CDS to save us the work of creating and maintaining a service which would essentially do the same thing. Our connector between CDS and Salesforce is just whatever Microsoft gives out of the box. Are you suggesting we need to create a custom connector if we want incremental refresh? – Brian Kessler Nov 13 '20 at 09:35
  • if you can't see into that Microsoft's component and it doesn't do what you're after - yep, please explore ADF, cometd clients or code something yourself. I don't know what MS did there; there's no raw access to SF databases, they had to implement some APIs SF exposes. And if they did it quick&dirty just select * on all rows, no date filters, no incremental options then it is what it is. Consider asking on salesforce.stackexchange.com too, there might be more clever solutions. I just worked with ADF a bit. – eyescream Nov 13 '20 at 12:16
  • We should we care about the underlying database in order to have an incremental refresh? Shouldn't `CreatedDate`, `LastModifiedDate`, and `SystemModStamp` provide enough information for a connector to work with? Is there reason to believe the standard connector can't work with these? – Brian Kessler Nov 13 '20 at 12:40