Salesforce tables have different columns after upgrading from Data Studio v1 to v2
If you've previously used Data Studio v1 to load data from your Salesforce instance, and have upgraded to v2, you will have noticed that the way we show column names from Salesforce tables, and the columns we display by default, has changed.
This is because the JDBC driver we use internally to connect to Salesforce has been updated in Data Studio v2, and the Salesforce data model mapping defaults have changed.
The changes are based on customer feedback, so should be an improvement. But don't worry, if you want to revert to the v1 behaviour (for example if you have v1 workflows that you want to migrate to v2), there are some configuration options that will allow you to do that.
So what are the changes?
The first thing you'll notice is that v2 returns more columns: For the Salesforce ACCOUNT table, Data Studio v2 brings back 63 columns, as opposed to 55 columns in v1. You'll also notice that some of the column names are different. This screenshot of v1 (top) and v2 (bottom) after loading the same table demonstrates some of the differences:
- In v2 we now expose audit fields (like
ISDELETED) and the master record ID field (
MASTERRECORDID). These were hidden in v1
- In v2 we no longer change the names of system fields, but expose them as they exist when mapping the Salesforce data model. So
Rowid(in v1) becomes
ID(in v2), and
Sys Name(in v1) becomes
- In v2 we use the "__c" suffix to identify custom column names when mapping the Salesforce data model, so
Customerpriority(in v1) becomes
Configuring the connection to change how columns are displayed
We can change these column mapping settings using the ConfigOptions connection property. This property is applied in the External System configuration page, and its value is a list of one or more options separated by a semi-colon:
Used in Data Studio:
Let's have a look at the options we need to include to change v2's Salesforce data model mapping to match the mapping in v1.
Hide audit columns with AuditColumns=none
To hide the audit columns, set AuditColumns to
none with the ConfigOptions connection property
Change the names of system fields with MapSystemColumnNames=1
To change the names of system fields so that
Id field is mapped as
ROWID and the remaining system columns are prefixed with
SYS_ , set MapSystemColumnNames to
Remove the custom column name suffix with CustomSuffix=strip
To remove the "
__c" suffix with custom table and column names when mapping the Salesforce data model set CustomSuffix to
Combining all three settings
So to use all three of these settings together, the connection property to add will be:
- key: ConfigOptions
Whenever you change how SF data model mapping is defined you will also need to specify a new SchemaMap property, which is the name of the file where the data model is written. You can set the value to any name you want, in my case I've used:
- key: SchemaMap
Finally, here's v2 with the same data model used in v1: