RFC: Parameterized HJSON Metric Changes

[aklish]

Overview

Parameterized metrics (and later dimensions and tables) will require a number of changes to HJSON, exported metadata, and the behavior of the API.

API Changes

The RSQL filter dialect will support parameters in the filter predicate's left hand side. RSQL allows square braces ('[' and ']') along with semicolons in the left hand side allowing the following new syntax:

columnName[argName1:argValue1][arg2Name:argValue2]

For example, the following paramterized filters will be valid:

1. filter=title[format=UPPERCASE]='THE GRAPES OF WRATH'
2. filter=score[agg:SUM][precision:2]>123.24

HJSON Changes

Example Configuration

The following example configuration file includes:

1. A metric (highScore) whose definition is parameterized by an argument. The parameter controls the aggregation function applied to the metric (sum, min, or max).
2. A metric (totalCost) whose definition requires a table join that is parameterized by an argument. The parameter controls the currency used to calculate the total cost.
3. A metric (averageScore) whose definition invokes highScore with a fixed parameter assignment.
4. A new array of arguments for measures.
5. Two different metadata mechanisms for enabling type-ahead search (values and tableSource). These are identical to the mechanisms used for dimension values today.
6. A mechanism to provide a default value for an argument. If no default is provided, the argument is required whenever the metric is queried.
7. New syntax to denote a physical vs logical column in Handlebars.

