Database Collectors

Configuration

Field	Required/ Optional	Comments
Type	Required	Database
Name	Required	Descriptive free-text name for the collector
Data Source	Required	Select a pre-configured data source from the drop down or click Create New to create a new data source.
Schema Name	Required	Enter a name for the schema where the collected data will be stored. This can be a pre-configured schema or a new schema which will be created the first time the collector is run.
Credential	Required	Select a pre-configured Database credential from the drop-down.
Query	Required	Select * FROM (Insert Database table name here)
Use Bookmark Button	Optional	Optional use

Bookmarks

Bookmarks can be used on certain collectors to set a dynamic value each time the collector runs. A field from the collector source will need to be selected to be associated with the Bookmark function. For example, ‘WHENMODIFIED’ is a field in the Sage Intacct GL Detail object that can be used as a Bookmark:

Once the Bookmark Field is set, every collection will save the value based on the latest run time. In the example above, the Bookmark is stored as a 'WHENMODIFIED' date of '05/17/2022 04:22:10'. The next time the Collector runs, it will only pull values with a different value, and then will update the Current Value field dynamically.

Other Notes about Bookmarks:

Most commonly set as date fields, the Bookmark Field can also be a non-date field.
Filters on the collector can still be applied, so even though a Bookmark may be set, if a filter is present it will still only collect data with both the Bookmark value and filters in mind.
Data gets collected into a new stream even when Bookmarks are present, the data does not automatically consolidate into one stream. Consolidation would need to be done in a query.
- To accomplish this consolidation in the query, your FROM section will need to leverage ‘All Streams’. This will also carry a risk that if the bookmark’s ‘Current Value’ is cleared or the bookmark is removed, the same data can be included into a new stream and therefore overstate the dataset that is being used in the query. It is important to be mindful of which collectors and queries are relying on the bookmark functionality and avoid modifying the bookmark while in use. To reset the bookmarked data, make sure to clear the ‘Current Value’ and also purge all streams or create a new Schema so that you’re starting from an empty dataset.

Parameters

Parameters can be applied to a collector to enable ease of access to variables that may change from time to time (e.g. date ranges, filters, mapping values, etc.)

To add a parameter, navigate to the parameters tab and click Add Parameter

Give the parameter a name and select a Type (Boolean, Date, Relative Date or String). Based on this choice, a specific Value field will be displayed:

String

A string is a parameter that is useful for characters, text, numbers, or symbols.

Date

Date parameters provide users the ability to collect data within a specific window of time. The dates are entered as specific dates such as Month/Day/Year.

Relative Date

Relative Date parameters provide users the ability to collect data within a relative window of time. The dates are entered as within a wide variety of timeframes such as start of the first quarter and end of the last quarter.

Boolean

A Boolean Parameter is useful for users wishing to utilize True, False, or NULL values.

Advanced

Field	Required/ Optional	Comments
Schema Update Type	Required	Required. Select Schema Update Type from the drop-down menu.
History Retention (Days)	Optional	Default set as zero. Users may set the days they wish their collector data to be stored.
Agent	Conditional	Onsite/ cloud databases require installation of the Database Agent. Simply select from the drop-down menu provided.
Timeout (seconds)	Optional	The Timeout section allows users to determine if they would like to timeout collections taking longer than a set number of seconds to collect data.
Schedule	Optional	The Schedule option is a convenient way for users to make sure collections are running at the desired time. Simply select from the presets menu provided.

Agent

Please note that your onsite/ cloud databases require installation of the Database Agent. The Agent must be included in your advanced tab.

Collected Data

The Database attribute metadata collection returns the complete set of all custom attributes (excluding the trading partner system attribute for consolidation instances), including member values. DataBlend makes these available in a tabular format with the following columns:

Column Name	Description
rowtype	Value will be either Attribute or AttributeValue. Indicates whether the row represents a custom attribute or the value of a custom attribute.
parentid	Populated for attribute values only. Indicates this value's parent attribute or attribute value.
id	Database internal id number. This value is required when making updates to the attribute or attribute value.
name	Name of the attribute or attribute value.
type	Populated for attributes only. Value will be either account, level, or dimension. Indicates whether the attribute can be applied to a Database, level or dimension.
autocreate	Populated for attributes only. Value will be either 0 or 1. Indicates whether an attribute value can be created automatically for this attribute when data is loaded. If 0, attribute values will not be created. If 1, attribute values will be created.
keepsorted	DataBlend is aware of an issue where no values for keepsorted are returned.
dimensionid	Populated for attributes of type dimension only. Indicates the custom dimension the attribute is associated with.
seqno	Per Database docs:

Details

The details section documents who the Collector was created and updated by and the corresponding times. This allows for easy tracking of multiple Collectors.

Latest Collection

The latest collection section documents the state of the collector, created time, and the status of the query. States include complete and error.

Collections

The Collections section documents when the Collector was created, started, completed and the total amount of data scanned. The status includes information regarding the state of the Collector. This allows for easy tracking of multiple collections.