Data relation rule

Data relation rules specify the conditions under which rows from a related table should be extracted when a row from another table is extracted. Data relation rules solve relational integrity problems that arise when copying rows from multiple tables related to one another.

When a task execution is extracting a row from a table A that is related to another table B, the row from table B can only be extracted if there is a data relation rule defined between A and B.

Data relation rules are uni-directional. That means a single rule always defines how data is extracted in table B when extracting data in table A. To also define how data is extracted in table A when extracting table B, there needs to be a second data relation rule.

The direction of the data relation rules is always from the current table the selection process is working on at the moment to another table. For example XDM starts selecting data from table TABLE_A with the start condition. In the next step it follows a data relation rule from table TABLE_A to table TABLE_B and selects all related entries in TABLE_B. Then XDM follows the data relation rules from table TABLE_B to table TABLE_C. One can imagine the data relation rule as kind of arrow pointing to the next table to be processed.

Concepts and more information

Properties

The table below documents the available properties for data relation rules. The 'name' column displays the property name as it can be used in Groovy and Java Scripts.

Name

Type

Default

Description

Active

active

Boolean

true

Specifies whether the rule is active and therefore used, or inactive and therefore ignored, when a task is executed.

Columns

columns

String

n/a

Specifies a comma separated list of columns from the base table that define the rule relationship.

Base table conditional script

conditionScript

String

n/a

A base table conditional script is Java Script, or Groovy code to determine whether a rule should be applied to the current row. If the script returns false, the data is deemed to be unsuitable and XDM will not apply the rule. The code has access to an array data[] with the column name as index to obtain the column values to be checked.

More details about the script can be found here.

Description

description

String

n/a

An optional description for this object. The description can contain multiple lines to give more context on the configured object. The description is not used in a technical context.

Name

displayName

String

n/a

Specifies the name of the object. The name is used to display and identify the object in lists. The name can contain any valid UTF-8 characters. This field is mandatory.

Libraries

libraries

File

n/a

A list of files that can be accessed in the base table condition script and lookup script. The content of the selected XDM files is automatically added to the script context. All methods, variables, and classes in the selected files can be accessed in the method code.

Lookup script

lookupScript

String

n/a

A lookup script is JavaScript code which is used to transform key data from a base table into a format suitable for comparison with the key data of a referenced table.

Keys can have different representations in different tables. For instance, the name of a person might be stored as separate values such as "Carl" and "Miller" in the columns FIRSTNAME and LASTNAME in one table and as a single value "Carl Miller" in the column NAME in another table. The lookup script would convert the key data from the first table, so that the converted value can be used to select key values in the second table.

More details about the script can be found here.

Name pattern

namePattern

String

n/a

Specifies the base table in the relationship defined by this rule.

Referenced application model

referencedApplicationModel

ApplicationModel

n/a

Specifies the application model from which the referenced table will be selected. This need only be supplied if it is different from that of the base table.

Referenced columns

referencedColumns

String

n/a

Specifies a comma separated list of columns from the referenced table corresponding to the column list for the base table in the rule.

Referenced modelling version

referencedModellingVersion

String

n/a

Specifies the application model from which the referenced table will be selected. This need only be supplied if it is different from that of the base table.

Referenced table

referencedTableName

String

n/a

Specifies the referenced table in the relationship defined by this rule.

Referenced schema

referencedTableSchema

String

n/a

Specifies the schema of the referenced table in the rule. The value is automatically set to the modelling schema of the referenced application model version. This value cannot be edited.

Schema pattern

schemaPattern

String

%

Specifies the schema of the base table in the rule. The value is automatically set to the modelling schema of the application model version in which this rule is defined. This value cannot be edited.

Script language

scriptLanguage

Enum

JAVA_SCRIPT

This script language specifies in which language all scripts of the object are written. Currently the following languages are supported:

Groovy (GROOVY)

specifies that the code is written in Groovy.

JavaScript (JS)

specifies that the code is a JavaScript.

Referenced table sql condition

sqlLookupExpression

String

n/a

A referenced table SQL condition script can be used to restrict the number of rows extracted from a referenced table. Only rows which satisfy the condition defined in the expression will be extracted. It is possible to use variables such as ${schema} in the referenced table SQL script. This enables the user to employ custom parameters.

The expression has to be the body of a WHERE clause containing at least all referenced columns of the rule and one ? wildcard for each of these columns to be matched. Example: FIRSTNAME=? AND SUBSTRING(LASTNAME, 0, 2)=?

