Configuring EDQ Case Management

4 Configuring EDQ Case Management

This chapter describes how to configure to use Case Management.

This chapter includes the following sections:

Case Management supports the manual investigation of results from data quality processes. Using Case Management, privileged users can manage and review matching results using highly configurable workflows.

The complete set of Case Management extended attributes that are used on an server are configured in the flags.xml file in the oedq_local_home/casemanagement directory. This file must be modified to add new extended attributes, and to define rules for how these attributes are populated.

An additional property file named flags.properties accompanies the base flags.xml file and specifies the labels for the extended attributes as they will appear in the graphical user interface (GUI). The settings in this file may be overridden for a specific client language by the creation of additional property files with an ISO 639-1 language code, such as flags_en.properties (for English) or flags_de.properties (for German). This language code is described at the ISO website found at http://www.iso.org/iso/home/standards/language_codes.htm.

If Oracle Watchlist Screening is installed, these files may already exist.

To ensure that Case Management publication works correctly, the flags.xml file is overwritten whenever a Case Source is imported using the Case Management Administration application. This is because Case Sources have a dependency on the format of the flags.xml file and requires the flags to be indexed and specified in the same way as on the server where the Case Source was defined. Oracle recommends that you back up the file before importing a Case Source in case there are any existing extended attributes in the flags.xml file on the server that need to be re-added once the import is complete.

4.1 Understanding and Adding Extended Attributes

This section describes the different types of extended attributes and how to add them for use in Case Management.

4.1.1 Default Extended Attributes

In an initial installation, the flags.xml file contains the following two extended attribute (flag) example definitions:

<f:flag index="1" label="%escalation" type="boolean" default="false" notnull="true"/>

<f:flag index="2" label="%priority.score" type="number" readonly="true"/>

Note:

The order in which these properties appear in each line may not match this example. The order of properties is immaterial. Also, if Oracle Watchlist Screening is installed, the contents of the flags.xml file is different.

4.1.2 Adding New Extended Attributes

To add a new extended attribute, add a line immediately after the existing attribute definitions in the flags.xml file, following the same syntax as the existing lines and using the following notes for each property:

Property	Allowed Values	Notes
`index`	Integer	Must be unique for each entry in the file
`label`	Any	The% character is used to indicate that the label for the UI should be retrieved from the `flags.properties` file for the client locale. If the% character is not used, the label will always be exactly as stated (in all languages).
`type`	`number`, `boolean`, or `string`	Controls the data type of the column.
`readonly`	`true` or `false`	Controls whether or not privileged users can edit the value of the extended attribute when editing a Case or Alert
`notnull`	`true` or `false`	Controls whether or not Null values are allowed in the extended attribute. If this is undefined, Null values are allowed (the same as the 'false' setting).
`default`	Any permissible value	Sets the default value of the extended attribute if not set to a specific value.

There is a character limit of 80 characters for extended attributes with a type of 'string'. Values longer than this cannot be inserted as values.

4.2 Configuring Data Entry Validation

You can restrict the format of user-specified data for an extended attribute. The restriction is checked when users edit extended attributes in the Case Management GUI, and when defining possible values to set for an extended attribute in the Workflow editor in Case Management Administration.

The restriction is not checked when cases and alerts are written to Case Management from a process, so it is possible to write invalid values into an extended attribute. The invalid values will appear in error, with an appropriate error message. This designed behavior protects the system against unnecessary job failure.

Restrictions are defined as part of the flags.xml file. There are two types of possible restrictions:

Predefined list means that the data to be written is checked against a predefined list of allowed values.
Regular expression means that the data to be written is checked against a regular expression.

4.2.1 Checking Predefined List Restrictions

To check that the data being entered into the extended attribute matches a predefined list of possible values, add XML elements in the following format after the definition of the extended attribute (flag):

<f:restrictions>	
<f:predefined>	
<f:value>first value</f:value>	
<f:value>second value</f:value>	
<f:value>third value</f:value>	
</f:predefined>	
</f:restrictions>	
</f:flag>

For example, the following XML fragment defines a custom 'Status' extended attribute that allows only the values 'active' and 'inactive':

<f:flag index="6" label="Status" type="string" readonly="false">	
<f:restrictions>	
<f:predefined>	
<f:value>active</f:value>	
<f:value>inactive</f:value>	
</f:predefined>	
</f:restrictions>	
</f:flag>

The extended attribute appears with a list of the valid values in the Case Management Edit Case (or Edit Alert) dialog:

Description of ext_attr_picklist.png follows

Description of the illustration ext_attr_picklist.png

Tip:

In this case, the user can specify a Null value for the Status field (as a 'notnull' condition was not set).

4.2.2 Checking Regular Expression Restriction

To check that data being entered into the extended attribute matches a regular expression, add XML elements in the following format after the definition of the extended attribute (flag):

<f:restrictions>	
<f:regex ignorecase="false" matchby="w">	
<f:value></f:value>	
</f:regex>	
</f:restrictions>

