Bi Dev Guide Ext Data
DOWNLOAD
First things first !
To download this implementation guide, click the download button below.
If you need more information about the implementation guide, you can read the Table of Contents below.
Analytics External Data API
Developer Guide
Salesforce Spring
salesforcedocs
Last updated January
Copyright Salesforce Inc All rights reserved Salesforce is a registered trademark of Salesforce Inc as are other
names and marks Other marks appearing herein may be trademarks of their respective owners
CONTENTS
External Data
Load External Data into a Dataset
External Data API Reference
EXTERNAL DATA
You can integrate external data into CRM Analytics to make the data available for queries from explorer and designer
The External Data API enables you to upload external data files to CRM Analytics The External Data API can upload csv files and you can
optionally specify the structure of your data by defining metadata in JSON format
The External Data API is available in API version and later
The highlevel steps for uploading external data by using the API are
Prepare your data in CSV format and then create a metadata file to specify the structure of the data
Connect programmatically to your Salesforce organization
Configure the upload by inserting a row into the InsightsExternalData object and then set input values such as the name of the
dataset the format of the data and the operation to perform on the data
Split your data into MB chunks and then upload the chunks to InsightsExternalDataPart objects
Start the upload by updating the Action field in the InsightsExternalData object
Monitor the InsightsExternalData object for status updates and then verify that the file upload was successful
Load External Data into a Dataset
External Data API Limits
When working with the External Data API consider the following limits in addition to the general CRM Analytics limits
The following limits apply to all supported editions
Limit
Value
Maximum file size per external data uploads
GB
Maximum file size for all external data uploads in a rolling hour GB
period
Maximum number of external data jobs per dataset that can be
run in a rolling hour period
Maximum number of characters in a field
Maximum number of fields in a record
including up to date fields
External Data
Prepare Data Files
Limit
Value
Maximum number of characters for all fields in a record
Prepare Data Files
To upload external data from csv files into a dataset first prepare your data files
External data can be loaded into a dataset by preparing two files
A data file which contains the external data in commaseparated value CSV format
An optional metadata json file which describes the structure of the data file
Note Providing a metadata file is recommended
The data and metadata files are used to populate a dataset with the external data For detailed information about formatting CSV data
and JSON metadata see the Analytics Extended Metadata XMD Developer Guide
Connect to Salesforce
After preparing your data files the next step in loading external data into CRM Analytics is to connect to your Salesforce organization
by using standard Salesforce APIs
Note The following examples use the SOAP API but you can use any of the Salesforce APIs such as the REST API or Apex The
examples assume that youre using the Web Services Connector
To load external data into CRM Analytics first connect to your Salesforce organization Use the PartnerConnection object to log in to
your organization as shown in the following example Youll need to supply a username password and endpoint
ConnectorConfig config new ConnectorConfig
configsetUsernameusername
configsetPasswordpassword
configsetAuthEndpointendpoint
PartnerConnection partnerConnection new PartnerConnectionconfig
For more information about the Web Services Connector WSC see Introduction to the Web Services Connector For more information
about user authentication see Security and the API in the SOAP API Developer Guide
Configure the Upload
Configure the external data upload by inserting a row into the InsightsExternalData object and setting configuration values
After establishing a connection with Salesforce insert a row into the InsightsExternalData object to configure and control the upload
The InsightsExternalData object provides a header that contains information about the upload such as the name of the dataset the
format of the data and the operation to perform on the data You can also provide the metadata file The following example inserts a
row into the InsightsExternalData object and sets configuration values
SObject sobj new SObject
sobjsetTypeInsightsExternalData
sobjsetFieldFormatCsv
sobjsetFieldEdgemartAlias DatasetName
sobjsetFieldMetadataJsonmetadataJson
External Data
Add the Data
sobjsetFieldOperationOverwrite
sobjsetFieldActionNone
SaveResult results partnerConnectioncreatenew SObject sobj
forSaveResult svresults
ifsvisSuccess
parentID svgetId
Note The WSC converts the metadata json file to a Baseencoded string but if youre using the REST API youll need to make
this conversion yourself
For detailed information about the InsightsExternalData object see InsightsExternalData
Add the Data
When uploading external data files you can use the InsightsExternalDataPart object to load the data in smaller chunks
After inserting a row into the InsightsExternalData header object split your data into MB chunks and upload the chunks to
InsightsExternalDataPart objects You associate the part objects with the header object by setting the InsightsExternalDataId
field on the part objects to the ID of the header object The part objects contain the bytes of data and must be assigned part numbers
in a contiguous sequence starting with
Ensure that the chunks of data are smaller than MB If the data is compressed it must be compressed first and then split into MB
chunks Only the gzip format is supported
The following example splits a file into MB chunks and then uploads the chunks to InsightsExternalDataPart objects
ListFile fileParts chunkBinarydataFile Split the file
forint i ifilePartssizei
SObject sobj new SObject
sobjsetTypeInsightsExternalDataPart
sobjsetFieldDataFile FileUtilsreadFileToByteArrayfilePartsgeti
sobjsetFieldInsightsExternalDataId parentID
objsetFieldPartNumberi Part numbers should start at
SaveResult results partnerConnectioncreatenew SObject sobj
forSaveResult svresults
ifsvisSuccess
rowId svgetId
For detailed information about the InsightsExternalDataPart object see InsightsExternalDataPart
Manage the Upload
After youve created a header and uploaded the data parts by using the InsightsExternalData and InsightsExternalDataPart
objects update the Action field on the header object to Process to start processing the data
The following example sets the Action field and updates the row in the InsightsExternalData object
SObject sobj new SObject
sobjsetTypeInsightsExternalData
sobjsetFieldActionProcess
External Data
External Data API Reference
sobjsetIdparentID This is the rowID from the previous example
SaveResult results partnerConnectionupdatenew SObject sobj
forSaveResult svresults
ifsvisSuccess
rowId svgetId
When the Action field is set to Process a dataflow job is created and marked active You can monitor the Status field of the
header object to determine when the file upload is completed After the Action field is updated to request processing no user edits
are allowed on the objects
External Data API Reference
The InsightsExternalData Object
The InsightsExternalData object enables you to configure and control external data uploads You can use it to provide metadata trigger
the start of the upload process check status and request cancellation and cleanup
The InsightsExternalData object is used with the InsightsExternalDataPart object which holds the parts of the data to be uploaded
Together they provide a programmatic way to upload a large file in parts and trigger a dataflow into a dataset The first step is to insert
a row into the InsightsExternalData object Data parts are then uploaded to InsightsExternalData objects The Mode field is used to
enable faster uploads The Action field of the InsightsExternalData object is updated to start processing and request cancellations
After the Action field is updated to request processing no user edits are allowed on the objects except to request cancellation
Note The standard system fields CreatedById CreatedDate LastModifiedById LastModifiedDate and
SystemModstamp are documented in System Fields in the Salesforce Object Reference
The InsightsExternalData object is available in API version and later
Supported Calls
create delete describeSObjects query retrieve update upsert
Fields
Field
Details
Action
Type
Picklist
Properties
Create Filter Group Sort Update
Description
The action to perform on this data Picklist values are
Abort
Reserved for future use The user no longer wants to upload the data and is requesting
that the system stop processing if possible
External Data
Field
The InsightsExternalData Object
Details
Delete
Reserved for future use The user wants to remove uploaded data parts as soon as
possible Implies that an Abort status is queued
None
The user has not completed the data upload This value is the default when the object
is created
Process
The user has completed the data upload and is requesting that the system process the
data
CompressedMetadataLength Type
Int
Properties
Create Filter Group Nillable Sort Update
Description
The length of the compressed metadata json file This field is overwritten when data is
uploaded This system field is not editable
Dataflow
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
For dataflows that were created in API version and later The unique ID of the dataflow
that was used to create the dataset You can use this field to get the status of the dataflow
This system field is not editable
Description
Type
String
Properties
Create Filter Nillable Sort Update
Description
The description of the dataset that is only used when creating the dataset
EdgemartAlias
Type
String
Properties
Create Filter Group Sort Update
Description
The alias of a dataset which must be unique across an organization The alias must follow
the same guidelines as other field names except that they cant end with c Can be up
External Data
Field
The InsightsExternalData Object
Details
to characters For more information see Field Names in the CRM Analytics External Data
Format Developer Guide
EdgemartContainer
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
The name of the app that contains the dataset
If the name is omitted when youre creating a dataset the name of the users private
app is used
If the name is omitted for an existing dataset the system resolves the app name
If the name is specified for an existing dataset the name is required to match the name
of the current app that contains the dataset
Note Use the developer name or the ID of the app for the name To get the developer
name or ID run the following query
SELECT IdDeveloperNameName AccessTypeCreatedDateType
FROM Folder where Type Insights
For example the display label of an app is Analytics Cloud Public Datasets but the
developer name is AnalyticsCloudPublicDatasets
EdgemartLabel
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
The display name for the dataset Can be up to characters
FileName
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
Identifier of the external data file such as the file name The value does not have to be unique
It can contain only alphanumeric characters and underscores It must begin with a letter
not include spaces not end with an underscore and not contain two consecutive underscores
The maximum file name is characters
Note CRM Analytics doesnt populate this field You can manually update it via the
External Data API
External Data
The InsightsExternalData Object
Field
Details
Format
Type
Picklist
Properties
Create Filter Group Sort Update
Description
The format of the uploaded data Picklist values are
Csv
The data is in CSV format
Binary
Reserved for Salesforce internal use
isDependentOnLastUpload Type
Boolean
Properties
Create Defaulted on create Filter Group Sort Update
Description
Reserved for future use When false indicates that this upload depends on the previous
upload to the same dataset name
isIndependentParts
Type
Boolean
Properties
Create Defaulted on create Filter Group Sort Update
Description
Reserved for future use When true indicates that file parts have been divided on row
boundaries and can be processed independently of each other The default is false
LicenseType
Type
picklist
Properties
Create Defaulted on create Filter Group Nillable Restricted picklist Sort Update
Description
The license type of the external data file Possible values are
Cdp
EinsteinAnalytics
MulesoftDataPath
Sonic
The default value is EinsteinAnalytics
External Data
The InsightsExternalData Object
Field
Details
MetaDataLength
Type
Int
Properties
Create Filter Group Nillable Sort Update
Description
The length of the metadata json file This field is overwritten when data is uploaded
This system field is not editable
MetadataJson
Type
Blob Baseencoded string
Properties
Create Nillable Update
Description
Metadata in JSON format which describes the structure of the uploaded file
Mode
Type
picklist
Properties
Create Filter Group Nillable Restricted picklist Sort Update
Description
The upload mode for the data Possible values are
None
The default value is None
NotificationEmail
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
The email address to send notifications to Can be up to characters and can contain only
one email address Defaults to the current users email address
NotificationSent
Type
Picklist
Properties
Create Filter Group Nillable Sort Update
Description
Indicates when to send notifications about the upload Picklist values are
Always
Always send notifications
External Data
Field
The InsightsExternalData Object
Details
Never
Never send notifications
Failures
Send notifications if the upload process failed
Warnings
Send notifications if warnings occurred during the upload
Success
Send notifications if the upload is successful
SuccessFailures
Send notifications if the upload is successful or if the process failed
WarningsFailures
Send notifications if warnings or failures occurred during the upload
WarningsSuccess
Send notifications if the upload is successful or if warnings occurred during the upload
The default value is WarningsFailures
Operation
Type
Picklist
Properties
Create Filter Group Sort Update
Description
Indicates which operation to use when youre loading data into the dataset Picklist values
are
Append
Append all data to the dataset Creates a dataset if it doesnt exist
Note If the dataset or rows contain a unique identifier the append operation
is not allowed
Delete
Delete the rows from the dataset The rows to delete must contain one and only one
field with a unique identifier
Overwrite
Create a dataset with the given data and replace the dataset if it exists
Upsert
Insert or update rows in the dataset Creates a dataset if it doesnt exist The rows to
upsert must contain one and only one field with a unique identifier For more
information about unique identifiers see isUniqueId in the CRM Analytics External Data
Format Developer Guide
Note A metadata JSON file is required for the append upsert and delete operations
The data and metadata for the append and upsert operations must match the dataset
on which the operation is happening All columns dimensions and measures must
External Data
Field
The InsightsExternalData Object
Details
match exactly The metadata for the delete operation must be a subset of the dataset
columns
Status
Type
Picklist
Properties
Create Filter Group Sort Update
Description
The status of this data upload The initial value is null Picklist values are
Completed
The data upload job was completed successfully Data parts are retained for seven days
after completion
CompletedWithWarnings
The data upload job completed but contains warnings Data parts are retained for seven
days after completion
Failed
The data upload job failed Data parts are retained for seven days after failure
InProgress
The data upload job is in progress
New
The data upload job has been created
NotProcessed
The data upload job was aborted on user request Data parts have been removed
Queued
The data upload job has been scheduled This system field is not editable
The default value is New
StatusMessage
Type
String
Properties
Create Nillable Update
Description
The reason for the file upload failed or has warnings This system field is not editable
SubmittedDate
Type
String
Properties
Create Filter Nillable Sort Update
Description
The time when the upload was submitted or set to Process This system field is not editable
External Data
The InsightsExternalDataPart Object
Field
Details
Target
Type
picklist
Properties
Create Filter Group Nillable Restricted picklist Sort Update
Description
The target for the external data Valid values are Dataset
The InsightsExternalDataPart Object
The InsightsExternalDataPart object enables you to upload an external data file that has been split into parts
The InsightsExternalDataPart object works with the InsightsExternalData object After you insert a row into the InsightsExternalData
object you can create part objects to split up your data into parts If your initial data file is larger than MB split your file into parts that
are smaller than MB
Note The standard system fields CreatedById CreatedDate LastModifiedById LastModifiedDate and
SystemModstamp are documented in System Fields in the Salesforce Object Reference
The InsightsExternalDataPart object is available in API version and later
Supported Calls
create delete describeSObjects query retrieve update upsert
Fields
Field
Details
CompressedDataLength
Type
Int
Properties
Create Filter Group Nillable Sort Update
Description
The length of the compressed data This field is overwritten when data is uploaded
DataFile
Type
Blob Baseencoded string
Properties
Create Nillable Update
Description
The data bytes Parts are required to be smaller than MB For data greater than MB
compress the file and then split it into parts Only the gzip format is supported
