Skip to content

DHI.Services.MCLite for Time Series — Internal Developer Guide

This page documents the MCLite time series providers that live in DHI.Services.MCLite. It’s written for internal developers who will use or extend these providers. If you’re looking for the MCLite “plumbing” (DB flavors, connection string parsing, DataUtility, etc.), see MCLite Providers. Here we’ll stay focused on how to use the time series providers effectively.

What’s here

We ship two provider flavors under the MCLite umbrella:

  1. Database-backed (CRUD + analytics): DHI.Services.Provider.MCLite.TimeSeriesRepository Backed by the MC/MCLite schema (PostgreSQL, SQL Server, or SQLite). Supports groups, add/update/delete, range operations, raw vs. blob storage, flags, aggregation (including ensembles), and more.

  2. Spreadsheet-driven (read/compute only): DHI.Services.Provider.MCLite.TimeSeriesFromSpreadsheetRepository Reads inputs from any repository type, pushes them into an Excel workbook (SpreadsheetGear), recalculates formulas, then reads a named output time series back out. Great for quick calculations and glue workflows. (Read-only: no SetValues/GetAll.)

You use both through the standard TimeSeriesService<string,double> surface.

Quick start (code)

using DHI.Services.Provider.MCLite;
using DHI.Services.TimeSeries;

var repo = new TimeSeriesRepository("database=mc2014.2"); // see Connection Strings below
var svc  = new TimeSeriesService<string, double>(repo);

// read a time series
var id = "/hydro/river/MainStation/Discharge";
var ts  = svc.Get(id).Value;                              // metadata (Quantity, Unit, DataType, etc.)
var dat = svc.GetValues(id, new DateTime(2024,1,1), DateTime.UtcNow).Value;

// write samples (deduped per second by default)
svc.SetValues(id, dat);

// add a new time series
var newTs = new TimeSeries<string,double>(id: "/scratch/Test/SignalA", name: "SignalA", group: "/scratch/Test")
{
    Quantity = "WaterLevel", Unit = "m", DataType = TimeSeriesDataType.Instantaneous,
    Data = dat // any ITimeSeriesData<double>
};
svc.Add(newTs);

// aggregate
var sum = svc.GetAggregatedValue(id, AggregationType.Sum,
           from: new DateTime(2024,1,1), to: DateTime.UtcNow);

Connections (Web API / Connections module)

If you use the Connections module (e.g., Time Series Web API + ServiceLocator), drop this in your connections config:

{
  "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[DHI.Services.IConnection, DHI.Services]], mscorlib",
  "mc-ws1": {
    "$type": "DHI.Services.TimeSeries.WebApi.GroupedUpdatableTimeSeriesServiceConnection, DHI.Services.TimeSeries.WebApi",
    "ConnectionString": "database=mc2014.2",
    "RepositoryType": "DHI.Services.Provider.MCLite.TimeSeriesRepository, DHI.Services.Provider.MCLite",
    "Name": "Local MC time series connection to workspace1",
    "Id": "mc-ws1"
  },
  "mclite": {
    "$type": "DHI.Services.TimeSeries.WebApi.GroupedUpdatableTimeSeriesServiceConnection, DHI.Services.TimeSeries.WebApi",
    "ConnectionString": "database=[AppData]MCSQLiteTest.sqlite;dbflavour=SQLite",
    "RepositoryType": "DHI.Services.Provider.MCLite.TimeSeriesRepository, DHI.Services.Provider.MCLite",
    "Name": "Local MC Lite time series connection to workspace1",
    "Id": "mclite"
  }
}

Then, at runtime, resolve "mc-ws1" or "mclite" and you’ll get a fully wired, grouped, updatable time series service backed by MCLite.

Connection strings (MCLite recap)

