Dimensional Table: Anagrafica Seriali

Ai fini di una corretta analisi riguardante l’affidabilità dei componenti, è stato ritenuto necessario l’uti- lizzo dell’anagrafica dei seriali prodotti dall’Azienda, in modo da tracciare il numero delle riparazioni subite ed informazioni aggiuntive per ognuno dei componenti.

La dimensione in questione è chiamata EDW_L_SERIALE ed è stata popolata partendo dalla tabella di- mensionale dei seriali propria dell’Azienda (ST_DIM_SERIALE), oltre che dalle tabelle relative ai prodotti ed articoli aziendali (rispettivamente ST_DIM_PRODOTTO e ST_DIM_ARTICOLO). Le tabelle ST_DIM sono tabelle già create dalla committenza per processi di business interni; la differenza dunque tra la tabella ST_DIM e la EDW_L_SERIALE è che mentre la prima contiene informazioni limitate ai seriali, la seconda oltre a tali informazioni andrà a contenere anche altri dettagli di rilevanza per il progetto

Logistica Inversa.

[...] COALESCE(F.CONFIRM_DATE, E.DATA_ULTIMA_PROD, DATE ’1900-01-01’) DATA_ULTIMA_PRODUZIONE FROM ST_DIM_SERIALE A

LEFT JOIN ST_DIM_PRODOTTO B ON A.CODICE_COMMERCIALE = B.CODICE_COMMERCIALE LEFT JOIN ST_DIM_ARTICOLO C ON A.CODICE_TECNICO = C.COD_ARTICOLO

LEFT JOIN WK1_FLG_ITERATION D ON A.SERIAL_NUMBER = D.SERIAL_NUMBER LEFT JOIN WK1_DATA_ULTIMA_PROD E ON A.SERIAL_NUMBER = E.SERIAL_NUMBER LEFT JOIN WK1_ETICHETTA_SERIALE F ON A.SERIAL_NUMBER = F.ETICHETTA

Per questioni di spazio è stata elisa dalla query la parte relativa alla SELECT. L’anagrafica viene popo- lata a partire dalle tabelle dimensionali relative a seriale, prodotto ed articolo, che ricordiamo essere di proprietà dell’Azienda. Le ultime tre tabelle con le quali viene eseguito un join invece sono working

tablesche contegono informazioni aggiuntive per ogni seriale in anagrafica, quali Flag Iteration, Data di

Ultima Produzioneed Etichetta del sub-componente riparato. Ognuna di queste tabelle è stata popolata a sua volta da un algoritmo specifico, descritti più nel dettaglio nei paragrafi successivi.

La tabella creata contiene informazioni solo ed esclusivamente dei seriali not expired (non scaduti), ma occorre considerare anche i seriali EXPIRED all’interno delle analisi in modo da rendere le stesse il più accurate possibile. A tal proposito è stata creata un’ulteriore tabella che raccogliesse solamente in- formazioni riguardanti le matricole scadute, con medesimo tracciato della precedente; eseguendo infine l’operatore di UNION tra le due tabelle è stata creata la tabella EDW_L_SERIALE_ALL, che contiene dunque la totalità dei seriali prodotti dall’Azienda, scaduti e non scaduti.

I campi d’interesse della tabella finale sono i seguenti:

Nome Campo Descrizione Campo

SERIAL_NUMBER (PK) Seriale componente

DATA_PRODUZIONE Data prima produzione seriale SALES_CATEGORY Famiglia commerciale

FLG_ITERATION Flag Iteration

DATA_ULTIMA_PRODUZIONE Data di ultima produzione seriale FLG_EXPIRED Seriale scaduto o non scaduto

Figura 4.13: Anagrafica dei seriali

Nei paragrafi successivi sono elencati e dettagliati gli algoritmi richiesti dalla committenza, già ac- cennati in precedenza, per il calcolo di informazioni quali Flag Iteration e la Data di Ultima Produzione.

4.3.5.1 Flag Iteration

Il parametro Flag Iteration fornisce un’indicazione rapida su quante riparazioni ha subìto un qualsiasi seriale dell’Azienda: viene valorizzato ad 1 in caso di una sola riparazione, 2 in caso di più riparazioni e N/A nei casi in cui il seriale in questione non è mai stato riparato.

Sono state costruite due tabelle: una per i dati provenienti da Oracle e un’altra per i dati provenienti da Formwork, in modo che venga calcolato il parametro per entrambe le sorgenti dati di riferimento. In seguito è stato unito il risultato di queste ultime nella tabella WK1_FLG_ITERATION; ciò è stato neces- sario poiché potrebbe accadere che lo stesso seriale venga riparato sia nella sede italiana dell’Azienda (dati Oracle) che dalle filiali della stessa (dati Formwork), rendendo quindi essenziale il controllo delle riparazioni per uno stesso seriale da ambo le sorgenti. La query che permette di ottenere la working table finale è la seguente:

