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.
As shown in the screenshot above, two plans are each connected to a separate task.
PL_DO_RNI_Aroma_STG_ODS_DM: (Daily)
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)
Talend Studio :
WS_DATA_OCEAN_DOMAIN_RNI
F003_RNLI_Aroma_Instruments_STG_ODS
| F004_Rnl_Aroma_Instrument_DM
|
|---|
Layer 2: Aroma SQL View
On top of the DM and fact tables, a SQL view was created to consolidate all Aroma-related information.
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.