Web API: Expression/query

The Expression query runs a query written using the expression language in the server and returns the query results.

Request Format

Request body is a JSON object supporting the following properties:

• ModelId: Model for which the calculation is run. This parameter is mandatory, if the calculation is run in an eventlog context.
• Filter: Filter definition as JSON. If Filter is defined, please do not use the FilterId parameter.
• FilterId: Filter for which the calculation run. If FilterId is not specified, calculation is run from the entire model. If FilterId is defined, please do not use the Filter parameter.
• ContextType: When eventlog, the root expression is run in an EventLog context, i.e.g EventLog's functions and properties can be used. When generic, the root expression is run in the generic context. If not specified, eventlog is used if FilterId or ModelId parameter is specified.
• RuntimeComparison: When set to true, the expression query works in the comparison mode (i.e. root causes mode) and the ComparisonEventLog property is available in the expressions. You also need to pass the Comparison parameter which the comparison between case sets are based on. The default value is false.
• Comparison: Comparison definition as JSON.
• Variable_<name>: Parameters starting with Variable_ define variables that are available as variables in the KPI expressions with name <name>. For example, when adding Variable_Region=Dallas to the query, there is a variable Region containing value Dallas that can be used in the expressions.
• EnableResultCaching: When set to true, expression query results are cached both in the client and server side. When false, query results are not cached. When ContextType=generic, no caching is used regardless of this setting.
• QueryIdentifier: Identifier of the query. Can be used to refer to the query, when cancelling pending queries.
• CancelEarlierQueriesWithIdentifier: Boolean value defining whether possible previous query with the same QueryIdentifier is cancelled when a new query with the same identifier is received by the server.
• Root: Expression returning objects that are used in the next calculation step, which is the dimensioning. The root expression is run either in the EventLog context or in the generic context, depending on the ContextType parameter.

Example query:

Url: POST /api/expression/query?ResponseType=object&StringifyValues=false
Content-Type: application/json;charset=UTF-8
Body:
{
  "ModelId": 1234,
  "Filter": {
    "Items": [
      {
        "Type": "IncludeCases",
        "Items": [
          {
            "Type": "EventType",
            "Values": [
              "Change Price"
            ]
          }
        ]
      }
    ]
  },
  "Configuration": {
    "Root": "Cases",
    "Dimensions": [
      {
        "Name": "Start Month",
        "Expression": "StartTime.Month"
      }
    ],
    "Values": [
      {
        "Name": "Average Case Duration in Days",
        "Expression": "Average(_.Duration).TotalDays"
      }
    ],
    "Ordering": [
      {
        "Name": "Start Month",
        "Direction": "Ascending"
      }
    ]
  },
  "QueryIdentifier": "myQuery1",
  "CancelEarlierQueriesWithIdentifier": true
}

HTTP request header Authorization with value Bearer <access token> needs to be in place to identify the session.

Response Format

The response is data in a tabular format (one-to-many named columns and zero-to-many rows). The following url parameters are available to define the results format:

• ResponseType: array (default) or object, determined by the parameter.
• StringifyValues: (boolean) (default false) determines that the cell values are stringified. When no stringification is used, values use normal JSON formatting for strings, numbers, booleans and nulls. Dates use format yyyy-MM-ddTHH:mm:ss.fff. Other data types use default stringification. More information about stringified values.

Expression Query

Expression allows configurable objects, such as cases, events, event types or flows, to be divided into configurable dimensions and calculable values (KPIs). In a basic form, the result of this query is a table with the following columns: (1) One column for each specified dimension, and (2) at least one column for each specified value. The result of this query is a table with one row for each unique dimension value combination.

When building expresssion query, it's essential to note in which contexts the individual expressions are run:

• Root expression is run either in the EventLog or generic context, depending on the ContextType setting.
• Dimension expressions are run in the context of the individual object returned by the root expression.
• Value (measure) expressions is run in the context of the array of items that are part of the dimensioned slice. If dimensioning is disabled (i.e. dimensions parameter is null), the value expression is run in the context of the individual object returned by the root expression.
• Row initialization expression is run in the same context as the measures. RowInitExpression is run before the measures, so variables initialized in the RowInitExpression are available in the measures.
• Aggregation expression is run in the context of an array of the rest of the items, i.e. items to be aggregated as the last row.

Dimensions

Array of Dimension objects. The rows are sliced into different groups based on how many unique values the dimension expressions produce. There can be zero to many dimensions. The result data contains a column for each dimension.

If dimension array is empty (i.e. Dimensions: []), all root objects are aggregated into a single row. If dimensions array is not defined at all (i.e. Dimensions: null), each object in the root expression will be as a separate row in the result. Then context for the value expression is (instead of array of objects) a single object.

Each dimension may have the following properties:

