Time Series Explorer

Created by Beth Pescud, Modified on Tue, 02 Apr 2024 at 05:27 PM by Beth Pescud

TABLE OF CONTENTS

Table of Contents
Introduction
Definitions
Location and Access
How To
Limitations

Introduction

The Time Series Explorer is a system that enables a user to build and run custom queries on the time series service, and export and download the data for further analysis, through a friendly user interface.

Definitions

Time series data: All data that relates to a specific time, for example: a vessel position or SCADA datapoints from a device.
Namespace: A collection of multiple datasets within the Time Series Service, equivalent to a database or an Excel workbook
Dataset: A grouping of data representing a single Fact or Dimension, equivalent to a database table or an Excel worksheet
Dimension: A dataset that does is not time-specific, for example: details about a specific vessel.
Fact: A dataset that is time-specific, for example: the position of a specific vessel
Field: A piece of data associated with a dataset, for example: the maximum speed of a vessel or the longitude of the vessel.
Timestamp or Datetime: A date and time indicating when a specific record was made.

Location and Access

Location: Offshore 'Admin' menu (or 'Analysis' menu where available).
Access: Roles which include the permission 'time_series_explorer'.

How To

How To: Create a Query for a Dimension Dataset

Select a Namespace from the dropdown
Select a Dataset from the dropdown. Dimension datasets are prepended with “Dimension”.
Using the Field Name dropdown, select all applicable fields. At least one field must be selected for the data export to be possible. Fields can be deleted from a query using the ?️ bin icon on the right of each row.
(Optional) By editing the name fields for existing fields, custom aliases can be created.
(Optional) Select the order direction to be used for sorting. If no direction is selected, the default sorting from the database will be used. If multiple fields have a direction selected, those fields will be used for sorting in order in which they appear on the list; this ordering can be changed using the ⬆️ up and ⬇️ down arrow buttons on the right of each row.
(Optional) Add a filter to the fields. Each filter can have a single condition applied to it, using the dropdown for a filter comparator (e.g. “equal to” or “greater than”) and the field for a value to compare against. Text fields have an additional comparator called “like”, which allows for search within the text. It allows the use of a wildcard character (%, the percent sign). For example, “A%” will find all values starting with A, “%A” will find values ending with A and “%A%” will find any values containing A inside. This is case-sensitive, therefore “A%” wil match “Africa” but not “apple”. All filters are combined using a logical AND operation, meaning that the system will return entries for which all filter conditions are satisfied.
(Optional) Select the query limit. This can be either set to a specific value (by default 100), or set to “no limit”, which will return all entries in the database.

How To: Create a Query for a Fact Dataset

Select a Namespace from the dropdown
Select a Dataset from the dropdown. Fact datasets are prepended with “Fact”.
Using the Field Name dropdown, select all applicable fields. Some fields in the dropdown are prepended by a dataset name, for example vessels.name. This indicates this data is requested from a different dataset, which allows getting more details on linked Dimension datasets. A timestamp is always included in Fact queries. At least one additional field must be selected for the data export to be possible. Fields, with the exception of the timestamp, can be deleted from a query using the ?️ bin icon on the right of each row.
(Optional) By editing the name fields for existing fields, custom aliases can be created.
(Optional) Select the order direction to be used for sorting. If no direction is selected, the data will be sorted using the timestamp, from the newest entries to the oldest entries. If multiple fields have a direction selected, those fields will be used for sorting in order in which they appear on the list; this ordering can be changed using the ⬆️ up and ⬇️ down arrow buttons on the right of each row.
(Optional) Add a filter to the fields. Each filter can have a single condition applied to it, using the dropdown for a filter comparator (e.g. “equal to” or “greater than”) and the field for a value to compare against. Text fields have an additional comparator called “like”, which allows for search within the text. It allows the use of a wildcard character (%, the percent sign). For example, “A%” will find all values starting with A, “%A” will find values ending with A and “%A%” will find any values containing A inside. This is case-sensitive, therefore “A%” wil match “Africa” but not “apple” All filters are combined using a logical AND operation, meaning that the system will return entries for which all filter conditions are satisfied.
(Optional) Select the query limit. This can be either set to a specific value (by default 100), or set to “no limit”, which will return all entries in the database.
(Optional) Select the time range. The time range can be either relative (a certain time with respect to now, e.g. “the past 24 hours” or “the past 2 weeks”) or absolute (between two specific dates, e.g. “from 1 January 2024, 13:00 to 14 January 2024, 18:00”). If no time range is selected, a default of “past 7 days” will be used.
(Optional) Select a time bucket. Rather than return every entry available, the query will group them based on the time bucket size.

Numeric fields must have an aggregate type selected to decide what should be done with the values. The available aggregate types are:

min: The minimum value within the time bucket
max: The maximum value within the time bucket
sum: The sum of all values in the time bucket
avg: The average of all values in the time bucket
first: The value associated with the earliest entry in the time bucket
last: The value associated with the last entry in the time bucket
stdev: The standard deviation of all entries in the bucket
array_agg: All the values in the time bucket aggregated as an array of values
count: The number of entries within the time bucket

How To: Save Queries

Create a ?Dimension or ?Fact query.
Press the ? dropdown next to the view selector.
Click the “Add Current View” button.
In the window that pops up, name the view and select whether it should be private to you (default) or shared with other users.
Click “Add View”. The view will now be saved. For Fact queries, absolute time ranges are not saved.

How To: Load Queries

Press the ? dropdown next to the view selector.
Select the name of the desired query. The query will be loaded.

How To: Update Queries

Either create a ?Dimension or ?Fact query, or ?load a query.
Press the “…” button within the view selector.
Select “Save Changes”
A confirmation window will ask whether to overwrite the query.

How To: Preview a Query and Its Results

Either create a ?Dimension or ?Fact query, or ?load a query.
Press the “Preview” button in the top-right of the Time Series Explorer dashboard.
A modal with the preview of the data will be displayed. You can switch between previewing the results and the query itself using the tabs at the top of the window.

The preview will show the first 100 entries for the query, but further queries can be viewed using the page navigation at the top of the table.

How To: Export and Download Data

Either create a ?Dimension or ?Fact query, or ?load a query.
Press the “Export” button in the top-right of the Time Series Explorer dashboard.
A pop-up will ask for the name of the export. This is a name used to aid you in identifying the job. By default it will include your name and the current date and time.
You will receive a notification in the message centre to indicate the job has been started. The notification also includes a link to a status dashboard.
You will receive a similar notification when the job completes. It will include a link to the same status dashboard.
Once on the status dashboard, if the job is complete, the “Download” button will be active. Press it to download a CSV file containing the exported data.

Limitations

Any query with a limit over 1000 will be batched to reduce the load on the system. This means queries larger than 1000 entries may run proportionally slower.
The export is based on a queue system. In situations where the queue is more heavily loaded, the exports may take a longer time.
Only one filter per field is supported.
It is not possible to exclude the timestamp field from fact dataset exports
It is not possible to save an absolute time range for a saved query. These will be automatically replaced with querying the past 7 days.
The generated files must be under 5TB in size.