Date table instead of dynamically-generated dates


#1

A bit old school, but we use a special date table as opposed to generating dates at query time. Here it is:

view: dim_date {
  sql_table_name: reporting.dim_date ;;

  dimension: business_days_mtd {
    type: int
    sql: ${TABLE}.business_days_mtd ;;
  }

  dimension: business_days_qtd {
    type: int
    sql: ${TABLE}.business_days_qtd ;;
  }

  dimension: business_days_remaining_mtd {
    type: int
    sql: ${TABLE}.business_days_remaining_mtd ;;
  }

  dimension: business_days_remaining_qtd {
    type: int
    sql: ${TABLE}.business_days_remaining_qtd ;;
  }

  dimension: business_days_remaining_ytd {
    type: int
    sql: ${TABLE}.business_days_remaining_ytd ;;
  }

  dimension: business_days_ytd {
    type: int
    sql: ${TABLE}.business_days_ytd ;;
  }

  dimension: date_description {
    sql: ${TABLE}.date_description ;;
  }

  dimension: date_id {
    type: int
    sql: ${TABLE}.date_id ;;
  }

  dimension_group: date_key {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.date_key ;;
  }

  dimension: day_name {
    sql: ${TABLE}.day_name ;;
  }

  dimension: day_of_month {
    type: int
    sql: ${TABLE}.day_of_month ;;
  }

  dimension: day_of_quarter {
    type: number
    sql: ${TABLE}.day_of_quarter ;;
  }

  dimension: day_of_week {
    type: int
    sql: ${TABLE}.day_of_week ;;
  }

  dimension: day_of_year {
    type: number
    sql: ${TABLE}.day_of_year ;;
  }

  dimension: is_business_day {
    type: yesno
    sql: ${TABLE}.is_business_day ;;
  }

  dimension: is_business_day_sat {
    type: yesno
    sql: ${TABLE}.is_business_day_sat ;;
  }

  dimension: is_dst_in_effect {
    type: yesno
    sql: ${TABLE}.is_dst_in_effect ;;
  }

  dimension: is_holiday {
    type: yesno
    sql: ${TABLE}.is_holiday ;;
  }

  dimension: is_month_end {
    type: yesno
    sql: ${TABLE}.is_month_end ;;
  }

  dimension: is_mtd {
    type: yesno
    sql: ${TABLE}.is_mtd ;;
  }

  dimension: is_mtd_end_by_day_of_month {
    type: yesno
    sql: ${TABLE}.is_mtd_end_by_day_of_month ;;
  }

  dimension: is_qtd {
    type: yesno
    sql: ${TABLE}.is_qtd ;;
  }

  dimension: is_qtd_end_by_day_of_quarter {
    type: yesno
    sql: ${TABLE}.is_qtd_end_by_day_of_quarter ;;
  }

  dimension: is_week_day {
    type: yesno
    sql: ${TABLE}.is_week_day ;;
  }

  dimension: is_wtd {
    type: yesno
    sql: ${TABLE}.is_wtd ;;
  }

  dimension: is_wtd2 {
    type: yesno
    sql: ${TABLE}.is_wtd2 ;;
  }

  dimension: is_ytd {
    type: yesno
    sql: ${TABLE}.is_ytd ;;
  }

  dimension: is_ytd_end_by_day_of_year {
    type: yesno
    sql: ${TABLE}.is_ytd_end_by_day_of_year ;;
  }

  dimension_group: last_business_day {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.last_business_day ;;
  }

  dimension_group: month_end {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.month_end ;;
  }

  dimension: month_name {
    sql: ${TABLE}.month_name ;;
  }

  dimension: month_number {
    type: int
    sql: ${TABLE}.month_number ;;
  }

  dimension_group: month_start {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.month_start ;;
  }

  dimension_group: mtd {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.mtd ;;
  }

  dimension_group: mtd_end_by_day_of_month {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.mtd_end_by_day_of_month ;;
  }

  dimension_group: next_business_day {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.next_business_day ;;
  }

  dimension_group: pay_period_end {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.pay_period_end ;;
  }

  dimension_group: pay_period_start {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.pay_period_start ;;
  }

  dimension_group: qtd {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.qtd ;;
  }

  dimension_group: qtd_end_by_day_of_quarter {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.qtd_end_by_day_of_quarter ;;
  }

  dimension_group: quarter_end {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.quarter_end ;;
  }

  dimension: quarter_name {
    sql: ${TABLE}.quarter_name ;;
  }

  dimension_group: quarter_start {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.quarter_start ;;
  }

  dimension: relative_business_day {
    type: int
    sql: ${TABLE}.relative_business_day ;;
  }

  dimension: relative_business_day_sat {
    type: int
    sql: ${TABLE}.relative_business_day_sat ;;
  }

  dimension: relative_calendar_day {
    type: int
    sql: ${TABLE}.relative_calendar_day ;;
  }

  dimension: relative_month {
    type: int
    sql: ${TABLE}.relative_month ;;
  }

  dimension: relative_pay_period {
    type: number
    sql: ${TABLE}.relative_pay_period ;;
  }

  dimension: relative_quarter {
    type: number
    sql: ${TABLE}.relative_quarter ;;
  }

  dimension: relative_week {
    type: int
    sql: ${TABLE}.relative_week ;;
  }

  dimension: relative_week_day {
    type: int
    sql: ${TABLE}.relative_week_day ;;
  }

  dimension: relative_year {
    type: int
    sql: ${TABLE}.relative_year ;;
  }

  dimension_group: transaction {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.transaction_date ;;
  }

  dimension_group: week_end {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.week_end ;;
  }

  dimension: week_number {
    type: int
    sql: ${TABLE}.week_number ;;
  }

  dimension: week_number2 {
    type: int
    sql: ${TABLE}.week_number2 ;;
  }

  dimension_group: week_start {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.week_start ;;
  }

  dimension_group: wtd {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.wtd ;;
  }

  dimension_group: year_end {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.year_end ;;
  }

  dimension: year_month {
    sql: ${TABLE}.year_month ;;
  }

  dimension: year_number {
    type: int
    sql: ${TABLE}.year_number ;;
  }

  dimension_group: year_start {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.year_start ;;
  }

  dimension_group: ytd {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.ytd ;;
  }

  dimension_group: ytd_end_by_day_of_year {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.ytd_end_by_day_of_year ;;
  }
}