Skip to main content
Skip table of contents

Export to OData

Overview

Seeq has an OData export feature that allows trends and calculations to be exported to Spotfire, Tableau, or Microsoft applications Excel and Power BI.

OData exports can be read directly by these software programs for use in dashboards or for further analysis.

Users can authenticate the OData feed using a Seeq Access Key or username & password. Alternatively, if Seeq is set up with Windows Auth and a user is reading an OData export in a Windows product (e.g. Excel, Power BI), the user can authenticate the OData feed seamlessly.

What’s new in R63

OData Export has changed significantly in R63:

New service

  • A new version of the OData Service has been added. See the “Modern Data Exports“ section compared to “Legacy Data Exports“ below.

Form

  • The “Samples table mode“ is now a directly-selectable option.

  • The “Samples table grid“ has “Ungridded original timestamps” added as an option.

Data tables (legacy service)

  • Samples export:

    • Modified: “Automatic” grid period now is calculated based on the time range being exported.

  • Capsules export:

    • Modified: Column ordering has been changed slightly to increase clarity. Capsule properties are now in alphabetical order.

    • Removed: Signal statistics within each capsule are no longer output to improve performance. Use the $condition.setProperty(...) formula if these statistics are still required.

Viewing OData Exports

Previously-created OData exports can be accessed using the “View OData Exports“ panel from the Tools menu in an Analysis, within the “Import/Export” section.

This list alphabetically shows all OData exports that have been created within this Analysis.

Each export includes buttons for editing the configuration, copying the link to your clipboard, and deleting the export. Clicking the body of an export will display the modal with the link(s) and instructions.

Creating & Editing OData Exports

The “Export to OData” tool panel can be accessed from the Tools menu in an Analysis, within the “Import/Export” section.

Name: Provide a relevant name that will be helpful to identify the export. The name provided here will be appended with a unique identifier.

Time range options: Select a Fixed time range or select Auto update for a moving time window. 

Auto Update and $RangeStart/$RangeEnd are premium features that require a Seeq Enterprise license. 

  • Auto update: When this option is selected, the time range is adjusted to end at ‘now’ whenever the export URL is queried by your OData client. The originally-selected duration is used to determine the start date. The auto update "scheduling" portion needs to be configured in the BI tool.

  • The OData service will support URL parameters $RangeStart and $RangeEnd. When the range parameters are specified, the originally-selected time range is ignored (the specified time zone is still respected).

    • Be sure you’ve specified a custom Sample Table Grid or Ungridded to ensure consistent sample frequencies are output.

    • Supported date-time formats include ISO-8601 with offset (2020-02-25T14:15:30+01:00), ISO-8601 without offset (2020-02-25T14:15:30, uses the configured time zone), and Microsoft Excel default US format (2/25/2020 2:15:30 PM, uses the configured time zone).

    • For example, this URL requests data for all of January 2022 in the configured time zone.
      https://mycompany.seeq.site/odata/0EE4E93A-AE3A-F9F0-B179-ADA1DC6AFBD2.svc/Samples?$rangestart=2022-01-01T00:00:00&$rangeend=2022-01-31T23:59:59

Time range: Specify the time range by adjusting the start, end and duration fields.

  • Time Zone: This is the time zone to be used for all times within the export. This setting defaults to that of the source worksheet. If the selected zone observes daylight savings time, ambiguities may occur at these events.

Samples table mode: The format to be used for the Samples sheet.

  • Calendar: Lists all signals' and conditions' values using real-world timestamps.

  • Chain: The same as Calendar mode, but filtered to only include timestamps where capsules are present.

  • Capsule: Lists signal values within each capsule. Timestamps are zero-based from the start of each capsule.

Sample table grid: The grid option determines the timestamps that will be reported in the sample table. 

  • Automatic: Resamples the signals to a grid period based on the amount of time being exported.
    For example, exporting 1 week of data will result in a grid period of 5 minutes. A 1 year export will use a 1 day grid period.

  • Custom: Resamples the signals according to the grid period you set. For example, a grid period of 1 hour leads to values with timestamps separated by an hour.

    • Override Grid Origin: The specific date and time to orient the grid period. For example, this allows a weekly grid to be calculated every Monday at 9:00 AM rather than the default of Sunday at midnight.

  • Ungridded original timestamps: Each signal’s original samples will be output.

Signals: Modify the list to only include the signals desired in the export.

Conditions: Modify the list to only include the conditions desired in the export.

Exporting to Other Applications

From the 'Export to an OData client' window, select the desired import application from the menu at the bottom for detailed instructions.

Copy the data export link and paste it to your preferred application.

Data Exports

There are two parallel OData services available: Legacy and Modern.

All defined OData exports are able to be exported using either service. Specifying a Modern or Legacy URL will dictate which service Seeq will utilize.

Administrators can set the Features/OData/DefaultProtocol configuration option to 4.0 to enable the Modern service as the default. 2.0 will show the Legacy export URLs instead.

Legacy exports will be deprecated in a future version of Seeq, then fully removed in a proceeding version.

Modern OData Exports

Modern exports are accessed via /odata/{id}.svc URLs. These endpoints support up to OData Version 4.0.

Four tables are available within each OData Export: Information, Items, Samples, and Capsules.

Information Table

The Information table details the meta information about the export. It includes which configuration options were used to generate the export, the link back to Seeq where this export can be modified, and other such data.

Items Table

The Items table lists the properties of all exported items.

