dbt Electronics cloud storage

Function Usage By Mesh

Function Usage By Mesh model for electronics cloud storage.

inferred

Blocks

dbt SQL LKML LookML

function_usage_by_mesh.sql · 34 lines

inferred

-- models/function_usage_by_mesh.sql
{{ config(materialized='view') }}

select va.mesh_id,
case when fil.name is null then 'Unknown function ' || va.function_id else fil.name end as function_name,
CASE
   WHEN fil.name like 'Tag%' or fil.name like 'UnTag%' THEN 1
   WHEN fil.name = 'FavoriteMobject' or fil.name = 'UnfavoriteMobject' THEN 2
   WHEN fil.name = 'DeleteMobject' THEN 4
   WHEN fil.name like 'Share%' THEN 8
   WHEN fil.name = 'EnterCamera' or fil.name = 'ExitCamera' or fil.name = 'CameraCapture' THEN 16
   WHEN fil.name like 'Chromecast%' THEN 32
   WHEN fil.name like 'Event%' THEN 64
   WHEN fil.name like 'EditPhoto%' THEN 128
   WHEN true THEN 0
END AS function_mask
from {{ this }}.view_access va
left join datacollection.function_id_lookup fil on va.function_id = fil.id
where va.function_id is not null
group by va.mesh_id, function_name, function_mask

# models/schema.yml
version: 2

models:
  - name: function_usage_by_mesh
    columns:
      - name: mesh_id
      - name: pk
        tests:
          - unique
          - not_null
      - name: function_name
      - name: function_mask

Assumptions

materialized='view' is the default; no persist hint found.
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

function_usage_by_mesh.sql · 17 lines

source-preserved validation: not checked

select va.mesh_id,
case when fil.name is null then 'Unknown function ' || va.function_id else fil.name end as function_name,
CASE
   WHEN fil.name like 'Tag%' or fil.name like 'UnTag%' THEN 1
   WHEN fil.name = 'FavoriteMobject' or fil.name = 'UnfavoriteMobject' THEN 2
   WHEN fil.name = 'DeleteMobject' THEN 4
   WHEN fil.name like 'Share%' THEN 8
   WHEN fil.name = 'EnterCamera' or fil.name = 'ExitCamera' or fil.name = 'CameraCapture' THEN 16
   WHEN fil.name like 'Chromecast%' THEN 32
   WHEN fil.name like 'Event%' THEN 64
   WHEN fil.name like 'EditPhoto%' THEN 128
   WHEN true THEN 0
END AS function_mask
from ${TABLE}.view_access va
left join datacollection.function_id_lookup fil on va.function_id = fil.id
where va.function_id is not null
group by va.mesh_id, function_name, function_mask

function_usage_by_mesh.view.lkml · 43 lines

inferred

view: function_usage_by_mesh {
  derived_table: {
    sql: select va.mesh_id,
case when fil.name is null then 'Unknown function ' || va.function_id else fil.name end as function_name,
CASE
   WHEN fil.name like 'Tag%' or fil.name like 'UnTag%' THEN 1
   WHEN fil.name = 'FavoriteMobject' or fil.name = 'UnfavoriteMobject' THEN 2
   WHEN fil.name = 'DeleteMobject' THEN 4
   WHEN fil.name like 'Share%' THEN 8
   WHEN fil.name = 'EnterCamera' or fil.name = 'ExitCamera' or fil.name = 'CameraCapture' THEN 16
   WHEN fil.name like 'Chromecast%' THEN 32
   WHEN fil.name like 'Event%' THEN 64
   WHEN fil.name like 'EditPhoto%' THEN 128
   WHEN true THEN 0
END AS function_mask
from ${TABLE}.view_access va
left join datacollection.function_id_lookup fil on va.function_id = fil.id
where va.function_id is not null
group by va.mesh_id, function_name, function_mask ;;
  }

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

  dimension: pk {
    primary_key: yes
    sql: ${mesh_id} || ${function_name} ;;
  }

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}

function_usage_by_mesh.view.lookml · 33 lines

source-preserved

- view: function_usage_by_mesh
  derived_table:
    sql: select va.mesh_id,
case when fil.name is null then 'Unknown function ' || va.function_id else fil.name end as function_name,
CASE
   WHEN fil.name like 'Tag%' or fil.name like 'UnTag%' THEN 1
   WHEN fil.name = 'FavoriteMobject' or fil.name = 'UnfavoriteMobject' THEN 2
   WHEN fil.name = 'DeleteMobject' THEN 4
   WHEN fil.name like 'Share%' THEN 8
   WHEN fil.name = 'EnterCamera' or fil.name = 'ExitCamera' or fil.name = 'CameraCapture' THEN 16
   WHEN fil.name like 'Chromecast%' THEN 32
   WHEN fil.name like 'Event%' THEN 64
   WHEN fil.name like 'EditPhoto%' THEN 128
   WHEN true THEN 0
END AS function_mask
from ${TABLE}.view_access va
left join datacollection.function_id_lookup fil on va.function_id = fil.id
where va.function_id is not null
group by va.mesh_id, function_name, function_mask
  fields:
  - dimension: mesh_id
    sql: ${TABLE}.mesh_id
  - dimension: pk
    primary_key: true
    sql: ${mesh_id} || ${function_name}
  - dimension: function_name
    sql: ${TABLE}.function_name
  - dimension: function_mask
    sql: ${TABLE}.function_mask
  - measure: count
    type: count
    drill_fields:
    - detail*

function_usage_by_mesh.sql
function_usage_by_mesh.sql
function_usage_by_mesh.view.lkml
function_usage_by_mesh.view.lookml

Function Usage By Mesh

Blocks

Assumptions

Contents