Cube Config Reference PivotData Microservice Documentation


Cube configuration model

Property Value Description
Id string unique cube identifier
Name string user-friendly cube title
Description string detailed cube description (optional)
SourceType sqldb | file | mongodb | elasticsearch specifies data source type
SourceDb Cube.DatabaseOptions model SQL database data source options (used when SourceType=sqldb)
SourceMongoDb Cube.MongoDbOptions model MongoDb data source options (used when SourceType=mongodb)
SourceElasticSearch Cube.ElasticSearchOptions model ElasticSearch data source options (used when SourceType=elasticsearch)
SourceFile Cube.FileOptions model CSV file data source options (used when SourceType=file)
InferSchema true | false If true dimensions and measures are determined automatically by probe data source query results.
Dimensions array of Cube.Member models List of dimensions that may be used in reports
Measures array of Cube.Member models List of measures that may be used in reports
Parameters array of Cube.Parameter models List of data source parameters that may be specified in reports

Cube.DatabaseOptions model

Property Value Description
Connector string specifies one of the following database connectors:
  • "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
  • "clickhouse" for Yandex ClickHouse
  • "odbc" for ODBC driver (supported only by .NET Framework and .NET Core 2 builds)
  • "oracle" for Oracle (supported only by .NET Framework build)
ConnectionString string connection string for ADO.NET provider
SelectSql string specifies SQL SELECT query that returns tabular data with columns used for dimensions and measures. SQL command template can use parameters with the following syntax:
@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:
  • { → {{
  • } → }}
  • ] → ]]
SelectSqlFile string specifies path to the file with SQL SELECT query (in this case "SelectSql" is not required). Relative path is resolved to the microservice's working directory.
GroupByRowsLimit int Max number of rows returned by GROUP BY query (1,000,000 by default). This option allows to prevent heavy server load and high RAM consumption in case if user chooses dimensions that lead to huge pivot table.
JoinsAfterGroup array of objects:
[
 {
  "JoinSql" : "LEFT JOIN table1 t1 ON (t1.id = t.main_table_fk)",
  "ApplyOnFields" : ["t1.dim1", "t1.dim2"]
 }
]
Conditional JOINs applied to result of GROUP BY statement. Used for effective queries to data marts with star-schema.

"JoinSql" represents JOIN clause; it can include parameter placeholders like in "SelectSql".
"ApplyOnFields" specifies list of dimension names that require this JOIN.

Cube.MongoDbOptions model

Property Value Description
ConnectionString string MongoDB connection string
Database string specifies database to use
Collection string specifies mongo collection to use
FilterJson string Mongo query as JSON string (optional). Query can use parameters with the same syntax as in SQL command template (see above).
Unwinds array of objects:
[
 {
  "Path" : fieldPath,
  "FilterJson" : filterJson,
  "ApplyOnDemand" : onDemand
 }
]
List of unwinds for sub-collections.
fieldPath
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)
AggregateOptions object:
{
  "AllowDiskUse" : true,
  "MaxTimeMS" : 120000
}
MongoDb aggregate options:
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).
AggregateRowsLimit int Max number of rows returned by aggregate query (1,000,000 by default). This option allows to prevent heavy server load and high RAM consumption in case if user chooses dimensions that lead to huge pivot table.
CustomStagesBeforeGroup array of objects:
[
 {
  "StageJson" : "{$project: ... }",
  "ApplyOnFields" : ["amount"]
 }
]
Conditional aggregate pipeline stages (like $project) applied before $group stage. May be used for calculations (needed either for dimensions or measures) that should be performed on MongoDb side.

"ApplyOnFields" specifies list of field names (used as dimensions or as measures) that require this stage.
CustomStagesAfterGroup array of objects:
[
 {
  "StageJson" : "{$lookup: ... }",
  "ApplyOnFields" : ["c.fld1", "c.fld2"]
 }
]
Conditional aggregate pipeline stages (like $lookup, $unwind) applied after $group stage. May be used for effective queries to data marts with star-schema.

Cube.ElasticSearchOptions model

Property Value Description
ConnectionUrl string Base URL of your ElasticSearch API endpoint
Index string the name of the Elastic index to query.
DocType string mapping type (for ElasticSearch 6.x no need to specify this option).
FilterRelex string filter expression that uses relex conditions syntax. Parameters can be used in this expression by specifying placeholders, for example: @param_name[ field.keyword = "{0}":var ]

Cube.FileOptions model

Property Value Description
Type string specifies input file type:
  • "csv" for CSV/TSV files (delimiter is determined automatically)
  • "serializedpivotdata" for special binary data files produced by PivotData SDK for .NET
Name string path to the file. Relative path is resolved to the microservice's working directory.

Cube.Member model

Property Value Description
Name string unique member identifier.
  • 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]".
LabelText string user-friendly title
Format string default value format string (.NET String.Format syntax)
Type string determines member type:
  • for dimensions: empty or "Expression"
  • for measures: type of the aggregator ("Count", "Sum", "Average", "Min", "Max" or "Expression" for custom formula)
Params array of strings Additional parameters:
  • for dimension with Type="Expression": first param is an expression, next ones are names of dimensions used in the expression
  • 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": name of the column for the aggregate function

Cube.Parameter model

Property Value Description
Name string unique parameter name
Type Variable | Claim | Condition determines how parameter value is resolved:
  • 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.
LabelText string user-friendly title
DataType string Type of the parameter value: "String", "Int32", "Int64", "Decimal", "Double", "DateTime", "Boolean"
Multivalue true|false If true parameter can accept several values (array) and can be used with "IN" operator