Pivot table by MongoDb

You can configure 'live' connection to your Mongo database and use it as a data source for pivot table (chart, flat table) reports. In this case data is not imported: SeekTable uses Mongo aggregate pipeline query (GROUP BY) to retrieve the necessary data for the report.

There are no any limitations on the dataset size, but your MongoDb should be able to execute aggregate queries fast enough (in seconds). If you have really huge collections that cannot be aggregated in the real-time you can apply database-level filtering (with help of "Parameters") or use pre-aggregated collections.

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. Fill 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 access only to specific databases.
   • if you get 'A timeout occured after 30000ms selecting a server' error and you use cloud MongoDb hosting like MongoLab try to add ?connect=replicaSet

   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.

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

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 dimensions as arguments (formula and arguments should be specified in "Parameters").

Name

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

Label

User-friendly dimension title (optional).

Format

Custom format string (.NET String.Format) for dimension values (optional). Examples:

• for number values: ${0:0.##} → $10.25
• for date values: {0:yyyy-MM-dd} → 2017-05-25

Parameters

For Type=Field: you can specify custom $project specification. For example: {$year:"$create_date"} (extracts year value from "create_date" field).
For Type=Expression: you can specify custom formula (1-st parameter) and dimension names for the arguments (2-nd, 3-rd etc parameter).

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.
Expression: measure defined as calculated field.

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 string (.NET String.Format) for measure values (optional). Example:

• ${0:0.##} → $10.25

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.