data.ml
dbt Analytics marketing

Optimization Output Flow Comparison

Optimization Output Flow Comparison model for analytics marketing.

inferred

Blocks

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

WITH
left_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition left_optimization_run_id %} optimization_run_id {% endcondition %}
),
right_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition right_optimization_run_id %} optimization_run_id {% endcondition %}
)
SELECT
  left_flow.optimization_run_id AS left_optimization_run_id,
  right_flow.optimization_run_id AS right_optimization_run_id,
  COALESCE(left_flow.source_site_name, right_flow.source_site_name) AS source_site_name,
  COALESCE(left_flow.source_site_type, right_flow.source_site_type) AS source_site_type,
  COALESCE(left_flow.source_site_address, right_flow.source_site_address) AS source_site_address,
  COALESCE(left_flow.source_site_city, right_flow.source_site_city) AS source_site_city,
  COALESCE(left_flow.source_site_state, right_flow.source_site_state) AS source_site_state,
  COALESCE(left_flow.source_site_postal, right_flow.source_site_postal) AS source_site_postal,
  COALESCE(left_flow.source_site_country, right_flow.source_site_country) AS source_site_country,
  COALESCE(left_flow.source_site_latitude, right_flow.source_site_latitude) AS source_site_latitude,
  COALESCE(left_flow.source_site_longitude, right_flow.source_site_latitude) AS source_site_longitude,
  COALESCE(left_flow.destination_site_name, right_flow.destination_site_name) AS destination_site_name,
  COALESCE(left_flow.destination_site_type, right_flow.destination_site_type) AS destination_site_type,
  COALESCE(left_flow.destination_site_address, right_flow.destination_site_address) AS destination_site_address,
  COALESCE(left_flow.destination_site_city, right_flow.destination_site_city) AS destination_site_city,
  COALESCE(left_flow.destination_site_state, right_flow.destination_site_state) AS destination_site_state,
  COALESCE(left_flow.destination_site_postal, right_flow.destination_site_postal) AS destination_site_postal,
  COALESCE(left_flow.destination_site_country, right_flow.destination_site_country) AS destination_site_country,
  COALESCE(left_flow.destination_site_latitude, right_flow.destination_site_latitude) AS destination_site_latitude,
  COALESCE(left_flow.destination_site_longitude, right_flow.destination_site_longitude) AS destination_site_longitude,
  COALESCE(left_flow.product_name, right_flow.product_name) AS product_name,
  COALESCE(left_flow.product_description, right_flow.product_description) AS product_description,
  COALESCE(left_flow.product_weight, right_flow.product_weight) AS product_weight,
  COALESCE(left_flow.product_volume, right_flow.product_volume) AS product_volume,
  COALESCE(left_flow.product_shelf_life, right_flow.product_shelf_life) AS product_shelf_life,
  COALESCE(left_flow.product_price, right_flow.product_price) AS product_price,
  COALESCE(left_flow.product_value, right_flow.product_value) AS product_value,
  COALESCE(left_flow.transportation_mode_name, right_flow.transportation_mode_name) AS transportation_mode_name,
  COALESCE(left_flow.period_name, right_flow.period_name) AS period_name,
  COALESCE(left_flow.period_index, right_flow.period_index) AS period_index,
  COALESCE(left_flow.period_start, right_flow.period_start) AS period_start,
  COALESCE(left_flow.period_end, right_flow.period_end) AS period_end,
  COALESCE(left_flow.flow_amount, 0.0) AS left_flow_amount,
  COALESCE(left_flow.flow_cost, 0.0) AS left_flow_cost,
  COALESCE(left_flow.flow_distance, 0.0) AS left_flow_distance,
  COALESCE(right_flow.flow_amount, 0.0) AS right_flow_amount,
  COALESCE(right_flow.flow_cost, 0.0) AS right_flow_cost,
  COALESCE(right_flow.flow_distance, 0.0) AS right_flow_distance
FROM
left_flow
FULL OUTER JOIN right_flow
  ON left_flow.source_site_name = right_flow.source_site_name
  AND left_flow.destination_site_name = right_flow.destination_site_name
  AND left_flow.product_name = right_flow.product_name
  AND left_flow.transportation_mode_name = right_flow.transportation_mode_name
  AND left_flow.period_name = right_flow.period_name

# models/schema.yml
version: 2

