DerivedSeries

ProvisioningTool - DerivedSeries tasks

• EXPORT, CREATE, and UPDATE operations are supported.
• DELETE operations are not supported for time-series (neither basic, reflected, nor derived). But you can use the LocationDeleter.exe tool for that.

CSV file format

The CSV format for creating/updating derived series is similar to the CSV format used by the RatingModelExchange tool.

• Blank rows are skipped.
• Rows starting with # or // are treated as comments and are skipped.
• Leading and trailing whitespace is trimmed from each field.
• Quotes are not required for string fields, unless the field contains a comma

9 row types are supported. The first field in each row defines the row type.

• A DerivedSeries row creates the outer shell for a derived series.
• All the other rows that follow define specific processing periods, beginning at a StartingFrom point in time.
• Each processing period must have a StartingFrom timestamp later than all preceeding periods.
• A derived series can have any number of processing periods defined.
• Many of the fields are optional, and use reasonable default values if blank/omitted.

Row type	Description
DerivedSeries	Defines a new derived series.
NoProcessing	Defines a period of no processing.
Passthrough	Defines a period of passing through the corrected signal from another series.
Calculation	Defines a period of using a formula to calculate values from one or more series.
RatingModel	Defines a period of rating model derivation from one series through a rating model.
Statistical	Defines a period of statistical processing from one series.
Transformation	Defines a period of transformation processing.
FillMissingData	Defines a period of filling data gaps in a source series with points from a secondary seris.
DatumConversion	Defines a period of datum conversion on one series into a specific datum.

This isn't a normal CSV file. Each row depends on the previous row

Just like the RatingModelExchange CSV file format, the DerivedSeries CSV format is not really a true table format. So in this sense it isn't a CSV file. It is more accurate to to say the file format is just a line-oriented text file, since each line is not the same same shape as the next.

Instead, each line depends on the context that precedes it.

Your CSV file will contain at least one DerivedSeries row, followed by zero or more processing rows for that derived series, until the next DerivedSeries row is encountered. We call this sequence of one DerivedSeries row plusits following processing rows a "processing plan".

The CSV file can contain any number of processing plans for any number of derived series.

# Create an hourly mean Stage series from telemetry
DerivedSeries, HG, m, HourlyMean, Loc12, , , , , , Mean, Hourly
Statistical, , , Mean, HG.Telemetry, , Hourly, 1

# Create a discharge series, starting with a historical discharge imported from another system
# and switching over to a rating model derivation from Oct 2021, when the AQTS system was created
DerivedSeries, QR, m^3/s, Derived, Loc12
Passthrough, , From Ted's Excel sheets, QR.Historical
RatingModel, 2021-10-01, , HG-QR.Rating, HG.Telemetry

DerivedSeries rows

#	Field name	Description
1	RowType	Must be DerivedSeries.
2	ParameterId	Parameter ID of the series. This is the ID, not the DisplayName, so 'HG' and not 'Stage' or 'Water Level'.
3	UnitId	Optional unit Id of the series.
4	Label	The label for the derived series.
5	LocationIdentifier	The identifier of the location which owns the derived series.
6	UtcOffset	Optional UTC offset, in +HH:MM or -HH:MM format. If blank, defaults to the location's UTC offset.
7	Description	Optional description for the series.
8	Comment	Optional comment for the series.
9	Publish	Optional Publish flag, defaults to false.
10	InterpolationType	Optional interpolation type. If set, must be one of: InstantaneousValues PrecedingConstant PrecedingTotals InstantaneousTotals DiscreteValues SucceedingConstant
11	ComputationIdentifier	Optional computation type. If set, must be one of: Min Max Sum Mean Median Selected Value Tidal High Tidal Lower High Tidal Higher Low Tidal Low Decumulated Max At Event Time Total Amount
12	ComputationPeriodIdentifier	Optional computation period. If set, must be one of: Annual Monthly Weekly Daily Hourly Minutes Points WaterYear

While there can be many fields in a DerivedSeries row, only the first 5 fields are required. The remaining 7 fields are optional and assume reasonable default values.

When the UnitId field is not explicitly set:

• Use the unit ID of the first input series of the first processing period.
• If no processing is define, use the parameter's default unit ID.

When the InterpolationType field is not explicitly set:

• PrecedingConstant if the first processing plan is Statistical.
• Else use the interpolation type of the first input time series of the first processing period.
• If no processing is defined, the parameter's default interpolation type will be used.

When the ComputationIdentifier field is not explicitly set:

• If any statistical processing is configured, use first statistic's StatisticType field value
• Else leave it blank.

When the ComputationPeriodIdentifier field is not explicitly set:

• If any statistical processing is configured, use first statistic's Period field value
• Else leave it blank.

NoProcessing rows

#	Field name	Description
1	RowType	Must be NoProcessing.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.

Passthrough rows

#	Field name	Description
1	RowType	Must be Passthrough.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	InputTimeSeries	The input time-series.
5	Method	The optional method code.

InputTimeSeries

Input time-series can be specified with no location identifier, as {ParameterId}.{Label} (eg. HG.Telemetry). The location of the derived series will be assumed. This is the most succinct and most common form.

Input time-series can also be specified with an explicit location identifier, , as {ParameterId}.{Label}@{LocationIdentifier} (eg. HG.Telemetry@Loc2). This form is required when one location's series needs to pull in data from a different location.

