Datasources Usage Guide

Overview

Each project ingested into Druid produces three main datasources representing the process data at different levels:

Cases: one row per case (process execution)
Tasks (_vertex): one row per task execution
Links (_edge): one row per transition between tasks

Datasource naming pattern:

${projectId}
${projectId}_vertex
${projectId}_edge

These datasources can be enriched using Druid lookups such as:

${projectId}_variant_name
${projectId}_excluded_cases
${projectId}_cases_is_completed
business_rule_<ruleId>
conformance_<modelId>

Lookups behave similarly to joins and can be used using the LOOKUP() function.

Example:

SELECT caseid, LOOKUP(graphkey, '${projectId}_variant_name') AS variant_name
FROM ${projectId}

Cases datasource

The Cases datasource contains one row per case and aggregates information computed from all events belonging to that case.

Datasource name pattern: ${projectId}

Example: b7bd7acb-6a13-44a9-bebb-26f8e7e96c48

This datasource is typically used for:

case-level analytics
duration and performance metrics
filtering cases
variant or process analysis

Name	Type	Description
__time	Timestamp	start date of the first case event
enddate	Long	end date of the last case event
duration	Long	case duration in milliseconds
graphkey	UUID	variant id the case belongs to (model variant)
processkey	UUID	process id the case belongs to (process variant)
processsequence	UUID	process_sequence id belongs to (sequence variant)
caseid	String	case unique identifier
version	String	data version.
case_version	String	<caseid>-<version>, used to filter obsolete case data
active_duration	Long	total active time in the case
concurrency	Double	case concurrency rate
rework	Long	case total rework
tasks_count	Long	number of tasks in the case (excluding start/end)
distinct_vertices	Array[String]	distinct task identifiers in the case (including start and end)
distinct____vertices____count	Long	number of distinct tasks (including start and end)
distinct_edges	Array[String]	distinct transitions in the case
distinct____edges____count	Long	number of distinct transitions.
hll_vertices	Complex<HLLSketch>	HyperLogLog sketch of distinct vertices
hll_edges	Complex<HLLSketch>	HyperLogLog sketch of distinct edges.
last_tasks_names	Array[String]	names of last tasks before case completion.
vertices_dates	Complex<json>	start/end timestamps for each task in the case
igx____metric-slug____lpkr	Double	aggregated value of the metric at the case level
igx____dimension-slug____lpkr	String	aggregated value of the dimension at the case level
is_predicted	Boolean	indicates if row is prediction data
is____predicted____finished	Boolean	indicates if predicted case is finished

Tasks datasource (_vertex)

The Tasks datasource contains one row per task execution within a case.

Datasource name pattern: ${projectId}_vertex

Example: b7bd7acb-6a13-44a9-bebb-26f8e7e96c48_vertex

This datasource is typically used for:

task performance analysis
task-level filtering
bottleneck detection
concurrency analysis

Name	Type	Description
ingestion_timestamp	Long	insertion timestamp
__time	Timestamp	task start date
enddate	Long	task end date
duration	Long	task duration in milliseconds
graphkey	UUID	variant id the case belongs to (model variant)
processkey	UUID	process id the case belongs to (process variant)
processsequence	UUID	process_sequence id belongs to (sequence variant)
caseid	String	identifier of the task’s case
case_version	String	<caseid>-<version>, used to filter obsolete case data
vertex_id	UUID	task non-unique identifier based on task name (two identical task names has same vertex_id)
task_id	UUID	task unique id
vertex_name	String	task name (null for Start and End vertices)
igx____metric-slug____lpkr	Double	metric value for the event
igx____dimension-slug____lpkr	String	dimension value for event
concurrent_vertices	Multi UUID	concurrent tasks non-unique ids
loop_path	Multi UUID	for a loop starting and finishing with vertex_id, non-unique ids of links composing the loop
occurrences____in____case	Long	number of times a the task appears in the case (based on task name)
predicted____occurrences_______in____case	Long	number of times a the task appears in the predicted case (based on task name)
rework	Long	number of repetitions of the task (without concurrency) in the case
case____igx_______metric-slug____lpkr	Double	aggregated value of the metric for the case
case____igx_______dimension-slug____lpkr	Multi String	aggregated value of the dimension for the case
case____start____date	Long	case start date
case____end____date	Long	case end date
case_duration	Long	case duration
case_rework	Long	case rework
case_concurrency	Double	case concurrency
case____active____duration	Long	case active duration
case____idle____duration	Long	case idle duration
case____tasks____count	Long	number of tasks in the case
case____distinct____vertices	Array[String]	distinct task identifiers in the case (including start and end)
case____distinct____edges	Array[String]	distinct transitions in the case
case____is_______predicted____finished	Boolean	indicates if predicted case is finished
is_predicted	Boolean	row belongs to prediction data
is____in____prediction	Boolean	task is part of predicted case

