Filtering in QPR ProcessAnalyzer Queries

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

QPR ProcessAnalyzer queries for models data can be filtered to include only selected cases or events to the calculation. The expression query have the parameter Filter to pass a filter definition. The Root cause analysis has also the parameter Comparison which divides the analyzed data into two groups to compare them in the analysis. The Comparison parameter has the same structure as in the Filter parameter.

If a filter (or comparison) refers to information that doesn't exist in the model, e.g. non-existing event type names or case attribute values, the non-existing values are ignored. Still, if the CaseAttributeValue or EventAttributeValue filter refers to an attribute name that doesn't exist, an error is given.

Filter and Comparison parameters

The filter definition has syntax demonstrated by the following example: 

{
  Items: [
    {
      Type: "IncludeCases",
      Items: [
        {
          Type: "EventType",
          Values: ["Sales Order Created", "Payment Received"]
        },
        {
          Type: "CaseAttributeValue",
          Attribute: "Region",
          Values: ["Dallas", "Austin"]
        }
      ]
    },
    {
      Type: "ExcludeCases",
      Items: [
        {
          Type: "Case",
          Values: ["Case1", "Case2", "Case3"]
        },
        {
          Type: "flow",
          Values: [
            {
              From: "Shipment",
              To: "Invoice"
            }
          ]
        }
      ]
    }
  ]
}

The outmost object has a property Items which is an array of object with following properties:

  • Type: Specifies the type of the filtering operation with following options:
    • IncludeCases / ExcludeCases: Includes / excludes cases. All filter rule types can be used with this option.
    • IncludeEvents / ExcludeEvents: Includes / excludes events. Only Attribute filter rule types can be used with this option.
    • IncludeEventTypes / ExcludeEventTypes: Includes / excludes events based on the event type. Only Event type and Expression filter rule types can be used with this option.
  • Disabled: (boolean) Defines whether the filter rule is applied or not. This is used by the UI to temporarily disable filter rule without removing them.

The following chapters list different filter rule types that can be used in the above defined Items property.

Filter Rule Types

Expression

Filter rule type ExpressionValue (for in-memory calculation) or SqlExpressionValue (for dataframe calculation) selects cases or events that match the given expression. There is property Configuration which has properties:

  • Root: The root expression returning objects that are used to evaluate the Expressions (see below). If not defined, all the cases in the current event log are used as root objects. The root expression should return following types of objects: (1) for case filters: cases, events, event types, flows or flow occurrences, (2) for event filters: events and (3) for event type filters: event types. The expressions are typically what can be used as chart dimension expressions.
  • Expressions: Array of expressions specifying the filter rules. There is the AND logic between the filter rules, so all expressions need to match for the objects to come to the filter. Properties:
    • Expression: When filter rule type is ExpressionValue, specifies an eventlog based expression that is evaluated for each root object. When filter rule type is SqlExpressionValue, specifies an SQL expression that is evaluated for each root object.
    • Values: Array of values to match to the expression result. Suitable only for strings, numeric and boolean values.
    • StringifiedValues: Array of values to match to the expression result, and the values are defined in the stringified format. This has a better support for different data types than in the Values property.
    • NumberPrecision: Similar setting as the expression analysis dimensions have to round numerical type of result of the expression.
    • DatetimeTruncation: Similar setting as the expression analysis dimensions have to round date type of result of the expression.
    • TimespanPrecision: Similar setting as the expression analysis dimensions have to round timespan type of result of the expression. Supported only for expression type ExpressionValue.
    • Comparison: Defines type of comparison operation among the following: equal (default), notequal, greaterthan, lessthan, greaterthanorequal and lessthanorequal. When comparison is equal or notequal, several values can be specified. For other comparison types, there must only be one value. The lower and greater than comparisons work with numbers, datatimes and timespans. In the lower and greater than comparisons, null values in both sides of the comparison result in a non-match. Rounding or truncation defined by the NumberPrecision, DateTimeTruncation or TimespanPrecision are done before comparison, so do not use them if comparison should be based on the original values.
    • Columns: For SqlExpressionValue type, additional calculated columns that can be used in the main expression. Defined as an array of objects with following properties:
      • Name: Column name.
      • Expression: Column expression. In the expression, it's possible to refer to the previous columns.
  • Variables: Object specifying key-value pairs, where each pair creates a variable into the expression context where key is the name of the variable and value is its value. The values will be converted to strings. Supported only for expression type ExpressionValue.

Examples:

Select cases named "case1" and "case2". 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "Name",
        Values: ["case1", "case2"]
      }
    ]
  }
}

Cases where duration is as rounded 3 or 4 days. 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "Duration.TotalDays",
        NumberPrecision: 0,
        StringifiedValues: ["43", "44"]
      }
    ]
  }
}

Cases where duration is as rounded 3 or 4 days. 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "Duration",
        TimespanPrecision: "day",
        StringifiedValues: ["73.00:00:00.000", "74.00:00:00.000"]
      }
    ]
  }
}

Cases that have started in October or November 2020. 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "StartTime",
        DatetimeTruncation: "month",
        StringifiedValues: ["22020-10-01T00:00:00.000", "22020-11-01T00:00:00.000"]
      }
    ]
  }
}

