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 | xmla 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)
SourceXmla Cube.XmlaOptions model XMLA Client data source options (used when SourceType=xmla)
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
PivotFilter Cube.PivotFilter model Pivot table filter to DB query condition translation options.
FormatCulture string Use specified culture (in format languagecode2-country for .NET CultureInfo) for values formatting. For example: en-US, en-GB, fr-FR, ru-RU. If cube-specific culture is not set global PivotData microservice FormatCulture setting is used.

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
  • "oracle" for Oracle
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 Limit for max number of rows returned in result of 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 high-cardinality dimensions that lead to huge pivot table.
UseGroupByCube boolean Enables calculation of totals / sub-totals for custom-SQL measures ("FirstValue" measure type) with GROUP BY CUBE SQL syntax. This feature is disabled by default.
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 Limit for 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 high-cardinality 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. Report parameters can be used in this expression by specifying placeholders, for example: @param_name[ field.keyword = "{0}":var ]

Cube.XmlaOptions model

Property Value Description
ConnectionString string connection string for ADOMD.NET
Note: .NET Core builds support only HTTP(S)-based data sources (including 'asazure:').
SelectMdx string a template for MDX SELECT. Placeholders for the tokens (dynamically-generated MDX parts) are specified with @token_name syntax.
MdxTokens array of objects
{ Name:"token_name", Expression:"expression_to_eval"}
You can define custom tokens for MDX template that are calculated in a run-time with expressions. See XMLA OLAP connector documentation for more details on MdxTokens usage.
AggregateRowsLimit int Limit for max number of rows returned by MDX query (1,000,000 by default). This option allows to prevent heavy server load and high RAM consumption in case if user chooses high-cardinality dimensions that lead to huge pivot table.

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.
CsvSettings object
{"Separator":","}
optional CSV settings (like separator). If not specified these settings are determined automatically.
FilterRelex string filter expression that uses relex conditions syntax. Report parameters can be used in this expression by specifying placeholders, for example: @param_name[ "csv column":field = "{0}":var ]

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: 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.
Params array of strings Additional cube member parameters:
  • 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.
ReportType Any | Pivot | Table | None determines type of reports where this cube member is applicable
Properties object
{"name":"value"}
Custom metadata associated with this cube member. These properties are exposed in cube schema returned by web API and may be used by front-end code.

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
Expression string With this option you can specify an expression to evaluate parameter value for the data query. In expression you can access original parameter value with Parameter["param_name"].

Cube.PivotFilter model

Property Value Description
ApplyAsCondition boolean if true enables pivot table filter translation to database query condition (if possible). Translation is possible only when filter keyword(s) has dimenion 'hint(s)' that clearly identify a dimension that corresponds to a database column (or SQL expression). Filter translation is disabled by default.
TranslateLike boolean Translate 'like' conditions (for filters like "name:John*"). This options is disabled by default.
Include array of strings Explicit list of dimension names that can be translated into database query condition. By default all dimensions (except Type=Expression) can be translated.
Exclude array of strings Explicit list of dimension names that cannot be translated into database query condition.