Where: the value property defines the regular expression, and the ignorecase and matchby properties defines how it is matched. The possible values for the matchby condition are as follows:

Value	Description
`w`	WHOLE - The whole value must match the Regular Expression.
`s`	STARTS - The beginning of the value must match the Regular Expression.
`e`	ENDS - The end of the value must match the Regular Expression.
`c`	CONTAINS - The value must contain a string that matches the Regular Expression.

For example, the following XML fragment defines a custom 'National ID' extended attribute that allows only values in the format NN-NN-NNN (2 digits, hyphen, 2 digits, hyphen, 3 digits):

<f:flag index="7" label="National ID" type="string" readonly="false" notnull="true">	
<f:restrictions>	
<f:regex ignorecase="false" matchby="w">	
<f:value>\d{2}-\d{2}-\d{3}</f:value>	
</f:regex>	
</f:restrictions>	
</f:flag>

The following shows the error message displayed when a user attempts to add a value that does not match the regular expression:

Description of ext_attr_regex.png follows

Description of the illustration ext_attr_regex.png

It is also possible to customize this error message with the errormessage attribute. Either enter a simple text string to be displayed as the error message, or begin the string with a percent (%) symbol to direct the application to look in the flags.properties file for a localized value.

For example, the following XML fragment causes the e1.message error message to be retrieved from the flags.properties file when an error occurs:

<f:restrictions><f:regex ignorecase="false" matchby="w" errormessage="%e1.message"><f:value>\d{3}-\d{2}-\d{4}</f:value></f:regex></f:restrictions>

4.3 Understanding Case Management Configuration Properties

This section lists the main parameters in director.properties that are used to configure Case Management.

Parameter	Description0
`case.management.fail.on.long.flags`	This property controls the Case Management behavior when flag values that are longer than 80 characters are generated. If this property is set to `true`, the process will generate an error and will stop. If it is set to `false`, long flag values will be truncated and a warning will be written to the log file. This property is set to `false` by default.
`cm.index.queue.limit`	This property controls the maximum size of the index queue limit.
`index.directory`	This property allows an absolute path for the Lucene index directories to be configured. By default, the index directories are always created within the `localhome` directory. In some circumstances, these directories can become very large, and storing them in a separate location may facilitate better management of disk space.

4.4 Reporting for Case Management Filter Execution

This appendix describes how to extract information about Case Management filter execution in Oracle Enterprise Data Quality.

EDQ includes trigger points that are called at the start and end of each filter execution. Information about the filter execution is passed to the triggers, which can then be sent to a streaming service such as Java Message Service (JMS), Oracle Cloud Infrastructure Streaming service, and Amazon Kinesis Data Streams.

The trigger paths are as follows:

/casemanagement/filter/start
/casemanagement/filter/end

Run functions in triggers accepting these paths are defined as shown below:

function run(path, id, env, json) {
 ...
}

The json argument is a stringified JSON object containing the following attributes:

Attribute	Description
id	Internal filter execution ID
filter	String representation of the filter
type	Possible values are `search_filter`, `report`, `bulk_update`, `bulk_delete` and `export`
xaxis	String representation of the report X axis (present for reports only)
yaxis	String representation of the report Y axis (present for reports only)
server	Name of the server where the filter was executed
userid	Internal numeric ID of the user running the filter
user	Name of the user running the filter
userdisplay	Display name of the user running the filter
start	Timestamp of filter start
duration	Filter execution time in milliseconds (present for end calls only)
status	Indicates the reason if the filter did not complete successfully
sql	SQL statement used for the filter (SQL execution mode only)
args	Array of SQL bind arguments

The args attribute in a report for a SQL execution contains the bind values which replace the '?' placeholders in the SQL text. Each entry in the array contains the following:

type

string, number or date

value

argument value

Example

Suppose a trigger sends filter execution end reports to JMS, an OCI notification service topic, an OCI stream and a Kafka topic.

addLibrary("oci");
addLibrary("jms");
addLibrary("kafka");
 
var oci    = OCI.create("OCI 1")
var topic  = oci.topic("ocid1.onstopic.oc1.phx.aaaaaaaa....")
var stream = oci.stream("ocid1.stream.oc1.phx.aaaaaaaa....")
 
var props  = config.loadTriggerProperties("jms", "jms\\.properties");
var jms    = JMS.open(Object.assign({}, props, config.loadCredentials("", props)))
var kprops = config.loadTriggerProperties("kafka", "kafka\\.properties");
var kprod  = KAFKA.producer(kprops.topic, kprops)
 
function getPath() {
  return "/casemanagement/filter/(start|end)";
}
 
function run(path, id, env, json) {
  if (path.endsWith("end")) {
    topic.publish("filter done", json)
    stream.publish(null, json)
    jms.send(json)
    kprod.publish(null, json)
  }
}

Note that the script trigger framework uses one instance for general queries and additional instances for execution. The first instance will never be triggered and thus resources such as JMS connections are not needed. The framework creates a built in variable runnable which is false for the single configuration instance. To optimise resources, this can be used as shown below:

