Trying to use Data Factory to extract Salesforce Report: formula-based column missing in result set

Question

I am trying to call a Salelsforce report from Data Factory and load results into an Azure SQL table. The report has a formula based column that does not show up in the results. This is the only field showing this behavior. If I use another tool to access the Salesforce report, like Power BI, the column does show up in the results.

Is this a limitation of the Salesforce connector in Data Factory?

Answer 1

Hello @Derek Horrall and welcome to Microsoft Q&A.

I don't have a definite answer yet, but I have a few possibilities. I do not have a Salesforce account to test with, so please let me know if I am going in the wrong direction.

First of all, I found an article in Salesforce which sounds very similar. A user tried exporting to Excel, and the formula-based column didn't show up. If this also applies to your report, I think I can explain why it happens. A formula-based columns isn't 'concrete' like normal columns, and isn't acutally stored data, but instead calculated on the fly. The equivalent in SQL is called computed-columns.

Depending upon your use case, it may be better to implement the compluted columns in the SQL table instead of trying to pull the value from Salesforce. This is because depending upon the formula, the value could change as you add more rows to the table / report.

For example, if you had a column that described the percent contribution of a value to the total value of a table ( row's value / sum of all rows' values), this would become incorrect / outdated, as new rows are added.