models:
  - name: optimization_output_flow_comparison
    columns:
      - name: left_optimization_run
      - name: right_optimization_run
      - name: source_site_name
      - name: source_site_address
      - name: source_site_city
      - name: source_site_country
      - name: source_site_latitude
      - name: source_site_longitude
      - name: source_site_location
      - name: source_site_postal
      - name: source_site_state
      - name: source_site_type
      - name: destination_site_name
      - name: destination_site_address
      - name: destination_site_city
      - name: destination_site_country
      - name: destination_site_latitude
      - name: destination_site_longitude
      - name: destination_site_location
      - name: destination_site_postal
      - name: destination_site_state
      - name: destination_site_type
      - name: product_name
      - name: product_description
      - name: product_price
      - name: product_shelf_life
      - name: product_value
      - name: product_volume
      - name: product_weight
      - name: transportation_mode_name
      - name: period_name
      - name: left_flow_amount
      - name: left_flow_unit_cost
      - name: left_flow_distance
      - name: left_flow_cost
      - name: right_flow_amount
      - name: right_flow_unit_cost
      - name: right_flow_cost
      - name: right_flow_distance
      - name: flow_type

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.
optimization_output_flow_comparison.sql · 60 lines
source-preserved validation: not checked 
WITH
left_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition left_optimization_run_id %} optimization_run_id {% endcondition %}
),
right_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition right_optimization_run_id %} optimization_run_id {% endcondition %}
)
SELECT
  left_flow.optimization_run_id AS left_optimization_run_id,
  right_flow.optimization_run_id AS right_optimization_run_id,
  COALESCE(left_flow.source_site_name, right_flow.source_site_name) AS source_site_name,
  COALESCE(left_flow.source_site_type, right_flow.source_site_type) AS source_site_type,
  COALESCE(left_flow.source_site_address, right_flow.source_site_address) AS source_site_address,
  COALESCE(left_flow.source_site_city, right_flow.source_site_city) AS source_site_city,
  COALESCE(left_flow.source_site_state, right_flow.source_site_state) AS source_site_state,
  COALESCE(left_flow.source_site_postal, right_flow.source_site_postal) AS source_site_postal,
  COALESCE(left_flow.source_site_country, right_flow.source_site_country) AS source_site_country,
  COALESCE(left_flow.source_site_latitude, right_flow.source_site_latitude) AS source_site_latitude,
  COALESCE(left_flow.source_site_longitude, right_flow.source_site_latitude) AS source_site_longitude,
  COALESCE(left_flow.destination_site_name, right_flow.destination_site_name) AS destination_site_name,
  COALESCE(left_flow.destination_site_type, right_flow.destination_site_type) AS destination_site_type,
  COALESCE(left_flow.destination_site_address, right_flow.destination_site_address) AS destination_site_address,
  COALESCE(left_flow.destination_site_city, right_flow.destination_site_city) AS destination_site_city,
  COALESCE(left_flow.destination_site_state, right_flow.destination_site_state) AS destination_site_state,
  COALESCE(left_flow.destination_site_postal, right_flow.destination_site_postal) AS destination_site_postal,
  COALESCE(left_flow.destination_site_country, right_flow.destination_site_country) AS destination_site_country,
  COALESCE(left_flow.destination_site_latitude, right_flow.destination_site_latitude) AS destination_site_latitude,
  COALESCE(left_flow.destination_site_longitude, right_flow.destination_site_longitude) AS destination_site_longitude,
  COALESCE(left_flow.product_name, right_flow.product_name) AS product_name,
  COALESCE(left_flow.product_description, right_flow.product_description) AS product_description,
  COALESCE(left_flow.product_weight, right_flow.product_weight) AS product_weight,
  COALESCE(left_flow.product_volume, right_flow.product_volume) AS product_volume,
  COALESCE(left_flow.product_shelf_life, right_flow.product_shelf_life) AS product_shelf_life,
  COALESCE(left_flow.product_price, right_flow.product_price) AS product_price,
  COALESCE(left_flow.product_value, right_flow.product_value) AS product_value,
  COALESCE(left_flow.transportation_mode_name, right_flow.transportation_mode_name) AS transportation_mode_name,
  COALESCE(left_flow.period_name, right_flow.period_name) AS period_name,
  COALESCE(left_flow.period_index, right_flow.period_index) AS period_index,
  COALESCE(left_flow.period_start, right_flow.period_start) AS period_start,
  COALESCE(left_flow.period_end, right_flow.period_end) AS period_end,
  COALESCE(left_flow.flow_amount, 0.0) AS left_flow_amount,
  COALESCE(left_flow.flow_cost, 0.0) AS left_flow_cost,
  COALESCE(left_flow.flow_distance, 0.0) AS left_flow_distance,
  COALESCE(right_flow.flow_amount, 0.0) AS right_flow_amount,
  COALESCE(right_flow.flow_cost, 0.0) AS right_flow_cost,
  COALESCE(right_flow.flow_distance, 0.0) AS right_flow_distance
