User Defined Tables
Nominated Owner: @geoff
Summary
There are many use-cases that require user-defined table. An example of this is reason codes for Material put on quality hold.
In the ISA-95 standard, there are no generic look-up lists. This is understandable as the ISA-95 standard is an Integration message model standard, and in an integration message, something like the reason for a coil being on hold is passed as a string property value. The integration spec is not concerned with the implementation details of how the reason got into the message.
Configuring user-defined lookup lists requires creating extensions to the ISA-95 schema to hold this data.
Retrospective
Motivation
- Use Cases:
- Reason Code lists
- Cause Code Lists
- Any other user-defined lookup list
Guide-level explanation
- New Named Concepts:
- User Defined Lookup List - A table of information that the user can define. the user can define the fields and the data types for the fields. It is assumed that the lists are stand-alone, and not related to any of the standard ISA-95 objects (Perhaps other than HierarchyScope?)
- Lookup lists should be able to be hierarchical self-related lists (I.e. they should have a parent-child relationship)
- Lookup Lists are intended to be used in application logic, either in BPMN Processes, custom microservices or Front-end applications.
- Example: Quality Hold Reason List:
- A User-defined list of possible Quality-Hold reasons for a particular site. The User is able to define fields of “Code” as a unique numeric, non-nil value, “Name” as a non-nil text field, and “Description” as a nillable text field.
- Delay Reason Code: Reason codes are often defined in a hierarchy, with organisation-wide standard codes at the top of the hierarchy, and more specific reasons that might be different by site or production unit at the leaves of the hierarchy. A user can define a delay reason User Defined Lookup table that has parent-child relationship. The User can define the fields and data-type for each field in the lookup table.
Reference-level explanation
- Frontend:
- The user is able to define “User Defined Tables” as items in the Admin UI. When the user adds or edits a User Defined Table the Frontend would call the BAAS schema alter endpoint with the change to the schema. BAAS will dynamically generate and publish the changes to the GraphQL Schema.
- The change to the schema will need to trigger the re-generation of the Federated Schema in the Apollo Router
- The user is able to add rows of data to the table which will use the newly generated GraphQL API. Data is stored in the newly defined type in BAAS.
- Editing the schema: GraphQL Changes MUST be backward compatible. The backward compatibility checks will need to be performed in the backend
- The frontend will need to dynamically build a CRUD page for the user defined table. This could be done by introspecting the GraphQL API. @vishnu.chander is this possible?
Drawbacks
- Source control - How do we store these table definitions in source control and deploy them across environments?
Rationale and alternatives
- Alternatives:
- Define a Rhize schema that stores the definition of a table, and a schema that would store the fully normalised form of the data to be stored in the table allowing the user to define the table and the data in the table without the need to product schema changes:
- Very complex queries
- Does not make use of the protform’s capability of generating the api based on a schema
- Data Validation rules need to be written in backend code rather than making use of the GraphQL Schema to validate.
- Define a Rhize schema that stores the definition of a table, and a schema that would store the fully normalised form of the data to be stored in the table allowing the user to define the table and the data in the table without the need to product schema changes:
- Store the schema changes in source control, and establish a CI/CD pipeline that deploys schema extensions to the platform rather than having schema changes as a user-configurable capability. Defining table is a platform administration action like depoying a custom microservice rather than a user action like definiing an Equipment Class or BPMN Process.
Prior art
- using OperationsEventDefinitions to store lookup list values. OperationsEventDefinitions are an ISA95 standard type, which have classes and properties. Some Rhize implementations have used OperationsEventDefinitions to store list of lookup items. OperationsEventDefinitionClasses are configured for each of the different lookup lists, with OperationsEventDefinition for each lookup item. The solution that used this approach had classes for Scrap reasons, Quality Hold Reasons, and Downtime Reasons.
For Downtime Reasons, the approach has some merit, and the OperationsEventDefinition is able to be linked to a WorkMaster that defines the work required to be done for a particular type of delay.
An example of this is a Delay for “Product Changeover”. There is work being done here, the production line is being set up for the new product. That work can have an expected duration, That expected duration can be used to trigger an escalation if the changeover takes longer than expected.
The work master can be used to hold task lists, check lists or other Work-instruction type information that may relate to the work being done.
There can be some debate about if a delay reason constitutes an OperationsEvent. Based on the ISA-95 definition is can make sense:
Operations event information is generated as result of the occurrence of a real-world event that warrants notification to interested parties. Operations event information is published as time stamped notifications using the operations event information exchange object. The operations event exchange explicitly includes the process context of the real-world event and all pertinent information actioned by the publisher that is associated with the real-world event. The subsequent processing of operations events by subscribers is not of concern to the operations event publisher.
Unresolved questions
Future possibilities
- Time Series Data Collection Sets. The same approach could be used to define the schema and GraphQL API for writing user-defined data sets to InfluxDB @andy.german @daniel.becker