in implementation guides ~ read.
Bi Dev Guide Ext Data Format

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.

Download

Analytics External Data Format
Reference
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 Metadata Overview
External Data Metadata Format Reference

EXTERNAL DATA METADATA OVERVIEW

To upload external data into an 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 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
format

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

External Data Metadata Overview

Dates must conform to specific formats and they must match the formats exactly For more information see Date Formats

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 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

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 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

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 numeric or date value See also Numeric Formats and
Date values Date Formats
only
Example
format Numeric
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

External Data Metadata Format Reference

Field Name

Type

Required Description
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

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 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

External Data Metadata Format Reference

Field Name

Type

Required Description
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

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 isnt used in data ingestion It is used only to specify how numeric values are formatted when

displayed in the UI 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

Z

The reference UTC time zone

***