Cube Config Reference PivotData Microservice Documentation

• "mssql" for MS SQL Server, Azure SQL
• "mysql" for MySQL and protocol compatible databases (MariaDB, MemSql)
• "postgresql" for PostgreSQL and protocol compatible databases (Amazon Redshift)
• "sqlite" for local SQLite database
• "duckdb" for local DuckDB database, CSV/JSON/Parquet files, MotherDuck
• "clickhouse" for Yandex ClickHouse
• "odbc" for ODBC driver
• "oracle" for Oracle DB
• "snowflake" for Snowflake
• "presto" for Presto/Trino

@ParamName

inserts value of parameter with Name='ParamName'

@ParamName[ AND col={0} ]

if parameter with Name='ParamName' is defined inserts "AND col=ParamValue "; otherwise nothing is inserted.
Note that inside @ParamName[ ] format string the following symbols should be escaped:

• { → {{
• } → }}
• ] → ]]

[
 {
  "JoinSql" : "LEFT JOIN table1 t1 ON (t1.id = t.main_table_fk)",
  "ApplyOnFields" : ["t1.dim1", "t1.dim2"]
 }
]

[
 {
  "Path" : fieldPath,
  "FilterJson" : filterJson,
  "ApplyOnDemand" : onDemand
 }
]

Path

mongo path to the field with sub-collection

FilterJson

match specified filter after unwind

ApplyOnDemand

if true unwind is performed only if field from the sub-collection is used in a report (as dimension or measure)

PreserveNullAndEmptyArrays

If true, if the path is null, missing, or an empty array, unwind outputs the document (false by default).

{
  "AllowDiskUse" : true,
  "MaxTimeMS" : 120000
}

AllowDiskUse

Enables writing to temporary files; if false aggregation result cannot exceed 100MB (true by default).

MaxTimeMS

Time limit in milliseconds for processing aggregate query (120 seconds by default).

[
 {
  "StageJson" : "{$project: ... }",
  "ApplyOnFields" : ["amount"]
 }
]

[
 {
  "StageJson" : "{$lookup: ... }",
  "ApplyOnFields" : ["c.fld1", "c.fld2"]
 }
]

• "csv" for CSV/TSV files (delimiter is determined automatically)
• "serializedpivotdata" for special binary data files produced by PivotData SDK for .NET

{
  "UnpivotColumns": [ "col_1", "prefix*" ],
  "ColumnDimension": "col_names",
  "ValueDimension": "col_values"
}

• for dimensions: name should correspond to tabular data column returned by a data source
• for measures: may be omitted, in this case name is generated automatically by pattern "[Type]Of[Parameter]".

• for dimensions: Field or Expression (if not set "Field" is assumed)
• for measures: type of the aggregate function. Count, Sum, Average, Min, Max, Expression (custom formula calculated over aggregated data) are available for any data source type. Other types are data-source specific; please check appropriate documentation page to get more information about what measure types can be used.

• for dimension with Type="Expression": first param is an expression, next ones are names of dimensions used in the expression
• for dimension with Type="Field": first param may define data source specific query-level expression for the dimension values (please check appropriate data source documentation page for more details).
• for measure with Type="Expression": first param is a formula, next ones are names of measures used in the formula
• for measure with Type!="Expression": for generic measures like "Sum", "Average", "Min", "Max" first param is a name of the column (field) for the aggregate function. Other types may have other meaning of the parameters.

• Variable: value is taken from report configuration "Parameters" entry.
• Claim: value is taken from JWT payload (claim value).
• Condition: value is taken from report configuration "Parameters" and parsed as NReco.Data relex condition.