The expression has to contain at least all columns of the key of the referenced table, and it has to return true or false.
The referenced table SQL script only works on the referenced table. If the referenced table is a parent table of the base table, restricting the referenced table may lead to inconsistent data in the base table. To reduce the number of base table rows used for selection in the referenced table, use a reduction rule.

If the expression is completely independent of the actual key parameters, the expression may start with __generalCondition: followed by the actual expression. In this case the expression is only generated once and used for all keys. This enhances the overall performance. Example: __generalCondition: OTHER_COLUMN=1

If parts of the expression are independent of the key parameters, the expression may start with key parameters, followed by __generalCondition: and the independent expression. Example: FIRSTNAME=? AND SUBSTRING(LASTNAME, 0, 2)=? __generalCondition: OTHER_COLUMN=1

Tags

tags

Tag

n/a

Contains the tags that apply to this object. These tags can be used in the search to find objects quickly and effortlessly.

Extract traversal count

traversal

Number

-1

Defines how often a data relation rule is used in the selection process. By default the traversal is -1, that means there is no limitation. If the value of the traversal is 0, then the data relation rule would not be used.

For example, suppose there are two tables with a data relation rule from table one to table two and a data relation rule from table two to table one. With the traversal you can define how many times these rules are used to select data. Let’s say both have a traversal value of 1, that means the selection would go from table one to table two and back to table one, but not any further.

If one or more data relation rules which reference tables belonging to the installed application have a traversal value greater than -1, then this can result in more memory usage and longer runtime.

Actions

The available actions are described below. Some actions apply to the list, while others are specific to selected data relation rules.

List Actions

The following actions are available on the data relation rules list. If the action is disabled a tooltip will provide the exact reason for the deactivation. The required permissions are described in detail for each action.

  • Bulk Delete

  • Create

  • Export CSV

  • Import CSV

  • List History

Delete the selected objects.

The following options are available:

Cascade

Recursively delete depending objects.

When using cascade, dependent objects are deleted first also with cascade enabled. Thus, a cascade deletion is a recursive function that deeply searches for dependent objects and deletes them first. There is only a confirmation for the first object. The dependent objects are deleted without confirmation but only when the user has the DELETE permission.

This feature is only available in development mode. More information about development mode can be found in the chapter User Settings. It should be used with caution.

An object in the result list can have two different states, these are:

DELETED

The object could be deleted.

NOT_DELETED

The object could be not deleted. This may be because the executing person does not have a delete permission on the object or the object is still referenced by others. A detailed reason can be determined with the help of the error message. If the object is still in use, these objects are also displayed.

The following permissions are required on the list:

  • READ

  • WRITE

Creates a new object in the current list. Depending on the object type either a popup dialog is shown for the most important settings, or the complete object is shown in edit mode. The dialog provides the option to create the object and remain in the current list or to switch to the newly created object in edit mode to perform further changes.

The following permissions are required on the list:

  • CREATE

Exports the current list in CSV format. This will start a download operation for your browser.

The following permissions are required on the list:

  • READ

Creates new objects in the list from a CSV file. The format must comply with the format produced by the export. All imported objects will be added to the list. The import terminates with an error message if an object with the same name already exists and Replace rules is set to false.

Replace rules

The Replace rules option determines whether a rule is appended or replaced. If set to true, all current rules will be replaced with the new rules, otherwise the new rules are appended to the existing rules.

The following permissions are required on the list:

  • WRITE

The history list tracks all modifications made to objects within it. A new record is added each time an object is created, edited, or deleted. A record indicates who made the change, which object was affected, and when the change was made.

For more information about the concept of the history refer to the history concepts.

The following permissions are required on the list:

  • READ

Object Actions

The following actions are available on specific data relation rules. In order to execute the action, the user must possess the necessary permissions for the object. The permissions required for each action are described individually. If the user does not have these permissions, the action will be disabled and the tooltip will provide the exact reason for the deactivation.

  • Check

  • Delete

  • Duplicate

  • Edit

  • Object History

This action validates the object and its dependencies, reporting configuration errors that could cause issues during task or workflow execution. The validation will cascade through the child objects of the checked objects and objects referenced by them.

For instance, if an installed application of an environment is checked, the check will process the application model, the specified version, the connection, modification sets, and involved modification methods. If an object has rules, all active rules will be checked. The modeling connection and version, including their modification sets and methods, will also be checked. Deactivated objects will not be included in recursive checks, but can be checked individually if the check is executed on the object itself.