Links datasource (____edge)

The Links datasource represents transitions between tasks in the process.

Datasource name pattern: ${projectId}____edge

Example: b7bd7acb-6a13-44a9-bebb-26f8e7e96c48_edge

The edges form a Direct Follower Graph (DFG), meaning only direct transitions between tasks are represented (no BPMN gateways).

This datasource is typically used for:

path analysis
transition frequency analysis
performance analysis between tasks
loop detection

Name	Type	Description
__time	Timestamp	edge start date (end of event A)
enddate	Long	edge end date (start of event B)
duration	Long	edge duration in milliseconds
graphkey	UUID	variant id the case belongs to (model variant)
processkey	UUID	process id the case belongs to (process variant)
processsequence	UUID	process_sequence id belongs to (sequence variant)
caseid	String	identifier of the edge’s case
case_version	String	<caseid>-<version>, used to filter obsolete case data
edge_id	UUID	edge non-unique identifier based on edge name (two identical edges names have the same edge_id)
edge_name	String	edge name (A->B)
start_taskid	UUID	unique identifier of the starting task A
start_vertexid	UUID	non-unique identifier of the starting task A
start_vertexname	String	name of starting task A
end_taskid	UUID	unique identifier of the end task B
end_vertexid	UUID	non-unique identifier of the end task B
end_vertexname	String	name of end task B
igx_____metric-slug_______lpkr____source	Double	task A metric value
igx____metric-slug_______lpkr____destination	Double	task B metric value
igx____dimension-slug_______lpkr____source	String	task A dimension value
igx____dimension-slug_______lpkr____destination	String	task B dimension value
concurrentEdges	Multi UUID	non-unique ids of concurrent links
occurrences____in____case	Long	number of times the link appears in the case (based on the link name)
predicted____occurrences_______in____case	Long	number of times the link appears in the predicted case (based on the link name)
rework	Long	number of repetitions of the link (without concurrency) in the case
case____igx_______metric-slug____lpkr	Double	aggregated value of the metric at the case level
case____igx_______dimension-slug____lpkr	Multi String	aggregated value of the dimension at the case level
case____start____date	Long	case start date
case____end____date	Long	case end date
case_duration	Long	case duration
case_rework	Long	case rework
case_concurrency	Double	case concurrency
case____active____duration	Long	case active duration
case____idle____duration	Long	case idle duration
case____tasks____count	Long	number of tasks in the case
case____distinct____vertices	Array[String]	distinct task identifiers in the case (including start and end)
case____distinct____edges	Array[String]	distinct transitions in the case
case____is_______predicted____finished	Boolean	indicates if predicted case is finished
is_predicted	Boolean	row belongs to prediction data
is____in____prediction	Boolean	link is part of predicted case

Common Query Patterns

1 Filtering excluded cases

When multiple versions of a case exist during ingestion, some versions must be ignored.

Use the ${projectId}_excluded_cases lookup.

SELECT *
FROM ${projectId}
WHERE LOOKUP(case_version, '${projectId}_excluded_cases') IS NULL

Explanation:

excluded cases are stored in the lookup
if lookup returns a value → case version is excluded
if lookup returns NULL → case version is valid

2 Retrieving the variant name

Variants are stored as graphkey. To get the readable name:

SELECT
  caseid,
  graphkey,
  LOOKUP(graphkey, '${projectId}_variant_name') AS variant_name
FROM ${projectId}

Example use cases:

variant analysis filtering specific variants

WHERE LOOKUP(graphkey, '${projectId}_variant_name') = '3'

3 Filtering completed cases

Cases completion is computed by business logic and stored in a lookup.

SELECT caseid
FROM ${projectId}
WHERE LOOKUP(caseid, '${projectId}_cases_is_completed') = 'TRUE'

Possible values depend on the end case rule (business rule).

4 Using business rules

Business rules results are stored in dynamic lookups.

Lookup name pattern:

business_rule_<businessRuleId>

Example:

SELECT caseid
FROM ${projectId}
WHERE LOOKUP(caseid, 'business_rule_12345') = 'TRUE'

5 Filtering by conformance score

Conformance scores are stored in lookups.

Example:

SELECT caseid,
LOOKUP(processkey, 'conformance_model1') AS fitness
FROM ${projectId}
WHERE LOOKUP(processkey, 'conformance_model1') < 0.8

This allows filtering low-conformance cases.

6 Task-level analysis

Example: average task duration

SELECT
  vertex_name,
  AVG(duration) AS avg_duration
FROM ${projectId}_vertex
GROUP BY vertex_name

7 Transition analysis

Example: most frequent transitions

SELECT
  start_vertexname,
  end_vertexname,
  COUNT(*) AS transitions
FROM ${projectId}_edge
GROUP BY start_vertexname, end_vertexname
ORDER BY transitions DESC

