Timeseries Process Object

The Integrator Timeseries process object is an object that simplifies the creation of time period columns based on a set of rows. It is equivalent to the Diver's Time Series functionality, with options to simplify the calculation of Time Series columns.

The Timeseries object operates by combining rows that are identical for a set of Dimension columns, but differ based on a period column. It creates a set of Summary columns that represent various sums of these columns, based on different selections of these rows.

For example, asking for a year-to-date (YTD) will create a Summary field "Summary Field YTD", which is the summation of all records up to the given period in a specific calendar year.

The Timeseries can create multiple rows for each unique set of Dimension columns, varying the output period from the start period to the end period defined by the start and end attributes. Each row represents a given output period, and the Timeseries data is calculated relative to that time period; year-to-date will be the summation of data from the periods up to that period in that year.

For example, if the start period is "2013-01" and the end period is "2013-12", the Timeseries will create 24 rows for each combination of Dimensions, with Summary columns calculated relative to the different months in that time range. If the start period is equal to the end period, Timeseries columns are calculated relative to that one period.

With multiple output periods, the data must be filtered later on (such as through a Dive or a QuickView) to provide a consistent snapshot of the data as of that output period. The Timeseries object assumes the data is sorted according to the requested Dimensions. If not, you can use the autosort attribute to force such a sort.

Timeseries Attributes