Checks often require additional information from the context of the objects being checked, such as necessary connections or custom parameter values. The check will gather information from the objects being checked and use it to perform checks on child objects. Any required additional information must be provided before the check begins. The check queries the user to provide these missing information.

Database object checks

For all rules which reference database objects such as tables, columns, etc, the check verifies that the those objects exist in the database system. If a connection can be inferred from the context, then this connection is used. If no connection is available in the context, it must be specified before the check is executed.

Connection checks

For objects which configure access to external systems, such as connections or storage locations, the configuration check verifies that access can be established using the given credentials. Furthermore, additional operations on database connections are performed to check whether the credential user has the necessary authorization to access relevant database objects. In particular, the credential user’s permission to read source tables and write to target tables is verified. Similarly, for storage locations the check verifies that the credential user has permission to write to the working directory.

Code checks

For all entities containing code segments, such as modification methods or condition scripts, the syntax for the code is checked. This does not check, however, whether at run time all necessary variables are likely to be available.

The following permissions are required:

  • READ

Delete the object. If the object is still used by another entity, an error message is displayed, and the object is not deleted. The delete operation must be confirmed in a separate popup.

The following options are available:

Cascade

Recursively delete depending objects.

When using cascade, dependent objects are deleted first also with cascade enabled. Thus, a cascade deletion is a recursive function that deeply searches for dependent objects and deletes them first. There is only a confirmation for the first object. The dependent objects are deleted without confirmation but only when the user has the DELETE permission.

This feature is only available in development mode. More information about development mode can be found in the chapter User Settings. It should be used with caution.

The following permissions are required:

  • READ

  • WRITE

Will create an exact copy of the current object with a different display name in the same list. Users can decide whether they want to copy child objects like rules, permissions or tasks. It is only possible to select complete classes of objects and not to select individual child objects. Copied child-objects will preserve their display name. The default is to copy all child objects.

The following permissions are required:

  • READ

  • WRITE

Opens the current entity in edit mode.

The following permissions are required:

  • READ

  • WRITE

The history displays all changes made to the respective XDM object, including any changes made to its rules.

Each change record includes information about the operation performed (e.g. CREATE, UPDATE, DELETE), the timestamp, and the user responsible for the change.

For more information about the concept of the history refer to the history concepts.

The following permissions are required:

  • READ

Appendix

Traversal example

To illustrate this with a practical use case, here are a few examples. Here we have two tables. A table PERSON and a table PERSON_RELATIONSHIP. In each of the following scenarios there is a cyclic relationship defined between the two tables using two data relation rules with the specified traversal settings. The start table is PERSON and the start condition is: PERSON.PERSON_ID = 1.

PERSON

Person_ID

Name

First_Name

1

Schmidt

John

2

Schmidt

Hans

3

Schmidt

Olivia

4

Williams

Charlotte

PERSON_RELATIONSHIP

Person_ID

Ref_Person_ID

Relationship

1

2

is father of

2

3

is married to

3

4

is cousin of

Example 1

Name

Base table

Columns

Referenced table

Referenced columns

Traversal

DRR1

Person

Person_ID

Person_Relationship

Person_ID

-1

DDR2

Person_Relationship

Ref_Person_ID

Person

Person_ID

-1

In this scenario both data relation rules would be traversed as often as possible. The result is that every row to which we can navigate via a data relation rule would be selected. All rows in both tables would therefore be selected.

Example 2

Name

Base table

Columns

Referenced table

Referenced columns

Traversal

DRR1

Person

Person_ID

Person_Relationship

Person_ID

0

DDR2

Person_Relationship

Ref_Person_ID

Person

Person_ID

?

The Traversal value for DRR2 is not relevant in this case, because DRR1 traversal is zero. As a result no rows would be selected from PERSON_RELATIONSHIP. Only rows in PERSON which satisfy the start condition would be selected. In this case only one row.

PERSON_SELECTED

Person_ID

Name

First_Name

1

Schmidt

John

Example 3

Name

Base table

Columns

Referenced table

Referenced columns

Traversal

DRR1

Person

Person_ID

Person_Relationship

Person_ID

1

DDR2

Person_Relationship

Ref_Person_ID

Person

Person_ID

1

In this case each data relation rule is traversed precisely once.

PERSON_SELECTED

Person_ID

Name

First_Name

1

Schmidt

John

2

Schmidt

Hans
PERSON_RELATIONSHIP_SELECTED

Person_ID

Ref_Person_ID

Relationship

1

2

is father of