Bi Dev Guide Ext Data Format
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 Format
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 Metadata Overview
External Data Metadata Format Reference
EXTERNAL DATA METADATA OVERVIEW
To upload external data into a CRM Analytics dataset make sure that you have prepared your data and metadata files
You can load external data into a dataset by preparing two files
A data file which contains the external data in commaseparated value CSV format
An optional metadata file which describes the structure of the data file in JSON format
Note Providing a metadata file is recommended Otherwise every field is treated as text
The data and metadata files are used to populate a dataset with the external data
CSV Example
The following CSV example contains data that conforms to the json metadata file thats described next
NameAmountCloseDate
opportunityA
opportunityB
The first row in the CSV file lists the field names for your dataset Each subsequent row corresponds to a record of data A record consists
of a series of fields delimited by commas For information on creating valid field names see External Data Metadata Format Reference
orgs
JSON Example
The following JSON example represents a SalesData object with three fields Name Amount and CloseDate The example corresponds
to the preceding CSV example
fileFormat
charsetName UTF
fieldsDelimitedBy
fieldsEnclosedBy
fieldsEscapedBy
linesTerminatedByrn
numberOfLinesToIgnore
objects
connector AcmeCSVConnector
description
fullyQualifiedName SalesData
label Sales Data
name SalesData
fields
External Data Metadata Overview
description
fullyQualifiedName SalesDataName
label Account Name
name Name
isSystemField false
isUniqueId false
isMultiValue false
type Text
description
fullyQualifiedName SalesDataAmount
label Opportunity Amount
name Amount
isSystemField false
defaultValue
isUniqueId false
type Numeric
precision
scale
description
fullyQualifiedName SalesDataCloseDate
label Opportunity Close Date
name CloseDate
isSystemField false
isUniqueId false
type Date
format MMddyyyy
fiscalMonthOffset
CSV Format
The External Data API uses a strict format for field values to optimize processing for large sets of data Note the following when generating
csv files
If a field value contains a control character or a new line the field value must be contained within double quotes or your
fieldsEscapedBy value The default control characters fieldsDelimitedBy fieldsEnclosedBy
fieldsEscapedBy or linesTerminatedBy are comma and double quote For example Director of
Operations Western Region
If a field value contains a double quote escape the double quote by preceding it with another double quote or your
fieldsEscapedBy value for example This is the gold standard
Field values arent trimmed A space before or after a delimiting comma is included in the field value A space before or after a double
quote generates an error for the row For example JohnSmith is valid John Smith is valid but the second value is
Smith John Smith isnt valid
External Data Metadata Overview
The maximum numeric value is and the minimum is
Dates must conform to specific formats and they must match the formats exactly For more information see External Data Metadata
Format Reference orgs
At least one column in the CSV file must contain dimension values
If column headers are specified the number of column headers must equal the number of columns in each record
For more information about field names see External Data Metadata Format Reference orgs
External Data Limits
The following limits apply to all supported editions
Value
Limit
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
Maximum number of characters for all fields in a record
EXTERNAL DATA METADATA FORMAT REFERENCE
The metadata describes the structure of external data files The metadata file is in JSON format The json file consists of main sections
file format object information and field information Include all required fields when you create a record but you can leave out optional
fields
The File Format Section
The file format section of the metadata file specifies information about the format of the data file including the character set and delimiter
character
Field Name
Type
Required Description
charsetName
String
No
The character set of the csv file If this field is included it must be set
to UTF
Example
charsetName UTF
fieldsDelimitedBy
String
No
The character that separates field values in the csv file Any single
character is supported
Example
fieldsDelimitedBy
fieldsEnclosedBy
String
No
The character that can be used to enclose fields in the csv file Any
single character that isnt the fieldsDelimitedBy value is
supported If a double quote is used within a field escape it by
preceding it with another double quote
Example
fieldsEnclosedBy
fieldsEscapedBy
String
No
The character used to escape or bypass the creation of a row of data
based on detecting the fieldsDelimitedBy value The default
is a double quote which would surround the data to escape
Example
fieldsEscapedBy
Example
This is an alternate escaping option to fieldsEnclosedBy To
illustrate if fieldsDelimitedBy is comma
fieldsEnclosedBy is double quote and
External Data Metadata Format Reference
Field Name
Type
Required Description
fieldsEscapedBy is backslash these two lines would result in
the same thing
colcolwithcommascol
colcolwithcommascol
linesTerminatedBy
String
numberOfLinesToIgnore Number
No
Deprecated Do not use
No
The number of lines for the parser to ignore Allows you to specify a
header
When the csv file doesnt have a header set to
When the csv file has a header set to
Example
numberOfLinesToIgnore
The Objects Section
The objects section of the metadata file specifies information about the toplevel database object including objectlevel security
information display name and API name
Note The metadata file can contain only object definition
Field Name
Type
rowLevelSecurityFilter String
Required Description
No
The predicate thats used to apply rowlevel security on the dataset
When entering the predicate in the metadata file JSON escape double
quotes around string values
Example
rowLevelSecurityFilter OwnerId
UserId
For more information about creating the predicate see the Analytics
Security Implementation Guide
connector
String
Yes
The string that uniquely identifies the client application
Example
connector AcmeCSVConnector
description
String
No
The description of the object Must be less than characters
Example
description The SalesData object tracks
basic sales data
External Data Metadata Format Reference
Field Name
Type
Required Description
fullyQualifiedName
String
Yes
The full path that uniquely identifies the record Must be less than
characters
Example
fullyQualifiedName CRMSalesData
For information on creating valid field names see Field Name
Restrictions in this topic
label
String
Yes
The display name for the object Can be up to characters
Example
label Sales Data
name
String
Yes
The unique API name for the object Can be up to characters
Example
name SalesData
For information on creating valid field names see Field Name
Restrictions in this topic
fields
Array
Yes
The array of fields for this object
The Fields Section
The fields section of the metadata file specifies information about each field in the record including data type and formatting information
Note The fields must be in the same order as the CSV columns are in
Field Name
Type
Required Description
fullyQualifiedName
String
Yes
The full path that uniquely identifies the field objectfield Must be
less than characters
Example
fullyQualifiedName SalesDataAmount
For information on creating valid field names see Field Name
Restrictions in this topic
label
String
Yes
The display name for the field Can be up to characters
Example
label Opportunity Amount
name
String
Yes
The unique API name for the field Can be up to characters
External Data Metadata Format Reference
Field Name
Type
Required Description
Example
name Amount
Note With Summer Plus we recommend that field names
in dataset use no more than characters Long field names
increase the likelihood of exceeding character limits when you
augment dataset because names are appended Field names
longer than characters are currently supported but we
recommend shortening them to avoid issues
For information on creating valid field names see Field Name
Restrictions in this topic
description
String
No
The description of the field Must be less than characters
Example
description The Amount field contains the
opportunity amount
isSystemField
Boolean
No
Indicates whether this field is a system field to be excluded from query
results
Example
isSystemField false
type
String
Yes
The type of the field Can be Text Numeric or Date
Example
type Numeric
defaultValue
String
No
The default value of the field if any All numeric types require a default
value
isUniqueId
Boolean
No
Indicates whether this field is the primary key for the record This field
is required for incremental extract Only field can be set to be the
unique ID
Note Only text fields can be unique IDs Numeric date and
multivalue fields cant be unique IDs
Example
isUniqueId false
isMultiValue
Boolean
No
For text fields only Indicates whether the field has multiple values
Applies only to Text fields
Example
isMultiValue false
External Data Metadata Format Reference
Field Name
Type
Required Description
multiValueSeparator
String
No
For text fields only The character that separates multiple values The
default is
If isMultiValue equals true specify a value
If isMultiValue equals false this field can be set to null
Example
multiValueSeparator
format
String
Yes for
The format of the date or numeric value See also Date Formats and
Date values Numeric Formats
only
Example
format ddMMyy HHmmss Date
Example
format Numeric
precision
Number
Yes for
Numeric
values
The maximum number of digits in a numeric value or the length of a
text value
For numeric values Includes all numbers to the left and to the right of
the decimal point but excludes the decimal point character Value
can be up to
For text values Value defaults to characters but can be up to
characters
Example
precision
scale
Number
Yes for
Numeric
values
The number of digits to the right of the decimal point in a numeric
value Must be less than the precision value
Example
scale
canTruncateValue
Boolean
No
For text fields only Indicates whether to truncate a value when the
value exceeds the precision The default is true
If true truncates the value
If false the row is rejected
Example
canTruncateValue true
currencySymbol
String
No
For numeric fields only The character that signifies the values currency
Example
currencySymbol
External Data Metadata Format Reference
Field Name
Type
Required Description
decimalSeparator
String
No
For numeric fields only The character that separates digits in a decimal
number Can be used to handle international number formats where
the decimal separator is The default is
Example
decimalSeparator
fiscalMonthOffset
Number
No
For date fields only The difference in months between the fiscal year
and the calendar year For example if the fiscal year starts in January
the offset is If the fiscal year starts in October the offset is
Example
fiscalMonthOffset
Note This attribute also controls whether Analytics generates
fiscal date fields To generate fiscal date fields set
fiscalMonthOffset to a value other than
See also Date Handling in Datasets
groupSeparator
String
No
For numeric fields only The character that separates digit groups in a
number Can be used to handle international number formats where
the group separator is The default is
Example
groupSeparator
isYearEndFiscalYear
Boolean
No
For date fields only Indicates whether the fiscal year is the year in which
the fiscal year ends or begins Because the fiscal year can start in one
calendar year and end in another specify which year to use for the
fiscal year
If true then the fiscal year is the year in which the fiscal year ends
The default is true
If false then the fiscal year is the year in which the fiscal year begins
Example
isYearEndFiscalYear true
This field is relevant only when fiscalMonthOffset is greater
than
See also Date Handling in Datasets
firstDayOfWeek
Number
No
For date fields only The first day of the week for the calendar year and
if applicable fiscal year Use to set the first day to be Sunday to set
the first day to be Monday and so on Use to set the first day to be
January The default is
Example
firstDayOfWeek
External Data Metadata Format Reference
Field Name
Type
Required Description
See also Date Handling in Datasets
isSkipped
Boolean
No
Indicates whether to skip the field when the data is uploaded
Example
isSkipped true
Field Name Restrictions
Field names in the csv file and the metadata file
Can contain only alphanumeric and underscore characters
Must begin with a letter
Cant end with an underscore
Cant contain consecutive underscore characters except when ending with c casesensitive
Must be unique across all fields of the object
Numeric Formats
An example of a typical numeric value is which is represented as in the format field Youre required to
specify the precision and scale of the number The format is specified by using the following symbols
Symbol
Meaning
One digit Use to add leading or trailing s like for
Adds zero or one digit
Default symbol used as the decimal separator Use the decimalSeparator field to
set the decimal separator to a different symbol
Minus sign
Grouping separator Use the groupSeparator field to set the group separator to a
different symbol
Currency sign Use the currencySymbol field to set the currency indicator to a different
symbol
Note The format for numeric values when displayed in the UI defaults to No Format Existing formatting is removed For data
ingestion numeric values cant contain any formatting such as currency symbols or grouping separators For example
isnt a valid numeric value the correct value is
Valid characters when defining a numeric format are
External Data Metadata Format Reference
Date Formats
For Date fields specify the format of the date by using one of the following supported formats Dates must match the format exactly
and cant have any extra text For example if the date format is MMddyyyy hhmmss and the value is
the upload fails because the value has extra milliseconds
Note The date formats listed here are the twodigit versions for date fields that use leading zeros for example
AM If a date field doesnt have leading zeros use the onedigit version of the format For example use the format Mdyy
hms a for date values such as AM If you use a twodigit format for a field rows containing values with onedigit
date parts will fail
The timestamp part of each date format is optional
Format
Sample Value
yyyyMMddTHHmmssSSSZ
TZ
yyMMddTHHmmssSSSZ
TZ
yyyyMMddTHHmmssZ
TZ
yyMMddTHHmmssZ
TZ
yyyyMMdd HHmmss
yyMMdd HHmmss
ddMMyyyy HHmmss
ddMMyy HHmmss
ddMMyyyy HHmmss
ddMMyy HHmmss
ddMMyyyy hhmmss a
AM
ddMMyy hhmmss a
AM
ddMMyyyy HHmmss
ddMMyy HHmmss
ddMMyyyy hhmmss a
AM
ddMMyy hhmmss a
AM
MMddyyyy hhmmss a
AM
MMddyy hhmmss a