Attribute	Type	Description
process_type (required)	String	Identifies the object as a Timeseries process object. The value of this string is "timeseries".
input (required)	String	Defines the object from which the data flow is arriving.
dimensions	Array of Strings	Defines the input columns that are used to combine rows. Rows that have identical values for all the Dimension columns are combined, with Summary fields calculated based on period types.
summary	Array of Strings	Defines the input columns that should be totalled and assigned to Timeseries columns based on period. The values in the Summary columns must be numeric or blank (null value). Integrator will issue warnings for non-numeric Summary values. It will treat null values as 0 when combining them with non-null values, but will return a null value if no value appears for the given Timeseries columns. For each Summary column, there will be a Timeseries column for each specified period_type.
other_columns	Array of Strings	Defines the input columns that are preserved and passed along as output from the Timeseries object. The values for the other_columns should be identical across input rows with the same combination of Dimensions, otherwise, Integrator issues a warning. To allow for easy cut and paste from a Builder Description file, the attribute can also be named "info", and characters following a ":" in a string will be ignored (since Build Descriptions list Info Fields as "info name":"dimension name". See the description for the remove_other_columns attribute. This attribute is optional, but useful for keeping the data intact.
remove_other_columns	Array of Strings	Defines columns that should not be included in other_columns. If this attribute is present, the other_columns are defined to be all input columns that are not listed in the dimensions, summary, or remove_other_columns attributes. These remaining columns should be identical across input rows with the same combination of Dimensions, as described above for the other_columns attribute. This attribute may be blank (an empty array of strings), in which case all remaining columns are defined as other_columns.
dimcounts	Array of Strings	Defines the input columns whose distinct values should be counted and assigned to Timeseries columns based on period. For each DimCount column, there will be a Timeseries column for each specified period_type. The name of the resulting Timeseries column will be "<period> <dimcount column> Count"; the value will be the number of distinct values of the DimCount count that appears for that period.
period_unit	String	Defines the unit of time for the period that is processed by the Timeseries object. Currently this string must be "month". NOTE: This attribute is PeriodUnit in Visual Integrator.
start	String	Defines the start range of periods that will be output. It should be of the form YYYY-MM or YYYY/MM. If this attribute is not given, it defaults to the end period.
end (required)	String	Defines the end range of periods that will be output. It should be of the form YYYY-MM or YYYY/MM.
input_period_column (required)	String	Defines the input column that defines the time period for the row. The data in this column must be of the form YYYY-MM or YYYY/MM. NOTE: This attribute is InputPeriodColumn in Visual Integrator.
output_period_column	Array of Strings	Defines the Time Series columns created by the Timeseries object. For each period type given, a new summary column is created with the name "period_type Summary Name", created by summing up different sets of rows based on time periods. Possible period types for calendar years (Jan - Dec) include: MONTH (Month)—Defines the period of the end month. SMLY (Same month last year)—Defines the period of the end month for the year previous to the end year. YTD (Year To Date)—Defines the period from January through the end month of the end year. LYTD (Last Year To Date)—Defines the period from January through the end month for the year previous to the end year. LY (Last Year)—Defines the period from January through December in the previous year. Possible period types for fiscal years defined by the fiscal_end attribute include: FYTD (Fiscal Year To Date)—Defines the period from the beginning of the fiscal year (defined by fiscal_end attribute) through the end month. FLYTD (Fiscal Last Year To Date)—Defines the period from the beginning of the previous fiscal year (defined by fiscal_end attribute) through the end month a year ago. FLY (Fiscal Last Year)—Defines the period from the beginning of the previous fiscal year (defined by fiscal_end attribute) through the end of the previous fiscal year. Possible period types for rolling “n” include: R<n> (Rolling <n>)—Defines the period starting from <n>-1 months before the end period through to the end period. <n> is a number from 2 to 100. For example, R3 will be a rolling 3 period, including the end month and the previous two months. R<n>LY (Rolling <n> Last Year)—Defines the period starting from <n>-1 months before the end month in the previous year, through to the end month in the previous year. <n> is a number from 2 to 100. This is the same as the R3 example above, but ends a year earlier. NOTE: This attribute is OutputPeriodColumn in Visual Integrator.
custom_period_types	Array of Objects	The custom period types is an array of objects that defines custom period types that have not been defined as strings in the period_types array. See the table below (custom_period-types Sub-Object Attributes) for the attributes of these sub-objects. NOTE: This attribute is Custom Period Types in Visual Integrator.
keep_blank_rows	Boolean	Determines whether or not the Timeseries objects returns rows with blank Timeseries columns. If this attribute is "true", all possible rows for all output periods are returned. If this attribute is "false", only rows returned are those where a returned Timeseries column is set. This attribute is optional, and defaults to "false". The returned columns are columns that are used by later objects. For example, the number of rows returned by the Timeseries will vary by which columns are listed in an output object "columns" attribute. If no Time Series columns are requested, all rows will be returned by the Timeseries object. NOTE: This attribute is KeepBlankRows in Visual Integrator.
autosort	Boolean	Determines whether the Timeseries object automatically sorts the input flow according to the Dimensions. It does this by creating an anonymous Sort object with sort columns matching the Dimensions, thus saving the script writer the effort of writing a Sort object. This attribute is optional. When not set, this attribute defaults to "false". When developing scripts using the Visual Integrator application, this attribute is set to "true" by default. The attribute name in the graphical user interface appears as auto_sort but the underlying script uses the Integrator attribute name (autosort). The sort uses default values for Sort attributes such as temp_directory and sort_size. If these need to be set, an explicit Sort object should be used. Large data sets (data that does not fit into the memory of the machine running Data Integrator) are best sorted before hand with the Sort object. NOTE: This attribute is AutoSort in Visual Integrator.
warn_limit	Integer	Controls how many data integrity warnings are displayed. By default, this limit is one. After the limit is exceeded, Integrator will issue the following message: `"Further data integrity warnings suppressed for timeseries object 'xxx'. Set the warn_limit attribute to see more."` The warn_limit can be set to 0, but if it is, no warning information will be provided. This setting should be used with caution. NOTE: This attribute is WarnLimit in Visual Integrator.

custom_period-types Sub-Object Attributes

Attribute	Type	Description
type	String	Defines the subtype of the period type. Possible values include: YEAR—The default range will be from the beginning of the year through the end of the year. YTD—The default range will be from the beginning of the year through the end period. Month_Range—Months will be defined by the start_offset and end_offset attributes.
name	String	Names the period type for the period defined.
names	Array of Strings	Names the set of period types. If multiple names are specified, multiple custom period types are defined, with the delta attribute used to change the start_offset and end_offset for each succeeding name.
fiscal	Boolean	If "true", use the fiscal_end attribute as defined by the fiscal_end_column (if specified), the fiscal_end defined by the custom period type (if specified), or the fiscal_end attribute defined in the main Timeseries object. Defining a fiscal_end_column or a fiscal_end value implies a fiscal period type; this attribute is only used to define a fiscal period type that should pick up the fiscal end from the top-level fiscal_end or fiscal_end_column attribute.
fiscal_end	Integer	Defines the fiscal end of the year. This value must be a number between 1 and 12 inclusive. The default is 12 (December). This attribute is optional. This attribute conflicts with the fiscal_end_column sub-object attribute. If specified, this attribute overrides the top-level Timeseries fiscal_end or fiscal_end_column attribute for the custom period type. This attribute conflicts with the fiscal_end_column sub-object attribute. NOTE: This attribute is FiscalEnd in Visual Integrator.
fiscal_end_column	String	Defines an input column that determines the fiscal end of the year for these period types. For example, this allows the fiscal year to vary by customer. This attribute conflicts with the fiscal_end sub-object attribute. If specified, this attribute overrides the top-level Timeseries fiscal_end or fiscal_end_column attribute for the custom period type. NOTE: This attribute is FiscalEndColumn in Visual Integrator.
start_offset	Integer	Defines an offset for the start range of the period. For "month_range" period types, this is an offset from the end period. For "YTD" period types, this is an offset from the beginning of the year. This attribute is required for month range period types. Otherwise, it defaults to 0.
end_offset	Integer	Defines an offset for the end range of the period. For all period types, this is an offset from the end period. This attribute defaults to 0.
delta	Integer	Defines the number of months subtracted for each succeeding period type defined. Typically, this will be 12, indicating that each succeeding period type will be a year earlier.