MCLite parses the connection string via Db:

  • Common keys: database, workspace (maps to schema), host, port, username, password, dbflavour (PostgreSQL|SqlServer|SQLite), cachemode. Defaults: host=localhost, port=5432 (PG), username=dss_admin, password=secretdss_admin, workspace=workspace1, flavour=PostgreSQL.

  • Examples:

    • PostgreSQL: database=mc2014.2;host=db.local;port=5432;username=mc;password=***
    • SQL Server: database=MC2014;host=sql.local;port=1433;username=mc;password=***;dbflavour=SqlServer
    • SQLite: database=[AppData]MCSQLiteTest.sqlite;dbflavour=SQLite

More details: ../domain_services_mclite.md.

ID & grouping model

  • A time series ID is its full name: "/group/subgroup/Name". The group path is all segments except the last (Name). No trailing slash for time series IDs.

  • A group full name ends with /, e.g. "/group/subgroup/". Some APIs may return group full names when you ask for “groups only”.

  • Tree options in group queries (suffix on the group string):

    • ;nonrecursive – only direct children in a group
    • ;groupsonly – return only subgroups
    • ;nonrecursive;groupsonly – both modifiers

Example:

var list = repo.GetByGroup("/hydro/basins;nonrecursive");  // only immediate time series in that group
var names = repo.GetFullNames("/hydro;groupsonly");        // subgroup paths under /hydro

Provider 1: Database-backed — TimeSeriesRepository

Namespace: DHI.Services.Provider.MCLite Base: BaseGroupedUpdatableTimeSeriesRepository<string,double>, IGroupedUpdatableRepository

Capabilities at a glance

  • Lookup/metadata: Contains, Get, GetAll, GetByGroup, GetFullNames, ContainsDateTime, GetFirst/LastDateTime, GetLastValue…
  • Read values: GetValues(id), GetValues(id, from, to)
  • Write/update: Add, SetValues, Update, Remove, RemoveValues(from,to)
  • Aggregation: GetAggregatedValue, GetAggregatedValues (bulk), ensemble variants, and GetAggregateTimeSeries(ids, type, interval)
  • Storage modes: Raw (per-sample rows) and Blob (periodized ProtoBuf chunks)
  • Flags: Optional per-timestamp integer flags (new table layout auto-detected)
  • Multi-DB: PostgreSQL, SQL Server, SQLite (see DbFlavour)

Reading

var ts = svc.Get("/gauges/DistrictA/Sensor42").Value;

var window = svc.GetValues("/gauges/DistrictA/Sensor42",
                           from: DateTime.Parse("2024-06-01"),
                           to:   DateTime.Parse("2024-06-30")).Value;

// If flags are present and you need them:
var withFlags = window as ITimeSeriesDataWFlag<double,int?>; // may be null if flags absent

Notes

  • On raw storage we read from time_series_value (ordered by date_time, sequence). Flags (if table time_series_flags exists) are joined by timestamp.
  • On blob storage we stream, deserialize List<Binary { Time, Value, Flag }> and filter by time range.

Writing

// default dedup: PerSecond. To change:
var repo = new TimeSeriesRepository(
    "database=mc2014.2",
    logger: null,
    options: new TimeSeriesRepositoryOptions { DefaultDedup = DedupResolution.ExactTimestamp });

// Write values (dedup behavior applies if your data DOES NOT implement ITimeSeriesDataWFlag)
svc.SetValues("/import/Station1/Level", new TimeSeriesData<double>(
    new [] { t0, t1, t2 }.ToList(),
    new double?[] { 1.23, null, 1.40 }.ToList()));

// Write with flags (no dedup pass is applied to ITimeSeriesDataWFlag)
var flagged = new TimeSeriesDataWFlag<double,int?>();
flagged.Append(t0, 1.23, 100);
flagged.Append(t1, null, null);
svc.SetValues("/import/Station1/Level", flagged);

Deduplication If the input does not implement ITimeSeriesDataWFlag, we deduplicate before writing:

  • ExactTimestamp – keep one sample per exact DateTime
  • PerMillisecond – group by (Y,M,D,H,m,s,ms)
  • PerSecond (default) – group by (Y,M,D,H,m,s)

