No way to use pre-aggregations with MongoDB

[igorlukanin]

Describe the bug
Cube.js connects to MongoDB via its BI Connector which is accessed in the read-only fashion. That's why Cube.js is unable to store temporary tables when building pre-aggregations. So, it's also unable to determine the types of the values in the query result. This leads to no pre-aggregations possible when using MongoDB.

To Reproduce

1. Connect Cube.js to MongoDB
2. Enable pre-aggregations
3. See the error that Cube.js cannot build them

Expected behavior

• Cube.js is able to build pre-aggregations when using MongoDB as a data source (see Native MongoDB connector that does not require Mongo BI #1555)
• Or, at least, Cube.js emits informative error messages when pre-aggregations are enabled with MongoDB as a data source

[mspiegel31]

is it expected that external pre-aggregations via cubestore or mysql would work with the current mongobi driver? If so, that could be part of the error messaging for

Or, at least, Cube.js emits informative error messages when pre-aggregations are enabled with MongoDB as a data source

[igorlukanin]

@mspiegel31 It's not, unfortunately, because Cube.js wouldn't build pre-aggregations with MongoDB as a data source.

[mspiegel31]

I'm not familiar with cube's current pre- aggregation implementation, but would that be an acceptable workaround? "Make MongoBI driver compatible with external preaggregations"? As I understand it, CubeJS is tied pretty heavily to SQL syntax. The ability to leverage pre-aggregations at all would be a big win for our team, and if it's less work to make it work with an external DB, I imagine that would be enough for most current mongo users.

[atishayjainmono]

@igorlukanin @mspiegel31
I have setup external db as mysql for pre-aggregation with MongoBI as source DB.
I was able to find pre-aggregated data in mysql, and also was able to query pre-aggregated data for certain period(14 days).
But beyond certain period, I was getting this error.

 preAggregations: {
    testaggregation: {
      type: `rollup`,
      external: true,
      sqlAlias: `actvty`,
      measureReferences: [syncCount, activationCount, downloadCount],
      dimensionReferences: [name, subType, customerId],
      timeDimensionReference: date,
      scheduledRefresh: true,
      granularity: `day`,
      partitionGranularity: `week`,
      refreshKey: {
        every: `1 day`,
        incremental: true,
        updateWindow: `7 day`,
      },
    },
  },

[paveltiunov]

This one should be resolved. If you think it isn't please create a new issue.