Setup MongoDb for pivot table reports

How to configure MongoDb data source

1. Click on "Connect to Database" item at "Cubes" view, or just open this link (ensure that you're logged in).
2. Select "MongoDb" in Data Source Type selector:
3. Please fill out all required fields:

   Cube Name

   short title that describes this data source

   Connection String

   Valid connection string for MongoDb driver. Example:

   mongodb://user:password@host:port/database[?options]

   Notes:

   • "database" is required if user has an access only to the concrete database.
   • if you get an error A timeout occured after 30000ms selecting a server:
     • ensure that your MongoDb server/cloud service allows connections from SeekTable server (SeekTable.com IP is included into whitelist)
     • if you use cloud MongoDb service try to add these options: connect=replicaSet, replicaSet=yourSet, authSource=yourAuthDB
     • if your MongoDb allows only SSL connections add this option: ?ssl=true (this option is required for Azure CosmosDB)

   Database Name

   the name of the database to use.

   Collection Name

   the name of collection to use; document and sub-document fields can be used as dimensions or measures in a pivot table reports.

   Filter JSON

   here you can define custom JSON for $match stage. For report-specific filtering you can use report parameters.

   Unwinds for sub-collections

   If documents have sub-collections you can unwind them to use fields from these sub-documents. Unwind may be applied conditionally, only when some specific fields are used in the report.

   Custom Aggregate Pipeline Stages

   Additionally you can specify any number of custom MongoDb aggregate pipeline stages (with JSON) that are applied before or after $group. Custom stages may be used for lookups resolution or calculations that are performed on MongoDb side. Custom stages also may be applied conditionally only when some specified fields are used in the report.

4. Infer dimensions and measures by columns option: keep it checked to determine dimensions and measures by first N documents - in this case you don't need to define Dimensions and Measures by yourself. You can edit configuration later and remove excessive elements or customize automatically determined ones.
5. Click on "Save" button.

If everything is fine you should see a new cube dashboard with the list of available dimensions. In case of connection error you'll see an orange box with the error message.

Dimensions setup

Dimensions determine what document fields may be used as grouping criteria. It is possible to project dimension by specifying calculation as 1-st "Parameter".

Type

Field: dimension value is a document field or result of $project specification (can be provided as first "Parameter").

Expression: dimension is defined as calculated field with custom formula that uses another dimension(s) as argument(s); formula and arguments are specified in "Parameters".

Name

Unique dimension identifier. For Type=Field this is document or sub-document field specifier like field.sub_field.

Label

User-friendly dimension title (optional).

Format

Custom format template (syntax is for .NET String.Format, only zero-index placeholder {0} can be used). Examples:

• prefix {0} suffix → append custom prefix and/or suffix
• {0:yyyy-MM-dd} → format date (or timestamp) as 2017-05-25
• {0:MMM} → format month number (1-12) as a short month name (Jan, Feb etc)
• {0:MMMM} → format month number (1-12) as a full month name (January, February etc)
• {0:ddd} → format day-of-week number (0-6) as a short day-of-week name (Mon, Tue etc)
• {0:dddd} → format day-of-week number (0-6) as a full day-of-week name (Monday, Tuesday etc)

Parameters

when dimension Type=Field: first value can be a JSON with custom projection. For example: {$year:"$date_field"}.
Important: if your projection uses a field from sub-collection that is configured for on-demand unwind dimension name should include path to this field; for example: project.release_date-year.

for dimension Type=Expression: first value is an expression, and next values are arguments (names of another dimensions).

Date-part dimensions calculated with a projection

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate Mongo projection:

1. add new dimension with Type=Field
2. fill Name with some unique value: say, "date_field_year"
3. add one Parameter which should be a date-part projection definition:
   • For year: {$cond: [{ $ifNull: ["$date_field", 0] }, {$year:"$date_field"}, null]}
   • For month: {$cond: [{ $ifNull: ["$date_field", 0] }, {$month:"$date_field"}, null]} + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
   • For day: {$cond: [{ $ifNull: ["$date_field", 0] }, {$dayOfMonth:"$date_field"}, null]}
   • For day-of-week: {$cond: [{ $ifNull: ["$date_field", 0] }, {$subtract:[ {$isoDayOfWeek:"$date_field"}, 1]}, null]} + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)
   • For quarter: {$cond: [{ $ifNull: ["$date_field", 0] }, { $dateTrunc: { date: "$date_field", unit: "quarter" } }, null]} (MongoDB 5+)
   • For week-of-year: {$cond: [{ $ifNull: ["$date_field", 0] }, {$isoWeek:"$date_field"}, null]}