Their Seeq ID, type, name, asset path, and description are listed first. All other metadata properties follow after that in alphabetical order (minus specific properties that are unlikely to be useful such as UI Config). Finally, any conditions with capsule properties list those properties alphabetically and their associated unit of measure.

Samples Table

The Samples table reports the value of each signal at different timestamps. The grid period options determine the timestamps that will be output. The signal’s value at the given timestamp will be present in either the numeric_value or string_value column, depending on the signal type.

Calendar and Chain mode

These modes output the signals' value at each real-world timestamp. Calendar mode lists all timestamps within the exported interval. Chain mode filters out times where no capsules are present.

Capsule mode

Capsule mode represents a signal segment within each capsule. This data includes the same columns as Calendar/Chain mode, but also include the condition’s ID, the capsule’s ID, and the time this sample is located relative to the capsule’s start (in minutes).

Capsules Table

The Capsules table lists all capsules within the exported time range. The start, end, and duration are listed for all capsules (when available) followed by the capsule properties in alphabetical order.

Legacy OData Exports

Note: The columns output by legacy OData exports are based on the signal and condition names & asset paths. If any duplicate columns are present, it can cause errors for some clients. If exports with duplicate columns are defined, archive those exports and clear your client cache to fix the issue.

Note: Memory limitations for legacy exports may cause exports to return 502 Bad Gateway responses or 400 responses with a This OData export is estimated to be larger than the server configuration allows... error message. Systems which regularly export large amounts of data will likely want to preemptively increase Memory/Appserver/Size and/or Memory/Appserver/OData/MaxEstimatedMemoryUtilization config options to ensure legacy exports work as expected.

It is generally a good idea to limit the expected size of legacy exports to prevent memory errors and to get responses quickly. The specifics vary from server to server, but ensuring your exports have fewer than 10 items and 100,000 rows is a good upper boundary to help ensure performance.

Legacy exports are accessed via /odata.svc/{name}_DataSet URLs. These endpoints support up to OData Version 2.0.

Each export configuration equates to a single table. This table can either be a Samples or Capsules export.

When the Features/OData/DefaultProtocol configuration is set to 4.0, the legacy export links can be accessed by expanding the Legacy section of the “Export to an OData client” modal.

Samples Export

The Samples table reports the value of each signal (and condition) at different timestamps. The grid period options determine the timestamps that will be output.

Calendar and Chain mode

These modes output the signals' value at each real-world timestamp. Conditions are represented as signal-like data using the $condition.countOverlaps() formula function.

Calendar mode lists all timestamps within the exported interval. Chain mode filters out times where no capsules are present.

Capsule mode

Capsule mode outputs a column for each signal + capsule pair. The signal segments are gridded from the start key of each capsule.

Capsules Export

The Capsules table lists all capsules within the exported time range. The start, end, and duration are listed for all capsules (when available) followed by the capsule properties in alphabetical order.

Examples

Authenticating using Seeq Username and Password or Access Key in Microsoft Power BI Desktop

Step 1. Open Microsoft Power BI and navigate to the "Get Data" dropdown â†’ OData option selection (note it must be the desktop version of the application):


Step 2. Insert the export URL and click OK. Select “Basic” authentication and enter your Seeq login credentials.

Either an Access Key or your regular username+password are acceptable. Access Keys must be used if you log into Seeq using Single Sign-On such as Azure Active Directory or Okta.

Note for OData automations: Access Keys by default are valid for 1 week after logging into Seeq interactively. You may need to work with your admin to increase your key’s duration.

Authenticating using Passwordless Windows Auth in Microsoft Power BI Desktop

Step 1. Open Microsoft Power BI and navigate to the "Get Data" dropdown â†’ OData option selection (note it must be the desktop version of the application):

Step 2. Insert the export URL and click OK. Select “Windows” authentication and click "Use my current credentials."

Note that this method is only supported for systems using the Windows Auth Connector or the LDAP Connector to provide passwordless authentication for users, and the user logged into Windows must match a Seeq user who has access to the exported content. If you are using an authentication provider that is not currently supported, please utilize Access Keys as credentials for the OData Export.

Joining modern OData tables in Microsoft Excel

Step 1. Open Microsoft Excel. Navigate to the “Data” tab, choose the "Get Data" dropdown â†’ “From Other Sources“ → “OData Feed.“  Insert the export URL and click OK.

If necessary, authenticate using an Access Key or Windows Auth as described above.

image-20240109-225405.png

Step 2. Download the raw table data.

From the Navigator modal, check the “Select multiple items“ option. Select the desired tables. Then click “Load To.“

On the Import Data form, select “Table” and “New worksheet“ then clock OK. This will load each of the tables' raw data as their own sheet.

image-20240109-225851.png
image-20240109-225957.png

Step 3. Join the Items table to a data tables.

The Samples or Capsules table is not particularly human-readable in its default state. Excel can merge multiple tables to help with this.

From the “Data” tab, choose the "Get Data" dropdown â†’ “Combine Queries“ → “Merge.”

From the Merge window, select the desired data table for the first selection and the Items table for the second. Highlight the item_id columns in each. Ensure Left Outer join is selected. Then click OK.

From the Power Query Editor window, you can select which columns from the Items table to include. In this example, we will Expand the table to include the Name, Path, and Description. Click OK and the preview will be shown in the Power Query Editor.

Click the “Close and Load” button to load those results into a new worksheet.

image-20240109-230706.png
image-20240109-230957.png

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.