search

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications
https://cube.dev
Other
17.81k stars 1.76k forks source link

Visualizing in PowerBI fails because of timeout #6884

Open Lampan-git opened 1 year ago

Lampan-git commented 1 year ago

Hello, I have a problem with visualizing in PowerBI. I have one cube with mulitple joins and I want to visualize the count measure and by sorting on dimensions from the joined cubes. This works until I add the date dimension cube( or add more than 5 slicers in PowerBI to filter on, I only tried once). It works however when only sorting on the date dimension cube. But when adding another slicer, I start to get this error. I don't get this error in the playground.

The join of the date dimension is normal, I've tried both with and without pre aggregations.

Failed SQL ERROR [cubesql::compile] It may be this query is not supported yet. Cube SQL Error: undefined

Logical Plan Error during rewrite: Can't find rewrite due to timeout reached. Please check logs for additional information.

Version: 0.33.35

Additional context It's very similar to: https://github.com/cube-js/cube/commit/b765838ff6c27ae34272feec00f1b60e7932b2c7

The query that seems to be failing is: 

{
  "appName": "NULL",
  "apiType": "sql",
  "sanitizedQuery": 
"SELECT \"_\".\"t11.b_name\" AS \"b_name\", \"_\".\"t21.verdict\" AS \"verdict\", \"_\".\"a0\" AS \"a0\" FROM (SELECT \"rows\".\"t11.b_name\" AS \"t11.b_name\", \"rows\".\"t21.verdict\" AS \"t21.verdict\", sum(\"rows\".\"t21.count\") AS \"a0\" FROM (SELECT \"_\".\"t21.count\", \"_\".\"t21.verdict\", \"_\".\"t11.b_name\" FROM (SELECT \"$Outer\".\"t21.count\", \"$Outer\".\"t21.distinct_test_cases\", \"$Outer\".\"t21.row_id\", \"$Outer\".\"t21.row_task_id\", \"$Outer\".\"t21.dim_test_case_sk\", \"$Outer\".\"t21.dim_t_m_sk\", \"$Outer\".\"t21.dim_a_t_sk\", \"$Outer\".\"t21.dim_activity_sk\", \"$Outer\".\"t21.dim_product_sk\", \"$Outer\".\"t21.dim_jira_id\", \"$Outer\".\"t21.dim_tag_sk\", \"$Outer\".\"t21.verdict\", \"$Outer\".\"t21.verdict_message\", \"$Outer\".\"t21.jenkins_url\", \"$Outer\".\"t21.activity_url\", \"$Outer\".\"t21.activity_id\", \"$Outer\".\"t21.is_latest_execution\", \"$Outer\".\"t21.is_latest_activity\", \"$Outer\".\"t21.robot_report\", \"$Outer\".\"t21.timestamp\", \"$Outer\".\"t21.activity_date\", \"$Outer\".\"t21.__user\", \"$Outer\".\"t21.__cubeJoinField\", \"$Outer\".\"t5.date\", \"$Outer\".\"t5.pi\", \"$Outer\".\"t5.pi_sprint\", \"$Outer\".\"t5.r_event\", \"$Outer\".\"t5.year\", \"$Outer\".\"t5.month\", \"$Outer\".\"t5.day\", \"$Outer\".\"t5.week\", \"$Outer\".\"t5.weekday\", \"$Outer\".\"t5.__user\", \"$Outer\".\"t5.__cubeJoinField\", \"$Inner\".\"t11.count\", \"$Inner\".\"t11.id\", \"$Inner\".\"t11.b_name\", \"$Inner\".\"t11.r_event\", \"$Inner\".\"t11.delivery_type\", \"$Inner\".\"t11.project\", \"$Inner\".\"t11.flow_type\", \"$Inner\".\"t11.confidence_level\", \"$Inner\".\"t11.v_url\", \"$Inner\".\"t11.created_timestamp\", \"$Inner\".\"t11.__user\", \"$Inner\".\"t11.__cubeJoinField\" FROM (SELECT \"_\".\"t21.count\", \"_\".\"t21.distinct_test_cases\", \"_\".\"t21.row_id\", \"_\".\"t21.row_task_id\", \"_\".\"t21.dim_test_case_sk\", \"_\".\"t21.dim_t_m_sk\", \"_\".\"t21.dim_a_t_sk\", \"_\".\"t21.dim_activity_sk\", \"_\".\"t21.dim_product_sk\", \"_\".\"t21.dim_jira_id\", \"_\".\"t21.dim_tag_sk\", \"_\".\"t21.verdict\", \"_\".\"t21.verdict_message\", \"_\".\"t21.jenkins_url\", \"_\".\"t21.activity_url\", \"_\".\"t21.activity_id\", \"_\".\"t21.is_latest_execution\", \"_\".\"t21.is_latest_activity\", \"_\".\"t21.robot_report\", \"_\".\"t21.timestamp\", \"_\".\"t21.activity_date\", \"_\".\"t21.__user\", \"_\".\"t21.__cubeJoinField\", \"_\".\"t5.date\", \"_\".\"t5.pi\", \"_\".\"t5.pi_sprint\", \"_\".\"t5.r_event\", \"_\".\"t5.year\", \"_\".\"t5.month\", \"_\".\"t5.day\", \"_\".\"t5.week\", \"_\".\"t5.weekday\", \"_\".\"t5.__user\", \"_\".\"t5.__cubeJoinField\" FROM (SELECT \"$Outer\".\"t21.count\", \"$Outer\".\"t21.distinct_test_cases\", \"$Outer\".\"t21.row_id\", \"$Outer\".\"t21.row_task_id\", \"$Outer\".\"t21.dim_test_case_sk\", \"$Outer\".\"t21.dim_t_m_sk\", \"$Outer\".\"t21.dim_a_t_sk\", \"$Outer\".\"t21.dim_activity_sk\", \"$Outer\".\"t21.dim_product_sk\", \"$Outer\".\"t21.dim_jira_id\", \"$Outer\".\"t21.dim_tag_sk\", \"$Outer\".\"t21.verdict\", \"$Outer\".\"t21.verdict_message\", \"$Outer\".\"t21.jenkins_url\", \"$Outer\".\"t21.activity_url\", \"$Outer\".\"t21.activity_id\", \"$Outer\".\"t21.is_latest_execution\", \"$Outer\".\"t21.is_latest_activity\", \"$Outer\".\"t21.robot_report\", \"$Outer\".\"t21.timestamp\", \"$Outer\".\"t21.activity_date\", \"$Outer\".\"t21.__user\", \"$Outer\".\"t21.__cubeJoinField\", \"$Inner\".\"t5.date\", \"$Inner\".\"t5.pi\", \"$Inner\".\"t5.pi_sprint\", \"$Inner\".\"t5.r_event\", \"$Inner\".\"t5.year\", \"$Inner\".\"t5.month\", \"$Inner\".\"t5.day\", \"$Inner\".\"t5.week\", \"$Inner\".\"t5.weekday\", \"$Inner\".\"t5.__user\", \"$Inner\".\"t5.__cubeJoinField\" FROM (SELECT \"_\".\"count\" AS \"t21.count\", \"_\".\"distinct_test_cases\" AS \"t21.distinct_test_cases\", \"_\".\"row_id\" AS \"t21.row_id\", \"_\".\"row_task_id\" AS \"t21.row_task_id\", \"_\".\"dim_test_case_sk\" AS \"t21.dim_test_case_sk\", \"_\".\"dim_t_m_sk\" AS \"t21.dim_t_m_sk\", \"_\".\"dim_a_t_sk\" AS \"t21.dim_a_t_sk\", \"_\".\"dim_activity_sk\" AS \"t21.dim_activity_sk\", \"_\".\"dim_product_sk\" AS \"t21.dim_product_sk\", \"_\".\"dim_jira_id\" AS \"t21.dim_jira_id\", \"_\".\"dim_tag_sk\" AS \"t21.dim_tag_sk\", \"_\".\"verdict\" AS \"t21.verdict\", \"_\".\"verdict_message\" AS \"t21.verdict_message\", \"_\".\"jenkins_url\" AS \"t21.jenkins_url\", \"_\".\"activity_url\" AS \"t21.activity_url\", \"_\".\"activity_id\" AS \"t21.activity_id\", \"_\".\"is_latest_execution\" AS \"t21.is_latest_execution\", \"_\".\"is_latest_activity\" AS \"t21.is_latest_activity\", \"_\".\"robot_report\" AS \"t21.robot_report\", \"_\".\"timestamp\" AS \"t21.timestamp\", \"_\".\"activity_date\" AS \"t21.activity_date\", \"_\".\"__user\" AS \"t21.__user\", \"_\".\"__cubeJoinField\" AS \"t21.__cubeJoinField\" FROM \"public\".\"fact_test_case\" AS \"_\") AS \"$Outer\" LEFT JOIN (SELECT \"_\".\"date\" AS \"t5.date\", \"_\".\"pi\" AS \"t5.pi\", \"_\".\"pi_sprint\" AS \"t5.pi_sprint\", \"_\".\"r_event\" AS \"t5.r_event\", \"_\".\"year\" AS \"t5.year\", \"_\".\"month\" AS \"t5.month\", \"_\".\"day\" AS \"t5.day\", \"_\".\"week\" AS \"t5.week\", \"_\".\"weekday\" AS \"t5.weekday\", \"_\".\"__user\" AS \"t5.__user\", \"_\".\"__cubeJoinField\" AS \"t5.__cubeJoinField\" FROM \"public\".\"dim_date\" AS \"_\") AS \"$Inner\" ON (\"$Outer\".\"t21.__cubeJoinField\" = \"$Inner\".\"t5.__cubeJoinField\")) AS \"_\" WHERE \"_\".\"t5.date\" >= TIMESTAMP '2023-06-03 00:00:00') AS \"$Outer\" LEFT JOIN (SELECT \"_\".\"count\" AS \"t11.count\", \"_\".\"id\" AS \"t11.id\", \"_\".\"b_name\" AS \"t11.b_name\", \"_\".\"r_event\" AS \"t11.r_event\", \"_\".\"delivery_type\" AS \"t11.delivery_type\", \"_\".\"project\" AS \"t11.project\", \"_\".\"flow_type\" AS \"t11.flow_type\", \"_\".\"confidence_level\" AS \"t11.confidence_level\", \"_\".\"v_url\" AS \"t11.v_url\", \"_\".\"created_timestamp\" AS \"t11.created_timestamp\", \"_\".\"__user\" AS \"t11.__user\", \"_\".\"__cubeJoinField\" AS \"t11.__cubeJoinField\" FROM \"public\".\"dim_product\" AS \"_\") AS \"$Inner\" ON (\"$Outer\".\"t21.__cubeJoinField\" = \"$Inner\".\"t11.__cubeJoinField\")) AS \"_\" WHERE \"_\".\"t11.delivery_type\" = '[REPLACED]' AND \"_\".\"t5.date\" < TIMESTAMP '2023-07-15 00:00:00') AS \"rows\" GROUP BY \"t11.b_name\", \"t21.verdict\") AS \"_\" WHERE NOT \"_\".\"a0\" IS NULL LIMIT 1000001"
  "protocol": "postgres"
} 
Error during rewrite: Can't find rewrite due to timeout reached. Please check logs for additional information.
paveltiunov commented 1 year ago

Please see #6307