Property	Description
Name	Name of the dimension. Dimension name works as the title for the dimension column. In addition, the dimension name is used when referring to this dimension in the ordering.
Expression	Expression to calculate the dimension value for each root object, i.e. each of the root objects are used as the context for the dimension expression.
ValueExpression	Specifies an expression that is used as the final returned value of the dimension. The ValueExpression is calculated in the context of an unordered array containing of all the objects in the dimension represented by the row (i.e. the context is different than in the Expression). The ValueExpression can be used to improve performance as follows: the Expression is used to provide a simplified expression that's enough for the dimensioning, and define a more complex expression in the ValueExpression to provide the final returned value. The benefit is that the ValueExpression is not calculated for rows that are left out by the MaximumRowCount setting.
NumberPrecision	Number of decimals for rounding numerical dimension values. If not defined, no rounding is done. Rounding is done before dividing values into distinct dimension slots. If any value of this dimension is other than numerical, error is given.
DatetimeTruncation	Dimension values that are of the datetime type, are truncated (i.e. rounded downwards) using the defined granularity. Available values are year, quarter, month, week, day, hour, minute and second. Truncation is done before dividing values into distinct dimension slots. If any value of this dimension is other than datetime, error is given.
TimespanPrecision	Dimension values that are of the timespan type, are rounded using the defined granularity. Available values are year, quarter, month, week, day, hour, minute and second. Rounding is done before dividing values into distinct dimension slots. If any value of this dimension is other than timespan, error is given.
IsHidden	Is the dimension hidden (true or false). Hiding dimensions don't affect the calculation, but the hidden columns are not returned to the client.

Values

Values are the actual calculated values (i.e. measures or KPI's). Value expressions are calculated for each sliced data group, i.e. for each unique dimension value. The context of the value expression is an array of objects.

Property	Description
Name	Name for the value. Used only when ValueType is Single.
Expression	Behavior depends on the ValueType property: Single: Expression for calculating the value for the single column. The expression is calculated for each row (i.e. the unique dimension combinations), and the expression usually contains aggregations from the group of objects into a single value. Dynamic: Expression for calculating the value for each dynamically generated column specified by ValueDimensionExpression property. The result of the ValueDimensionExpression is accessible via ValueDimension -variable. Pivot: Expression to produce an array, where each item represents a dynamically generated column. The result shown by the Expression query is number of items belonging to each column.
AggregationExpression	Expression to calculate the aggregated value for the rest of the rows for this column when using the AggregateOthers setting. All the aggregated values are provided as the context (_) for the aggregation expression. If no aggregation expression is defined, null value is used. Example aggregation expressions: Row count: "AggregationExpression": "Count(_)" Sum of values (for numerical columns): "AggregationExpression": "Sum(_)" Showing count, minimum and maximum values: "AggregationExpression": "\"(\" + Count(_) + \" others) \" + Min(_) + \" - \" + Max(_)"
ValueType	Type of the value, which is one of the following: Single (default): Produces a single column calculated by the expression. Dynamic: There is an expression for defining the actual produced columns dynamically (ValueDimensionExpression) and another expression (Expression property) for specifying the actual values for the columns. Pivot: Produces as many columns as there are unique values in the expression. Columns are ordered according to the order defined by DimensionOrderExpression.
ValueDimensionExpression (ValueType: Dynamic, Pivot)	Expression used to generate columns in Dynamic and Pivot value types. The expression should return an array where each item represents a column. This expression is executed once when calculating the Expression query. For Dynamic type value ValueDimensionExpression field is mandatory, and for Pivot type it's not mandatory. The result of this expression will be given as the value of variable ValueDimension when evaluating the Expression. For Pivot type value, this can be used to override the default set of columns created automatically based on the actual cell evaluation results. An array of all the actual dimension values created by the actual cell evaluation result is given as context object for the evaluation.
NameExpression (ValueType: Dynamic, Pivot)	Expression used to generate column names in Dynamic and Pivot value types. This expression is run once for each column, and the expression has as a context the column generated in the ValueDimensionExpression, and as a result the expression should give the column name.
DimensionOrderExpression (ValueType: Pivot)	Expression used to order the dimensions when pivot value type value is used. If not defined, the following default expression will be used: OrderByValue(_).
PivotAggregationExpression (ValueType: Pivot)	Expression used to aggregate all the values within one cell of pivot type values. If the expression returns an array of arrays, the first item in the inner array is used as the ValueDimension and the second item is used as the root object when evaluating PivotAggregationExpression. By default, if expression returns only an array of atomic objects, the root object of the evaluation of PivotAggregationExpression is the value of the current ValueDimension.
DefaultValueExpression (ValueType: Pivot)	Expression whose result is used to fill the "gaps" of the matrix created when pivot value type is used.

JSON structure example:

{
  "Root": "Root expression",
  "Dimensions": [
    {
      "Name": "Dimension 1 name",
      "Expression": "Dimension 1 expression"
    },
    {
      "Name": "Dimension 2 name",
      "Expression": "Dimension 2 expression"
    },
    ...
  ],
  "Values": [
    {
      "Name": "KPI 1 name",
      "Expression": "KPI 1 expression"
    },
    {
      "Name": "KPI 2 name",
      "Expression": "KPI 2 expression"
    },
    ...
  ],
  "Ordering": [ 
    {
      "Name": "Ordered column 1 name",
      "Direction": "Ascending/Descending"
    },
    {
      "Name": "Ordered column 2 name",
      "Direction": "Ascending/Descending"
    },
    ...
  ]
}