To preserve raw duplicates exactly, pass an ITimeSeriesDataWFlag<double,int?> implementation (even if flags are null).

Storage selection Storage is set by the time series row: storage_type = 0 (raw) or 1 (blob).

  • Raw: we delete any overlapping window first, then bulk insert rows.
  • Blob: we partition by the configured blob period (hour/day/week/month/year), merge/rewrite touched periods, and optionally delete missing periods when rewriting whole ranges.

Flags If the time_series_flags table exists, we delete overlapping flags for the window then (for ITimeSeriesDataWFlag) insert new ones. Reading returns a flag per timestamp (downcastable from ITimeSeriesData<double>).

Aggregation

Per-ID:

var avg = svc.GetAggregatedValue("/hydro/A/B", AggregationType.Average,
                                 from: t0, to: t1);

Bulk:

var dict = svc.GetAggregatedValues(new []{"/A","/B","/C"}, AggregationType.Sum, t0, t1);
// dict["/A"] => double?

Ensemble aggregation (per sequence):

var perMember = svc.GetEnsembleAggregatedValues("/forecast/Run17", AggregationType.Maximum, t0, t1);
// IList<double?>, one entry per ensemble member (sequence)

Aggregate as time series Aggregate into fixed intervals (sum in the implementation) across many series:

var series = repo.GetAggregateTimeSeries(
    ids: new[]{ "/A", "/B", "/C" },
    aggregationType: AggregationType.Sum,
    interval: 3600 /* seconds */);
// returns TimeSeries<string,double> per input, each with values at interval grid

Group queries & names

var all = repo.GetAll();
var inGroup = repo.GetByGroup("/hydro/basins");
var directOnly = repo.GetByGroup("/hydro/basins;nonrecursive");

var names = repo.GetFullNames("/hydro;groupsonly"); // subgroup full paths (with trailing '/')

Internally we use DataUtility helpers to navigate group ids. For deep details, see MCLite Providers.

Metadata we populate on TimeSeries

  • Quantity, Unit, DataType
  • Metadata["StartTime"], ["EndTime"]
  • Metadata["IsPublic"] (if column exists)
  • Metadata["IsEnsemble"] (if member_count > 1)
  • Metadata["AssociatedFrom"] (feature associations map)
  • Metadata["ForecastTime"] (if simulation association exists)

SQL-flavor specifics & performance

  • SQL Server: we clamp times below SqlDateTime.MinValue via _GetMSSQLTime.
  • Batching: inserts are batched (defaults chosen to stay under SQL Server’s 2,100 parameter limit).
  • ContainsDateTime on blob storage reads into memory and searches — be mindful on very large series.
  • Schema differences: we auto-detect the presence of the time_series_flags table and (for older schemas) whether time_series_blob has an id column.

Common operations (quick snippets)

// Create if missing
if (!repo.Contains("/scratch/Test/SignalA")) svc.Add(newTs);

// Delete everything
repo.Remove("/scratch/Test/SignalA");

// Delete a window (and its flags)
repo.RemoveValues("/scratch/Test/SignalA", from: t0, to: t1);

// Delete an entire group tree (entities first, then groups)
repo.RemoveByGroup("/scratch");

// First/last timestamps and values
var first = repo.GetFirstDateTime(id).Value;
var last  = repo.GetLastDateTime(id).Value;
var prev  = repo.GetLastValueBefore(id, DateTime.UtcNow).Value;

Provider 2: Spreadsheet-driven — TimeSeriesFromSpreadsheetRepository

Namespace: DHI.Services.Provider.MCLite Base: BaseDiscreteTimeSeriesRepository<string,double> (read-only)

This provider lets you compose time series by feeding inputs into a spreadsheet, letting the workbook calculate, and reading outputs from a named column pair. It’s ideal when domain users maintain a calculation workbook (SpreadsheetGear engine).

