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 February
Copyright salesforcecom inc All rights reserved Salesforce is a registered trademark of salesforcecom 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 Analytics to make the data available for queries from explorer and designer
The External Data API enables you to upload external data files to 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 Analytics limits
The following limits apply to all supported editions Contact salesforcecom to extend the limits
External Data Limits
Limit
Value
Maximum file size per external data upload
GB
Maximum file size for all external data uploads in a rolling hour GB
period
Maximum number of external data jobs that can be run in a
rolling hour period
Maximum number of characters in a field
External Data
Prepare Data Files
Limit
Value
Maximum number of fields in a record
including up to date fields
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 Reference
Connect to Salesforce
After preparing your data files the next step in loading external data into 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 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 Connectoron the Salesforce
Developers website For more information about user authentication see Security and the API in the SOAP API Developers 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
External Data
Add the Data
sobjsetFieldMetadataJsonmetadataJson
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
External Data
External Data API Reference
sobjsetFieldActionProcess
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 cancelation 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 Action field of the
InsightsExternalData object is updated to start processing and request cancelations After the Action field is updated to request
processing no user edits are allowed on the objects except to request cancelation
Note The standard system fields CreatedById CreatedDate LastModifiedById LastModifiedDate and
SystemModstamp are documented in System Fields in the SOAP API Developers Guide
The InsightsExternalData object is available in API version and later
Fields
Field
Details
Id
Type
String
Properties
Defaulted on create Filter Group Sort
Description
The unique ID that the system generates for new jobs This system field is not editable
EdgemartAlias
Type
String
Properties
Create Filter Group Sort Update
External Data
Field
The InsightsExternalData Object
Details
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 to
characters For more information see Field Names in the Analytics External Data Format Reference
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
MetadataJson
Type
Blob Baseencoded string
Properties
Create Nillable Update
Description
Metadata in JSON format which describes the structure of the uploaded file
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
External Data
Field
The InsightsExternalData Object
Details
Binary
Reserved for Salesforce internal use
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 Analytics External Data Format Reference
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 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
External Data
Field
The InsightsExternalData Object
Details
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
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
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
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
isDependentOnLastUpload Type
Boolean
Properties
Create Defaulted on create Filter Group Sort Update
External Data
Field
The InsightsExternalData Object
Details
Description
Reserved for future use When false indicates that this upload depends on the previous
upload to the same dataset name
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
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
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
Never
Never send notifications
Failures
Send notifications if the upload process failed The API name is Failures
Warnings
Send notifications if warnings or errors occurred during the upload The API name is Warnings
NotificationEmail
Type
String
Properties
Create Filter Group Nillable Sort Update
External Data
Field
The InsightsExternalData Object
Details
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
EdgemartLabel
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
The display name for the dataset Can be up to characters
IsDeleted
Type
Boolean
Properties
Defaulted on Create Filter Group Sort
Description
Indicates whether the object has been moved to the Recycle Bin true or not false 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
WorkflowId
Type
String
Properties
Create Filter Group Nillable Sort Update
Description
Deprecated in API version Use the Dataflow attribute instead
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
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 Analytics doesnt populate this field You can manually update it via the External
Data API
Description
Type
String
Properties
Create Filter Nillable Sort Update
Description
The description of the dataset that is only used when creating the dataset
StatusMessage
Type
String
Properties
Create Nillable Update
Description
The reason for the file upload failed or has warnings This system field is not editable
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 SOAP API Developers Guide
The InsightsExternalDataPart object is available in API version and later
External Data
The InsightsExternalDataPart Object
Fields
Field
Details
Id
Type
String
Properties
Defaulted on create Filter Group Sort
Description
The unique ID of the part
PartNumber
Type
Int
Properties
Create Filter Group Sort Update
Description
The part number Part numbers are required to be in a contiguous sequence starting with
For example etc
InsightsExternalDataId
Type
String
Properties
Create Filter Group Sort
Description
The ID of the InsightsExternalData object that this part belongs to
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
DataLength
Type
Int
Properties
Create Filter Group Nillable Sort Update
Description
The length of the data This field is overwritten when data is uploaded