FROM
left_flow
FULL OUTER JOIN right_flow
  ON left_flow.source_site_name = right_flow.source_site_name
  AND left_flow.destination_site_name = right_flow.destination_site_name
  AND left_flow.product_name = right_flow.product_name
  AND left_flow.transportation_mode_name = right_flow.transportation_mode_name
  AND left_flow.period_name = right_flow.period_name
optimization_output_flow_comparison.view.lkml · 282 lines
inferred 
view: optimization_output_flow_comparison {
  derived_table: {
    sql: WITH
left_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition left_optimization_run_id %} optimization_run_id {% endcondition %}
),
right_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition right_optimization_run_id %} optimization_run_id {% endcondition %}
)
SELECT
  left_flow.optimization_run_id AS left_optimization_run_id,
  right_flow.optimization_run_id AS right_optimization_run_id,
  COALESCE(left_flow.source_site_name, right_flow.source_site_name) AS source_site_name,
  COALESCE(left_flow.source_site_type, right_flow.source_site_type) AS source_site_type,
  COALESCE(left_flow.source_site_address, right_flow.source_site_address) AS source_site_address,
  COALESCE(left_flow.source_site_city, right_flow.source_site_city) AS source_site_city,
  COALESCE(left_flow.source_site_state, right_flow.source_site_state) AS source_site_state,
  COALESCE(left_flow.source_site_postal, right_flow.source_site_postal) AS source_site_postal,
  COALESCE(left_flow.source_site_country, right_flow.source_site_country) AS source_site_country,
  COALESCE(left_flow.source_site_latitude, right_flow.source_site_latitude) AS source_site_latitude,
  COALESCE(left_flow.source_site_longitude, right_flow.source_site_latitude) AS source_site_longitude,
  COALESCE(left_flow.destination_site_name, right_flow.destination_site_name) AS destination_site_name,
  COALESCE(left_flow.destination_site_type, right_flow.destination_site_type) AS destination_site_type,
  COALESCE(left_flow.destination_site_address, right_flow.destination_site_address) AS destination_site_address,
  COALESCE(left_flow.destination_site_city, right_flow.destination_site_city) AS destination_site_city,
  COALESCE(left_flow.destination_site_state, right_flow.destination_site_state) AS destination_site_state,
  COALESCE(left_flow.destination_site_postal, right_flow.destination_site_postal) AS destination_site_postal,
  COALESCE(left_flow.destination_site_country, right_flow.destination_site_country) AS destination_site_country,
  COALESCE(left_flow.destination_site_latitude, right_flow.destination_site_latitude) AS destination_site_latitude,
  COALESCE(left_flow.destination_site_longitude, right_flow.destination_site_longitude) AS destination_site_longitude,
  COALESCE(left_flow.product_name, right_flow.product_name) AS product_name,
  COALESCE(left_flow.product_description, right_flow.product_description) AS product_description,
  COALESCE(left_flow.product_weight, right_flow.product_weight) AS product_weight,
  COALESCE(left_flow.product_volume, right_flow.product_volume) AS product_volume,
  COALESCE(left_flow.product_shelf_life, right_flow.product_shelf_life) AS product_shelf_life,
  COALESCE(left_flow.product_price, right_flow.product_price) AS product_price,
  COALESCE(left_flow.product_value, right_flow.product_value) AS product_value,
  COALESCE(left_flow.transportation_mode_name, right_flow.transportation_mode_name) AS transportation_mode_name,
  COALESCE(left_flow.period_name, right_flow.period_name) AS period_name,
  COALESCE(left_flow.period_index, right_flow.period_index) AS period_index,
  COALESCE(left_flow.period_start, right_flow.period_start) AS period_start,
  COALESCE(left_flow.period_end, right_flow.period_end) AS period_end,
  COALESCE(left_flow.flow_amount, 0.0) AS left_flow_amount,
  COALESCE(left_flow.flow_cost, 0.0) AS left_flow_cost,
  COALESCE(left_flow.flow_distance, 0.0) AS left_flow_distance,
  COALESCE(right_flow.flow_amount, 0.0) AS right_flow_amount,
  COALESCE(right_flow.flow_cost, 0.0) AS right_flow_cost,
  COALESCE(right_flow.flow_distance, 0.0) AS right_flow_distance
