4.3 Progettazione e creazione del Data Warehouse
4.3.8 Fact Table: Fact Affidabilità
L’ultima tabella analizzata è la Fact Table del Data Warehouse, che contiene dunque tutte le informazioni relative all’area Affidabilità del progetto Logistica Inversa.
Per la costruzione della Fact dell’Affidabilità sono stati inseriti gli identificativi delle tabelle dimensio- nali descritte in precedenza, collegando in questo modo le stesse alla Fact; i legami ottenuti permettono di eseguire filtraggi per più dimensioni d’interesse, facendo variare i KPIs presenti all’interno della Fact Table e quindi nelle analisi finali.
Di seguito viene presentata la lista dei join effettuati per la definizione della tabella EDW_ORA_F_AFFIDABILITA:
WITH T1 AS ( SELECT [...]
FROM EDW_ORA_F_SR_TIME_MKT A LEFT JOIN EDW_ORA_L_SR B
ON A.INCIDENT_ID=B.INCIDENT_ID LEFT JOIN EDW_ORA_L_DEPOT C
ON A.INCIDENT_ID=C.INCIDENT_ID
LEFT JOIN EDW_L_SERIALE_ALL Y <-- Controllo se si tratta di un’etichetta... ON NVL(C.ETICHETTA,’N/A’) = Y.SERIAL_NUMBER
LEFT JOIN EDW_L_SERIALE_ALL W <-- ...altrimenti è un elettromandrino ON NVL(C.SERIAL_NUMBER, ’N/A’) = W.SERIAL_NUMBER
LEFT JOIN EDW_L_CLIENTE D
ON A.CUST_ACCOUNT_ID=D.CUST_ACCOUNT_ID LEFT JOIN ST_DIM_ARTICOLO ARTICOLO
ON C.INVENTORY_ITEM_ID_DEPOT = ARTICOLO.INVENTORY_ITEM_ID LEFT JOIN FW_F_INT_TECNICI_ITA G
ON A.INCIDENT_ID=G.INCIDENT_ID LEFT JOIN EDW_L_ALBERO_GUASTI H
ON NVL(C.ID_FAULT1,G.ID_FAULT1) = H.ID_FAULT1 LEFT JOIN EDW_L_STATO_GAR SG
ON A.STATO_GAR_ID = SG.STATO_GAR_ID ), [...calcolo MTTF...]
Per questioni di ordine e di spazio è stata inserita all’interno della query solamente la parte relativa al FROM, che mostra i join e le chiavi di join che permettono una corretta costruzione della fact dell’affidabilità riguardante riparazioni ed interventi tecnici della sede italiana. Inoltre nella parte finale della query è stata inserita una seconda WITH per il calcolo dell’indicatore MTTF, spiegato nel dettaglio nel paragrafo successivo.
N.B. Come mostrato nello script sopra è presente un join con la tabella Forwmork FW_F_INT_TECNICI_ITA: tale scelta è dettata dal fatto che gli interventi tecnici sono salvati sia su Oracle EBS ma anche su fogli Excel; è stato dunque ritenuto opportuno far confluire anche i dati riguardanti gli interventi tecnici provenienti da Formwork all’interno del flusso Oracle.
Per quanto riguarda l’insieme dei dati di riparazioni ed interventi tecnici delle filiali è stata creata la struttura EDW_FW_F_AFFIDABILITA, che presenta lo stesso tracciato della tabella Oracle. La tabella in questione è stata costruita in questo modo:
WITH T1 AS ( SELECT [...]
FROM FW_L_DEPOT_FILIALI A <--- Riparazioni filiali
LEFT JOIN EDW_L_SERIALE_ALL B ON A.SERIAL_NUMBER=B.SERIAL_NUMBER LEFT JOIN EDW_L_CLIENTE C
ON A.COD_CLIENTE=C.COD_CLIENTE LEFT JOIN ST_DIM_ARTICOLO D
ON A.COD_ARTICOLO=D.COD_ARTICOLO ), [...calcolo MTTF...]
UNION
WITH T1 AS ( SELECT [...]
FROM FW_L_SR_FILIALI A <--- Interventi tecnici filiali
LEFT JOIN EDW_L_SERIALE_ALL B ON A.SERIAL_NUMBER=B.SERIAL_NUMBER LEFT JOIN EDW_L_CLIENTE C
ON A.COD_CLIENTE=C.COD_CLIENTE LEFT JOIN ST_DIM_ARTICOLO D
ON A.COD_ARTICOLO=D.COD_ARTICOLO ), [...MTTF fittiziato a NULL...]
La tabella EDW_FW_F_AFFIDABILITA contiene dunque i dati di riparazioni ed interventi tecnici delle filiali. Per ottenere ciò sono state create due maschere di imputazione dati Formwork, una per le riparazioni e l’altra per gli interventi tecnici, in modo da popolare rispettivamente le tabelle FW_L_DEPOT_FILIALI e FW_L_SR_FILIALI. Infine le righe provenienti da entrambe le tabelle sono state unite tramite UNION, ottenendo così la totalità dei dati delle filiali aziendali. L’MTTF in questo caso è stato calcolato solamente per la parte relativa alle riparazioni, valorizzandolo invece a null in caso di intervento tecnico.
Per ottenere la Fact Table finale dunque è stato necessario unire le tabelle EDW_ORA_F_AFFIDABILITA e EDW_FW_F_AFFIDABILITA ottenendo la fact finale EDW_F_AFFIDABILITA. Di seguito sono elencati i campi rilevanti della Fact Table ottenuta:
Nome Campo Descrizione Campo
SOURCE_SYSTEM_ID Dato Oracle o Formwork ID_TIPOLOGIA_GUASTO Riparazione o Int. Tec.
REPAIR_NUMBER Numero riparazione Depot REPAIR_LINE_ID (PK) ID linea riparazione
COD_ENTITY Codice filiale INCIDENT_ID (PK) ID Service Request
SERIAL_NUMBER Seriale elettromandrino FLG_ITERATION Flag Iteration COD_ARTICOLO Codice Articolo riparato
ID_FAULT1 ID guasto
ID_CLIENTE ID Cliente SR_CREATION_DATE_FDATE Data creazione SR DR_CREATION_DATE_FDATE Data creazione Depot DATA_ULTIMA_PROD_FDATE Data ultima produzione
QTA_GUASTO Quantità guasti WARRANTY_STATUS_CODE ID stato garanzia
ETICHETTA Seriale sub-componente
VAL_MTTF Valore MTTF
Figura 4.17: Screenshot Fact Table Affidabilità
Attraverso i campi mostrati è possibile raggiungere e filtrare le analisi per le tabelle dimensionali precedentemente menzionate utilizzando le chiavi identificative presenti sulla Fact. Oltre queste sono stati inseriti i due KPIs definiti in fase di analisi dei requisiti, ovvero il tasso di guasto ed il valore dell’MTTF.
I campi aventi suffisso FDATE sono in formato stringa ’YYYYMMDD’ e serviranno in seguito per eseguire i join con l’anagrafica del calendario ST_DIM_CALENDAR. Quest’ultima possiede come chiave primaria la stringa costruita come ’YYYYMMDD’ che identifica univocamente ogni data, formata da anno, mese e giorno. I campi FDATE saranno utilizzati in fase di modellazione per accedere dalla Fact Table all’anagrafica del calendario tramite la chiave appena descritta.
Figura 4.18: Anagrafica del calendario
4.3.8.1 MTTF
Il primo KPI misura il tempo medio di vita di ogni componente dell’Azienda, calcolato come la differenza in giorni tra la data del guasto e la data di ultima spedizione, ovvero quando è stato effettivamente spedito al cliente che ne ha fatto richiesta. Inoltre è da ricordare che l’indicatore MTTF viene calcolato
esclusivamenteper le Riparazioni.
L’algoritmo per il calcolo del valore MTTF è stato inserito di seguito ai join che permettono la creazione della fact table EDW_ORA_F_AFFIDABILITA, sotto forma di più tabelle WITH, che per passi successivi effettuano le differenze tra le date citate. Lo script che ha permesso di ottenere il valore dell’MTTF è il seguente:
[...] ), T2 AS (
SELECT B.REPAIR_NUMBER, A.COD_MATRICOLA, B.CREATION_DATE_SR, MAX (
CASE WHEN A.CONFIRM_DATE < B.CREATION_DATE_SR THEN A.CONFIRM_DATE ELSE NULL
END
) CONFIRM_DATE FROM WK1_ORA_L_DEPOT B LEFT JOIN ST_FACT_SALES A
ON A.COD_MATRICOLA = B.COD_MATRICOLA_SHIP <--- Seriale spedito
GROUP BY B.REPAIR_NUMBER, A.COD_MATRICOLA,B.CREATION_DATE_SR ), [...]
In questa WITH, secondo le specifiche dell’Azienda, per ogni numero di riparazione del depot è necessario recuperare la più recente data di spedizione antecedente alla data di creazione dell’SR.
Nel caso in cui questa non venga trovata sarà valorizzata come nulla. È stata utilizzata come chiave di join tra depot e la tabella delle vendite il codice della matricola spedita, poiché per il calcolo è necessario considerare i seriali che sono stati effettivamente spediti.
[...] ), T3 AS (
SELECT A.*, D.CONFIRM_DATE, CASE
WHEN D.CONFIRM_DATE IS NOT NULL
THEN A.SR_CREATION_DATE_FDATE - D.CONFIRM_DATE
WHEN NVL(SER.SERIAL_NUMBER,’N/A’) <> ’N/A’ <--- Etichetta è presente in anagrafica AND A.DATA_PRODUZIONE_ETI > DATE ’’1900-01-01’’
THEN A.SR_CREATION_DATE - A.DATA_PRODUZIONE_ETI
WHEN NVL(SER.SERIAL_NUMBER,’N/A’) = ’N/A’ <--- Etichetta NON è presente in anagrafica
THEN A.SR_CREATION_DATE - A.DATA_PRODUZIONE_SN ELSE NULL
END VAL_MTTF,
FROM T1 A <--- Tutti i campi della Fact Table
LEFT JOIN T2 D
ON A.REPAIR_NUMBER = D.REPAIR_NUMBER LEFT JOIN EDW_L_SERIALE_ALL SER
ON A.ETICHETTA = SER.SERIAL_NUMBER <--- Controllo se ho etichetta in anagrafica
) [...] <--- Selezione di tutti i campi della fact e VAL_MTTF
Nella seconda parte dell’algoritmo vengono effettuate le operazioni tra le date, trovando così il valore di MTTF espresso in giorni attraverso una serie di condizioni if then else, dettagliate nei seguenti punti:
• Data di Spedizione nella tabella T2 non nulla: in questo caso l’MTTF sarà pari alla differenza tra
Data di Creazione SRe la Data di Spedizione (CONFIRM_DATE) recuperata nella T2;
• Altrimenti se la Data di spedizione è nulla:
• Se il seriale associato alla riparazione è un’etichetta in anagrafica, con data di produzione
diversa dalla data fittizia, l’MTTF sarà dato dalla differenza tra la Data di Creazione SR e la
Data di Produzione Originale dell’etichetta;
• Altrimenti se il seriale associato alla riparazione non è il seriale di un sotto-componente
l’MTTF sarà la differenza tra la Data di Creazione SR e la Data di Produzione Originale del
seriale.
Con l’algoritmo appena mostrato vengono ricoperte tutte le casistiche per il calcolo del parametro MTTF, analizzando i casi in cui sono stati sia riparati elettromandrini che i loro componenti interni.
Inoltre è da specificare in quali casi l’MTTF è stato posto a nullo:
• Quando nessuna delle condizioni nell’algoritmo è rispettata;
• Assenza di Numero di Riparazione, ovvero in caso di intervento tecnico, o nei casi in cui nono- stante ci sia una riparazione non è presente a sistema il numero della riparazione stessa;
• MTTF finale maggiore di 30000. Tale numero viene ottenuto nei casi di differenze con date fittizie poste a Gennaio 1900;
• MTTF≤ 0.
È di fondamentale importanza la presenza di valori nulli per la colonna indicante l’MTTF poiché trat- tandosi di un valore che sarà aggregato successivamente per AVG, valori troppo elevati o troppo bassi influenzerebbero negativamente la media risultante, ottenendo così un risultato non affidabile.
Gli stessi passaggi dell’algoritmo sono stati eseguiti anche per i dati riguardanti le riparazioni delle filiali all’interno del primo blocco SQL presente a pagina 69, utile per la costruzione della tabella EDW_FW_F_AFFIDABILITA. In riferimento allo stesso script, per gli interventi tecnici delle filiali non bisogna calcolare il valore relativo all’MTTF, ponendo lo stesso a null per ogni riga.
4.3.8.2 Tasso di Guasto
Il Tasso di Guasto è il rapporto tra il numero di non conformità dei componenti per anno di produzione e le quantità vendute nello stesso anno.
Per ottenere il parametro in questione è stata inserita all’interno delle fact table Oracle e Formwork la colonna QTA_GUASTO valorizzata sempre ad 1, sia in casi di riparazione che intervento tecnico, poiché ogni riga della tabella rappresenta un singolo caso di malfunzionamento.
Inoltre per un corretto calcolo della metrica in questione è necessario calcolare la data di ultima produzione per ogni Depot; ciò è necessario poiché il tasso di guasto deve essere calcolato per anno di produzione e la data di ultima produzione relativa ad ogni DR permette di ottenere questo dettaglio. Di seguito sarà descritto l’algoritmo per calcolare tale data per le informazioni Oracle.
WITH SERIALI_ORA AS ( SELECT A.REPAIR_NUMBER
,NVL(MAX(COALESCE(E.CONFIRM_DATE,D.CONFIRM_DATE)), DATE ’1900-01-01’) AS DATA_ULTIMA_PROD_ORA FROM EDW_ORA_L_DEPOT A
LEFT JOIN EDW_ORA_F_SR_TIME_MKT B ON A.INCIDENT_ID = B.INCIDENT_ID LEFT JOIN STG_ORA_L_DEPOT Z ON A.REPAIR_NUMBER = Z.REPAIR_NUMBER
LEFT JOIN EDW_L_SERIALE_ALL W ON NVL(Z.SERIAL_NUMBER, ’N/A’) = W.SERIAL_NUMBER LEFT JOIN EDW_ORA_L_SR C ON B.INCIDENT_ID = C.INCIDENT_ID
LEFT JOIN ST_FACT_SALES D --SERIAL_NUMBER
ON CASE WHEN B.COD_MATRICOLA = ’N/A’ THEN NVL(W.SERIAL_NUMBER,’N/A’) ELSE B.COD_MATRICOLA END = D.COD_MATRICOLA
AND D.CONFIRM_DATE < A.CREATION_DATE LEFT JOIN ST_FACT_SALES E --ETICHETTA
ON A.ETICHETTA=E.COD_MATRICOLA AND E.CONFIRM_DATE < A.CREATION_DATE AND A.ETICHETTA <> ’N/A’
WHERE C.TYPE_DE = ’Repair’ GROUP BY A.REPAIR_NUMBER ), [...]
Attraverso la prima WITH viene estratta per ogni numero di riparazione la più recente data di spedi- zione inferiore alla data di creazione del Depot, richiesta dalla committenza, per ogni DR in Oracle. Inoltre i due join con la tabella delle vendite, con diversa chiave di join, e la funzione COALESCE situata nella SELECT, hanno permesso di recuperare tale informazione sia per i seriali che per le etichette.
SERIALI_DIA AS (
SELECT A.REPAIR_NUMBER
, NVL(MAX(COALESCE(E.DATA_RIG,D.DATA_RIG)),DATE ’1900-01-01’) AS DATA_ULTIMA_PROD_DIA FROM EDW_ORA_L_DEPOT A
LEFT JOIN STG_ORA_L_DEPOT Z ON A.REPAIR_NUMBER = Z.REPAIR_NUMBER LEFT JOIN EDW_ORA_F_SR_TIME_MKT B
ON A.INCIDENT_ID = B.INCIDENT_ID LEFT JOIN EDW_ORA_L_SR C
ON (B.INCIDENT_ID=C.INCIDENT_ID) LEFT JOIN OBIEE.MATRI D --SERIAL_NUMBER
ON CASE WHEN B.COD_MATRICOLA = ’N/A’ THEN NVL(Z.SERIAL_NUMBER, ’N/A’) ELSE B.COD_MATRICOLA END = TRIM(D.MATRICOLA)
AND D.DATA_RIG < TRUNC(A.CREATION_DATE) LEFT JOIN OBIEE.MATRI E --ETICHETTA ON A.ETICHETTA = TRIM(E.MATRICOLA) AND E.DATA_RIG < TRUNC(A.CREATION_DATE) AND A.ETICHETTA <> ’N/A’
WHERE C.TYPE_DE = ’Repair’ GROUP BY A.REPAIR_NUMBER ), [...]
Anche in questa query viene eseguita la stessa operazione precedente, ma su seriali salvati sul gestio- nale Diapason. SERIALI_PROD AS ( SELECT A.REPAIR_NUMBER, COALESCE(D.DATA_PRODUZIONE,C.DATA_PRODUZIONE,DATE ’1900-01-01’) AS DATA_PRODUZIONE FROM EDW_ORA_L_DEPOT A
LEFT JOIN STG_ORA_L_DEPOT Z ON A.REPAIR_NUMBER = Z.REPAIR_NUMBER LEFT JOIN EDW_ORA_F_SR_TIME_MKT B
ON A.INCIDENT_ID = B.INCIDENT_ID
LEFT JOIN EDW_L_SERIALE_ALL C --SERIAL NUMBER ON CASE WHEN B.COD_MATRICOLA = ’N/A’
THEN NVL(Z.SERIAL_NUMBER, ’N/A’) ELSE B.COD_MATRICOLA END = C.SERIAL_NUMBER LEFT JOIN EDW_L_SERIALE_ALL D --ETICHETTA
ON A.ETICHETTA = D.SERIAL_NUMBER AND A.ETICHETTA <> ’N/A’
), [...]
Nella WITH mostrata viene estratta la data di produzione originale associata ad ogni riparazione, sia che si tratti di un elettromandrino o di un sotto-componente dello stesso.
La query finale che collega le tre query WITH mostrate e che calcola la data di ultima produzione relativa ad ogni DR è la seguente:
SELECT A.REPAIR_NUMBER,
CASE WHEN E.SERIAL_NUMBER IS NOT NULL --Etichetta esiste in anagrafica
THEN NVL(E.DATA_PRODUZIONE,DATE ’1900-01-01’)
ELSE COALESCE(NULLIF(GREATEST(B.DATA_ULTIMA_PROD_ORA, C.DATA_ULTIMA_PROD_DIA), DATE ’1900-01-01’), NULLIF(D.DATA_PRODUZIONE, date ’1900-01-01’), DATE ’1900-01-01’)
END AS DATA_ULTIMA_PROD FROM EDW_ORA_L_DEPOT A
LEFT JOIN SERIALI_ORA B
ON A.REPAIR_NUMBER = B.REPAIR_NUMBER LEFT JOIN SERIALI_DIA C
ON A.REPAIR_NUMBER = C.REPAIR_NUMBER LEFT JOIN SERIALI_PROD D
ON A.REPAIR_NUMBER = D.REPAIR_NUMBER
LEFT JOIN EDW_L_SERIALE_ALL E --Controllo se ETICHETTA esiste in anagrafica ON A.ETICHETTA = E.SERIAL_NUMBER
AND A.ETICHETTA <> ’N/A’
Di seguito sono elencate nello specifico le casistiche prese in considerazione per il calcolo della data in questione, implementate tramite il costrutto CASE - WHEN:
• Se il seriale è un’etichetta ed esiste in anagrafica, allora la data di ultima produzione del DR asso- ciato a quell’etichetta sarà la data di produzione originale dell’etichetta;
• Altrimenti verrà presa la data maggiore tra data di ultima produzione di Oracle e Diapason, ottenute dalle WITH precedenti. Nel caso in cui entrambe risultassero nulle verrà presa la data di produzione originale del seriale.
La tabella risultante dalla query è chiamata WK1_ORA_LAST_PROD_DEPOT ed associa ad ogni numero di riparazione del DR la data di ultima produzione dello stesso.
Gli stessi passaggi sono stati effettuati anche per i dati provenienti da Formwork, utilizzando come tabella sorgente per tutte le WITH la tabella FW_L_DEPOT_FILIALI; a partire da quest’ultima è stata popolata a sua volta la struttura WK1_FW_LAST_PROD_DEPOT.
L’informazione in questione è stata integrata in seguito sulla fact EDW_ORA_F_AFFIDABILITA tramite un join con la tabella WK1_ORA_LAST_PROD_DEPOT e sulla fact EDW_FW_F_AFFIDABILITA tramite un join con la WK1_FW_LAST_PROD_DEPOT. Il campo che conterrà infine la data di ultima pro- duzione per ogni Depot è chiamato DATA_ULTIMA_PROD_FDATE.