Prepare your Knowledge Model for an Insight Explorer
Before setting up an Insight Explorer, you must choose what knowledge within your Knowledge Model (KM) you want to analyze for insights and then prepare that knowledge to be analyzed. The types of knowledge within your KM that can be analyzed include metrics, attributes (native and calculated), process attributes, and filters.
Knowledge requirements for Insight Explorer
All knowledge you provide as input to an Insight Explorer is checked against the requirements below. If an input fails to meet any requirement labeled as an ERROR in the Severity column of the table below, then 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 the requirements, try adding it to your Insight Explorer configuration.
Entity | Requirement | Warning message | Severity | Suggested action | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Metrics | M1: Is a KPI in the Knowledge Model |
KPI or record attribute not found in Knowledge Model. | ERROR | The KPI may have been deleted from the Knowledge Model. In this case, the user ID and event will be recorded in Admin & Settings > Audit Logs. | |||||||||||
M2: PQL is valid |
Invalid PQL expression: {...} | ERROR | Make sure the PQL is written properly and returns a value. | ||||||||||||
M3: Does not use parameters |
Metric expression is parametric. | ERROR | Create a new KPI that calls the original KPI and sets the parameters to the desired values. | ||||||||||||
M4: Has a desired direction of either Up Trend or Down Trend. |
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, edit the KPI and select either Up Trend or Down Trend as the desired direction. If the desired direction is None, then Down Trend will be used by default. | ||||||||||||
M5: Has one of the following aggregation types and syntaxes:
|
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 | 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. Examples: | ||||||||||||
M6: Does not contain too many |
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 | A1: Is a KPI or Record Attribute in the Knowledge Model |
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. | |||||||||||
A2: PQL is valid |
Invalid PQL expression: {...} | ERROR | Make sure the PQL is written properly and returns values. | ||||||||||||
A3: Does not use parameters |
Attribute expression is parametric. | ERROR | Create a new KPI that calls the original KPI and sets the parameters to the desired values. | ||||||||||||
A4: Is not an aggregation |
Attribute is not allowed to contain aggregations. | ERROR | Create a new record attribute without the aggregation function. | ||||||||||||
A5: Has a supported column type
|
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'... | ||||||||||||
A6: Does not contain too many NULL values |
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. The following are common examples of issues you may encounter and how to resolve them.
(Metric) Transforming COUNT to SUM
Original metric:
COUNT ( CASE WHEN "Table"."Country" = 'US' THEN "Table"."caseId" END )
Modified metric:
SUM ( CASE WHEN "Table"."Country" = 'US' THEN 1 ELSE 0 END )
(Metric) Transforming SUM / SUM to AVG
Original attribute:
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 attribute:
AVG (
CASE WHEN "Invoice"."clearingDate" IS NOT NULL THEN
CASE WHEN "Invoice"."paymentBehavior" = 'On Time' THEN 1 ELSE 0 END
END
)(Metric) Pulling 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 ) )
(Metric) 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" )
(Metric) Taking the weight outside of CASE WHEN
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"."healthScore" END * "Customer"."ARR" ) / SUM ( "Customer"."ARR" )
(Metric) 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 )
(Attribute) Pulling 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 --aggregate only distinct values ORDER BY "caseDetails"."Detail" ASC )