Introduction

Files generated by laboratory instruments are retrieved from sites' File Servers, since every LabPC is backuped through a service named "PeerSync".

The path for retrieving the LabPC folder is the following: \\%FIleServer%\LABO\%LabPc Name%

Security

Every folder in each site' File Server is managed through Active Directory Security: an Active Directory group is defined for granting read permission to the users that belongs to that group itself. For making it viewable by Talend, it's necessary opening a Service One ticket "LabPC / Support an Existing LabPC" to integrate Talend Service Account (DEV: EUA\SVC_TALEND_ADMIN_DEV; PROD: EUA\SVC_TALEND_ADMIN_PRD) within the security group.

Files Type conversion

Since Talend is capable of parsing TXT-like files (e.g., TXT, CSV, Excel, Doc...), sometimes it's necessary converting the software' proprietary format to a Talend-readable format.

Here below it's possible finding some techniques.

Lab Software automation through BAT files

This is the case of TRIOS files (Thermal analyses for Materials domain). The proprietary files are saved in "TRI" format, that cannot be read as a text file.

If the software could be called through Command Line, it's possible to develop a specific BAT file, that could perform the following tasks:

• searches within the folder where proprietary files are saved if there are new files (search filters: file extension; file creation date)
• converts, calling the instrument software through Command Line, the proprietary files to a new format, readable by Talend
• 
• saves the newly generated files within a the same folder, that will be read by Talend (using a filter on the "*.TXT" extension) as soon as PeerSync backups this data to the site' File Server

In case of TRIOS files, a possible BAT file is the following (the only parameter to change is the source path):

@echo off
setlocal enableextensions

set "source=C:\Users\IT93221\Desktop\DSC"

CD %source%

for /R %%I in ("*.TRI") do (
    
    for /f %%a in ('echo %%~tI') do (

        rem :: compare the two dates
        if "%%a" eq "%today%" (            
            "C:\Program Files (x86)\TA Instruments\TRIOS\trios.exe" /HideConnForm true /Type DSC25 /offline true /LoadFileExportToTextFullAndExit "%%I"
        )
    )
)

The last step consists in scheduling, through Windows Task Scheduler, this BAT on the LabPC itself. This action requires Admin rights on the LabPC, so it will be necessary opening a Service One ticket "LabPC / Support an Existing LabPC", for scheduling this task through the LabPC Service Account, EUA\U****** (where ****** is the LabPC name W-******) 

Extracting data from PDF Reports

This is the case of ICP files (Inductively Coupled Plasma mass spectrometry analyses for Materials domain). The proprietary files are saved in a PDF report, that cannot be read as a text file.

Through a script (language: VBS - Visual Basic Script) it's possible performing the following steps:

• Converts the PDF in a generic TXT file
• Parses the TXT file, to extract the necessary information (metadata / raw data) to be integrated in the Datalake
• Saves the necessary information in a CSV file, easily readable by Talend

In this case, a free PDF-To-TXT converter was used.

The script is the following:

Option Explicit 'force all variables to be declared

'Variables Declaration
Dim folderToSearch, extensionToSearch, folderTxt, strCurDir, currFileName, currentFilePath, currentCsv, decimalSeparator
Dim objFSO, arrayCompounds

Const ForReading = 1
Const ForWriting = 2

'Initial parameters
folderToSearch = "C:\Users\IT93221\Desktop\ICP\PDF"
folderTxt = "C:\Users\IT93221\Desktop\ICP\TXT"
extensionToSearch = "pdf"
decimalSeparator = "."

'Main Program

Set objFSO = Createobject("Scripting.FileSystemobject")

Recurse objFSO.GetFolder(folderToSearch)

Sub Recurse(objFolder)
    Dim objFile, objSubFolder

    For Each objFile In objFolder.Files
        If LCase(objFSO.GetExtensionName(objFile.Name)) = extensionToSearch Then
            currFileName = GetFilenameWithoutExtension(objFile.Name)
            GenerateTxtFile(objFile.Path)
            CompileExcel(currFileName)
        End If
    Next

    For Each objSubFolder In objFolder.SubFolders
        Recurse objSubFolder
    Next
End Sub

All the initial parameter could be changed (Folder To Search, Folder where to same the TXT files, Extension To Search, Decimal Separator).

The function "GenerateTXTFile" is the following:

