Skip to main content

Celonis Product Documentation

LINK_PATH
Description

Warning

To use this feature, Object Link needs to be configured with Object Link in the data model.

The LINK_PATH operator creates an internal activity and case table to represent individual paths calculated by traversing the Object Link graph. The resulting activity table is joined towards the input table and to the internal case table in a N:1 fashion. Additional tables containing link attributes are generated and can be accessed via the LINK_PATH_SOURCE/TARGET operators. An example of the created tables and how they are joined is shown below:

LINK_PATH_Join_Example.png

Warning

In some settings, this operator may require excessive CPU time. If the execution time exceeds 10 minutes, the execution is stopped and an error is reported.

The output of LINK_PATH is a column containing all objects of all calculated paths, where each row's data corresponds to the value of the object in the input column. If only individual objects are of interest and a complete path is not required, use LINK_SOURCE/LINK_TARGET instead. As the generated tables are joined to the input table, all objects, as specified in the Object Link mapping table, must reside within this one table in order to avoid join cycles in the data model.

Syntax
 LINK_PATH ( input_table.column [, direction ] [, CONSTRAINED BY ( [ START ( start_objects_expression ) ] [, END ( end_objects_expression ) ] [, LENGTH ( comparison ) ] [, ALL ( all_objects_expression ) ] [, WITH/WITHOUT CYCLES] ) ] )

  • LINK_PATH acquires object attributes from the specified input_table.column. Therefore, all objects, as they are specified in the Object Link mapping table, must be contained in this table. Entries of the input object table that are not mentioned in the mapping table are not legitimate objects for the Object Link graph and are ignored.

  • direction specifies the traversal direction. Valid values for the parameter are FORWARDS (default) and BACKWARDS.

  • The graph traversal can further be CONSTRAINED BY:

    • start_objects_expression is a condition to specify the start objects of the graph traversal.

    • end_objects_expression is a condition to specify the end objects of the graph traversal.

    • comparison is one of = X (equal), != X (not equal), <> X (not equal), < X (less than), <= X (less than or equal), > X(greater than) or >= X (greater than or equal), where X is a positive integer. This can be used to specify the desired path lengths (as measured by the number of objects). Another option is BETWEEN X AND Y, which means that the path lengths must be between X and Y (inclusive). X and Y must both be positive integers and X must be smaller than or equal to Y.

    • all_objects_expression is a condition to specify which objects of the graph may be traversed.

    • WITH CYCLES enables the traversal of cycles and WITHOUT CYCLES disables the traversal of cycles. The default is WITHOUT CYCLES.

NULL handling

  • Object Link mapping table entries with NULL values in the IN or OUT column will register the object specified in the none-NULL entry. However, no link will be added to the Object Link graph.

  • NULL values in the input_table.column are retained in the output.

Scenario

The Object Link graph traversed by LINK_PATH can be arbitrarily complex and large. Formally, the graph is a directed multigraph, meaning that each link has a source and target object as well as an identity which allows multiple unique links between the same source and target pair. We refer to a group of links that share the same source and target objects as a multi-link. Additionally, the graph may contain cycles and self-loops. Objects therefore can appear multiple times within a path.

Warning

A multi-link may not consist out of more than 65'535 individual links. In the visualized graph below, one multi-link, from 'Egg' to 'Hard boiled eggs', exists with a quantity of 2.

Object_Link_example_graph.png

Object Link graphs typically have objects that do not have INCOMING or OUTGOING links, these are called implicit START or END objects respectively and can also be found via the LINK_OBJECTS operator. In the illustration above, implicit START objects are indicated with an incoming link without a source and END objects are marked with a thick border.

This Object Link graph has the implicit start objects 'Olive oil', 'Flour' and 'Vegetables' and implicit end objects 'Veggie pasta', 'Chicken pasta' and 'Hard boiled eggs'. Note that 'Chicken' and 'Egg' are not implicit start objects as they each have an incoming link. These respective links form a cycle between those objects, however, a cycle can also be on one object itself, e.g. kneading object 'Dough' will again result in 'Dough'. This example graph also contains a multi-link for boiling eggs as we use different durations during the process.

Constraints

Parameters within the the CONSTRAINED BY clause are called constraints and control the traversal performed by LINK_PATH. They are intended to limit the result of the operator by specifying the critical areas within the graph. Also see the chapter about "Why does LINK_PATH hit the table row limit?". Each constraint may only appear once but can otherwise be freely combined with each other.