FROM
left_flow
FULL OUTER JOIN right_flow
  ON left_flow.source_site_name = right_flow.source_site_name
  AND left_flow.destination_site_name = right_flow.destination_site_name
  AND left_flow.product_name = right_flow.product_name
  AND left_flow.transportation_mode_name = right_flow.transportation_mode_name
  AND left_flow.period_name = right_flow.period_name ;;
  }

  dimension: left_optimization_run {
    sql: ${TABLE}.left_optimization_run_id ;;
  }

  dimension: right_optimization_run {
    sql: ${TABLE}.right_optimization_run_id ;;
  }

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

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

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

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

  dimension: source_site_latitude {
    type: number
    hidden: yes
    sql: ${TABLE}.source_site_latitude ;;
  }

  dimension: source_site_longitude {
    type: number
    hidden: yes
    sql: ${TABLE}.source_site_longitude ;;
  }

  dimension: source_site_location {
    type: location
  }

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

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

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

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

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

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

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

  dimension: destination_site_latitude {
    type: number
    hidden: yes
    sql: ${TABLE}.destination_site_latitude ;;
  }

  dimension: destination_site_longitude {
    type: number
    hidden: yes
    sql: ${TABLE}.destination_site_longitude ;;
  }

  dimension: destination_site_location {
    type: location
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: left_flow_unit_cost {
    type: number
    sql: ${TABLE}.left_flow_cost ;;
  }

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

  dimension: left_flow_cost {
    type: number
    sql: ${left_flow_unit_cost} * ${left_flow_amount} ;;
  }

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

  dimension: right_flow_unit_cost {
    type: number
    sql: ${TABLE}.right_flow_cost ;;
  }

  dimension: right_flow_cost {
    type: number
    sql: ${right_flow_unit_cost} * ${right_flow_amount} ;;
  }

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

  dimension: flow_type {
    type: string
    sql: CASE
  WHEN ${source_site_type} = 'facility' AND ${destination_site_type} = 'facility' THEN 'interfacility'
  WHEN ${source_site_type} = 'facility' AND ${destination_site_type} = 'customer' THEN 'customer'
  WHEN ${source_site_type} = 'customer' AND ${destination_site_type} = 'facility' THEN 'return'
  ELSE 'unknown'
END ;;
  }

  measure: left_flow_amount_total {
    type: sum
    sql: ${left_flow_amount} ;;
  }

  measure: left_flow_cost_total {
    type: sum
    sql: ${left_flow_cost} ;;
  }

  measure: right_flow_amount_total {
    type: sum
    sql: ${right_flow_amount} ;;
  }

  measure: right_flow_cost_total {
    type: sum
    sql: ${right_flow_cost} ;;
  }

  measure: flow_amount_difference {
    type: number
    sql: ${right_flow_amount_total} - ${left_flow_amount_total} ;;
  }

  measure: flow_cost_difference {
    type: number
    drill_fields: [source_site_name, destination_site_name, product_name, transportation_mode_name, period_name, flow_cost_difference, left_flow_cost_total, right_flow_cost_total]
    sql: ${right_flow_cost_total} - ${left_flow_cost_total} ;;
  }
}
optimization_output_flow_comparison.view.lookml · 197 lines
source-preserved 
- view: optimization_output_flow_comparison
  derived_table:
    sql: WITH