WITH T1 AS (

SELECT A.SERIAL_NUMBER SERIAL_NUMBER_ORA, NVL(A.FLG_ITERATION, ’N/A’) FLG_ITERATION_ORA, B.SERIAL_NUMBER SERIAL_NUMBER_FW,

B.FLG_ITERATION FLG_ITERATION_FW

FROM WK1_ORA_FLG_ITERATION A <---- Flag iteration Oracle

FULL OUTER JOIN WK1_FW_FLG_ITERATION B <---- Flag iteration FW

ON A.SERIAL_NUMBER = B.SERIAL_NUMBER )

SELECT

NVL(SERIAL_NUMBER_ORA, SERIAL_NUMBER_FW) SERIAL_NUMBER, CASE WHEN SERIAL_NUMBER_ORA = SERIAL_NUMBER_FW

THEN

CASE <---- Casistiche Flag Iteration di Oracle e FW

WHEN FLG_ITERATION_ORA = ’N/A’ AND FLG_ITERATION_FW = ’1’ THEN ’1’ WHEN FLG_ITERATION_ORA = ’N/A’ AND FLG_ITERATION_FW = ’2’ THEN ’2’ WHEN FLG_ITERATION_ORA = ’1’ AND FLG_ITERATION_FW = ’1’ THEN ’2’ WHEN FLG_ITERATION_ORA = ’2’ AND FLG_ITERATION_FW = ’1’ THEN ’2’ WHEN FLG_ITERATION_ORA = ’2’ AND FLG_ITERATION_FW = ’2’ THEN ’2’ ELSE ’2’ END

ELSE

CASE WHEN SERIAL_NUMBER_ORA IS NULL THEN FLG_ITERATION_FW ELSE FLG_ITERATION_ORA END

END FLG_ITERATION FROM T1;

Con la query mostrata è possibile stabilire il valore del Flag Iteration, basandosi sul quantitativo delle riparazioni effettuate su qualsiasi seriale riparato sia dalla sede italiana che estera dell’Azienda. In questo modo è stata ottenuta la tabella che associa ad ogni seriale il parametro Flag Iteration; infine la working table in questione è stata integrata con la dimensione EDW_L_SERIALE, come mostrato nello script a pagina 58.

4.3.5.2 Data di Ultima Produzione

La specifica relativa alla Data di Ultima Produzione è un dettaglio utile ad individuare l’ultima volta in cui un determinato componente è stato riparato. Ogni qualvolta un oggetto subisce una riparazione la sua Data di produzione dovrà aggiornarsi; per questo motivo è stato inserito un nuovo dettaglio all’interno dell’anagrafica dei seriali chiamato Data di ultima produzione che sarà valorizzato con la Data di ultima Spedizione del seriale. Di default nel caso in cui il seriale non è mai stato riparato (e quindi mai spedito), la data di ultima produzione è stata valorizzata con la data di produzione originale del seriale. In caso di intervento tecnico, invece, la data di ultima produzione del seriale in questione non è stata aggiornata. Tale meccanismo di aggiornamento deve essere effettivo dunque se e solo se il seriale subisce una riparazione. Anche in questo caso sono state gestite due tabelle differenti, una per le informazioni provenienti da Oracle e l’altra per le informazioni Formwork.

Per calcolare correttamente la specifica per la parte Oracle è stato necessario tenere conto di alcune riparazioni salvate in gestionale Diapason ed integrarle con i dati presenti in Oracle.

WITH SERIALI_ORA AS( SELECT A.COD_MATRICOLA

, MAX(NVL(C.CONFIRM_DATE,DATE ’1900-01-01’)) DATA_ULTIMA_PROD_ORA

FROM EDW_ORA_F_SR_TIME_MKT A <--- Fact Service Request (già esistente)

LEFT JOIN EDW_ORA_L_SR B <---- Lookup delle Service Request (già esistente)

ON (A.INCIDENT_ID=B.INCIDENT_ID)

LEFT JOIN ST_FACT_SALES C <---- Fact delle vendite dell’Azienda (già esistente)

ON B.INCIDENT_NUMBER=C.INCIDENT_NUMBER GROUP BY A.COD_MATRICOLA

)

Nella prima WITH viene ottenuta la data di ultima produzione per ogni seriale presente in Oracle, partendo dalla fact contenente tutte le SR aziendali, per poi identificare la data di spedizione (Confirm

Date) più recente per ogni seriale. Nel caso in cui non fosse presente la data in questione (o perché fittiziata a Gennaio 1900) la committenza ha richiesto di recuperare la data di chiusura del DR, in sostituzione dunque della data di spedizione.