Sub GenerateTxtFile(filePath)
    Dim oShell, command, strCurDir
    Set oShell = WScript.Createobject ("WScript.Shell")
    strCurDir = oShell.CurrentDirectory
    command = """" & oShell.CurrentDirectory & "\Converter\pdftotext.exe"" -table """ & filePath & """ """ & oShell.CurrentDirectory & "\TXT\" & currFileName & ".txt"""
    oShell.run "cmd.exe /S /C " & """" & command & """"
    currentFilePath = oShell.CurrentDirectory & "\TXT\" & currFileName & ".txt"
    Set oShell = Nothing
End Sub

And all the business rules for parsing and extracting metadata / raw data from the TXT file could be defined in the function "CompileExcel"

The last step consists in scheduling, through Windows Task Scheduler, this VBS file on the LabPC itself. This action requires Admin rights on the LabPC, so it will be necessary opening a Service One ticket "LabPC / Support an Existing LabPC", for scheduling this task through the LabPC Service Account, EUA\U****** (where ****** is the LabPC name W-******) 

Summary Results are taken from ALP instruments to Labware LIMS via a talend job 

→ WIP ..

🖌 Coatings

🏢 Coatings is a business unit under the GBU "Novecare"

Materials

🏢 Materials is its own GBU which regroup 2 BU : Specialty polymers et Composites Materials. 

Data Flow

1/Data Source:

• LIMS ( software used is Labware)
  LIMS is a tool used by the lab workers for reporting theirs experiments. It gathers all the data from the analysis.
• Instruments connected
  Used to measure result of test applied to the materials (eg: corrosion resistance, fatigue test, etc.). All data are archived into a local server located at each lab location. 

2/ Data integration via Talend

3/ Data warehouse via Google cloud platform

4/ Visualization via Tableau.

Sub_teams

⌨️  The development of the data source is outscope. We just go and take the data  

👨‍🚒 Sami is the focused data Eng.

👩‍🎨 Justine and Francisco are the data viz

Here is a nice explanation of the dashboard (from slide 5) :

What does Materials do ? 

In the GBU's

🔋 Battery 

🏢 Battery is a Growth platforms.

📍 4 labs : Aubervilliers, NOH, Bollate and EWHA. 

Sub_Dev Teams

⌨️ Romain handles the ELN development.

👨‍🚒 Ahcene, Smain are the focused data Engs. ( They handle the data from the ELN & Connected Instruments)

👩‍🎨 Justine and Francisco are the data viz

Main stakeholders : Jules Valente (AUB) , Stefano Mauri (Bollate), Guillaume Muller.

Data Flow

1/Source of data:

• ELN (Workbook) (For more information about the ELN, click HERE)
• Instruments connected 

2/ Data integration via Talend

3/ Data warehouse via Google cloud platform

4/ Visualisation via Tableau.

What does Battery do ?

In the battery lab, they are developing raw materials useful for battery manufacturer. 

There are 3 types of raw materials : 

• Inorganics (e.i: Sulfides,...)
• Polymers ( PVDF, ...)
• Organics ( e.i: LiFSI, SA076,... )

Each of these types has its own synthesis lab and thus, its own highly-confidential processes. It means that each lab has one relevant ELN workflow. 

(Once the synthesis have been made, it is necessary to test their characteristic)  

In BatMat they are working on several generations of Batteries:

• Aubervilliers (RICP) and NOH, work on Solid State Battery. 
• Bollate and EWHA work on liquid state battery. (Final product that Solvay are currently selling)

Ionic conductivity is the important measure for a battery

Aubervilliers and NOH (Solid State Battery)

• Key stakeholder : Jules valente

The lab of Aubervilliers is focused on developing a new material in order to create solid state battery. 

In the lab, they mix raw materials and solvent to create solid electrolytes (often refers as "the product"). After the synthesis, they need to characterize them with a series of test and most important, they will analyze their ionic conductivity ( = performance).

Afterwards, the product is sent to NOH lab. They will use the product as an ingredient in a formulation and then create a battery prototype.

At each step, it's really important to characterize the product and analyze their performance.

The Ultimate goal is to be able to identify trend, cause, etc. between what/ how a material has been done and the final result on the battery characteristic and optimize it. 

At AUB, there are three workflows:

• Mechanosynthesis of solid electrolyte
• XRD characterization of the solid electrolyte
• Ionic conductivity measurement of the solid electrolyte 

At NOH

ALB Tableau User Roles

ALB Tableau Access Rights by Role

Manual App Lab Booster User list

Here is the manual list that shows an updated list of all our users crossed ALB products:

https://docs.google.com/spreadsheets/d/1vE9wPHvCnbWI_-YBijwdu2rZixU-I4QEZh0nRtoqqVM/edit#gid=1200907161

New link

WIP :Personas

Goal

Identifying personas help us understand what projects fall under our scope and more importantly, develop what has the best value to our users,

Definitions

Here interviews made by the design studio