Spreadsheet contract (workbook layout)

  • Required worksheets:
    • Definitions — table of inputs to pull
      • Required columns: Repository, Connection, Id
        • Repository is the full .NET type name of a repository (e.g., "DHI.Services.Provider.MCLite.TimeSeriesRepository, DHI.Services.Provider.MCLite").
        • Connection is the connection string to pass to the repository constructor.
        • Id is the full time series id to fetch (e.g., "/hydro/A/B").
    • Data — will be cleared and repopulated. For each row in Definitions, we write 2 columns:
      • Column pair layout per input: Row 0: the input Id (header) Rows 1..N: first column DateTime (Excel OA date), second column Value (double or blank)
    • Output — read area. Row 0 contains output time series names (headers) in every second column: [0] Name0, [1] (unused), [2] Name1, [3] (unused), … Below each name, two columns hold (datetime, value) pairs.

After writing Data, we call WorkbookSet.CalculateFull() so your formulas/macros can reference those cells and produce Output.

ID format (when calling this repository)

Pass a semicolon-separated key=value string:

Spreadsheet=<path-or-storage-key>;Timeseries=<output-name>[;Debug=true|false][;TrimBlanks=true|false]
  • Spreadsheet – the workbook identifier used by SpreadsheetRepository (same connection string as the provider’s ctor).
  • Timeseries – exact header name found in Output row 0.
  • Debug (optional) – if true, saves a copy to the temp folder after calculation.
  • TrimBlanks (optional) – if true, leading/trailing null values are trimmed from the result window.

Example

var repo = new TimeSeriesFromSpreadsheetRepository("database=mc2014.2"); // same conn style as MCLite Db
var svc  = new TimeSeriesService<string,double>(repo);

var id = "Spreadsheet=/calc/RatingCurves.xlsx;Timeseries=/computed/FlowAtS2;TrimBlanks=true";
var values = svc.GetValues(id, DateTime.Parse("2024-01-01"), DateTime.Parse("2024-02-01")).Value;

What the provider does under the hood

  1. Parse the ID with TimeSeriesFromSpreadsheetRepositoryId.
  2. Instantiate a SpreadsheetRepository using the provider’s connection string.
  3. Read the Definitions sheet into rows (expects columns Repository, Connection, Id).
  4. For each row, reflect the Repository type (TimeSeriesService<string,double>.GetRepositoryTypes()), Activator.CreateInstance(repoType, connection), and pull values from from..to.
  5. Flatten each input into the Data sheet (two columns per input).
  6. Recalculate the workbook (SpreadsheetGear).
  7. Read Output headers (row 0, every second column) to find your Timeseries column.
  8. Read down until the first blank row; parse DateTime from OA dates; parse double values; return TimeSeriesData<double>.

Supported members

  • Contains(id) — true if Timeseries is among Output row-0 headers
  • Get(id) — returns a TimeSeries<string,double> shell (id/name = Timeseries)
  • GetValues(id[, from, to]) — the main event
  • SetValues, GetAll — not implemented (throws)

Debugging & errors

  • "The spreadsheet does not exist" — workbook not found by SpreadsheetRepository.
  • "The spreadsheet must contain a Data sheet" / "Output sheet" — missing required sheets.
  • "Cannot resolve the repository type …" — the Repository cell must be a full assembly-qualified type known to GetRepositoryTypes().
  • Debug=true saves a snapshot to %TEMP% named like RatingCurves.xlsx yyyy-MM-dd HH-mm-ss.xlsx.

Practical tips

  • Treat the Data sheet as volatile — we clear UsedRange every call.
  • In Output, keep header names exact and unique. The provider matches them 1:1.
  • Use TrimBlanks=true if your formulas produce padded null margins.
  • Inputs can be from any provider, not just MCLite DB — that’s the whole point of the Repository/Connection/Id triple.

Using through the Web API (brief)