, SERIALI_DIA AS (

SELECT TRIM(A.MATRICOLA) COD_MATRICOLA,

MAX(NVL(A.DATA_RIG, DATE ’1900-01-01’)) DATA_ULTIMA_PROD_DIA

FROM OBIEE.MATRI A <---- Matricole Diapason

GROUP BY TRIM(A.MATRICOLA) )

Nel secondo frammento di codice viene individuata la data di ultima riparazione (Data Rig) più recente per ogni seriale salvato in Diapason.

,D_CLOSED AS (

SELECT A.SERIAL_NUMBER, NVL(MAX(B.DATE_CLOSED), DATE ’’1900-01-01’’) DATE_CLOSED FROM WK1_L_SERIALE_ALL A

JOIN STG_ORA_L_DEPOT B ON A.SERIAL_NUMBER = B.SERIAL_NUMBER GROUP BY A.SERIAL_NUMBER

)

In questo punto viene estratta per ogni seriale la data di chiusura del depot (DATE_CLOSED), utile in caso di assenza di data di spedizione (Confirm Date).

SELECT A.SERIAL_NUMBER, CASE

WHEN NVL(B.DATA_ULTIMA_PROD_ORA,DATE ’1900-01-01’) = DATE ’1900-01-01’ THEN GREATEST(D.DATE_CLOSED, C.DATA_ULTIMA_PROD_DIA, A.DATA_PRODUZIONE)

ELSE GREATEST(B.DATA_ULTIMA_PROD_ORA, C.DATA_ULTIMA_PROD_DIA, A.DATA_PRODUZIONE) END AS DATA_ULTIMA_PROD

FROM WK1_L_SERIALE_ALL A LEFT JOIN SERIALI_ORA B

ON A.SERIAL_NUMBER=B.COD_MATRICOLA LEFT JOIN SERIALI_DIA C

ON A.SERIAL_NUMBER=C.COD_MATRICOLA LEFT JOIN D_CLOSED D

ON A.SERIAL_NUMBER = D.SERIAL_NUMBER

A partire dalle WITH precedenti, nell’ultima parte dell’algoritmo viene calcolata la data di ultima produzione per ogni seriale; più nel dettaglio la data viene trovata in questo modo:

• Se CONFIRM DATE = 1900-01: nel caso in cui non è presente la data di spedizione, come accennato in precedenza, occorre considerare la data di chiusura del DR; a tal proposito viene presa la data più recente (GREATEST) tra Data Chiusura Depot, Data Ultima Produzione Diapason e Data Produzione Originale Seriale. Quest’ultima viene presa di default nei casi in cui le restanti due date dovessero essere nulle o pari a Gennaio 1900;

• Se CONFIRM DATE è presente: in questo caso la data di ultima produzione è ottenuta come la data più recente tra Data Spedizione, Data Ultima Produzione Diapason e Data Produzione Originale Seriale.

L’algoritmo appena mostrato popola la tabella WK1_ORA_DATA_ULTIMA_PROD, che associa ad ogni seriale in Oracle la relativa data di ultima produzione. È risultato necessario utilizzare lo stesso

modus operandianche per i seriali provenienti da Formwork, ovvero dalle filiali dell’Azienda:

