Skip to main content

Celonis Product Documentation

Prepare your Knowledge Model for Insight Explorer

Before setting up Insight Explorer, it is important to prepare your Knowledge Model so that the knowledge you want to analyze is eligible to be used with Insight Explorer. The knowledge entities to pay attention to are: Metrics, Attributes, and Filters.

Some terminology

  • Metric: A metric is an aggregated number. Common aggregations include AVG, SUM, and COUNT. Examples of metrics would be SLO Adherence or On-Time Delivery.

  • Attribute: An attribute describes an individual object and groups metrics as semantically useful categories. It can be boolean, categorical, numerical, or a datetime. Examples of attributes would be Region or Material.

Knowledge requirements for Insight Explorer

All metrics and attributes you provide as input to Insight Explorer are checked against the requirements below. If an input fails to meet any requirement labeled as an error, the input is excluded from the analysis. Not meeting a requirement labeled as a warning does not prevent an input from being included in the analysis, but may result in less optimal insights. To verify if a metric or attribute meets requirements, you can try adding it to the Insight Explorer configuration.

Entity 

Requirement 

Warning message 

Severity 

Suggested action 

Metrics 

1. Is a KPI in the Knowledge Model

KM_OBJECT_NOT_FOUND 

KPI or record attribute not found in knowledge model.

Error

The KPI might have been deleted from the Knowledge Model. In this case, the user ID and event will be captured in Admin & Settings > Audit Logs.

2. PQL is valid

ENGINE_RESPONSE 

Invalid PQL expression: {...}

Error

Make sure the PQL is written properly and returns a value.

3. Does not use parameters

SYNTAX_INCOMPATIBILITY 

Metric expression is parametric.

Error

Create a new KPI that calls the original KPI and sets the parameters to the desired values.

4. Has a desired direction (either Up Trend or Down Trend)

KPI_DESIRED_DIRECTION_INCOMPATIBILITY 

Metric KPI requires a non-neutral desired direction. For this metric 'DECREASE' will be used by default. Please adjust accordingly if 'INCREASE' should be used.

Warning

In the Knowledge Model, set the desired direction of the KPI to either Up Trend or Down Trend. If the desired direction is None, Down Trend will be used by default.

5. Has one of the following aggregation types and syntaxes:

Aggregation type 

Syntax 

Count

COUNT(expression)

Sum

SUM(expression)

Average

 AVG(expression)

Median

MEDIAN(expression)

Weighted average

 SUM(value*weight) / SUM(weight)

AGGREGATION_TYPE_INCOMPATIBILITY 

No compatible aggregation type found for this metric. Supported metrics must be of the form AVG(), SUM() or MEDIAN().

Metric appears to be of aggregation type {aggregation_type}, but is not written with a compatible syntax. Please adjust the query to include it as a metric.

Error

It is often the case that you can rewrite the PQL of the KPI into one of the supported forms. If the KPI is already being used in other assets, first duplicate it to avoid affecting the other assets.

For examples, see:

6. Does not contain too many NULL values

NULL_VALUES_INCOMPATIBILITY 

Metric has more than 80% NULL values.

Metric has a high rate (>30%) of NULL values.

Error (>80%)

Warning (>30%)

There may be missing data. If the values are left blank intentionally, consider applying a filter to Insight Explorer to exclude the NULL values.

Attributes 

1. Is a KPI or Record Attribute in the Knowledge Model

KM_OBJECT_NOT_FOUND 

KPI or record attribute not found in knowledge model.

Error

The KPI or record attribute might have been deleted from the Knowledge Model. In this case, the user ID and event will be captured in Admin & Settings > Audit Logs.

2. PQL is valid

ENGINE_RESPONSE 

Invalid PQL expression: {...}

Error

Make sure the PQL is written properly and returns values.

3. Does not use parameters

SYNTAX_INCOMPATIBILITY 

Attribute expression is parametric.

Error

Create a new KPI that calls the original KPI and sets the parameters to the desired values.

4. Is not an aggregation

CONTAINS_AGGREGATION 

Attribute is not allowed to contain aggregations.

Error

Create a new record attribute without the aggregation function.

5. Has a supported column type

Boolean

Supported

Categorical

Supported

Continuous

Supported

Datetime

Not supported

ATTRIBUTE_TYPE_INCOMPATIBILITY 

Attribute is not of supported type. Currently only categorical and boolean attributes are supported.

Error

If the attribute is numerical, convert it to a categorical one by using CASE WHEN to map each number falling within a certain range to a string. 

For example:

CASE
  WHEN "Invoice"."netValue" BETWEEN 0 AND 100 THEN 'a) 0-100'
  WHEN "Invoice"."netValue" BETWEEN 100 AND 200 THEN 'b) 100-200'
  ...