8 Variant performance

Example: average case duration per variant

SELECT
  LOOKUP(graphkey, '${projectId}_variant_name') AS variant,
  AVG(duration) AS avg_duration
FROM ${projectId}
GROUP BY graphkey

9 Using prediction fields

Prediction-related fields allow comparing predicted and real data.

Example:

SELECT *
FROM ${projectId}
WHERE is_predicted = true

Example: predicted tasks

SELECT *
FROM ${projectId}_vertex
WHERE is_in_prediction = true

Best Practices

Always:

filter excluded cases using the lookup
avoid join to filter on dimension or metric when case_xx exists
prefer the Cases datasource for aggregated metrics
use Tasks datasource for activity analysis
use Links datasource for path analysis
use lookups instead of joins whenever possible

Resources

IGrafx has extended SQL Druid by some specific functions:

Sha256

SELECT SHA256('hello world') AS hash;

Md5

SELECT MD5('hello world') AS hash;

NewID

SELECT NEWID() AS uuid;

Tip: You can use NEWID() and MD5 to get a random sample (about 10%) of data.

SELECT "caseid" from "cases" WHERE BITWISE_AND(md5(CONCAT("caseid", NEWID())), 2147483647) / 2147483647.0 < 0.1;

Be careful that when using NEWID() in a query, it will return the same UUID for each row in the result set. If you would like to get random sample of data using distributed SQL, you cannot use NEWID() directly, but a variable set before instead.

BUSINESS_DURATION