var jms = runnable && JMS.open(Object.assign({}, props, config.loadCredentials("", props)))
...
function run(path, id, env, json) {
  ...
  jms && jms.send(json)
}

4.5 Configuring External Storage for Case Management Attachments

In EDQ 14.1.2 there is a new option to store Case Management attachment data in the file system or cloud storage. New attachments are stored externally, while existing attachment data remains in the database table. You can also use REST APIs to migrate data to external storage or move external data back into the database.

4.5.1 Configure Storage in the File System

To configure case management storage in the file system, add the following to director.properties:

case.management.attachment.storage.directory = pathtodirectory

If the directory path is not an absolute path, the directory is relative to the "local home" configuration directory. EDQ uses the internal ID of the attachment record in the name of the stored file. You can find the internal ID in the attachment_id column in the dn_caseattachments record. If you want to store the file using a different name, set the following:

case.management.attachment.storage.entryname = filename

The filename value can contain these substitutions:

Substitution	Value
${id}	The internal ID of the attachment. This is always required.
${source}	The source name. The source name must not contain any characters that are not valid in file names.
${caseid}	The internal ID of the associated dn_case record.

For example:

case.management.attachment.storage.directory = cmatts

case.management.attachment.storage.entryname = ${source}/${id}

Here the attachment data are stored in the directory named cmatts in the "local home" configuration directory, with file names constructed from the source name and internal ID.

4.5.2 Configure Storage in Cloud Storage Providers

To configure case management storage in the file system, add the following to director.properties:

case.management.attachment.storage.url = baseurl

EDQ constructs the full attachment URL by appending the entry name to the base URL. The entry name is formed from the case.management.attachment.storage.entryname value.

You can set the credentials for upload and downloads to and from the cloud by using any of the following:

case.management.attachment.storage.credentials = stored credentials name
case.management.attachment.storage.platform.credentials = true
If case.management.attachment.storage.platform.credentials is set to true, EDQ uses the credentials that are associated with the compute instance. EDQ supports platform credentials for AWS, OCI, and GCP.

Example for OCI Object Storage

case.management.attachment.storage.url         = https://mytenancy.objectstorage.us-phoenix-1.oci.customer-oci.com/n/mytenancy/b/cmbucket/o/
case.management.attachment.storage.credentials = OCI 1
case.management.attachment.storage.entryname   = cm/${source}/${id}

Example for Amazon S3

case.management.attachment.storage.url         = https://cmbucket.s3.eu-west-1.amazonaws.com/cm/
case.management.attachment.storage.credentials = aws1

GCP storage has different rules for download and upload URLs. To configure external storage on GCP:

case.management.attachment.storage.url    = https://storage.googleapis.com/storage/v1/b/bucketname/o/
case.management.attachment.storage.cloud  = gcp

4.5.3 REST API for External Attachment Storage

When you enable external attachment storage, the existing attachment data remains in the database table. You can use REST APIs that are provided as part of the Case Management Administration endpoint to migrate existing records to external storage, or move extermal data back into the database.

Migrate to/from external storage

POST http://server:port/edq/cmadmin/migrateattachments

POST http://server:port/edq/cmadmin/unmigrateattachments

The payload is a JSON object containing these attributes:

Attribute	Description	Default Value
start	The internal ID of the first attachment to process.	0
count	The number of records to process.	unlimited
batch	The number of records to process in a single database query.	10000

The migration runs asynchronously. The result to the call is a JSON object containing a key corresponding to the execution. The key may be used to poll for execution completion:

{ "executionkey":"a279513d1ea44b0198094ac31ae68258" }

Get conversion status

GET http://server:port/edq/cmadmin/conversionstatus/EXECUTIONKEY

This call gets the status of an asynchronous conversion. The EXECUTIONKEY component of the URL is the key that is returned from a previous migrate call.

The result is a JSON object with these attributes:

Attribute	Type	Description
complete	boolean	true if conversion is complete.
failed	boolean	true if an error occurred during conversion.
start	string	The timestamp when the conversion started.
end	string	The timestamp when the conversion completed.
error	string	Error message, or null for a successful conversion.
lastid	integer	The ID of the last attachment that is processed during the conversion.
processed	integer	The number of records processed. If this is zero, it means that all records have been processed.
converted	integer	The number of records that are converted.

The start and count attributes can be used to perform an incremental migration of a large system over a number of idle periods. Set start = 0 for the first call and lastid+1 for subsequent calls and choose a count that allows the process to run in a reasonable time.

4.5.4 Performance Considerations during Bulk Delete

The use of external storage should not affect user actions with attachments. However, when you delete cases and alerts in bulk you must delete the associated external items, whether they are files or cloud storage objects, individually. This could impact the overall execution time of the deletion.

Cloud storage objects are deleted by performing multiple calls simultaneously. The maximum number of deletions executed in a single batch is 10. You can change this by setting the following:

case.management.attachment.storage.deletebatch = batchsize