Does Cube.js support preAggregations when using Context Variables inside the SQL definition of a cube?

Question

According to the Cube.js documentation, one can define cubes using something called Context Variables, that allows the injection of filters (such as a particular user email) when querying that cube. I haven't tried it yet, but I want to know if this feature is compatible with preaggregations, and also if it handles empty filter cases.
For example, if I define this cube (same as the example from the docs):

    cube(`OrderFacts`, {
      sql: `SELECT * FROM orders WHERE ${FILTER_PARAMS.OrderFacts.date.filter('date')}`,

      measures: {
        count: {
          type: `count`
        }
      },

      dimensions: {
        date: {
          sql: `date`,
          type: `time`
        }
      }
    });

will I be able to define a preAggregation such as this?

  preAggregations: {
    date: {
      type: `rollup`,
      measureReferences: [someMeasure],
      dimensionReferences: [someDimension],
      timeDimensionReference: date,
      granularity: `month`
    }
  }

and can I perform queries without a date dimension filter? (so the sql would end as SELECT * FROM orders WHERE;) ?

To sum up: is there a way of injecting filters to a cube's sql definition dynamically, but not at query time and instead at schema compile time?
I ask this because we are currently extending some cubes with information retrieved from our API, and we were overwriting the segment field from this cubes, but due to performance issues we would prefer to overwrite the cube's sql field (to filter unnecessary data from the start).

NOTE: We are using asyncModule to perform the queries to our API. We also need to build different cubes (for all our clients) referencing a common table, but with a dynamic SQL that will change depending on the client.

Our desired output should be (for an Orders table and F1, ..., Fn client filters from our API):
N cubes that extend a base Orders cube: OrdersC1, OrdersC2, ..., OrdersCn.
Each OrdersCi cube with a modified version of the base Orders sql, containing its Fi filter.
Each OrdersCi cube with the same dimensions, measures and preAggregations definitions, inherited from the base Orders cube.

We managed to implement all that I said before, but instead of modifying the sql field, we overwrote the segments field.

score 0 · Answer 1 · answered Feb 17 '20 at 20:12

In general only partitioned rollups pass FILTER_PARAMS for partitioned time dimensions. All other pre-aggregations doesn't allow passing context variables. There're two approaches you can use to leverage pre-aggregations in multi-tenant environments.

Override sql for each customer cube such as OrdersC1, OrdersC2, etc. In this case all pre-aggregations defined in base Orders cube will be inherited. Each customer cube will have it's own set of pre-aggregations. It means if there're N customers and M pre-aggregations then N * M pre-aggregation tables should be built which can be costly in some scenarios.

cube(`Orders`, {
  sql: `SELECT * FROM orders`,

  preAggregations: {
    date: {
      type: `rollup`,
      measureReferences: [someMeasure],
      dimensionReferences: [someDimension],
      timeDimensionReference: date,
      granularity: `month`
    },
    // ...
  }
});

cube(`OrdersC1`, {
  extends: Orders,
  sql: `SELECT * FROM orders WHERE customer_id = 'C1'`,
});

Use tenant field as a dimension of rollup. Every segment can be converted to dimension which provides an opportunity to use single rollup table for all customers. To route requests to right tenant data queryTransformer can be used.

cube(`Orders`, {
  sql: `SELECT * FROM orders`,

  // ...

  dimensions: {
    // ...

    customerId: {
      sql: `customer_id`,
      type: `string`
    }
  },

  preAggregations: {
    date: {
      type: `rollup`,
      measureReferences: [someMeasure],
      dimensionReferences: [customerId, someDimension],
      timeDimensionReference: date,
      granularity: `month`
    },

    // ...
  }
});

Does Cube.js support preAggregations when using Context Variables inside the SQL definition of a cube?

1 Answers1