Device

view: device {
  sql_table_name: devices.device ;;

  dimension: psk {
    type: int
    primary_key: yes
    sql: ${TABLE}.psk ;;
  }

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

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

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

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

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

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

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

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

  dimension: osname_normalized {
    label: "Device Type"
    sql: CASE
           WHEN ${osname} ILIKE 'iphone%' THEN 'iOS'
           WHEN ${osname} ILIKE 'android%' THEN 'Android'
           WHEN ${osname} ILIKE 'blackberry%' THEN 'BlackBerry'
           WHEN ${osname} ILIKE 'win%' THEN 'Windows'
           ELSE 'unknown device type'
         END ;;
  }

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }
  set: detail {
    fields: [name, osname_normalized, model, formfactor]
  }
}

- view: device
  sql_table_name: devices.device
  fields:
  - dimension: psk
    type: int
    primary_key: true
    sql: ${TABLE}.psk
  - dimension: apple_udid
    sql: ${TABLE}.apple_udid
  - dimension: disabled_reason
    type: int
    sql: ${TABLE}.disabled_reason
  - dimension: formfactor
    type: number
    sql: ${TABLE}.formfactor
  - dimension: manufacturer
    sql: ${TABLE}.manufacturer
  - dimension: model
    sql: ${TABLE}.model
  - dimension: name
    sql: ${TABLE}.name
  - dimension: operatingsystem
    type: number
    sql: ${TABLE}.operatingsystem
  - dimension: osname
    sql: ${TABLE}.osname
  - dimension: osname_normalized
    label: Device Type
    sql: CASE
           WHEN ${osname} ILIKE 'iphone%' THEN 'iOS'
           WHEN ${osname} ILIKE 'android%' THEN 'Android'
           WHEN ${osname} ILIKE 'blackberry%' THEN 'BlackBerry'
           WHEN ${osname} ILIKE 'win%' THEN 'Windows'
           ELSE 'unknown device type'
         END
  - dimension: osver
    sql: ${TABLE}.osver
  - dimension: udid
    sql: ${TABLE}.udid
  - dimension: user
    type: int
    sql: ${TABLE}.user
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created
  - dimension_group: until
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.until
  - measure: count
    type: count
    drill_fields:
    - detail*