Aroma Pipeline & SQL View

Useful links:

ELN Aroma Folder list : \\ASEW1PTLNDENG01.oxo.priv\data\PROD\DATA_OCEAN_DOMAIN_RNI\Aroma\Input\ELNsubFolder.xlsx
Data model ODS: Luci - ELN / ODS entities.
Data model DM & facts: Luci - Aroma DM
Mapping doc (DMs & facts): Luci - Aroma Mapping (DM & Facts)

Layer 1:Ingestion & Processing

We run a daily scheduled pipeline that pulls data from the ELN and loads it through the usual Staging , ODS, DM flow.

You can find the full setup in TMC Project: WS_DATA_OCEAN_DOMAIN_RNI.

DT Initiatives for SCo > LB Aroma Workflow Integration > image-2025-12-19_12-10-42.png

As shown in the screenshot above, two plans are each connected to a separate task.

PL_DO_RNI_Aroma_STG_ODS_DM: (Daily)

DT Initiatives for SCo > LB Aroma Workflow Integration > image-2025-12-19_12-17-15.png

Talend Studio :

WS_DATA_OCEAN_DOMAIN_RNI

F001_RNI_Aroma_ELN_USP_Vanilin_STG_ODS_OrchFlow	F002_RNI_Aroma_ELN_USP_Vanilin_DM_OrchFlow

PL_DO_RNI_Aroma_Instruments_STG_ODS_DM: (Daily)

DT Initiatives for SCo > LB Aroma Workflow Integration > image-2025-12-19_12-18-45.png

Talend Studio :

WS_DATA_OCEAN_DOMAIN_RNI

F003_RNLI_Aroma_Instruments_STG_ODS	F004_Rnl_Aroma_Instrument_DM

On top of the DM and fact tables, a SQL view was created to consolidate all Aroma-related information.

DT Initiatives for SCo > LB Aroma Workflow Integration > image-2025-12-19_12-27-21.png

Note: The SQL view currently contains a known bug that has not yet been fixed.

SELECT 
Distinct
migroorg_enzym_init_concentration_gkg
FROM `gcp-sqo-data-dm-ri-p.DS_Datalab.vw_aroma_experiment_eln_results`

→ Expected result IS NOT NULL.