Data Delivery Service

Overview

Data Delivery Service (DDS) enables companies to extract massive amounts of data from Sage Intacct and send that data to a cloud storage location.

DDS is useful for companies that do cross-system reporting, that want to use analytics tools outside of Sage Intacct, and/or need to share information across other companies they own or manage.

DDS is not configured separately for entities and data is always extracted from the top level.

Subscription

Data Delivery Service can be subscribed in your company by navigating to Company > Admin > Subscriptions.

DDS is not a free subscription. Make sure to verify that this subscription has been purchased beforehand.

Jobs

DDS Jobs can be scheduled or run on demand in the Sage Intacct UI, or they can be run on demand from the API. You need to define a cloud storage destination in the UI before you can run any jobs.

You can run asynchronous DDS jobs for single objects or batches of objects. Consider whether you want to get all the records for the given objects, or only those records that changed since the last run.

DDS also supports synchronized job runs if you want to avoid reporting gaps. You can request the changed records for up to five objects in a synchronized run.

Job files are always delivered as separate CSV files. Files can be split based on a maximum number of records. It is your responsibility to process and load the files into your own database.

Objects

DDS allows certain Sage Intacct standard objects and all custom platform objects to be exported as jobs. The standard objects can be found on the DDS Objects page, or you can list both standard and custom objects using the get getDdsObjects API call.

DDS job output file

The result of a DDS job is one or more CSV job files, with the name of the object used as the beginning of the file name. Information about fields in the job file is provided below.

Standard fields

The following fields are included in every job file.

Name Description
ddsReadTime Timestamp when the record was read from the Sage Intacct UI. See the information about data integrity between objects below.
ddsChangeType For jobs that publish only changed records, this field indicates whether the record was created, updated, or deleted. Reported values are create, update, or delete. Note that by default, deleted records are reported in a separate CSV file.
WHENMODIFIED Timestamp when a Sage Intacct user last modified the record. Note that this field might have slight variations in its name. Derived tables will have a link to a table that provides this value. If the value is 1970-01-01T00:00:00Z, it means the record was created before auditing and timestamps were implemented in the Sage Intacct system.
MODIFIEDBY Foreign key that identifies the user who last modified the record. Typically, this is the RECORDNO value of the USERINFO object.
WHENCREATED Timestamp when the record was initially created. Note that this field might have slight variations in its name and derived tables will not have this value. If the value is 1970-01-01T00:00:00Z, it means the record was created before auditing and timestamps were implemented in the Sage Intacct system.
CREATEDBY Foreign key that identifies the user who created the record. Typically, this is the RECORDNO value of the USERINFO object.

About ddsReadTime and data integrity

In order to prevent issues with data integrity, DDS runs all related jobs against data as it existed when the job started (at ddsReadTime).

This prevents problems that might have otherwise occurred when:

Denormalized fields

Many objects contain fields that were stored in related tables within Sage Intacct. These fields are added directly to reduce needed joins. For example, the CUSTOMER table includes many fields from the related CONTACT.

Special fields

The following fields merit additional attention.

Name Description
RECORDNO Most objects have a RECORDNO field, which is Sage Intacct’s internal key.
MEGAENTITYKEY In multi-entity shared implementations, the foreign key (RECORDNO) of the location for the entity that owns this record.
MEGAENTITYID Like MEGAENTITYKEY, but links to the LOCATIONID field of the location.
MEGAENTITYNAME Like MEGAENTITYKEY, but links to the NAME field of the location.
STATUS Inactive records are typically hidden from list views in Sage Intacct and are often prohibited in transactions. Values can be either active or inactive.

Field types

DDS follows the published object definitions as closely as possible. If data types for a field are not found in the list of DDS objects, refer to the object list in the UI under Platform Services > Objects. Alternatively, you can use the inspect API function to get the data types for fields when the details argument is set.

Following are general notes about data types:

Boolean

Boolean values are typically represented as either TRUE or FALSE. There may be a few exceptions where these values are represented as T, F, true, or false. Null values are possible in Boolean fields and usually indicate an unset value.

Date and Datetime

All date and datetime values follow the ISO-8601 standard for date formatting, YYYY-MM-DD and YYYY-MM-DDThh:mm:ssTZD.

Sample Date: 2017-04-26

Sample Datetime: 2017-04-26T06:55:40Z. Note that the Z is a special UTC (Coordinated Universal Time) designator.

Foreign keys

DDS follows a few patterns for defining relationships between objects:

Pattern Description
*KEY RECORDNO where * is the name of the related object.
*ID ID where * is the name of the related object.

Refer to the entity relationship diagrams or object definitions to get details on foreign keys relationships.

Encoding

Sage Intacct stores all data encoded in UTF-8. Differences in observed formatting may be related to the tool used to view or import the data.

Job queueing

DDS Jobs can consume extraordinary computing resources. For this reason, DDS jobs are queued for execution. DDS jobs run in a queue shared by other offline jobs such as scheduled transactions and offline reports. DDS jobs do not compete with either user interface or API requests. Organizations with the standard Level of Service are limited to serial execution of these jobs. Customers with a premium Level of Service may be allowed two or more parallel job executions (depending on their Level of Service).

Multi-entity handling

DDS is not configured separately for entities and data is always extracted from the top level. This is true even if you slide into the entity of a multi-entity company before invoking the job. DDS jobs may not be invoked by users with entity restrictions. Each object that may have entity restrictions includes columns for defining those restrictions. Refer to the MEGAENTITY prefixed fields above in Special Fields.

Record change types

By default, DDS separates records that were either created or updated from records that were deleted. This allows developers to run a single routine to handle all creates and updates and a separate routine to handle record deletions. DDS takes special care to record the correct action:

File compression

Optionally, you can configure a DDS job to compress files. DDS uses PKZIP compression, so any standard ZIP decompression tool can be used to unzip the files.

Loader sample

A DDS loader sample using AWS S3 and Elastic Beanstalk was presented at Advantage 2016. You can find the sample code in the intacct-advantage-2016 repository.

Provide feedback