Selects cases whose duration is longer than one day. 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases.Where(Duration.TotalDays > 1)"
  }
}

Same as previous using the Comparison property: 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "Duration.TotalDays",
        Comparison: "greaterthan",
        Values: [1]
      }
    ]
  }
}

All Cases that have started before year 2020: 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "StartTime",
        Comparison: "lessthan",
        StringifiedValues: ["22020-01-01T00:00:00.000"]
      }
    ]
  }
}

Returns all cases that are conformant with the specified BPNM model. 

{
  Type: "ExpressionValue",
  Configuration: {
    Variables: {
      bpmnXml: "<XML BPMN model>"
    },
    Root: "Let(\"myConformanceModel\", DesignModelFromXml(bpmnXml));Cases.Where(IsConformant(myConformanceModel));"
  }
}

Pass date values as ticks and convert them into date objects (that are used in the actual comparison). 

{
  Type: "ExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [
      {
        Expression: "myDatetimeAttribute",
        ValueConversionExpression: "_.DateTimeFromTicks(_)",
        Values: [636214968000000000, 636239160000000000, 636291828000000000]
      }
    ]
  }
}

{
  Type: "SqlExpressionValue",
  Configuration: {
    Root: "Cases",
    Expressions: [{
      Expression: "AggregateFrom(Events, \"Count\")",
      Values: [4,5]
    }]
  }
}

Attribute

Filter rule type Attribute selects events having given event attribute values. This filter rule is available only when processing method is DataFrame, and when filter operation type is IncludeEvents/ExcludeEvents. Properties:

  • Attribute: Event attribute name.
  • Values: Array of event attribute values. Suitable only for string, integer and boolean values.
  • StringifiedValues: Array of event attribute values in the stringified format. Better support for different data types. Note that due to the equality comparison, float values cannot be used.

Examples: 

{
  Type: "Attribute",
  Attribute: "Role",
  Values: ["Consultant", "Sales", "Support"]
}

{
  Type: "Attribute",
  Attribute: "Automation",
  StringifiedValues: ["0Manual"]
}

Case attribute value

Filter rule type CaseAttributeValue selects cases having given value in given case attribute. Properties:

  • Attribute: Case attribute name.
  • Values: Array of case attribute values. Suitable only for string, integer and boolean values.
  • StringifiedValues: Array of case attribute values in the stringified format. Better support for different data types. Note that due to the equality comparison, float values cannot be used.

Example: 

{
  Type: "CaseAttributeValue",
  Attribute: "Region",
  Values: ["Dallas", "Austin", "New York"]
}

{
  Type: "CaseAttributeValue",
  Attribute: "Region",
  StringifiedValues: ["0Dallas", "0Austin", "0New York"]
}

Event attribute value

Filter rule type EventAttributeValue selects cases having events with given event attribute values. Properties:

  • Attribute: Name of the event attribute.
  • Values: An array of event attribute values. Suitable only for string, integer and boolean values.
  • StringifiedValues: Array of event attribute values in the stringified format. Better support for different data types. Note that due to the equality comparison, float values cannot be used.

Example: 

{
  Type: "EventAttributeValue",
  Attribute: "Organization",
  Values: ["Organization 1", "Organization 2"]
}

{
  Type: "EventAttributeValue",
  Attribute: "Organization",
  StringifiedValues: ["0Organization 1", "0Organization 2"]
}

Event type

Filter rule type EventType selects cases containing selected event types (Type=IncludeCases/ExcludeCases) or selects event types (Type=IncludeEventTypes/ExcludeEventTypes). It has property Values which is an array of event type names.

Example: 

{
  Type: "EventType",
  Values: ["Sales Order Created", "Payment Received", "Delivery Sent"]
}

Variation

Filter rule type Variation selects cases belonging to selected variations. It has the property Paths which is an array of variations. Each variation is a string array of event type names in the variation.

Example: 

{
  Type: "Variation",
  Paths: [
    ["Activity 1", "Activity 2", "Activity 3"],
    ["Activity 3"],
    ["Activity 2", "Activity 4"]
  ]
}

Flow

Filter rule type Flow selects cases having the defined flows. It has property Flows which selects an array of objects with following properties:

  • From: Event type name which starts the flow.
  • To: Event type name into which the flow goes.
  • Occurrence: Zero-based occurrence index of the selected flow within its case. If not specified, represents all occurrences.

Example: 

{
  Type: "Flow",
  Flows: [ {From: "EventType1", To: "EventType2", "Occurrence": 1} ]
}

Case ID

Filter rule type Case selects individual cases. It supports property Values which is an array of case ID's (strings).

Example: 

{
  Type: "Case",
  Values: ["case1", "case2", "case3"]
}

Same example in the whole JSON configuration: 

{
  Items: [
    {
      Type: "IncludeCases",
      Items: [
        {
          Type: "Case",
          Values: ["case1", "case2", "case3"]
        }
      ]
    }
  ]
}
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Filtering_in_QPR_ProcessAnalyzer_Queries&oldid=23551"