Dynamic measures based on multiple parameters, MDN Included

[jghorta]

Hi, 

So, a bit of context first:

In Table A, I have data with a granularity of one row per observation, with known attributes and three predicted labels (classified, normalized and transformed). Table B is a derived table where I aggregate those observations as per a given category and calculate precision recall. 

And the question: 

Is there a way to use liquid variables to give the user the ability to select multiple parameters for a single calculation? In other words, I want to let the user select 1) what metric they want to see (precision or recall), 2) what type of aggregation they want (average, median, min, max, percentiles), and 3) for which stage of prediction (classification, normalization, transformation)? 

I’ve tried using nested CASE and IF statements with no avail… 

[Dawid · Accepted solution]

Ahh I didn’t know you would be using full statements in the SQL parameter.

Let’s see if we can figure it out. First of all the “value” of your parameter needs to match the SQL of your database/data warehouse.

Second of all, you could change sql_table_name value for the stage:

view: name {
  sql_table_name: {% parameter output_stage %} ;;
}

And then your measure simply becomes:

measure: dynamic_calculation {
  type: number
  sql: {% parameter measure_type %}({% parameter metric_to_calculate %}) ;;
}

What dialect do you use?

[Dawid]

From the top of my head I think this could be doable but can’t be 100% sure without seeing your code and what you tried to do. 

[jghorta]

Thanks @Dawid - Here are my parameters

 parameter: measure_type {
    type: unquoted
    default_value: "mdn"
    allowed_value: {
      value: "MIN"
      label: "Min"
    }
    allowed_value: {
      value: "mdn"
      label: "Median"
    }
    allowed_value: {
      value: "percentile_25"
      label: "25th Percentile"
    }
    allowed_value: {
      value: "percentile_75"
      label: "75th Percentile"
    }
    allowed_value: {
      value: "MAX"
      label: "Max"
    }
    allowed_value: {
      value: "AVG"
      label: "Average"
    }
  }

parameter: metric_to_calculate {
  type: string
  allowed_value: {value: "precision"}
  allowed_value: {value: "recall"}
}

parameter: output_stage {
  type: string
  allowed_value: {value: "classifier"}
  allowed_value: {value: "normalizer"}
  allowed_value: {value: "transformed"}
} 

for the measure, I tried a few things, the first one:

measure: dynamic_calculation {
  type: number
  sql: select {% parameter measure_type %}({% parameter metric_to_calculate %}) from {% parameter output_stage %} end ;;
}

but it doesn’t work since MEDIAN is not a built-in SQL function (and I’m not sure the “from” statement works either)… 

Would appreciate any help around this

[Dawid · Accepted solution]

Ahh I didn’t know you would be using full statements in the SQL parameter.

Let’s see if we can figure it out. First of all the “value” of your parameter needs to match the SQL of your database/data warehouse.

Second of all, you could change sql_table_name value for the stage:

view: name {
  sql_table_name: {% parameter output_stage %} ;;
}

And then your measure simply becomes:

measure: dynamic_calculation {
  type: number
  sql: {% parameter measure_type %}({% parameter metric_to_calculate %}) ;;
}

What dialect do you use?

[jghorta]

Thanks @Dawid, 

sorry if it’s a basic question, but I have an ETL that writes the output of the model (originally JSON) into a table directly in BQ (so BQ SQL)… for each observation, I have 3 different prediction values,  so I don’t quite get why/how do I need to change the name of the sql table as per the output_stage… should I create pivot tables (derived? aggregated?) for each stage? 

Thanks again, much appreciated! 

[Dawid]

Since I don’t see the full scope of your setup, I assumed you have multiple tables because you used “SELECT FROM {stage}” in your sql

[jghorta · Accepted solution]

Ah, gotcha, ok. 

So I ended up solving it by adding the stage to the ‘metric_to_calculate’ so: 

parameter: measure_type {
    description: "Select the type of measure you want to see"
    type: unquoted
    default_value: "mdn"
    allowed_value: {
      value: "MIN"
      label: "Min"
    }
    allowed_value: {
      value: "mdn"
      label: "Median"
    }
    allowed_value: {
      value: "percentile_25"
      label: "25th Percentile"
    }
    allowed_value: {
      value: "percentile_75"
      label: "75th Percentile"
    }
    allowed_value: {
      value: "MAX"
      label: "Max"
    }
    allowed_value: {
      value: "AVG"
      label: "Average"
    }
  }

 parameter: metric_to_calculate {
    description: "Select the measure you want to calculate, use together with measure type"
    type: unquoted
    allowed_value: {
      value: "precision_classifier"
      label: "Precision Stage 1 (classifier)"
    }
    allowed_value: {
      value: "recall_classifier"
      label: "Recall Stage 1 (classifier)"
    }
    allowed_value: {
      value: "f1_classifier"
      label: "F1 Stage 1 (classifier)"
    }
    allowed_value: {
      value: "MCC_classifier"
      label: "MCC Stage 1 (classifier)"
    }
    allowed_value: {
      value: "precision_normalizer"
      label: "Precision Stage 2 (before downgrades)"
    }
    allowed_value: {
      value: "recall_normalizer"
      label: "Recall Stage 2 (before downgrades)"
    }
    allowed_value: {
      value: "f1_normalizer"
      label: "F1 Stage 2 (before downgrades)"
    }
    allowed_value: {
      value: "MCC_normalizer"
      label: "MCC Stage 2 (before downgrades)"
    }
    allowed_value: {
      value: "precision_transformer"
      label: "Precision Stage 3 (final)"
    }
    allowed_value: {
      value: "recall_transformer"
      label: "Recall Stage 3 (final)"
    }
    allowed_value: {
      value: "f1_transformer"
      label: "F1 Stage 3 (final)"
    }
    allowed_value: {
      value: "MCC_transformer"
      label: "MCC Stage 3 (final)"
    }

and then for the measurements: 

measure: dynamic_mdn {
    type: median
    sql: {% parameter metric_to_calculate %} ;;
  }

  measure: dynamic_25th_percentile {
    type: percentile
    percentile: 25
    sql:  {% parameter metric_to_calculate %} ;;
  }

  measure: dynamic_75th_percentile {
    type: percentile
    percentile: 75
    sql:  {% parameter metric_to_calculate %} ;;
  }

  measure: dynamic_calculation {
    label_from_parameter: metric_to_calculate
    description: "Use this calculation with the selectors for measure type and metric to calculate"
    type: number
    sql: {% if measure_type._parameter_value == 'mdn'%} ${dynamic_mdn}
    {% elsif measure_type._parameter_value == 'percentile_25' %} ${dynamic_25th_percentile}
    {% elsif measure_type._parameter_value == 'percentile_75' %} ${dynamic_75th_percentile}
    {% else %} {% parameter measure_type %}({% parameter metric_to_calculate %})
    {% endif %}
    ;;
  }

Longer than I wanted, but it does the trick...

[jghorta]

Thanks for all your help!