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 (NET6) 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.