Implementing B2MML In LIMS
By Jeff Vannest, Senior Consultant
In the process of implementing an ERP to LIMS interface, one of the challenges to overcome was how to clearly communication between Oracle E-Business Suite (EBS) and SQL*LIMS v5. In the past, I had been exposed to several methods of interface communication, text files containing single bits of data, records inserted directly into a database, and concatenated strings transmitted over a network. Each of these methods had at least one serious flaw: if the data structure changed the interface stopped working. This article will review how Business To Manufacturing Markup Language (B2MML) was used to robustly transfer data and instructions between an ERP and LIMS system.
History
In late 2008 I had the opportunity to meet Dennis Brandl, member of the ISA-SP88 committee and World Batch Forum (WBF) Working Group. The context of the meeting was to help a global pharmaceutical manufacturing company use B2MML to communicate with auxiliary systems, including the company’s LIMS. While the application of how B2MML might be used to express and communicate ERP data and decisions was clear, the task of teaching the LIMS system how to consume this data and return communication using B2MML was less clear. The company had already settled on using web services whenever possible, so my job was to work out the specific technologies that might be used to accomplish the task.
Challenge
After reviewing the B2MML schemas, it became clear that the level of possible detail would be overwhelming. Further, while the schemas presented data using rational and coherent relationships, the overall data model looked nothing like LIMS data. For example, in a LIMS system, lots and samples are frequently created using information pulled from different parts of the system: a sample may require an attribute stored on the material, a lot may require an attribute stored on the lot plan, etc. When modeled into B2MML, the data required to log a LIMS lot might be spread out over several schemas, which made the creation and update of lots complex. To make the matter even more difficult, the LIMS needed to communicate back to the ERP using B2MML, which meant that the mapping of ERP data to LIMS data had to be bi-directionally accurate.
Solution
To solve this dilemma, I chose to write an XML schema that described the SQL*LIMS lot and included the relationships to essential LIMS information such as lot dispositions, formulations, specifications, etc. With the LIMS data model fully described in an XML schema, I chose to use Extensible Stylesheet Language Transformations (XSLT) to convert B2MML XML data into SQL*LIMS XML data. Therefore, when a web service received B2MML data from the ERP, an XSLT Stylesheet was used to transform the data into the LIMS’ schema and it was written to a table in the Oracle XML Database, which was added to the database running the LIMS system.
This solution allowed all custom LIMS code to be read and written to a single data specification. In other words, instead of programming the LIMS to look for “ERP Datum A” and place it into “LIMS Datum C”, all code was simply written to read and write data to known locations. If the ERP system chose to move “ERP Datum A” to “ERP Datum D”, no LIMS API code needed to be modified. Instead, the XSLT Stylesheet was simply updated and placed on the LIMS web services host. Since changes to an XSLT would not be considered a “code change”, only brief acceptance testing would be conducted to verify that the flow of data was not interrupted between systems. Additionally, the ERP system could start sending new data at any time and the LIMS project could work on its own timeline without being concerned that the change in data would adversely affect the overall system.
Conclusion
I am not aware of any LIMS system that has integrated B2MML communication. However, with a little help from web services and an XML schema mapping program, I believe any LIMS system could take advantage of this rich resource.