tables: [{
  name: testTable
  table: test_table
  joins: [
    { 
      name: rates
      to: currencyRates
      kind: toOne
      type: left

      ##
      ## This join is parameterized on a particular column's arguments.
      ##
      definition: '{{rates.$id}} = {{$$column.args.currency}} AND {{rates.$date}} = {{date}}'
    }
  ]
  measures : [
    { 
      name : highScore
      type : INTEGER
      definition: '{{$$column.args.aggregation}}({{$highScore}})'
      arguments: [
        { 
          name : aggregation
          type : TEXT
          values: [SUM, MIN, MAX]
          default: 'SUM'
        }
      ]
    } 
    { 
      ##
      ## Example metric which invokes another parameterized metric.
      ##
      name : averageScore
      type : INTEGER  
      definition: "{{sql table='testTable' column='highScore[aggregation:SUM]'}} / {{$num_participants}}"
    } 
    { 
      name : totalCost
      type : MONEY
      definition: 'SUM({{$cost}}) * {{rates.currentRate}}'
      arguments: [
        { 
          name : currency
          type : TEXT
          tableSource: rates.id
          ##
          ##If there is no default value, the argument is required.
          ##This argument is referenced in the join on clause so no default should exist here
          ##
        }
        { 
          name : format
          type : TEXT
          default: '$0.00'
          ##
          ## No tableSource or values indicates context aware filtering is not supported.
          ##
        }
      ]
    }
  ]
  dimensions : [
    { 
      name : date
      type : TIME
      definition: '{{$date}}'
    }
  ]
}

Templating

Column & Join Definition Templates

The handlebars context will be initialized with the logical table that is being queried along with contextual object that store additional information. The handlebars context will have the following fields:

1. {{$columnName}} - Expands The physical database column name for the current table.
2. {{columnName}} - Expands the logical column name for the current table.
3. {{joinName.column}} - Expands the logical column name for the joined table.
4. {{joinName.$column}} - Expands the physical database column name for the joined table.
5. {{$$user}} - Returns the user context (see below).
6. {{$$request}} - Returns the request context (see below).
7. {{$$tables}} - Returns the table dictionary context (see below).
8. {{$$request.columns.columnName.args.argumentName}} - References an argument in the current request context.
9. {{$$column.args.argumentName}} - References an argument in the current column context.

Contextual Objects

User Object

Represents the client principal.

$$user
 |__identity
 |__groups

Request Object

Represents the client request.

$$request
 |__table
 | |__name
 | |__args
 |   |__arg1Name
 |   |__arg2Name
 |__columns
   |__column1Name
     |__args
       |__arg1Name
       |__arg2Name
   |__column2Name
     |__args
       |__arg1Name
       |__arg2Name

Tables Object

Represents the static list of available tables in the semantic layer.

$$tables
 |__table1Name
   |__measures
     |__measure1Name
     |__measure2Name
   |__dimensions
     |__dimension1Name
     |__dimension2Name
   |__timeDimensions
     |__timedim1Name
       |__grains
     |__timedim2Name
       |__grains
 |__table2Name
   |__measures
     |__measure1Name
     |__measure2Name
   |__dimensions
     |__dimension1Name
     |__dimension2Name
   |__timeDimensions
     |__timedim1Name
       |__grains
     |__timedim2Name
       |__grains

Column Object

Represents the current column that is being expanded.

 |__name
 |__args
    |__arg1Name
    |__arg2Name

Invoking a Parameterized Column

The 'sql' handlerbars helper can expand any logical column, parameterized logical column, or physical column to its corresponding SQL:

1. {{sql table='tableName' column='columnName[arg1:value1][arg2:value2]'}} - Handlebar helper that expands a logical column with explicit arguments to SQL.
2. {{sql table='tableName' column='columnName'}} - Handlebar helper that expands a logical column to SQL.
3. {{sql table='tableName' column='$columnName'}} - Handlebar helper that expands a physical database column to SQL.

Time Grain Templates

Instead of leveraging the expression '{{}}', time grain templates will be expanded with handlebars using the same scheme described above.

Parameterized Columns that reference other Parameterized Metrics

When defining a parameterized column that references another parameterized column, all parameters required to expand the column definition must be defined in the HJSON (there are no implicit or inherited parameters). This has a number of advantages with respect to parameter overrides:

1. The ability to override the default value of a dependent column parameter with a new default value.
2. The ability to remove a parameter from a dependent column by pinning it to its default value.
3. The ability to remove a parameter from a dependent column by pinning it to a fixed, non-default value (new handlebar syntax required).

Overriding the type of a parameter is forbidden and will result in an error during configuration processing.

Reserved parameters

Some parameter names ('grain' for time dimensions for example) are reserved by Elide. Elide should prevent these from being defined in the HJSON files.

API Restrictions

Elide will throw an HTTP client error in the following situations:

1. Selecting a metric with required arguments, but not providing one or more of the required arguments.
2. Filtering a metric with required arguments, but not providing one or more of the required arguments.
3. Filtering a metric that is not also projected in the query response.

Metadata Model Changes

Existing FunctionArgument Model

public class FunctionArgument {
    @Id
    private String id;

    private String name;

    private String description;

    private ValueType type;

    private FunctionArgumentType functionArgumentType;

    private String expression;
}

Recommended FunctionArgument Model

public class FunctionArgument {
    @Id
    private String id;

    private String name;

    private String description;

    private ValueType type;

    private ValueSourceType valueSourceType;

    private final Set<String> values;

    private final String tableSource;

    private Object defaultValue;
}

Repurpose MetricFunction

Change MetricFunction to Function and allow both metrics, dimensions, and tables to have them.

[moizarafat]

Unify time grain templates to match column and join definition templates leveraging the same context object.

Will there be any other advantages of this in terms of usability, functionality?

Filtering on a column with required arguments but that is not also present in the projection

In SQL, Is this similar to a where clause using a column that is not in the select?

[aklish]

Unify time grain templates to match column and join definition templates leveraging the same context object.

Will there be any other advantages of this in terms of usability, functionality?

Possibly. We may eventually want to leverage handlebars to expand other items inside the grain. If we keep '{{}}', it may break handlebars.

Filtering on a column with required arguments but that is not also present in the projection

In SQL, Is this similar to a where clause using a column that is not in the select?

Yes, except we will still allow filters where the projection is missing - but only for:

1. Non-parameterized metrics.
2. Parameterized metrics where all arguments have default values.

[moizarafat]

If there is another column avgHighScore that depends on highScore Will we have to redefine the arguments or the same ones will be available by default from highScore?

Will avgHighScore be able to introduce more arguments?

[aklish]

Good call. I recommend redefinition as it has a number of benefits with respect to overriding a metric argument:

• The ability to override the default value of a dependent metric argument with a new default value.
• The ability to remove an argument from a dependent metric by pinning it to its default value.
• The ability to remove an argument from a dependent metric by pinning it to a fixed, non-default value (new handlebar syntax required).

For the last bullet, I recommend something like {{invoke column=columnName arg1Name=foo arg2Name=bar}}.

The only downside I see is the potential to override the argument type (which we should not allow).

[jkusa]

@aklish a few comments:

• Can you provide an example when the {{sql}} helper might be helpful
• Can you provide an example of a metric with an open-ended (within a type) param value
• Do we want to reserve any parameter keys?