Interoperability
Leaving the kernel to build LIMS in the cloud

Eric Smith. Assured Flow Solutions. Houston, TX

Outline

Flow Assurance Lab

Building a data management platform

Data management issue

Commercial options

Alex

WL LIMS

database

storage

utilities

Where the Wolfram Framework could better serve Oil & Gas

Flow Assurance

Hydrates

Wax

Emulsions

Other

Scale

Asphaltenes

Chemical stability

Material compatibility

Corrosion

Fluid characterization

Data Management (Problem)

Network drives

Rigid hierarchy

Not indexed (on Windows)

Data not in a computable format

Can’t query data

Prone to human error

Limited information in file contents and metadata - rely on naming conventions

The inexplicable file path limit (on Windows)

TabView[{"Lab"->Pane[,800,ImageSizeAction->"ResizeToFit"],"Data"->Pane[,800,ImageSizeAction->"ResizeToFit"]}]

Data Management (Goals)

Database driven

Computable Data

Query by client, test, equipment, category (e.g wax, hydrate), fluid type, result values

Be able to perform data analytics on past results

Provide insights from past lab & engineering work

Data management

Reduce burden of lab technicians and chemists to determine an (ultimately unsatisfactory) folder structure

Reduce potential of losing correct data set

Lab efficiency

Streamline report generation

Reduce manual entry of stored information

Wolfram Hubris

Button[Style["Definition:",FontSize->18],CellPrint[The belief you’re more capable of creating a better product from scratch than those delivered by long-standing successful companies because you have the power of Wolfram Technologies],Appearance->"Frameless"]

Commercial solutions

There are over 200+ LIMS products on the market

They’re ideally suited for routine tests

e.g. 500 tasks per week, 5 different experiments/tests

Not ideal for specialty labs

AFS: 20 tasks per week, 70 different experiments/tests

Because AFS is an engineering firm with lab capabilities, our analysis is more than reporting numbers from a test. The analysis we do goes beyond spreadsheets. We’ve been using the Wolfram Language for years to process the output from lab equipment. Commercial LIMS are not suited to handle this application

Other option:

Wolfram Appliance

Alex

Solving the Excel utility nightmare

Style[Button[Style["Open Alex",FontSize->20],SystemOpen[FileNameJoin@{$UserBaseDirectory,"Applications/AFS - Alex.cdf"}],ImageSize->{200,60},Method->"Queued"],FontWeight->Bold]

Interoperability

PaaS

Cloud storage

AWS S3

Cryptography

HTTPRequest

Database connectivity

NoSQL: MongoDB

Python: ExternalEvaluate

JSON

Reporting

Report Generation

NETLink - Excel Connectivity

Report Templates

PDF Creation

Miscellaneous

OS & UI

File Streaming

System UI elements & customization

Deployment

Enterprise CDF

*Enterprise Private Cloud

MongoLink

Names["MongoLink`*"]

Lab Collections
	•  Clients
	•  Projects
	•  Samples
	•  Tests

mdbFind["Samples",<|"Sample ID"->"19AFS03001"|>,<|"Tests"->0|>]

mdbFindOne["Tests",<|
"Test"->"Capillary Flowloop Stability Test - 7 Day",
"Test Date"-><|"$gt"->DateObject[{2019,9,1}],"$lt"->DateObject[{2019,9,30}]|>|>];
mdbSample[<|"_id"->%["Samples"][[1]]|>]

Surprises

Certificate Authority

$MongoDefaultCAFile

JSON Compatibility

ImportString["{ \"myField\" : { \"$elemMatch\" : { \"$gt\" : 1, \"$lt\" : 2} } }","RawJSON"]

ExportString[<|"scalarField"->2,"arrayField"->{1,2,3},"ObjectField"-><|"a"->1,"b"->2|>|>,"RawJSON"]

Python

import pymongo
pymongo.version

scheduler_database = client.Scheduler

scheduler_database.collection_names()

Get the Queue and LicenseInfo collections.

queue_collection = scheduler_database.Queue
license_info_collection = scheduler_database.LicenseInfo

Recent updates

12.0 compatible with DateObject

11.3

