Bin with personalized interval

Follow

Gianfranco Campana

• November 15, 2023 17:07
• Edited

Hello,
in case of time series based on a timestamp column with 15 minutes interval rate ( 00, 15, 30, 45), is it possible to draw a combo chart ordered by date and counting rows in between each interval ?

In this scenario, if the timestamp is a date, I only can use the default binning choice (Days, hours, minutes, and so on), and therefore the count is incorrect because e.g. all the four interval ( 00, 15, 30, 45) are added, if binning over hours.

If the timestamp is casted to TEXT, no ordering is made anymore.

So, in theory, I should be able to bin by the personalized interval time, or bin by DATE_TRUNC(minutes, timestamp_column).

I hope I've been clear, someone has some idea on how to achieve the desired result in Immerse?

Thanks in advance

 

 

0

• 

  Candido Dessanti

  • November 16, 2023 09:46

  HI Gianfranco,
  
  I'm not sure if I got the point, but you can create any binning using customeized dimensions. Just disable the default binning and add your own binning
  
  

  Candido

  0

  Comment actions Permalink
• 

  Gianfranco Campana

  • November 16, 2023 10:44

  Hi Candido,
  I can create a custom dimension, but if I disable the Bin, the chart it is not ordered by date anymore: it is ordered by descending number of count rows. Is it correct ?  I still need to order by date.

  

  As a side note, my custom dimension builder is a little different from your. No "scope" input.

  Immerse version 7.1.1

   

   

   

  it seems that my Custom Dimension Editor is different from your: 

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • November 16, 2023 10:58

  Hi,
  
  Yap, it's because the  order by in this case is by the measure, rather than custom dimension
  
  You can choose the field used for sorting in the Data and formatting tab in Legend and setting
  
  When changing the sort field, the bars are ordered based on the timestamp.
  
  
  

  0

  Comment actions Permalink
• 

  Gianfranco Campana

  • November 16, 2023 12:19

  Thanks for the solution, it works , but I'm hitting the

  "Could not render chart
  Sorting the result would be too slow"
  
  error since I started working with the sorting in the Data and formatting tab in Legend and setting.

  Using only 4.3 M rows table, 10 groups, and filtered only for a few thousand rows:

  

  

  Any hints?

   

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • November 17, 2023 09:25

  HI,

  You can turn off the watchdog setting the parameter enable_watchdog to false.
  Are you getting the error also when the Date MAX filter is disabled?
  
  

  0

  Comment actions Permalink
• 

  Gianfranco Campana

  • November 17, 2023 12:28

  Thanks, I know I can turn off watchdog, but it is strange having the error on so few rows.

  And yes, the same happens turning off Date MAX.

   

   

   

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • November 17, 2023 14:59

  Hi Gianfranco,

  I hope this message finds you well. I'm seeking clarification on whether the dashboard is functioning with the watchdog turned off. There are two conditions that can trigger the reported exception, and in both cases, the number of rows to be sorted exceeds one million when default parameters are used. Therefore, it is unusual to encounter this exception when a group-by query is employed.

  Could you please retrieve the query that is triggering this issue by checking the INFO logs? Look for the error message in Immerse. To expedite the search, you can use the /logs/info endpoint in Immerse.

  Thank you for your collaboration.

  Best regards,
  Candido

  0

  Comment actions Permalink
• 

  Gianfranco Campana

  • November 17, 2023 15:26

  Below you can find the query triggering the error, let me know if you need anything else.

  The Dimension0 (dataora), once grouped , returns about 27000 groups.

  2023-11-17T15:12:32.151243 E 28 5452 36326278 DBHandler.cpp:1678 Sorting the result would be too slow
  2023-11-17T15:12:32.151571 I 28 5452 36326278 DBHandler.cpp:1619 stdlog sql_execute 7312 365 heavyai admin 913-FnJB {"query_str","client","nonce"} {"SELECT count(*) AS measure0, dataora AS dimension0 FROM operint GROUP BY dimension0 ORDER BY dimension0 desc NULLS LAST LIMIT 500","http:192.xxxx.xxx.xxx","11/1-0"}

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • November 18, 2023 07:04

  Hi Gianfranco,

  Thank you for providing those logs; they have been instrumental in identifying the root cause of your issue. The query is failing because it's overestimating the number of rows returned. In this case, the watchdog assumes the potential number of rows rather than the actual number of rows returned, triggering the exception.

  As a workaround, you can either disable the watchdog or increase the parallel-top-max from the default of 2,000,000 to a very high number.

  If you have any further questions or need assistance with the workaround, feel free to reach out.

  Best regards,
  Candido

  1

  Comment actions Permalink
• 

  Gianfranco Campana

  • November 18, 2023 09:03

  Thank you for your excellent support and the useful explanation, Candido.

  Best regards,
  Gianfranco

  0

  Comment actions Permalink

Please sign in to leave a comment.