left_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition left_optimization_run_id %} optimization_run_id {% endcondition %}
),
right_flow AS (
  SELECT *
  FROM optimization_output_flow_view
  WHERE
    {% condition right_optimization_run_id %} optimization_run_id {% endcondition %}
)
SELECT
  left_flow.optimization_run_id AS left_optimization_run_id,
  right_flow.optimization_run_id AS right_optimization_run_id,
  COALESCE(left_flow.source_site_name, right_flow.source_site_name) AS source_site_name,
  COALESCE(left_flow.source_site_type, right_flow.source_site_type) AS source_site_type,
  COALESCE(left_flow.source_site_address, right_flow.source_site_address) AS source_site_address,
  COALESCE(left_flow.source_site_city, right_flow.source_site_city) AS source_site_city,
  COALESCE(left_flow.source_site_state, right_flow.source_site_state) AS source_site_state,
  COALESCE(left_flow.source_site_postal, right_flow.source_site_postal) AS source_site_postal,
  COALESCE(left_flow.source_site_country, right_flow.source_site_country) AS source_site_country,
  COALESCE(left_flow.source_site_latitude, right_flow.source_site_latitude) AS source_site_latitude,
  COALESCE(left_flow.source_site_longitude, right_flow.source_site_latitude) AS source_site_longitude,
  COALESCE(left_flow.destination_site_name, right_flow.destination_site_name) AS destination_site_name,
  COALESCE(left_flow.destination_site_type, right_flow.destination_site_type) AS destination_site_type,
  COALESCE(left_flow.destination_site_address, right_flow.destination_site_address) AS destination_site_address,
  COALESCE(left_flow.destination_site_city, right_flow.destination_site_city) AS destination_site_city,
  COALESCE(left_flow.destination_site_state, right_flow.destination_site_state) AS destination_site_state,
  COALESCE(left_flow.destination_site_postal, right_flow.destination_site_postal) AS destination_site_postal,
  COALESCE(left_flow.destination_site_country, right_flow.destination_site_country) AS destination_site_country,
  COALESCE(left_flow.destination_site_latitude, right_flow.destination_site_latitude) AS destination_site_latitude,
  COALESCE(left_flow.destination_site_longitude, right_flow.destination_site_longitude) AS destination_site_longitude,
  COALESCE(left_flow.product_name, right_flow.product_name) AS product_name,
  COALESCE(left_flow.product_description, right_flow.product_description) AS product_description,
  COALESCE(left_flow.product_weight, right_flow.product_weight) AS product_weight,
  COALESCE(left_flow.product_volume, right_flow.product_volume) AS product_volume,
  COALESCE(left_flow.product_shelf_life, right_flow.product_shelf_life) AS product_shelf_life,
  COALESCE(left_flow.product_price, right_flow.product_price) AS product_price,
  COALESCE(left_flow.product_value, right_flow.product_value) AS product_value,
  COALESCE(left_flow.transportation_mode_name, right_flow.transportation_mode_name) AS transportation_mode_name,
  COALESCE(left_flow.period_name, right_flow.period_name) AS period_name,
  COALESCE(left_flow.period_index, right_flow.period_index) AS period_index,
  COALESCE(left_flow.period_start, right_flow.period_start) AS period_start,
  COALESCE(left_flow.period_end, right_flow.period_end) AS period_end,
  COALESCE(left_flow.flow_amount, 0.0) AS left_flow_amount,
  COALESCE(left_flow.flow_cost, 0.0) AS left_flow_cost,
  COALESCE(left_flow.flow_distance, 0.0) AS left_flow_distance,
  COALESCE(right_flow.flow_amount, 0.0) AS right_flow_amount,
  COALESCE(right_flow.flow_cost, 0.0) AS right_flow_cost,
  COALESCE(right_flow.flow_distance, 0.0) AS right_flow_distance