Calculation rows

#	Field name	Description
1	RowType	Must be Calculation.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	Method	The optional method code.
5	Formula	The formula for calculating y from the x1 through xN input series.
6	MasterInput	Must one of x1 through xN. The timestamps from points in the master input will be used to interpolate values in all the secondary inputs.
7	x1	InputTimeSeries 1
8	x2	InputTimeSeries 2
...
6+N	xN	InputTimeSeries N

• A calculation is a formula based on one or more time-series (up to 50 time-series).
• One series is designated the master input, and the remaining series, if any are secondary series.
• The timestamps from the master input will be used as the interpolation timestamps for any secondary series, using each series's interpolation type rules.
• Any input time series can optionally be "time-lagged", moving all the points forwards or backwards in time by a specified amount.

TimeLaggedInputTimeSeries

Each calculation input series can specified like a InputTimeSeries using either {ParameterId}.{Label} or {ParameterId}.{Label}@{Location} syntax.

If a time-lag is required, then a +HH:MM:SS@ or -HH:MM:SS@ prefix will preceed the input time series. If the time-lag exceeds a day, then a +d.HH:MM:SS@ or -d.HH:MM:SS@ prefix will be used.

• HG.Telemetry is an un-lagged stage telemetry signal.
• -00:15:00@HG.Telemetry@Loc2 is the telemetry signal from location 2, but each point occurring 15 minutes earlier than in the original signal.

This small CSV would create QR.Delta15@Loc1 and report the change in discharge over the last 15 minutes from the QR.Working@Loc1 signal.

DerivedSeries, QR, m^3/s, Delta15, Loc1
Calculation, , , , y = x2 - x1, x1, QR.Working, -00:15:00@QR.Working

RatingModel rows

#	Field name	Description
1	RowType	Must be Model.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	InputRatingModel	The input rating model.
5	InputTimeSeries	The input time-series, whose parameter must match the rating model's input parameter.
6	Method	The optional method code.

InputRatingModel

Input rating models can be specified with no location identifier, as {InputParameterId}-{OutputParameterId}.{Label} (eg. HG-QR.Derived). The location of the derived series will be assumed. This is the most succinct and most common form.

Input time-series can also be specified with an explicit location identifier, , as {InputParameterId}-{OutputParameterId}.{Label}@{LocationIdentifier} (eg. HG-QR.Derived@Loc2). This form is required if the rating model is owned by another location.

Statistical rows

#	Field name	Description
1	RowType	Must be Statistical.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	StatisticType	Must be one of: Minimum Maximum Sum Mean Median SelectedValue TidalHigh TidalLowHigh TidalHighLow TidalLow MaximumAtEventTime TimeIntegral
5	InputTimeSeries	The input time-series.
6	Method	The optional method code.
7	Period	Must be one of: Annual Monthly Weekly Daily Hourly Minutes Points WaterYear
8	PeriodValue	The number of periods over which the statistic will be aggregated.
9	BinAnchorOffsetPeriod	Optional anchor point within the bin, in "MM-DD HH:MM" (month-day hour:minute) format. Defaults to 00-00 00:00.
10	PlacementOfComputedValueInBin	If set, must be one of Start or End. Defaults to End.
11	MinimumCoveragePercentageForComputation	If set, should be an integer value between 1 and 100.
12	AutomaticGradeForForMinimumCoverageAndAbove	Optional numeric grade code used when minimum coverage rules are triggered. Defaults to -1.
13	DailyTimeOffsetInMinutes	For SelectedValue statistics only, an optional integer offset, in minutes, for the start of the daily selected value.

Statistical calculations are one of the most useful derivation processing types. They are also the most complex processing option to configure.

• The Decumulated statistic type is not supported by this row. Use the Transformation row instead.
• For SelectedValue statistics, fields 8 through 12 are ignored.

Transformation rows

#	Field name	Description
1	RowType	Must be Transformation.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	StatisticType	Can only be Decumulated.
5	InputTimeSeries	The input time-series.
6	Method	The optional method code.
7	RemoveNegativeValues	Optional boolean value, which defaults to false.

FillMissingData rows

#	Field name	Description
1	RowType	Must be FillMissingData.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	SourceTimeSeries	The source time-series.
5	SecondaryTimeSeries	The secondary time-series to be used when gaps occur in the source.
6	Method	The optional method code.
7	OutputGapTolerance	Optional output gap tolerance, in +HH:MM or -HH:MM format.
8	EndOfSignalWaitBeforeFillingData	Optional wait before data from the secondary series is used to fill gaps in the source, in +HH:MM or -HH:MM format.

DatumConversion rows

#	Field name	Description
1	RowType	Must be DatumConversion.
2	StartingFrom	Optional starting time of the processing period.
3	Description	Optional description of the processing period.
4	InputTimeSeries	The input time-series.
5	Source	The source datum or reference point. A blank or empty value will default to location's local assumed datum.
6	TargetDatum	The target datum or reference point. A blank or empty value will default to location's local assumed datum.
7	Method	The optional method code.

Timestamp formats

The StartingFrom date/times in each processing period can:

• Be completely blank, to indicate starting from the beginning of record.
• Just specify the date in yyyy-MM-dd format
• Include an optional time-of-day, in HH:mm or HH:mm:ss format (midnight is assumed if omitted)
• All timestamps are assumed to be in the timezone of the series unless an explicit UTC offset is provided.