mdbUpdateOne["Sample",<|"Sample ID"->"19AFS03001"|>,<|"$set"-><|"Date"->"2019-09-01"|>|>]

12.0+

mdbUpdateOne["Sample",<|"Sample ID"->"19AFS03001"|>,<|"$set"-><|"Date"->Now|>|>]

Works with queries

mdbFind["Samples",<|"Date Entered"-><|"$gt"->Now-Quantity[15,"Days"]|>|>,<|"Sample ID"->1|>]

Cloud Storage

Undocumented Libraries

Up to 11.3: AWSLink`

12.0: ExternalStorage`

Using REST API - Cryptography

Functions Used: BaseEncode, BaseDecode, Hash, BitXor, StringToByteArray, URLBuild, HTTPRequest, ByteArray, ByteCount

Building an HTTPRequest

<|
  "content-length" -> ByteCount[aContent],
  "content-type" -> aContentType,
  "host" -> mBucketURL,
  "x-amz-content-sha256" -> "UNSIGNED-PAYLOAD"(* mContentHash*),
  "x-amz-date" -> mTime (*GMT*)
|>//KeySort

publicKey="***********",
privateKey="******************************",
scheme="AWS4",algorithm="HMAC-SHA256",terminator="aws4_request",service="s3"

mAllHashes=FoldList[hmacBase64["SHA256",#2,#1]&,scheme<>privateKey,{mDate,aRegion,service,terminator}];
mSignature=hmacBase64["SHA256",mStringToSign,mAllHashes[[-1]]];
mSignatureString=StringJoin@@((IntegerString[#,16]&/@Normal[BaseDecode[mSignature]])/.x_/;StringQ[x]&&StringLength[x]==1:>"0"<>x);

mSite=URLBuild[{"https:////",mBucketURL,mKey}];
mHTTPRequest=URLExecute[HTTPRequest[mSite,<|Method->"PUT","Headers"->mHeader,"Body"->mContent|>],TimeConstraint->10];

Report Templates

Excel issues:

WL → VBA

Lot’s of manual coding to translate each value to an Excel cell/range

COM Objects

Slow

Alignment different in 365 vs 2016 vs 32 bit vs Windows/Mac

No “Repeating blocks”

Row[{Button["Open Template",SystemOpen["~/Alex/LabUtilities/HPViscometer/HP Viscometer Template Notebook.nb"]],Button["Open Report",SystemOpen@"/Volumes/GoogleDrive/My Drive/Presentations/20191024-AFS-031305-test.pdf"]},Spacer[10]]

Report Templates - Pros

Stay in the WL

Translate data to visualizations and custom formatting easier

Repeated blocks

Script document formatting (Headers/Footers)

Report Templates - Cons

Formatting: Ems to Printer Points to Inches don’t work (ticket open)

Page Header settings need to be evaluated twice to pick up template slots

Repeated blocks don’t work well with CellPrint, and formatting doesn’t work well without CellPrint

Demo

Reduce Reundant Effort, improve QA/QC

TabView[{"Old"->,"New"->}]

Thoughts on the Wolfram Cloud

Interoperability is not well supported

NETLink can’t be used (Security reasons)

MongoLink can’t be used

AWS/ExternalStorage not supported

SystemModel not supported

Cloud limitations

Forms are limited in capability and issues arise with SystemDialogInput

Formatting of graphics and UI elements doesn’t translate well from desktop

Oil and Gas security concerns make it a tough deployment option

Enterprise Private cloud

Sandboxed by default - Similar limitations

SystemModeler not supported

Too costly to justify this application with our limited scope

Conclusions

AFS has built a data management platform that serves our lab utilities

The data management platform is built on PaaS solutions and can be customized for other companies and applications

We are streamlining lab experiments from proposal to report generation with the Wolfram Language

Interoperability in the Wolfram Language is powerful and more complete than I imagine most users think it is. It’s a testament to Wolfram’s vision of maintaining a development platform that doesn’t require library hunting

“Figuring out how other people want to use tools and creating workflows that are broadly useful is one of those long-tail development problems that open source typically leaves to the user to solve.” - Jon McLoone