FROM
left_flow
FULL OUTER JOIN right_flow
  ON left_flow.source_site_name = right_flow.source_site_name
  AND left_flow.destination_site_name = right_flow.destination_site_name
  AND left_flow.product_name = right_flow.product_name
  AND left_flow.transportation_mode_name = right_flow.transportation_mode_name
  AND left_flow.period_name = right_flow.period_name
  fields:
  - dimension: left_optimization_run
    sql: ${TABLE}.left_optimization_run_id
  - dimension: right_optimization_run
    sql: ${TABLE}.right_optimization_run_id
  - dimension: source_site_name
    sql: ${TABLE}.source_site_name
  - dimension: source_site_address
    sql: ${TABLE}.source_site_address
  - dimension: source_site_city
    sql: ${TABLE}.source_site_city
  - dimension: source_site_country
    sql: ${TABLE}.source_site_country
  - dimension: source_site_latitude
    type: number
    hidden: true
    sql: ${TABLE}.source_site_latitude
  - dimension: source_site_longitude
    type: number
    hidden: true
    sql: ${TABLE}.source_site_longitude
  - dimension: source_site_location
    type: location
  - dimension: source_site_postal
    sql: ${TABLE}.source_site_postal
  - dimension: source_site_state
    sql: ${TABLE}.source_site_state
  - dimension: source_site_type
    sql: ${TABLE}.source_site_type
  - dimension: destination_site_name
    sql: ${TABLE}.destination_site_name
  - dimension: destination_site_address
    sql: ${TABLE}.destination_site_address
  - dimension: destination_site_city
    sql: ${TABLE}.destination_site_city
  - dimension: destination_site_country
    sql: ${TABLE}.destination_site_country
  - dimension: destination_site_latitude
    type: number
    hidden: true
    sql: ${TABLE}.destination_site_latitude
  - dimension: destination_site_longitude
    type: number
    hidden: true
    sql: ${TABLE}.destination_site_longitude
  - dimension: destination_site_location
    type: location
  - dimension: destination_site_postal
    sql: ${TABLE}.destination_site_postal
  - dimension: destination_site_state
    sql: ${TABLE}.destination_site_state
  - dimension: destination_site_type
    sql: ${TABLE}.destination_site_type
  - dimension: product_name
    sql: ${TABLE}.product_name
  - dimension: product_description
    sql: ${TABLE}.product_description
  - dimension: product_price
    type: number
    sql: ${TABLE}.product_price
  - dimension: product_shelf_life
    type: number
    sql: ${TABLE}.product_shelf_life
  - dimension: product_value
    type: number
    sql: ${TABLE}.product_value
  - dimension: product_volume
    type: number
    sql: ${TABLE}.product_volume
  - dimension: product_weight
    type: number
    sql: ${TABLE}.product_weight
  - dimension: transportation_mode_name
    sql: ${TABLE}.transportation_mode_name
  - dimension: period_name
    sql: ${TABLE}.period_name
  - dimension: left_flow_amount
    type: number
    sql: ${TABLE}.left_flow_amount
  - dimension: left_flow_unit_cost
    type: number
    sql: ${TABLE}.left_flow_cost
  - dimension: left_flow_distance
    type: number
    sql: ${TABLE}.left_flow_distance
  - dimension: left_flow_cost
    type: number
    sql: ${left_flow_unit_cost} * ${left_flow_amount}
  - dimension: right_flow_amount
    type: number
    sql: ${TABLE}.right_flow_amount
  - dimension: right_flow_unit_cost
    type: number
    sql: ${TABLE}.right_flow_cost
  - dimension: right_flow_cost
    type: number
    sql: ${right_flow_unit_cost} * ${right_flow_amount}
  - dimension: right_flow_distance
    type: number
    sql: ${TABLE}.right_flow_distance
  - dimension: flow_type
    type: string
    sql: CASE
  WHEN ${source_site_type} = 'facility' AND ${destination_site_type} = 'facility' THEN 'interfacility'
  WHEN ${source_site_type} = 'facility' AND ${destination_site_type} = 'customer' THEN 'customer'
  WHEN ${source_site_type} = 'customer' AND ${destination_site_type} = 'facility' THEN 'return'
  ELSE 'unknown'
END
  - measure: left_flow_amount_total
    type: sum
    sql: ${left_flow_amount}
  - measure: left_flow_cost_total
    type: sum
    sql: ${left_flow_cost}
  - measure: right_flow_amount_total
    type: sum
    sql: ${right_flow_amount}
  - measure: right_flow_cost_total
    type: sum
    sql: ${right_flow_cost}
  - measure: flow_amount_difference
    type: number
    sql: ${right_flow_amount_total} - ${left_flow_amount_total}
  - measure: flow_cost_difference
    type: number
    drill_fields:
    - source_site_name
    - destination_site_name
    - product_name
    - transportation_mode_name
    - period_name
    - flow_cost_difference
    - left_flow_cost_total
    - right_flow_cost_total
    sql: ${right_flow_cost_total} - ${left_flow_cost_total}

Contents

  • optimization_output_flow_comparison.sql
  • optimization_output_flow_comparison.sql
  • optimization_output_flow_comparison.view.lkml
  • optimization_output_flow_comparison.view.lookml