END

6. Does not contain too many NULL values

NULL_VALUES_INCOMPATIBILITY 

Attribute has more than 80% NULL values.

Attribute has a high rate (>30%) of NULL values.

Error (>80%)

Warning (>30%)

There may be missing data. If the values are left blank intentionally, consider applying a filter to Insight Explorer to exclude the NULL values.

Examples

In most cases, you can modify the PQL of a metric or attribute to meet the knowledge requirements while still keeping its value the same. The following are common examples of issues you may encounter during the setup of Insight Explorer and how to resolve them. 

Transforming COUNT / COUNT to AVG

Original metric: 

COUNT ( CASE WHEN "Invoice"."paymentBehavior" = 'On Time' THEN "Invoice"."Id" ELSE NULL END )
/
COUNT ( "Invoice"."Id" )

Modified metric: 

AVG ( CASE WHEN "Invoice"."paymentBehavior" = 'On Time' THEN 1 ELSE 0 END )
Transforming SUM / SUM to AVG with CASE WHEN in denominator

Original metric: 

SUM ( CASE WHEN "Invoice"."paymentBehavior" = 'On Time' THEN 1 ELSE 0 END )
/
SUM ( CASE WHEN "Invoice"."clearingDate" IS NOT NULL THEN 1 ELSE 0 END )

Modified metric: 

AVG (
  CASE WHEN "Invoice"."clearingDate" IS NOT NULL THEN
    CASE WHEN "Invoice"."paymentBehavior" = 'On Time' THEN 1 ELSE 0 END
  END
)
Transforming SUM / SUM to weighted average

Original metric: 

SUM ( "Invoice"."gainedCashDiscount" )
/
SUM ( "Invoice"."possibleCashDiscount" )

Modified metric: 

SUM (
  ( "Invoice"."gainedCashDiscount" / "Invoice"."possibleCashDiscount" )
  * "Invoice"."possibleCashDiscount"
)
/
SUM ( "Invoice"."possibleCashDiscount" )
Taking the weight outside of CASE WHEN in a weighted average

Original metric: 

SUM ( CASE WHEN "Customer"."Segment" = 'Strategic' THEN "Customer"."healthScore" * "Customer"."ARR" END )
/
SUM ( CASE WHEN "Customer"."Segment" = 'Strategic' THEN "Customer"."ARR" END )

Modified metric: 

SUM (
  CASE WHEN "Customer"."Segment" = 'Strategic' THEN "Customer"."ARR" END
  * "Customer"."healthScore"
)
/
SUM ( CASE WHEN "Customer"."Segment" = 'Strategic' THEN "Customer"."ARR" END )
Transforming TRIMMED_MEAN to AVG

Original metric: 

TRIMMED_MEAN ( "Table"."cycleTime" )

Modified metric: 

AVG (
  CASE WHEN
    INDEX_ORDER ( FILTER_TO_NULL ( "Table"."cycleTime" ) , ORDER BY ( "Table"."cycleTime"  ASC ) )
    > FLOOR ( PU_MAX ( CONSTANT ( ) , INDEX_ORDER ( FILTER_TO_NULL ( "Table"."cycleTime"  ) , ORDER BY ( "Table"."cycleTime"  ASC ) ) ) * 0.1 )
    AND
    INDEX_ORDER ( FILTER_TO_NULL ( "Table"."cycleTime" ) , ORDER BY ( "Table"."cycleTime"  ASC ) )
    <= CEIL ( PU_MAX ( CONSTANT ( ) , INDEX_ORDER ( FILTER_TO_NULL ( "Table"."cycleTime"  ) , ORDER BY ( "Table"."cycleTime"  ASC ) ) ) * 0.9 )
  THEN "Table"."cycleTime" ELSE NULL END
)
Pulling a metric to the case table

Original metric: 

SUM ( CASE WHEN "activityTable"."userType" = 'B' THEN 1 ELSE 0 END )

Modified metric: 

SUM (
   PU_SUM ( "caseTable" , CASE WHEN "activityTable"."userType" = 'B' THEN 1 ELSE 0 END )
)
Pulling an attribute to the case table

Original attribute: 

"caseDetailTable"."caseDetail"

Modified attribute: 

PU_STRING_AGG (
  "caseTable" ,
  "caseDetails"."Detail" ,
  ', ' ,
  INDEX_ORDER ( "caseDetails"."Detail" , PARTITION BY ( "caseTable"."caseId" , "caseDetails"."Detail" ) ) = 1
  ORDER BY "caseDetails"."Detail" ASC
)