Article Content

  1. Overview
  2. Application order of data-transformations
  3. All currently available datasources
    1. Filterable Result Table (FRT)
    2. REST Endpoints
    3. Raw
    4. Datasource
    5. Job
    6. Data Table
    7. Connection

Overview

Datasources are the definitions of data which should be visualized by Elements. Datasources itself do not request any data, this only happens if they are actually used inside an element which is part of the layout which is currently shown. While in classic ONE DATA everything had to be done with result tables, there are 3 `schema` options within Apps:


  • data as table (close to what we had with result tables)

  • data as list (ideal for filling drop-downs)
  • data as string (used e.g. for KPIs)

Depending on the data source type not all `schema` options can be used, however there is a specific datasource available which is able to convert from tables to lists or strings, and from lists to strings.


Application order of data-transformations

  1. Datasource transformations (e.g. SQL at the Datasource)
  2. SyncSet filters (e.g. A Table will be filtered after you click on a BarChart Bar)
  3. Element transformations (not applied if getting distinct values and "distinctValuesUseAllTransformations" is not set) (e.g. SQL at the Element)
  4. User filters (e.g. Applied Single/Multi Select Filter)
  5. User sorting (e.g. ASC or DESC sorting of a table column


All currently available datasources


Filterable Result Table (FRT)


Note that FRTs are deprecated and will no longer be supported in newer versions of ONE DATA! Use Data Tables instead.


Datasource Origin
Possible Schemas
Description
frt
table
Data from FRTs, when used in e.g. highcharts visualizations, the amount of data specified in the pagination will be retrieved. Only special FRT-tables will later on provide the current FRT features with filtering, etc.


There are currently thre ways to specify the target FRT:

  • frtId or dataId: the id of the target FRT (it is advisable to use this option).
    • frtId: The frtId can be looked up in the workflows Filterable Result Table Processor in the tab ab "Json-results"
    • dataId: The dataId of a FRT can be looked up in the ONE DATA Datatable-Tab (more information can be found here)
  • frtId and workflowJobId: The frtId and the workflowJobId of the job that generates that FRT (this option is mainly kept for backwards compatibility)


Example

{
  "id": "uniqueNameForDataSource",
  "origin": "frt",
  "config": {
    "schema": "table",
    "frtId": "c9a2dfe6-c6a5-40e2-b85a-79c25aff2aea",
    // optional argument to execute a custom sql statement on the table
    "dataOptions": {
      // A whitelist of columns to be retrieved
      "columns": ["Col1", "Col2", "Col3"],
      // Specify if other columns should be retrieved. In this case,
      // the columns selected above will be shown first, then the additional ones
      "retrieveOtherColumns": true,
      // configures the default pagination settings 
      // (what is shown on first load of an app)
      "pagination": {
        // Type can either be offset or random, offset means that the data is
        // retrieved page-wise, so then page and size are mandatory to be given.
        // Random retrieves a single (random) junk of data, 
        // for this only the size is mandatory        
       "type": "offset",
        // the page of the dataset that should be retrieved
        // (if offset type is chosen)
        // if size is 10 and page is 2, this means that the
        // rows 11 to 20 will be returned by default
        "page": 1,
        // the size of a single page, or the overall result
        // if random pagination type is chosen        
        "size": 10,
        // indicator if the total amount of pages should be computed,
        // may potentially make loading times slower, but the UI/UX is nicer
        "totalPages": false
      },
      // optional sorting which is applied AFTER the custom sql is executed
      // multiple entries can be supplied with either "asc" or "desc" order
      "sort": [
        {
          "name": "Col1",
          "order": "asc"
        }
      ],
      // define aggregations to be applied to columns
      // only columns listed here will be in the result dataset
      "aggregations": [
        {
          "column": "Col1",
          "aggregationOperation": "SUM_OPERATION"
        },
        {
          "column": "Col2",
          "aggregationOperation": "GROUP_OPERATION"
        }
      ],
      // custom SQL to be executed on the FRT
      "sql": "SELECT Col1 from inputTable",
      // A set of advanced aggregations to be applied to the FRT, in order,
      // If transformations are configured, the simple options above are ignored,
      // except for pagination
      "transformations": [
        {
          "type": "sql",
          "config": "SELECT Col2 from inputTable"
        },
        {
          "type": "filter",
          "config": {
            "column": "Col1",
            "columnType": "INT",
            // Either filter by distinct values
            "values": [1,2,3],
            // Or filter by range
            "range": {
              "min": 1,
              "max": 5
            }
          }
        },
        {
          "type": "columnSelection",
          "config": {
            "columns": ["Col1", "Col2"]
          }
        },
        {
          "type": "aggregation",
          "config": [
            {
              "column": "Col1",
              "aggregationOperation": "SUM_OPERATION"
            },
            {
              "column": "Col2",
              "aggregationOperation": "GROUP_OPERATION"
            }
          ]
        },
        "order": {
          "config": {
            "name": "Col1",
            "order": "asc"
          }
        }
      ]
    },
    "filterOptions": {
      // By default it is allowed to filter columns by min/max values
      // and a search string, if this should also be disabled,
      // then set this flag to false.
      "enabled": true,
      // Computing distinct values is a time-intensive task and should
      // only be enabled on datasets with smaller amounts of rows.
      // By default, distinct values are not calculated.
      "distinctValues": false
    }
  }
}



REST Endpoints

Datasource Origin
Possible Schemas
Description
rest
table, list, string
Data from rest endpoints, json response can be limited via json-path expressions to match the specified schema.


Example

{
  "id": "otherUniqueName",
  "origin": "rest",
  "config": {
    "schema": "list",
    "url": "https://whatever.de",
    "headers": {
      "custom": "header"
    },
    "body": "json body, or empty string",
    "method": "GET",
    // when requesting with schema string or list `json_path`
    "jsonPath": "$.json.path",
    // when requesting with schema table
    "columns": [
      {
        "name": "colName",
        "jsonPath": "$.json.path"
      }
    ]
  }
}



Raw

Datasource Origin
Possible Schemas
Description
raw
table, list, string
Raw data specified by the user, it has to match the given schema.


Example

{
  "id": "rawName",
  "origin": "raw",
  "config": {
    "schema": "string",
    // if schema is string, then data is a string
    // if schema is list, then data is a list of string
    // if schema is table then data is a list of objects
    "data": "singleString"
  }
}



Datasource

Datasource OriginPossible SchemasDescription
datascourcelist, stringTakes another datasource and transforms its data to the given schema (e.g. to a list by selecting a column out of a table, or to a string by selecting a row of a column).


Example

{
  "id": "transformedFrtToList",
  "origin": "datasource",
  "config": {
    "schema": "list",
    "id": "frt_otherSource",
    // if schema of the referenced datasource is table, then column
    // is always needed
    "column": "certainColumnName",
    // if schema of this datasource is string, then row is always needed
    "row": 0
  }
}



Job

Datasource OriginPossible SchemasDescription
jobtableData from workflow job resulttables, when used in e.g. highcharts visualizations all data is retrieved


Example

{
  "id": "dataFromResultTable",
  "origin": "job",
  "config": {
    "schema": "table",
    "workflowId": "a0bdfae8-d154-4110-a00b-33c8290ec228",
    "resultId": "c9a2dfe6-c6a5-40e2-b85a-79c25aff2aea"
  }
}



Data Table

Datasource OriginPossible SchemasDescription
datatabletableData from standard ONE DATA datasets, when used in e.g. highcharts visualizations all data is retrieved


Example

{
  "id": "someName",
  "origin": "datatable",
  "config": {
    "schema": "table",
    "dataId": "a0bdfae8-d154-4110-a00b-33c8290ec228"
  }
}



Connection

Datasource Origin
Type
Description
connection
table
A ONE DATA connection, which is referenced through the id of the connection.

Currently only database connections are supported and allow the retrieval of a database table.
The target table can be specified directly via a tableName or a baseQuery, which is a SQL query that is executed on the database to retrieve the table data.

Similar to the frt datasource filterOptions and dataOptions are also supported and forwarded to the databse when set.


Example

  {
    "id": "uniqueNameForDataSource",
    "origin": "connection",
    "config": {
      "schema": "table",
      "connectionId": "c9a2dfe6-c6a5-40e2-b85a-79c25aff2aea",
    "tableName": "testTable"
    }
  }