An external partner (a preclinical lab services provider) runs compound safety studies for our Client on a regular basis, with more than a hundred studies per year. Each study report comes back electronically within two days of study completion, in a package of one summary report and a dozen data files. The data formats vary substantially by file: from a simple table of animal IDs and weight by cage to transcriptional biomarker data in many thousands of rows in Excel.
Because of the complexity of the studies and their multi-faceted design, a team of people run the experiments, capture the data, and compile the study results into multiple data files. Mistakes do occur regularly (e.g. wrong file sent, wrong animal IDs are included in the cage/study/group table).
An automated algorithmic data workflow runs in real time.
In this project we designed and implemented, then iteratively improved a data parser/validator/loader algorithm that processes all study results in real time as they come in. The data undergoes approximately one hundred validation checks when it is electronically received from the external partner. Some violations generate warnings (to be reviewed by the Client's pharmacology team). Some violations generate critical data integrity alerts and lead to a re-submittal of study results by the external partner.
When a study passes all checks its data is algorithmically reformatted and loaded into the Client's corporate database to be further reviewed by domain experts. The Spotfire views provide detailed references back into the raw data so that the reviewers can immediately pinpoint which file contained errors or inconsistencies.
Scientists now can access study data spanning multiple years, all at once. This was near-impossible to do before because the data was scattered across many spreadsheets which were overwhelming in their number.
