Web pivot tables by a custom web API Data Source Setup


Microservice settings are specified in the appsettings.json file.

A cube config for the custom connector should have "SourceType": "webapi" and an additional "SourceWebApi" section:

{
  "Id": "MyCustomConnectorCube",
  "Name": "Custom connector cube name",
  "SourceType": "webapi",
  "SourceWebApi": {
    "Url": "http://localhost:5001/customconnector",
    "ForwardHeaders": ["Authorization"],    // forward PivotDataService JWT, if used
  },
  "Dimensions": [
    {"Name": "id", "ReportType":"table"},
    {"Name": "name", "ReportType":"table" },
    {"Name": "category"},
    {"Name": "country"},
    {"Name": "date_year"},
    {"Name": "date_month"},
    {"Name": "date_day"},
    {"Name": "age"},
  ],
  "Measures": [
    {"Type":"Count"},
    {"Type":"Average","Params":["age"]}		  
  ]
}

Note that InferSchema is not supported for this kind of connector; therefore all dimensions & measures should be defined explicitely.

Custom connector implementation

Your custom connector API endpoint (either HTTP or HTTPS) should be able to handle POST requests from PivotDataService:

Request (POST)
Request Header Content-Type = application/json
Request Body = queryJson JSON object
Response
Response Body = JSON array with requested rows values.
[
  [ col_1_value, col_2_value, ... , col_N_value],
  [ col_1_value, col_2_value, ... , col_N_value],
]

C# example (.NET Core 3.1) of the connector that provides an access to a local SQLite database is here: https://github.com/seektable/examples/tree/master/custom-connector.

Query JSON model API parameter

Property Value Description
Columns array of columns to load
[
 {
  "Type":"Field",
  "Field":{"Name":"column_name"}
 },
 {
  "Type":"Aggregate",
  "Aggregate":{"Function":"aggr_function_name","Params":[ "aggr_field" ]}
 }
]
List of columns to load. Columns order in the JSON result should correspond to this list.
There are 2 column types to load:
Field
a column that refers to a fact row field.
Aggregate
a column that refers to an aggregate function. PivotDataService may request these functions: Count (no parameters), Sum (requires 1 parameter), Average (requires 1 parameter), Min (requires 1 parameter), Max (requires 1 parameter), FirstValue (parameters defined in the cube config may be used as you want. In simplest case, 1st parameter could be a name of the 'custom' aggregate function supported by your connector).
If at least one Aggregate column is present all Field columns should be used as a grouping criteria.
Sort array of sort columns
[
 {
  "Name":"column_name",
  "Direction":"Descending"
 }
]
Optional list of columns to sort by.
Parameters object with key-value pairs:
{
  "filter_year":2015,
  "country":"USA"
 }
Values of cube's parameters. These parameters may be already converted to filtering conditions that are provided in FilterRelex / FilterJson (this depends on the cube's configuration).
FilterRelex string (relex expression) Data filter in the form of relex conditions.
FilterJson JSON string (mongo filter) Data filter in the form of Mongo JSON filter. This is the same filter but in another form - so you can handle either FilterRelex or FilterJson.
Offset number Offset of the 1st row to return. Used only in queries of flat-table reports.
Limit number Max number of rows to return.