WITH DATA_FW AS(

SELECT A.SERIAL_NUMBER

, MAX(NVL(A.CONFIRM_DATE_SPEDIZIONE,DATE ’1900-01-01’)) DATA_ULTIMA_PROD_FW

FROM FORMWORK.FW_L_SR_FILIALI A <---- Tabella contenente SR di Formwork

WHERE A.CONFIRM_DATE_SPEDIZIONE <> DATE ’2999-12-31’ GROUP BY A.SERIAL_NUMBER

D_CLOSED AS (

SELECT A.SERIAL_NUMBER, NVL(MAX(B.DATE_CLOSED), DATE ’1900-01-01’) DATE_CLOSED FROM WK1_L_SERIALE_ALL A

JOIN FORMWORK.FW_L_DEPOT_FILIALI B <---- Tabella DEPOT di Formwokrk ON A.SERIAL_NUMBER = B.SERIAL_NUMBER

WHERE B.DATE_CLOSED <> DATE ’2999-12-31’ GROUP BY A.SERIAL_NUMBER

)

SELECT A.SERIAL_NUMBER, CASE

WHEN NVL(B.DATA_ULTIMA_PROD_FW,DATE ’1900-01-01’) = DATE ’1900-01-01’ THEN GREATEST(D.DATE_CLOSED, A.DATA_PRODUZIONE)

ELSE GREATEST(B.DATA_ULTIMA_PROD_FW, A.DATA_PRODUZIONE) END AS DATA_ULTIMA_PROD

FROM WK1_L_SERIALE_ALL A LEFT JOIN DATA_FW B

ON A.SERIAL_NUMBER=B.SERIAL_NUMBER LEFT JOIN D_CLOSED D

ON A.SERIAL_NUMBER = D.SERIAL_NUMBER

Il percorso lato Formwork è pressoché lo stesso, ad esclusione dei seriali nel gestionale Diapason e le differenti tabelle sorgenti per le Service Request e Depot Repair; la query appena mostrata permette di creare la tabella WK1_FW_DATA_ULTIMA_PROD.

Le due tabelle precedenti sono state infine unite in un’unica tabella (WK1_DATA_ULTIMA_PROD), popolata secondo la seguente query:

WITH T1 AS (

SELECT * FROM WK1_ORA_DATA_ULTIMA_PROD UNION

SELECT * FROM WK1_FW_DATA_ULTIMA_PROD )

SELECT SERIAL_NUMBER, MAX(DATA_ULTIMA_PROD) FROM T1 GROUP BY SERIAL_NUMBER

Nel caso un seriale venga riparato sia in sede italiana che in una delle filiali esso avrà come data di ultima produzione la più recente tra le date di ultima produzione in Oracle e Formwork.

Una volta ottenuta la tabella che associa ad ogni seriale l’ultima data di produzione, questa ha per- messo di popolare la colonna relativa alla data di ultima produzione sull’anagrafica dei seriali.

4.3.5.3 Etichetta

L’Azienda inserisce nel campo Etichetta il componente dell’elettromandrino che subìsce la riparazione e che dunque non possiede un seriale proprio all’interno dell’anagrafica. Per ottenere questa informazione

la committenza ha fornito la tabella che contiene l’etichetta ed il modo per recuperarla e successivamente integrarla con i dati già esistenti.

L’informazione dell’etichetta è stata integrata già nella STG_ORA_L_DEPOT, come mostrato nello script a pagina 49:

SELECT

[...LISTA CAMPI STAGING...],

CP.EXTERNAL_REFERENCE AS ETICHETTA <---- Etichetta

FROM APPS.CSD_REPAIRS CR <---- Tabella sorgente delle riparazioni e del DEPOT LEFT JOIN APPS.CSI_ITEM_INSTANCES CP ON CR.CUSTOMER_PRODUCT_ID = CP.INSTANCE_ID [...]

Oltre ciò è stata necessaria la creazione di una tabella che contenesse tutte le etichette dell’Azienda con relativa data di spedizione associata. La tabella in questione è la WK1_ETICHETTA_SERIALE ed è stata popolata come segue:

SELECT A.ETICHETTA,

MAX(NVL(B.CONFIRM_DATE, A.DATE_CLOSED)) CONFIRM_DATE FROM STG_ORA_L_DEPOT A

JOIN ST_FACT_SALES B

ON A.SERIAL_NUMBER = B.COD_MATRICOLA WHERE A.ETICHETTA IS NOT NULL

GROUP BY A.ETICHETTA

Per ogni etichetta viene presa in considerazione la data di spedizione più recente e, in assenza di questa, la data di chiusura del DR. Questa informazione è stata inserita all’interno della tabella EDW_L_SERIALE _{[Script Pagina 58], che conterrà dunque sia seriali di elettromandrini che dei sotto-} componenti. La Data di Spedizione dell’etichetta è stata quindi integrata con la funzione COALESCE:

[...] COALESCE(F.CONFIRM_DATE, E.DATA_ULTIMA_PROD, DATE ’1900-01-01’) DATA_ULTIMA_PRODUZIONE FROM ST_DIM_SERIALE A

[...]

LEFT JOIN WK1_DATA_ULTIMA_PROD E ON A.SERIAL_NUMBER = E.SERIAL_NUMBER LEFT JOIN WK1_ETICHETTA_SERIALE F ON A.SERIAL_NUMBER = F.ETICHETTA

In questo modo se esiste l’etichetta (e dunque la data di spedizione relativa), quest’ultima diventerà la data di ultima produzione per il sotto-componente; altrimenti, in caso di fallimento del precedente

join, ciò implica che il seriale in questione non è un’etichetta, pertanto è stato eseguito un ulteriore

join con la tabella WK1_DATA_ULTIMA_PROD, che associa ad ogni seriale la propria data di ultima produzione e popolata a partire dall’algoritmo spiegato nella sezione precedente.