data.ml
dbt Healthcare analytics

Operations Supply Facts

Operations Supply Facts model for healthcare analytics.

inferred

Blocks

operations_supply_facts.sql · 30 lines
inferred 
-- models/operations_supply_facts.sql
{{ config(materialized='view') }}

SELECT
   MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID AS MedicalProcedures_ID
 , CAST(SUM(MedicalProcedures_MedicalSupplies_Map.Count) AS UNSIGNED) AS Quantity_Used
 , COUNT(MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID) AS Distinct_MedicalSupplies_IDs_Used
 , COUNT(DISTINCT MedicalSuppliesCategory.MedicalSuppliesCategoryID) AS Distinct_MedicalSupplies_Types_Used
FROM MedicalProcedures_MedicalSupplies_Map
 , MedicalSupplies
 , MedicalSuppliesCategory
WHERE
       MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID = MedicalSupplies.MedicalSuppliesID
   AND MedicalSupplies.MedicalSuppliesCategoryID = MedicalSuppliesCategory.MedicalSuppliesCategoryID
GROUP BY
  MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID

# models/schema.yml
version: 2

models:
  - name: operations_supply_facts
    columns:
      - name: operations_id
        tests:
          - unique
          - not_null
      - name: quantity_used
      - name: distinct_supply_ids_used
      - name: distinct_supply_types_used

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.
operations_supply_facts.sql · 13 lines
source-preserved validation: parses 
SELECT
   MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID AS MedicalProcedures_ID
 , CAST(SUM(MedicalProcedures_MedicalSupplies_Map.Count) AS UNSIGNED) AS Quantity_Used
 , COUNT(MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID) AS Distinct_MedicalSupplies_IDs_Used
 , COUNT(DISTINCT MedicalSuppliesCategory.MedicalSuppliesCategoryID) AS Distinct_MedicalSupplies_Types_Used
FROM MedicalProcedures_MedicalSupplies_Map
 , MedicalSupplies
 , MedicalSuppliesCategory
WHERE
       MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID = MedicalSupplies.MedicalSuppliesID
   AND MedicalSupplies.MedicalSuppliesCategoryID = MedicalSuppliesCategory.MedicalSuppliesCategoryID
GROUP BY
  MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID
operations_supply_facts.view.lkml · 72 lines
inferred 
view: operations_supply_facts {
  derived_table: {
    sql: SELECT
   MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID AS MedicalProcedures_ID
 , CAST(SUM(MedicalProcedures_MedicalSupplies_Map.Count) AS UNSIGNED) AS Quantity_Used
 , COUNT(MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID) AS Distinct_MedicalSupplies_IDs_Used
 , COUNT(DISTINCT MedicalSuppliesCategory.MedicalSuppliesCategoryID) AS Distinct_MedicalSupplies_Types_Used
FROM MedicalProcedures_MedicalSupplies_Map
 , MedicalSupplies
 , MedicalSuppliesCategory
WHERE
       MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID = MedicalSupplies.MedicalSuppliesID
   AND MedicalSupplies.MedicalSuppliesCategoryID = MedicalSuppliesCategory.MedicalSuppliesCategoryID
GROUP BY
  MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID ;;
  }

  dimension: operations_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.MedicalProcedures_ID ;;
  }

  dimension: quantity_used {
    type: number
    sql: ${TABLE}.Quantity_Used ;;
  }

  dimension: distinct_supply_ids_used {
    type: number
    sql: ${TABLE}.Distinct_MedicalSupplies_IDs_Used ;;
  }

  dimension: distinct_supply_types_used {
    type: number
    sql: ${TABLE}.Distinct_MedicalSupplies_Types_Used ;;
  }

  measure: count {
    type: count
  }

  measure: average_quantity_used {
    type: average
    sql: ${quantity_used} ;;
  }

  measure: average_supply_ids_used {
    type: average
    sql: ${distinct_supply_ids_used} ;;
  }

  measure: average_distinct_supply_types_used {
    type: average
    sql: ${distinct_supply_types_used} ;;
  }

  measure: total_quantity_used {
    type: sum
    sql: ${quantity_used} ;;
  }

  measure: total_supply_ids_used {
    type: sum
    sql: ${distinct_supply_ids_used} ;;
  }

  measure: total_distinct_supply_types_used {
    type: sum
    sql: ${distinct_supply_types_used} ;;
  }
}
operations_supply_facts.view.lookml · 49 lines
source-preserved 
- view: operations_supply_facts
  derived_table:
    sql: SELECT
   MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID AS MedicalProcedures_ID
 , CAST(SUM(MedicalProcedures_MedicalSupplies_Map.Count) AS UNSIGNED) AS Quantity_Used
 , COUNT(MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID) AS Distinct_MedicalSupplies_IDs_Used
 , COUNT(DISTINCT MedicalSuppliesCategory.MedicalSuppliesCategoryID) AS Distinct_MedicalSupplies_Types_Used
FROM MedicalProcedures_MedicalSupplies_Map
 , MedicalSupplies
 , MedicalSuppliesCategory
WHERE
       MedicalProcedures_MedicalSupplies_Map.MedicalSuppliesID = MedicalSupplies.MedicalSuppliesID
   AND MedicalSupplies.MedicalSuppliesCategoryID = MedicalSuppliesCategory.MedicalSuppliesCategoryID
GROUP BY
  MedicalProcedures_MedicalSupplies_Map.MedicalProceduresID
  fields:
  - dimension: operations_id
    type: number
    primary_key: true
    sql: ${TABLE}.MedicalProcedures_ID
  - dimension: quantity_used
    type: number
    sql: ${TABLE}.Quantity_Used
  - dimension: distinct_supply_ids_used
    type: number
    sql: ${TABLE}.Distinct_MedicalSupplies_IDs_Used
  - dimension: distinct_supply_types_used
    type: number
    sql: ${TABLE}.Distinct_MedicalSupplies_Types_Used
  - measure: count
    type: count
  - measure: average_quantity_used
    type: average
    sql: ${quantity_used}
  - measure: average_supply_ids_used
    type: average
    sql: ${distinct_supply_ids_used}
  - measure: average_distinct_supply_types_used
    type: average
    sql: ${distinct_supply_types_used}
  - measure: total_quantity_used
    type: sum
    sql: ${quantity_used}
  - measure: total_supply_ids_used
    type: sum
    sql: ${distinct_supply_ids_used}
  - measure: total_distinct_supply_types_used
    type: sum
    sql: ${distinct_supply_types_used}

Contents

  • operations_supply_facts.sql
  • operations_supply_facts.sql
  • operations_supply_facts.view.lkml
  • operations_supply_facts.view.lookml