SELECT BUSINESS_DURATION(TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00'), TIME_PARSE('2023-10-01', 'YYYY-MM-dd', '+01:00'), 'FR') AS duration;

You can add optionally two parameters to the function:

LANGUAGE to describe the language to use for the calendar. The default is "en". Example "fr" for French.
HOLIDAY_TYPES to describe the type of holiday to use. Example "public observance". The different values are public, observance, bank, school, optional. The default is "public".
WEEK_HOURS to describe the business hours of the week. The default is "08:30-12:30, 13:30-17:30". You can specify a different range for each day of the week, e.g., '09:00-17:00' for Monday to Friday. Format of weekhour must follow https://github.com/yusuke/businessCalendar4J/blob/main/README.md#specify-holidays-and-business-hours (set business hours)
SPECIFIC_HOLIDAYS to describe custom holidays. The default is related to calendar you specified (3rd parameter). See https://github.com/commenthol/date-holidays/blob/c968f9a8e3cf734a32645efd605b67dac70ea509/docs/specification.md#grammar-for-day-rules for format of specific holidays.

ISBUSINESSDAY

SELECT IS_BUSINESS_DAY(TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00'), 'FR') AS businessday;

You can add optionally two parameters to the function, LANGUAGE, HOLIDAY_TYPES, WEEK_HOURS and SPECIFIC_HOLIDAYS, as described above.

ISHOLIDAYDAY

SELECT IS_HOLIDAY_DAY(TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00'), 'FR') AS businessday;

You can add optionally two parameters to the function, LANGUAGE, HOLIDAY_TYPES, WEEK_HOURS and SPECIFIC_HOLIDAYS, as described above.

DURATION_BETWEEN

SELECT DURATION_BETWEEN(ARRAY[TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00')], ARRAY[TIME_PARSE('2023-10-01', 'YYYY-MM-dd', '+01:00')], 'ALL', 'NEXT') AS duration;

You can add optionally two parameters to the function, CALENDAR, LANGUAGE, HOLIDAY_TYPES, WEEK_HOURS and SPECIFIC_HOLIDAYS, as described above if you want DURATION_BETWEEN calculated with business duration.

ISFOLLOWEDBY

SELECT IS_FOLLOWED_BY(ARRAY[TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00')], ARRAY[TIME_PARSE('2023-10-01', 'YYYY-MM-dd', '+01:00')]) AS followed;

LONGTOHEX

SELECT LONG_TO_HEX(MD5('hello world')) AS hex;

ARRAY_FLATTEN

SELECT ARRAY_FLATTEN('LONG',ARRAY[1, 2, 3, ARRAY[4, 5], ARRAY[6, 7]]) AS set_array;

ARRAYFLATTEN_AND__SORT

SELECT ARRAY_FLATTEN_AND_SORT('LONG',ARRAY[1, 2, 3, ARRAY[4, 5], ARRAY[6, 7]]) AS sorted_array;

CONCATFLATTEN_AND__SORT

SELECT CONCAT_FLATTEN_AND_SORT(',',ARRAY[1, 2, 3, ARRAY[4, 5], ARRAY[6, 7]]) AS sorted_string;

INTERVAL_UNION

SELECT INTERVAL_UNION(ARRAY[1, 2, 3, 5], ARRAY[4, 6]) AS interval_union;

INTERVALUNIONAGG

SELECT INTERVAL_UNION_AGG(field_array) AS interval_union_agg;

DURATION_INTERVAL

SELECT DURATION_INTERVAL(ARRAY[TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00'), TIME_PARSE('2023-10-01', 'YYYY-MM-dd', '+01:00')], 'FR') AS duration;

You can add optionally two parameters to the function, LANGUAGE, HOLIDAY_TYPES, WEEK_HOURS and SPECIFIC_HOLIDAYS, as described above.

GET_HOLIDAYS

SELECT GET_HOLIDAYS(TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00'), TIME_PARSE('2023-10-01', 'YYYY-MM-dd', '+01:00'), 'FR') AS holidays;

You can add optionally two parameters to the function, LANGUAGE, HOLIDAY_TYPES, WEEK_HOURS and SPECIFIC_HOLIDAYS, as described above.

GET_CALENDARS

SELECT GET_CALENDARS() AS calendars;

JSONOBJECTSIZE

SELECT JSON_OBJECT_SIZE("key1", "value1", "key2", "value2") AS json_size;

INBITMAPBASE64

SELECT IN_BITMAP_BASE64("LONG_COL", 'roaringbitmapBase64_01', 'roaringbitmapBase64_02') AS in_bitmap;

ARRAYSUCCESSIVEDIFF

SELECT ARRAY_SUCCESSIVE_DIFF(ARRAY[10, 20, 30, 35, 40]) AS successive_diff;

ENCODEBASE64UTF8

SELECT ENCODE_BASE64_UTF8("TOENCODESTRING") AS encoded_str;

ARRAY_SUBSET

SELECT ARRAY_SUBSET(ARRAY[1, 2], ARRAY[1, 2, 3]) AS is_subset;

GRAPHJACCARDSIMILARITY

SELECT GRAPH_JACCARD_SIMILARITY(ARRAY['v1', 'v2', 'v3'], ARRAY['v1->v2', 'v2->v3'], ARRAY['v1', 'v2', 'v3', 'v4'], ARRAY['v1->v2', 'v2->v3', 'v3->v4']) AS jaccard_similarity;

END_DATE

SELECT END_DATE(TIME_PARSE('2023-09-21', 'YYYY-MM-dd', '+01:00'), 4233033, 'FR') AS duration;

You can add optionally two parameters to the function, LANGUAGE, HOLIDAY_TYPES, WEEK_HOURS and SPECIFIC_HOLIDAYS, as described above.

Overview​

Cases datasource​

Tasks datasource (_vertex)​

Links datasource (____edge)​

Common Query Patterns​

1 Filtering excluded cases​

2 Retrieving the variant name​

3 Filtering completed cases​

4 Using business rules​

5 Filtering by conformance score​

6 Task-level analysis​

7 Transition analysis​

8 Variant performance​

9 Using prediction fields​

Best Practices​

Resources​

Sha256​

Md5​

NewID​

BUSINESS_DURATION​

IS____BUSINESS____DAY​

IS____HOLIDAY____DAY​

DURATION_BETWEEN​

IS____FOLLOWED____BY​

LONG____TO____HEX​

ARRAY_FLATTEN​

ARRAY____FLATTEN_______AND____SORT​

CONCAT____FLATTEN_______AND____SORT​

INTERVAL_UNION​

INTERVAL____UNION____AGG​

DURATION_INTERVAL​

GET_HOLIDAYS​

GET_CALENDARS​

JSON____OBJECT____SIZE​

IN____BITMAP____BASE64​

ARRAY____SUCCESSIVE____DIFF​

ENCODE____BASE64____UTF8​

ARRAY_SUBSET​

GRAPH____JACCARD____SIMILARITY​

END_DATE​

Overview

Cases datasource

Tasks datasource (_vertex)

Links datasource (____edge)

Common Query Patterns

1 Filtering excluded cases

2 Retrieving the variant name

3 Filtering completed cases

4 Using business rules

5 Filtering by conformance score

6 Task-level analysis

7 Transition analysis

8 Variant performance

9 Using prediction fields

Best Practices

Resources

Sha256

Md5

NewID

BUSINESS_DURATION

ISBUSINESSDAY

ISHOLIDAYDAY

DURATION_BETWEEN

ISFOLLOWEDBY

LONGTOHEX

ARRAY_FLATTEN

ARRAYFLATTEN_AND__SORT

CONCATFLATTEN_AND__SORT

INTERVAL_UNION

INTERVALUNIONAGG

DURATION_INTERVAL

GET_HOLIDAYS

GET_CALENDARS

JSONOBJECTSIZE

INBITMAPBASE64

ARRAYSUCCESSIVEDIFF

ENCODEBASE64UTF8

ARRAY_SUBSET

GRAPHJACCARDSIMILARITY

END_DATE