A clinical-stage biopharmaceutical client needed a solution for ensuring the Logical Integrity of their skid-based data.
After examining the current data collection process and database structure, it was determined that the client did not have a consistent way to extract the data into reports for more valuable analysis and understanding of their batch process execution. The database structure and data collection needed to be changed in order to provide reliable and trustworthy data throughout its lifecycle. This included optimizing the processes for defining the tags that need to be collected, as well as where/when in the process the collection was required. It also meant that the database structure needed to be changed to redefine Entity, Referential, User-Defined, and Domain Integrity.
The old system was collecting every tag needed at 5 second intervals and the key field was the date time stamp.
This caused duplication and overload of non-process critical data that was not beneficial to their needs; causing large storage requirements and resulting in slow replication of data from the process to the main centralized database.
A key capability of data collection is defining the context of each necessary data tag as a Setpoint, Process Variable, or Summary value. Doing this allowed for:
- Setpoints to be gathered at a start of the process, as well as on an exception-basis to capture all in-process changes.
- Process Variables to be collected on an exception-based criterion with configurable tolerances within the context of the batch process execution.
- Summary data to be collected at the end of the process.
The relational database design included multiple tables; parameter definition, main configuration Setpoints and Summary values, and Process data. Each table was defined in a way to allow key fields and foreign keys to link the relations to each table to ensure Entity integrity and Referential integrity. This ensured unique values were used to identify data, guarantee accuracy, and consistent extraction. The main configuration table was used for domain integrity to make sure columns were acceptable values or legitimate values for a process. In addition, the configuration and parameter table provided capability of user defined integrity.
The immediate benefits achieved were a drastic reduction in the storage requirements associated with the data collection, reduction in network traffic bandwidth requirements due to optimized collection methodologies, and standardized structure for any/all reporting criteria.
The new design also led to other benefits such as scalability to add equipment, processes within a batch model, and requisite parameter definitions without having to change the database table layout. The client can now easily change the desired report format and content by entering information in the parameter table.
The outcome was population of the new design tables and validation. There were many new reports developed to view the process and for validation of product. The reports and new database design allow for real-time, in-process batch review as well as post-Batch Summary report analysis.