Unconstrained

A LINK_PATH call that does not specify any constraints is called unconstrained and has the following default behavior:

  • START constraint: Use implicit START objects of the Object Link graph.

  • END constraint: Use implicit END objects of the Object Link graph.

  • LENGTH constraint: Use default LENGTH limit of 10. A warning will be emitted if longer paths exist.

  • ALL constraint: Allow ALL objects to be traversed.

  • CYCLES constraint: Enable/Disable cycles to be traversed.

[1]

The LINK_PATH operator internally creates new tables, most importantly an activity table in which each individual path is considered as a case. This allows process mining operators to be applied to graph-based data. This example shows how we apply process operators like CASE_ID_COLUMN and TIMESTAMP_COLUMN on the result of LINK_PATH. Note that the ACTIVITY_COLUMN will contain only NULL values and that we can acquire any object attribute that is defined in the input_table.

Query

Column1 

         CASE_ID_COLUMN ( LINK_PATH ( "MATERIAL"."ID" ) )

Column2 

         LINK_PATH ( "MATERIAL"."ID" )

Column3 

         MILLIS_BETWEEN ( {t 0 } , TIMESTAMP_COLUMN ( LINK_PATH ( "MATERIAL"."ID" ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : int

Column2 : string

Column3 : float

0

'Olive oil'

0.0

0

'Dough'

1.0

0

'Pasta'

2.0

0

'Veggie pasta'

3.0

1

'Olive oil'

0.0

1

'Dough'

1.0

1

'Pasta'

2.0

1

'Chicken pasta'

3.0

2

'Flour'

0.0

2

'Dough'

1.0

2

'Pasta'

2.0

2

'Veggie pasta'

3.0

3

'Flour'

0.0

3

'Dough'

1.0

3

'Pasta'

2.0

3

'Chicken pasta'

3.0

4

'Vegetables'

0.0

4

'Veggie pasta'

1.0

[2]

Besides the activity table, LINK_PATH also generates a new case table accordingly. We can use the generated tables together with Process and PU-functions to aggregate object information on a path level.

Query

Column1 

         CASE_TABLE(LINK_PATH("MATERIAL"."ID"))."PATH_ID"

Column2 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" ) )

Column3 

         PU_COUNT ( CASE_TABLE ( LINK_PATH ( "MATERIAL"."ID" ) ) , LINK_PATH ( "MATERIAL"."ID" ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : int

Column2 : string

Column3 : int

0

'Olive oil, Dough, Pasta, Veggie pasta'

4

1

'Olive oil, Dough, Pasta, Chicken pasta'

4

2

'Flour, Dough, Pasta, Veggie pasta'

4

3

'Flour, Dough, Pasta, Chicken pasta'

4

4

'Vegetables, Veggie pasta'

2

[3]

The resulting LINK_PATH activity table is also connected to the input table containing the traversed objects. With the following query we can find out which objects occur most often in all paths.

Query

Column1 

         "MATERIAL"."ID"

Column2 

         COUNT ( LINK_PATH ( "MATERIAL"."ID" ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

Column2 : int

'Chicken pasta'

2

'Dough'

4

'Flour'

2

'Olive oil'

2

'Pasta'

4

'Vegetables'

1

'Veggie pasta'

3

START and END

Traversal of the Object Link graph can be defined by specifying conditions on the input_table. Conditions in the START constraint specify where the traversal begins and the END constraint indicates where a path is finished. Note while specifying these constraints that the conditions are not automatically combined with the implicit START or END objects. If the implicit objects without INCOMING or OUTGOING links shall be used with additional conditions, we can utilize the LINK_OBJECTS operator to explicitily formulate the implicit objects.

[4]

By specifying only 'Egg' as a START material and objects of categories 'intermediate goods' and 'finished meals' as END objects, we limit the traversal to a smaller part of the Object Link graph. Note that we gained paths that are subsets of each other, namely 'Egg, Dough' which is included in 'Egg, Dough, Pasta' but we never get duplicated paths, e.g. 'Egg, Hard boiled eggs' only exists once even though there are two links between the connected objects.

Query

Column1 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" , CONSTRAINED BY ( START ( "MATERIAL"."ID" = 'Egg' ) , END ( "MATERIAL"."CATEGORY" IN ( 'intermediate goods' , 'finished meals' ) ) ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Egg, Dough'

'Egg, Dough, Pasta'

'Egg, Dough, Pasta, Veggie pasta'

'Egg, Dough, Pasta, Chicken pasta'

'Egg, Chicken, Chicken pasta'

'Egg, Hard boiled eggs'

[5]

In the following example, we specify implicit END objects explicitly via LINK_OBJECTS and acquire the same results.

Query

Column1 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" , CONSTRAINED BY ( END ( LINK_OBJECTS("MATERIAL"."ID")."OUTGOING" = 0 ) ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Olive oil, Dough, Pasta, Veggie pasta'

'Olive oil, Dough, Pasta, Chicken pasta'

'Flour, Dough, Pasta, Veggie pasta'

'Flour, Dough, Pasta, Chicken pasta'

'Vegetables, Veggie pasta'

LENGTH

Arbitrarily long paths could result from traversal on an Object Link graph. To allow and disallow paths of certain lengths we can make use of the LENGTH constraint.

[6]

In this example, we only want paths whose length (as measured by the number of involved objects) is equal to 2.

Query

Column1 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" , CONSTRAINED BY ( LENGTH ( = 2 ) ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Vegetables, Veggie pasta'

ALL

Thanks to the ALL constraint, we can guarantee that all objects in the resulting paths fulfill a certain condition.

[7]

If we want every path that does not involve traversing certain objects, we can avoid them via the ALL constraint. Here we gain every path that never visits material 'Dough'.

Query

Column1 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" , CONSTRAINED BY ( ALL ( "MATERIAL"."ID" != 'Dough' ) ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Vegetables, Veggie pasta'

CYCLES

With the cycles constraint we can enable or disable the traversal of cycles in the created graph. A cycle is detected when any object would occur twice within one traversed path. WITHOUT CYCLES is set by default, so that no cycles are traversed.

[8]

With cycles enabled, we get additional paths that contain the self-link on 'Dough'.

Query

Column1 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" , CONSTRAINED BY ( WITH CYCLES ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Olive oil, Dough, Dough, Pasta, Veggie pasta'

'Olive oil, Dough, Dough, Pasta, Chicken pasta'

'Olive oil, Dough, Pasta, Veggie pasta'

'Olive oil, Dough, Pasta, Chicken pasta'

'Flour, Dough, Dough, Pasta, Veggie pasta'

'Flour, Dough, Dough, Pasta, Chicken pasta'

'Flour, Dough, Pasta, Veggie pasta'

'Flour, Dough, Pasta, Chicken pasta'

'Vegetables, Veggie pasta'

Why does LINK_PATH hit the table row limit?

When the LINK_PATH operator traverses a large Object Link graph, it stores the paths it finds as rows in a table. If this table becomes too large, an error is returned indicating that the table row limit has been reached. To demonstrate how quickly the number of paths can grow, let's look at a simple example production process for making chocolate strawberry cake, as shown in the following figure.

Object_Link_Chocolate_Strawberry_Cake_Graph.png

When this Object Link graph is traversed without constraints using the query below, the result consists of 45 paths and 180 rows, which is quite a lot for such a small graph.

 LINK_PATH ( "Materials"."Description" )

However, if our use case does not require packaged cakes, we can add a constraint to stop traversing the graph when we reach the unpackaged chocolate strawberry cake as shown in the following query. This drastically reduces the size of the result, which is now only 14 paths and 44 rows. That's three times fewer paths and four times fewer rows just by omitting three packaged cakes. In real processes with thousands of objects and links, the number of paths will grow even faster. Therefore, it is critical to limit the traversal of the Object Link graph to only those objects that are essential to a use case.

 LINK_PATH ( "Materials"."Description" , CONSTRAINED BY ( END ( "Materials"."Description" = 'Chocolate strawberry cake' ) ) )

The following figure gives a rough indication of when the table row limit might be reached for Object Link graphs of different sizes if no constraints are used. For example, for an Object Link graph with 10,000 objects, the table row limit is likely to be reached when the number of links exceeds 26,000. In general, we strongly recommend the use of constraints once the ratio of links to objects is greater than 2.

Object_Link_Link_Path_Table_Row_Limit_Plot.png

Use the following queries to calculate the number of links, the number of objects, and the ratio of links to objects for your Object Link graph.

 COUNT ( LINK_SOURCE ( table.column ) )

 COUNT_TABLE ( LINK_OBJECTS ( table.column ) )

 COUNT ( LINK_SOURCE ( table.column ) ) / GLOBAL ( COUNT_TABLE ( LINK_OBJECTS ( table.column ) ) )
Advanced Examples

[9]

LINK_PATH can also handle complex graphs that contain cycles and multiple links between two objects. When traversing an Object Link graph like the one above, LINK_PATH has two mentionable features:

  1. Since the output of LINK_PATH is object-based, there is no clear distinction between the two paths 'Egg' ➔ 'Hard boiled eggs' over the boil links. This prevents identical paths from being duplicated, which drastically reduces the operator's output. Traversed links and their attributes can still be accessed using the LINK_PATH_SOURCE and LINK_PATH_TARGET operators.

  2. Although the output is link-independent, LINK_PATH marks the links as they are traversed so that they are not used a second time. For the given example, this means that the objects 'Chicken' and 'Egg' can not be visited infinitely many times, as the 'Chicken' ➔ 'Egg' ➔ 'Chicken' ➔ 'Egg' ➔ 'Chicken' ➔ ... loop is broken by disallowing reusing the same link within one path.

This example further illustrates how including cyclic paths easily blows up the result even though only a few objects are involved.

Query

Column1 

         VARIANT ( LINK_PATH ( "MATERIAL"."ID" , CONSTRAINED BY ( START ( "MATERIAL"."ID" IN ( 'Chicken' , 'Egg' ) ) , WITH CYCLES ) ) )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Chicken, Egg, Dough, Dough, Pasta, Veggie pasta'

'Chicken, Egg, Dough, Dough, Pasta, Chicken pasta'

'Chicken, Egg, Dough, Pasta, Veggie pasta'

'Chicken, Egg, Dough, Pasta, Chicken pasta'

'Chicken, Egg, Chicken, Chicken pasta'

'Chicken, Egg, Hard boiled eggs'

'Chicken, Chicken pasta'

'Egg, Dough, Dough, Pasta, Veggie pasta'

'Egg, Dough, Dough, Pasta, Chicken pasta'

'Egg, Dough, Pasta, Veggie pasta'

'Egg, Dough, Pasta, Chicken pasta'

'Egg, Chicken, Egg, Dough, Dough, Pasta, Veggie pasta'

'Egg, Chicken, Egg, Dough, Dough, Pasta, Chicken pasta'

'Egg, Chicken, Egg, Dough, Pasta, Veggie pasta'

'Egg, Chicken, Egg, Dough, Pasta, Chicken pasta'

'Egg, Chicken, Egg, Hard boiled eggs'

'Egg, Chicken, Chicken pasta'

'Egg, Hard boiled eggs'

[10]

The result of LINK_PATH are individual paths. However, sometimes we want to get a combined view of paths that have certain properties in common. The following example shows how to distinctively sum up an object attribute for all paths that share the same end object.

Query

KPI "link_path" 

         LINK_PATH ( {p1} , CONSTRAINED BY ( START ( "MATERIAL"."CATEGORY" = 'raw ingredients' ) ) )

Column1 

         PU_LAST ( CASE_TABLE ( KPI ( "link_path" , "MATERIAL"."ID" ) ) , KPI ( "link_path" , "MATERIAL"."ID" ) )

Column2 

         SUM ( CASE WHEN INDEX_ORDER ( UNIQUE_ID ( KPI ( "link_path" , "MATERIAL"."ID" ) , PU_LAST ( CASE_TABLE ( KPI ( "link_path" , "MATERIAL"."ID" ) ) , KPI ( "link_path" , "MATERIAL"."ID" ) ) ) , PARTITION BY ( UNIQUE_ID ( KPI ( "link_path" , "MATERIAL"."ID" ) , PU_LAST ( CASE_TABLE ( KPI ( "link_path" , "MATERIAL"."ID" ) ) , KPI ( "link_path" , "MATERIAL"."ID" ) ) ) ) ) = 1 THEN KPI ( "link_path" , "MATERIAL"."PRICE" ) END )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

Column2 : float

'Chicken pasta'

20.68

'Hard boiled eggs'

8.5

'Veggie pasta'

21.98

See also: