Metabase SQL API doesn't use pre-aggregation

[itestyoy]

Hi! We have pre-aggregations set up like this and we're using Metabase with the SQL API:

model_filters_1_2_rollup: {
    measures: [],
    dimensions: [
        CUBE.app_name,
        CUBE.user_first_touch_date,
        CUBE.date,
        CUBE.app_version,
        CUBE.user_dimensions_initial_app_version,
        CUBE.user_dimensions_country_code,
        CUBE.user_dimensions_network_group,
        CUBE.user_dimensions_device_type,
        CUBE.user_dimensions_is_adjust_user
    ],
    time_dimension: CUBE.date_technical,
    .............................
},

The query via the SQL API doesn't seem to recognize the pre-aggregation, possibly due to the time dimension.

However, if we create a query via the REST API, it correctly utilizes the pre-aggregation:

{
  "dimensions": [
    "analytics_analysis_view.user_dimensions_initial_app_version"
  ],
  "order": {
    "analytics_analysis_view.user_dimensions_device_type": "asc"
  },
  "filters": [
    {
      "member": "analytics_analysis_view.user_dimensions_country_code",
      "operator": "equals",
      "values": ["us"]
    },
    {
      "member": "analytics_analysis_view.app_name",
      "operator": "equals",
      "values": ["app"]
    },
    {
      "member": "analytics_analysis_view.date",
      "operator": "inDateRange",
      "values": ["2024-01-01", "2024-02-01"]
    },
    {
      "member": "analytics_analysis_view.user_first_touch_date",
      "operator": "inDateRange",
      "values": ["2024-01-01", "2024-02-01"]
    }
  ]
}

But the query generated via Metabase may not utilize the pre-aggregation properly. It might look like this with timeDimensions:

{
  "dimensions": [
    "analytics_analysis_view.user_dimensions_initial_app_version"
  ],
  "order": {
    "analytics_analysis_view.user_dimensions_device_type": "asc"
  },
  "filters": [
    {
      "member": "analytics_analysis_view.user_dimensions_country_code",
      "operator": "equals",
      "values": ["us"]
    },
    {
      "member": "analytics_analysis_view.app_name",
      "operator": "equals",
      "values": ["app"]
    },
    {
      "member": "analytics_analysis_view.date",
      "operator": "inDateRange",
      "values": ["2024-01-01", "2024-02-01"]
    }
  ],
  "timeDimensions": [
    {
      "dimension": "analytics_analysis_view.user_first_touch_date",
      "granularity": "day",
      "dateRange": "Last 7 days"
    }
  ]
}

How can we ensure that the pre-aggregation is utilized via the SQL API?

[igorlukanin]

Hi @itestyoy 👋

I see that you have date_technical as your time dimension in the pre-aggregation definition. However, it does not appear as a time dimension in the queries you demonstrate (user_first_touch_date and date do). I think the first step here would be to make sure that you use time dimensions of pre-aggs properly.

Also, is this issue a duplicate of this one #8256 you've also filed?

[itestyoy]

@igorlukanin
Hi! I hope this and that are two different use cases.

In this case, when we use the REST API and manually apply filters for analytics_analysis_view.date and analytics_analysis_view.user_first_touch_date, it works correctly, and the cube utilizes pre-aggregations.

However, when we use the SQL API, the cube doesn't utilize pre-aggregations.

[igorlukanin]

OK, I see. Does this advice help?

I see that you have date_technical as your time dimension in the pre-aggregation definition. However, it does not appear as a time dimension in the queries you demonstrate (user_first_touch_date and date do). I think the first step here would be to make sure that you use time dimensions of pre-aggs properly.