Using timestamp in dimension_group vs single dimension


#1

Hello!

Just found some behaviour in Looker that I cannot explain based on the generated SQL. We have an explore that counts unique users per 5 minutes. Because of mediocre housekeeping we have a
view with the following two time dimension definitions:

  dimension: minute5 {
    type: date_minute5
    sql: ${TABLE}.timestamp ;;
  }

  dimension_group: timestamp {
    type: time
    sql: ${TABLE}.timestamp ;;
    timeframes: [
      minute5,
      time,
      raw
    ]
  }

If I run a simple explore counting events per 5 minutes once using the minute5 single time dimension and once using the timestamp_minute5 dimension group I get slightly different results though the generated SQL appears to be identical. In the case of using the dimension_group the SQL also returns a row for the most recent timestamp where the results are null. This doesn’t happen when I use the single timestamp dimension.

Single dimension

Dimension group

When executing the generated SQL in BigQuery the top null row does not appear. Where does it come from in Looker, and how to avoid it?


(peter.whitehead) #2

Hi @barend_l!

When you ran the SQL in BigQuery did you copy and paste the Looker generated SQL directly into BigQuery? Also, this null may be appearing if you have dimension fill on. Could you select the “remove the filled in dates” option in the gear menu drop down on the Timestamp Minute5 field? Thanks for checking!


#3

Hi @peter.whitehead,

Thanks so much for your response! It was indeed the “filled in dates” option that I overlooked and was causing some raised eyebrows here. Glad to see it sorted now thanks to your pointer!

Cheers!