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
Reference
Salesforce Spring
salesforcedocs
Last updated March
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 Metadata Overview
External Data Metadata Format Reference
EXTERNAL DATA METADATA OVERVIEW
To upload external data into an Analytics Cloud 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 Field Names
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
fieldsEnclosedBy
fieldsDelimitedBy
numberOfLinesToIgnore
objects
connector AcmeCSVConnector
description
fullyQualifiedName SalesData
label Sales Data
name SalesData
fields
description
fullyQualifiedName SalesDataName
label Account Name
name Name
External Data Metadata Overview
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
The delimiter for field values in a row must be a comma
If a field value contains a comma a new line or a double quote the field value must be contained within double quotes 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 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
Numeric values in the CSV file cant contain any formatting such as currency symbols or grouping separators For example
is not a valid numeric value the correct value is
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 Date Formats
External Data Metadata Overview
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 Field Name Restrictions
External Data Limits
The following limits apply to all supported editions
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 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
fieldsEnclosedBy
String
No
The character that can be used to enclose fields in the csv file Only
double quotes are supported If a double quote is used within a field
escape it by preceding it with another double quote If this field is
included it must be set to
Example
fieldsEnclosedBy
fieldsDelimitedBy
String
No
The character that separates field values in the csv file If this field is
included it must be set to
Example
fieldsDelimitedBy
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
External Data Metadata Format Reference
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 Tableau
CRM 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
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 Names
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 Names
External Data Metadata Format Reference
Field Name
Type
Required Description
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 Names
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
Example
name Amount
Note With Summer Plus we recommend that field names
in datasets use no more than characters Long field names
increase the likelihood of exceeding character limits when you
augment datasets 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 Names
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
External Data Metadata Format Reference
Field Name
Type
Required Description
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
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 value Deprecated for numeric values See also
Date values Date Formats
only
Example
format ddMMyy HHmmss Date
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
External Data Metadata Format Reference
Field Name
Type
Required Description
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
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
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 Cloud
generates fiscal date fields To generate fiscal date fields set
fiscalMonthOffset to a value other than
See also Date Handling in Datasets
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
External Data Metadata Format Reference
Field Name
Type
Required Description
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
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 You are 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
Currency sign
External Data Metadata Format Reference
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
is not a valid numeric value the correct value is
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
AM
MMddyyyy hhmmss a
AM
External Data Metadata Format Reference
Format
Sample Value
MMddyy hhmmss a
AM
HHmmss ddMMyyyy
HHmmss ddMMyy
These formats use the following symbols
Symbol
Meaning
yyyy or yy
Fourdigit year yyyy or twodigit year yy
MM
Twodigit month
M
Onedigit month when month less than
dd
Twodigit day
d
Onedigit day when day less than
T
A separator that indicates that time of day follows
HH
Twodigit hour
H
Onedigit hour when hour less than
mm
Twodigit minute
m
Onedigit minute when minute less than
ss
Twodigit second
s
Onedigit second when second less than
SSS
Optional threedigit milliseconds
