The provider offers the possibility to upsert data into your Tableau CRM datasets.
Getting the metadata fileTo upsert data into an exisiting dataset, first you need to download the metadata file. You can download the metadata file during the process of creating a new dataset by performing the following steps:
- In Tableau CRM, go to the home page, click Create, and then choose Dataset.
- Select CSV file to upload and click Next.
- From new opened window download the JSON metadata schema file by clicking Download File in dropdown list at Data Schema File section.
- For more info on Metadata format refer to this guide: https://resources.docs.salesforce.com/200/latest/en-us/sfdc/pdf/bi_dev_guide_ext_data_format.pdf
or for an existing dataset, if you have a csv file with the dataset's data go through the same process as when creating a new dataset to get the metadata json file for it, but don't actually create the dataset.
Upserting dataTo upsert new data, the MetadataFilesLocation connection property is required. You must set its value to the location of the file where your metadata files are stored.
Note: The metadata file must contain one (and only one) field with a unique identifier.
UPSERT INTO Dataset_Example (Col1, Col2, Col3) Values ('val1', 'val2', 'val3')
UPSERT INTO SELECT (Bulk upserting)First you need to insert data into a temporary table. Temporary tables are denoted by a # appearing in their name. When using a temporary table to upsert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be upserting to. For instance:
INSERT INTO Dataset_Example#Temp (Col1, Col2, Col3) VALUES ('val1', 'val2', 'val3') INSERT INTO Dataset_Example#Temp (Col1, Col2, Col3) VALUES ('val4', 'val5', 'val6') INSERT INTO Dataset_Example#Temp (Col1, Col2, Col3) VALUES ('val7', 'val8', 'val9')Then you need to upsert to the actual table.
UPSERT INTO Dataset_Example (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM Dataset_Example#TempIf the AsyncBulkOperation connection property is set to 'true' the provider will allow Tableau CRM to process the data while your application continues executing. The downside of using asynchronous services is that you will need to check the status of the upload manually to see if Salesforce has finished processing the request and see if there were any issues. You can do this by calling the GetJobStatus stored procedure. You can get the value of the JobId required for the GetJobStatus stored procedure from the LastResultInfo#Temp table.
SELECT JobId FROM LastResultInfo#Temp