When wired with the Time Series Web API (e.g., via the Connections JSON above), your RepositoryType is the MCLite DB provider:

  • Read: GET /timeseries?id=/group/name... and GET /timeseries/values?id=...&from=...&to=...
  • Write: POST /timeseries/values with an ITimeSeriesData<double> payload (flags supported if present)
  • Group ops: controller supports grouped lists and tree options (;nonrecursive, ;groupsonly)

(Exact routes/DTOs are in the Time Series Web API module documentation.)

Behavior

  • Versioning: SetValues updates the version column so downstream caches can invalidate.
  • Min/Max: After writes, we update start_time/end_time to the min/max across existing + new samples.
  • Flags table: We autodetect “new flags” layout (time_series_flags) by checking table existence at first use.
  • Ensembles: GetValues returns a flat (DateTime, Value) stream ordered by (t, sequence); we expose ensemble-aware aggregation via GetEnsembleAggregatedValues. If you need per-member timeseries, prefer dedicated ensemble services/APIs.
  • SQL Server time floor: Any DateTime earlier than SqlDateTime.MinValue is bumped up one day.
  • Blob period: We group writes by _GetPeriodStart(time, period) where the period is Hour/Day/Week/Month/Year (0..4) on the time series row. Missing periods can be fully removed on rewrites (removeMissingPeriods=true path).
  • Deleting by group: RemoveByGroup(group) deletes entities first, then the group tree.
  • Spreadsheet provider limitations: read-only, and it strictly requires Definitions/Data/Output layout. It will overwrite the Data sheet each call.

End-to-end examples

1) Import a CSV → write to MCLite DB

var repo = new TimeSeriesRepository("database=mc2014.2;host=localhost");
var svc  = new TimeSeriesService<string,double>(repo);

var id = "/import/Well7/Head";
var ts = svc.Get(id).HasValue ? svc.Get(id).Value
                              : new TimeSeries<string,double>(id, "Head", "/import/Well7")
                                { Quantity="WaterLevel", Unit="m", DataType=TimeSeriesDataType.Instantaneous };

var times = new List<DateTime>();
var vals  = new List<double?>();
// ... fill from CSV ...
svc.Add(ts);               // no-op if it exists
svc.SetValues(id, new TimeSeriesData<double>(times, vals));

2) Compose using a spreadsheet

Definitions sheet:

Repository Connection Id
DHI.Services.Provider.MCLite.TimeSeriesRepository, DHI.Services.Provider.MCLite database=mc2014.2 /import/Well7/Head
DHI.Services.Provider.MCLite.TimeSeriesRepository, DHI.Services.Provider.MCLite database=mc2014.2 /import/Well7/PumpStatus

In Output row 0, place /computed/HeadDrawdown over two columns. Put your formulas to reference Data sheet ranges.

Then:

var srepo = new TimeSeriesFromSpreadsheetRepository("database=mc2014.2");
var ssvc  = new TimeSeriesService<string,double>(srepo);

var id = "Spreadsheet=/calc/Wellbook.xlsx;Timeseries=/computed/HeadDrawdown;TrimBlanks=true;Debug=false";
var dr  = ssvc.GetValues(id, DateTime.Parse("2024-01-01"), DateTime.Parse("2024-12-31")).Value;

3) Aggregate over hour bins for a set of ids

var ids = new[]{ "/A/B/C1", "/A/B/C2" };
var outSeries = ((TimeSeriesRepository)repo).GetAggregateTimeSeries(ids, AggregationType.Sum, interval: 3600);

foreach (var s in outSeries)
{
    Console.WriteLine($"{s.Id}  {s.Data.DateTimes.Count} points");
}

Extending

  • New DB columns/tables? Prefer detect-and-adapt patterns we use (e.g., flags table presence).
  • New aggregation? Mirror _GetRawAggregateValue/_GetBlobAggregateValue and their ensemble variants.
  • New storage mode? Add a new storage_type branch in _SQLBatchCreate with a cohesive read/write pair.