Measures setup

Type

Count: the number of aggregated documents.
Sum: the total sum of a numeric field.
Average: the average value of a numeric field.
Min: the minimal value of a column.
Max: the maximum value of a column.
FirstValue: custom acummulator aggregation pipeline expression (not supported yet for MongoDb).
Expression: measure is defined as a formula expression that is calculated on SeekTable side.

Parameters

For Type=Count: no parameters needed.
For Type=Sum/Average/Min/Max: document field or field path to aggregate.
For Type=FirstValue: collects field value of the first document.
For Type=Expression: first parameter is formula expression, and next parameters are names of measures used as arguments in the expression.

Name

Explicit unique measure identifier. You can leave it blank (for any measure types except "Expression") to generate the name automatically.

Label

User-friendly measure caption (optional).

Format

Custom format template (syntax is for .NET String.Format, only zero-index placeholder {0} can be used). Examples:

• {0:$#.##} → format number as $10.25 (or empty if no value)
• {0:0,.0#}k → show number in thousands with "k" suffix
• {0:0.#|k} → if number>1000 shorten it with "k" suffix
• {0:0,,.0#}M → show number in millions with "M" suffix
• {0:0.#|M} → if number>1000000 shorten it with "M" suffix
• {0:0.#|kMB} → shorten large number with appropriate "k"/"M"/"B" suffix

Report parameters setup

Report parameters are used when you need to declare user-defined variable and use it in the Mongo aggegate pipeline stages; typical usage is database-level filtering with Filter JSON, but parameter placeholders may be used in Custom Stage entries as well.

Name

Unique (for cube) parameter identifier.

Label

User-friendly parameter caption for UI (optional).

Data Type

String: text-based value.
Int32: 32-bit integer (max value is 2,147,483,647).
Int64: 64-bit integer (max value is 9,223,372,036,854,775,807).
Decimal: Fixed-point number with max 28 significant digits. Decimal point is '.' character.
DateTime: datetime or date value (in this case datetime value has 0:00:00 time). Date value should be specified as string in YYYY-MM-DD format.
Boolean: accepts only 'True' or 'False' value.

Multivalue

If checked parameter can accept several values (as array, in UI user can enter them as comma-separated string). Multivalue parameter can be used only with SQL IN condition.

Default Value

Defines default value of this parameter. Empty means 'not defined'.

Expression

Custom expression to evaluate final parameter value. Expression syntax is the same as in calculated cube members; you can access user-entered values with Parameter["param_name"].

When parameter is defined it can be used in Filter JSON as following:

{ 
  $and: [ 
    {"_id":{$exists:true}},
    @borough[ {{ "borough" : {0} }}, ]
    @cuisine[ {{ "cuisine" : {{ $regex : {0} }} }}, ]
  ]
}

• {"_id":{$exists:true}} just 'always true' part of $and condition (for case when params are not defined)
• parameter with name borough is should be equal to appropriate document field. Note that parameter placeholder (inside square brackets) { and } should be escaped as {{ and }}
• parameter with name cuisine is compared as 'like' with appropriate document field

Parameter's placeholder syntax:

@

identifies that this is a placeholder for the parameter

orderDate

parameter Name

[ ]

JSON between square brackets is used when parameter is defined. Optionally you may define an alternative JSON that is used when parameter is not defined in this way:

@paramName[ json_when_defined ; json_when_NOT_defined ]

{0}

an inner placeholder for the parameter's value. This value is included as a serialized JSON.

Notes:

• multivalue parameters may be used only with $in condition

  @country[ {{ "country" : {{ $in : {0} }} }}, ]

• Inside a placeholder several symbols have special meaning, and if you need to use these symbols in the formatting template they should be escaped in this way:
  ; → ;; or \;
] → ]] or \]
{ → {{ or \{
} → }} or \}
\ → \\
@ → \@
• it is possible to use nested placeholders. For example:

  @parameter1[ @parameter2[ {{ "field" : {0} }}, ] ]

  (= apply "parameter2" only if "parameter1" is defined)