cube
cube copied to clipboard
Metabase SQL API doesn't use pre-aggregation
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?
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 https://github.com/cube-js/cube/issues/8256 you've also filed?
@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.
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.