BUG pre-aggregation not selected on many-to-many relationship + measure about cube HOT 4 OPEN

Hi @kriKinesso 👋

My suspicion is that referencing multiple (too many?) pre-aggregations somehow breaks the query matching resolution:

        rollups: [
          CUBE.main,
          client.main,
          client_agency.main,
          agency.main,
        ],

Could you please try reworking your pre-aggregations in a way that there are only two pre-aggregations there, one from bq and one from pg?

from cube.

Hi @igorlukanin 👋, thank you for your answer.

So is there a limit number for the pre aggregations you can reference in a rollup join?

In my case I have to use pre-aggregations for this sort of queries, I absolutely can not allow users to query the datasource directly. If I remove any of the pre-aggregations from rollups the query won't match.

Also, I have other pre-aggregations, with a larger number of rollups that work well. This error comes up only in the case of many to many relationships, which leads me to think that either we are dealing with a bug, or pre-aggregations are not meant to be used with many to many relationships.

from cube.

In my case I have to use pre-aggregations for this sort of queries, I absolutely can not allow users to query the datasource directly. If I remove any of the pre-aggregations from rollups the query won't match.

Well, I don't think that you should not use pre-aggregations. However, I feel like you might try combining 3 pre-aggregations on the pg data source (that you reference in the rollup join) into one. And then reference only that one in the rollup join.

This error comes up only in the case of many to many relationships, which leads me to think that either we are dealing with a bug, or pre-aggregations are not meant to be used with many to many relationships.

I can't spot the issue by looking at your data model. Let me tag @paveltiunov in case he sees anything suspicions.

from cube.

I tried grouping the pre-aggregations, and I still get the same error, so unfortunately that is not the issue.

from cube.