Doing incremental load with Salesforce

[#1]

I need to setup my script to do incremental load with Salesforce data. I am facing challenges because Salesforce is restricted in their SQL query (called SOQL).

Anyone else have experience doing incremental load with Salesforce and willing to share how they accomplished it?

Thanks,

Amit

[johnw]

I only have a single load from Salesforce, and it's not incremental. But I'm not seeing how at least a standard date-based incremental load would be restricted by SOQL. According to the SOQL documentation online:

You can use date or datetime values, or date literals. The format for date and dateTime fields are different.

• SELECT Name FROM Account WHERE CreatedDate > 2011-04-26T10:00:00-08:00
• SELECT Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2011

[#3]

I would do incremental load based on the last created case in Salesforce and I use the peek function to set the variable vLastCreatedDate to equal that date and time.

When I do the incremental load using vLastCreatedDate I get an error because SOQL does not accept the format of the date value which is something like 2017-01-01 23:59:59.00000

This is why am asking if anyone has experience with doing incremental load with Salesforce and how they do it.

[johnw]

So format your variable like SOQL wants it formatted? It appears to want YYYY-MM-DDThh:mm:ss+hh:mm, where the last part can be plus or minus, and is a time zone offset, or you can stick a Z at the end, not sure what that does. "T" is unfortunately a format code in QlikView. There's maybe an elegant way to insert a literal T into the format code, but I don't know it. But your variable is being substituted literally into the SOQL, assuming you're using $(vLastCreatedDate), so you can do any sort of text manipulation on it that you want to get the right format. So this inelegant approach should work. Substitute in your time zone.

replace(timestamp(something,'YYYY-MM-DD~hh:mm:ss-08:00'),'~','T')

As another possibility, I don't tend to worry about what I loaded last run in my incremental loads. I tend to just load the last N days, where N is something that gives me some overlap, gives me time to fix errors and then reload the bad data, that sort of thing. I exclude duplicates by loading from the QVD where not exists(MyUniqueKey). Then I periodically do a full reload to be safe, once a week for instance. For the incremental part, it appears that in SOQL, instead of using a properly-formatted timestamp, you could just use something like this.

WHERE CreatedDate = LAST_N_DAYS:5

That's what I'd probably do personally. But fiddling with the timestamp format should do what you were originally intending.

[#5]

Thanks John those are some helpful tips that I will try and use.

I am facing issues with loading the whole data set from Salesforce and it's only loading part of the data without giving me any errors. This is why I want to change to incremental load which will solve the issues I am having with the full load.

I am wondering how big of the data set you load from Salesforce but I am facing issues with over 400K rows that is equivalent to my full load.

[johnw]

My data set is tiny. Looks like 565 rows last load. Some forecast data by customer and product group and month.

If you're having trouble just with retrieving more than 400k rows, and performance isn't the problem, then I'd be trying to solve that problem rather than converting to incremental load. I like full loads where performance allows.

Doing a little googling I see a limit of 2000 rows for SOSL. I'm less clear on SOQL. It seems like some ways of getting the data limit you to 50,000 or 2000, but that in general it should pull every record available unless you explicitly use the LIMIT parameter. But maybe QlikView is limited in some way. I don't think we have enough data in any table in Salesforce for me to test it myself, though I could be wrong.