Web pivot tables by MongoDb Data Source Setup


Cube schema is specified in the appsettings.json file.

Cube config for Mongo database should have "SourceType": "mongodb" and additional "SourceMongoDb" section:

{
  "Id": "restaurants",
  "Name": "Restaurants from MongoDB sample dataset",
  "SourceType": "MongoDb",
  "SourceMongoDb": {
    "ConnectionString": "mongodb://pivotdata:8NXkkC5pbF4NBM5w@ds053166.mlab.com:53166/nrecodemo",
    "Database": "nrecodemo",
    "Collection": "restaurants",
    "FilterJson": ""  /* match JSON */
  },
  "InferSchema": true,  /* dimensions and measures are determined automatically by first N documents */
  "Dimensions": [],
  "Measures": [],
  "Parameters": []
}

"ConnectionString" is a standard MongoDb connection string in URI format.
"FilterJson" allows to specify $match stage conditions, for example:

    "FilterJson": "{ $and: [ {\"_id\":{$exists:true}}, { \"borough\" : \"Brooklyn\" } ] }"

NOTE: "InferSchema" is useful for one-time or for development purposes; for production use it is better to specify list of dimensions and measures explicitely to avoid some overhead and get full control over cube member options.

Dimensions

In case of MongoDb data source dimension name should refer to the document field name:

  "Dimensions": [
    {
      "Name": "borough",
      "LabelText": "Borough"
    },
    {
      "Name": "address.street",  /* use dot to specify sub-document field */
      "LabelText": "Street"
    }
  ]

Measures

Measure parameter (if used) should also refer to the document field:

  "Measures": [
     {
       "Type": "Average",
       "Params": [ "grades.score" ],
       "LabelText": "Score Average"
     }
  ]

Parameters

Report parameters are used in stage definitions ("FilterJson", "StageJson") for applying filter conditions in MongoDb query, for example:

  "Parameters": [
    {
      "Name": "borough",
      "DataType": "string"
    },
    {
      "Name": "cuisine",
      "DataType": "string",
      "Multivalue": true
    },
  ]
  "FilterJson": "{ $and: [ {\"_id\":{$exists:true}}, @borough[ {{ \"borough\" : {0} }}, ] @cuisine[ {{ \"cuisine\" : {{ $in : {0} }} }}, ]  ]}",