Reporting in APEX usando servizi REST

Obiettivo di questo articolo è quello di mostrare come Oracle APEX non sia solo un ambiente di sviluppo per applicazioni gestionali ma una piattaforma dove sviluppare anche del reporting di buon livello.

APEX non ha la pretesa di sostituirsi ad ambienti di BI come Oracle Business Analytics  però è sicuramente adatto per una prima reportistica di base.

Veniamo al dunque: l’obiettivo è di illustrare come realizzare una parte del dashboard seguente visualizzabile qui: https://vmcap1.orasoft.it/ords/f?p=stats:coronavirusitalia usando tutte le funzionalità standard di APEX 19.2 o 20.1:

Il dashboard analizza l’evoluzione temporale della diffusione del COVID19 nel nostro paese usando i dati forniti giornalmente dalla protezione civile.

La protezione civile ci mette a disposizione i dati attraverso un servizio REST che sostanzialmente è un url ad un documento JSON:

https://raw.githubusercontent.com/pcm-dpc/COVID-19/master/dati-json/dpc-covid19-ita-regioni.json

i cui dati hanno il seguente formato:

[
    {
        "data": "2020-02-24T18:00:00",
        "stato": "ITA",
        "codice_regione": 13,
        "denominazione_regione": "Abruzzo",
        "lat": 42.35122196,
        "long": 13.39843823,
        "ricoverati_con_sintomi": 0,
        "terapia_intensiva": 0,
        "totale_ospedalizzati": 0,
        "isolamento_domiciliare": 0,
        "totale_positivi": 0,
        "variazione_totale_positivi": 0,
        "nuovi_positivi": 0,
        "dimessi_guariti": 0,
        "deceduti": 0,
        "totale_casi": 0,
        "tamponi": 5,
        "note_it": "",
        "note_en": ""
    },
    {
        "data": "2020-02-24T18:00:00",
        "stato": "ITA",
        "codice_regione": 17,
        "denominazione_regione": "Basilicata",
        "lat": 40.63947052,
        "long": 15.80514834,
        "ricoverati_con_sintomi": 0,
        "terapia_intensiva": 0,
        "totale_ospedalizzati": 0,
        "isolamento_domiciliare": 0,
        "totale_positivi": 0,
        "variazione_totale_positivi": 0,
        "nuovi_positivi": 0,
        "dimessi_guariti": 0,
        "deceduti": 0,
        "totale_casi": 0,
        "tamponi": 0,
        "note_it": "",
        "note_en": ""
    },
…
]

Il nostro obiettivo è quello di leggere prima i dati attraverso il Database Oracle e quindi creare un App in APEX per mostrare tali dati.

Lettura dei dati in Oracle

Il documento JSON che la protezione Civile ci mette a disposizione è di fatto un servizio REST. APEX ci mette a disposizione delle API per gestire i servizi web e nello specifico useremo la funzione: APEX_WEB_SERVICE.MAKE_REST_REQUEST per recuperare un documento JSON che poi trasformeremo in tabella attraverso la funzione JSON_TABLE. 

JSON_TABLE, nuova funzione di Oracle dalla versione 12c, ci permette di trasformare un documento JSON in una tabella, per fare ciò bisogna avere un po’ di dimestichezza con i Path JSON per definire la tabella che vogliamo ottenere, nel nostro caso:

select * FROM json_table(apex_web_service.make_rest_request(p_url => 'https://raw.githubusercontent.com/pcm-dpc/COVID-19/master/dati-json/dpc-covid19-ita-regioni.json',p_http_method => 'GET'),  
                         '$[*]' columns(  
                             data varchar2(20)  path'$.data', 
                             stato varchar2(10) path'$.stato', 
                             codice_regione varchar2(3) path'$.codice_regione', 
                             denominazione_regione varchar2(50) path'$.denominazione_regione', 
                             lat number path'$.lat', 
                             lon number path'$.long', 
                             ricoverati_con_sintomi int path'$.ricoverati_con_sintomi', 
                             terapia_intensiva int path'$.terapia_intensiva', 
                             totale_ospedalizzati int path'$.totale_ospedalizzati', 
                             isolamento_domiciliare int path'$.isolamento_domiciliare', 
                             totale_positivi int path'$.totale_positivi', 
                             nuovi_positivi int path'$.nuovi_positivi', 
			     variazione_totale_positivi int path'$.variazione_totale_positivi', 
                             dimessi_guariti int path'$.dimessi_guariti', 
                             deceduti int path'$.deceduti', 
                             totale_casi int path'$.totale_casi', 
                             tamponi int path'$.tamponi'))

a questo punto siamo tornati nel nostro terreno familiare dei database relazionali, convertiamo la data, aggiungiamo un rank() per calcolare il dato più recente e possiamo creare la nostra vista di lavoro:

CREATE OR REPLACE  VIEW  COR_DATI_VW   AS 
  SELECT 
        to_date(DATA,'yyyy-mm-dd"T"hh24:mi:ss')DATA,
        STATO,
        CODICE_REGIONE,
        DENOMINAZIONE_REGIONE,
        LAT,
        LON,
        RICOVERATI_CON_SINTOMI,
        TERAPIA_INTENSIVA,
        TOTALE_OSPEDALIZZATI,
        ISOLAMENTO_DOMICILIARE,
        TOTALE_POSITIVI,
        NUOVI_POSITIVI,
        VARIAZIONE_TOTALE_POSITIVI,
        DIMESSI_GUARITI,
        DECEDUTI,
        TOTALE_CASI,
        TAMPONI,  
        decode(rank() over (order by data desc),1,1,0) LATEST  
FROM json_table(apex_web_service.make_rest_request(p_url => 'https://raw.githubusercontent.com/pcm-dpc/COVID-19/master/dati-json/dpc-covid19-ita-regioni.json',p_http_method => 'GET'),  
                         '$[*]' columns(  
                             data varchar2(20)  path'$.data', 
                             stato varchar2(10) path'$.stato', 
                             codice_regione varchar2(3) path'$.codice_regione', 
                             denominazione_regione varchar2(50) path'$.denominazione_regione', 
                             lat number path'$.lat', 
                             lon number path'$.long', 
                             ricoverati_con_sintomi int path'$.ricoverati_con_sintomi', 
                             terapia_intensiva int path'$.terapia_intensiva', 
                             totale_ospedalizzati int path'$.totale_ospedalizzati', 
                             isolamento_domiciliare int path'$.isolamento_domiciliare', 
                             totale_positivi int path'$.totale_positivi', 
                             nuovi_positivi int path'$.nuovi_positivi', 
			     variazione_totale_positivi int path'$.variazione_totale_positivi', 
                             dimessi_guariti int path'$.dimessi_guariti', 
                             deceduti int path'$.deceduti', 
                             totale_casi int path'$.totale_casi', 
                             tamponi int path'$.tamponi'))

Poichè i dati non si aggiornano continuamente creiamo una vista materializzata che si aggiorna ogni ora

CREATE MATERIALIZED VIEW COR_DATI_MVW 
REFRESH FORCE ON DEMAND 
START WITH sysdate NEXT sysdate + 1/24 
AS SELECT * FROM COR_DATI_VW

Quindi possiamo iniziare a lavorare:

Per tutti i dati storici useremo

select * from cor_dati_mvw

Per i dati più recenti relativi all’ultimo giorno useremo

select * from cor_dati_mvw where latest = 1

L’applicativo APEX

Ora inizia la parte più divertente:

Andiamo su http://apex.oracle.com e colleghiamoci ad una nostra area di lavoro (se non l’avete la potete richiedere gratuitamente) ricordandoci di selezionare prima la lingua Italiano se vogliamo l’ambiente nella nostra lingua. La versione di APEX presente al momento è la 20.1 ma potete tranquillamente usare anche la versione 19.2 presente attualmente in Oracle Cloud Free Tier

Andiamo su SQL Workshop Comandi SQL 

e creiamo le 2 viste definite precedentemente:

Quindi andiamo su App Builder,  Crea e scegliamo nuova Applicazione

Diamo un nome e creiamo l’applicazione:

  Nella Home per prima cosa rendiamo la pagina pubblica :

Quindi verifichiamo che tutto funzioni eseguendo la pagina (triangolo in alto a destra).

L’applicativo funziona!! Un bel Report vuoto!

Sezione KPI, facciamo vedere i totali!

Come primo esempio potremmo provare a replicare la sezione sopra, dei riquadri con dei KPI, nel nostro caso dei semplici totali. Se ad esempio volessimo avere il totale dei guariti dovremmo eseguire la seguente query:

select sum(dimessi_guariti) from cor_dati_mvw where latest=1

In APEX per visualizzare tale informazione dobbiamo usare:

• Area di tipo Report Classico
• Modello Cards (il modello lo trovate negli attributi)
• Impaginazione Nessuna Impaginazione 
• Opzioni Modello come in figura:

Il Report Classico così configurato vuole come sorgente dati alcuni campi ben specifici: 

• Titolo        CARD_TITLE
• Sottotitolo   CARD_SUBTITLE
• Icona         CARD_ICON
• Codice Colore CARD_COLOR

tutte queste informazioni le scriviamo in una query apposita (un’evoluzione di quella sopra scritta) e le inseriamo nel report come in figura inserendo le stesse informazioni nella sezione Origine e Aspetto:

Ripetiamo per altre 5 volte le stesse operazioni usando altri valori da visualizzare; sotto la tabella con tutte e 6 le query utilizzate:

select to_char(sum(totale_positivi),                     ‘999G999G990’) CARD_TITLE,                  ‘Totale Positivi’ CARD_SUBTITLE,                     ‘fa-ambulance’ CARD_ICON,                        ‘u-color-9 ‘CARD_COLOR                              from  cor_dati_mvw                              where  latest=1	select to_char(sum(dimessi_guariti),                     ‘999G999G990’) CARD_TITLE,                   ‘Totale Guariti’ CARD_SUBTITLE,                       ‘fa-smile-o’ CARD_ICON,                        ‘u-color-5 ‘CARD_COLOR                              from  cor_dati_mvw                              where  latest=1
select to_char(sum(deceduti),                     ‘999G999G990’) CARD_TITLE,                  ‘Totale Deceduti’ CARD_SUBTITLE,                ‘fa-minus-circle-o’ CARD_ICON,                       ‘u-color-15’ CARD_COLOR                              from  cor_dati_mvw                              where  latest=1	select to_char(sum(nuovi_positivi),                     ‘999G999G990’) CARD_TITLE,      ‘Totale Positivi Giornalieri’ CARD_SUBTITLE,                     ‘fa-ambulance’ CARD_ICON,                        ‘u-color-9 ‘CARD_COLOR                              from  cor_dati_mvw                              where  latest=1
select to_char(sum(tamponi),                     ‘999G999G990’) CARD_TITLE,                   ‘Totale Tamponi’ CARD_SUBTITLE,                 ‘fa-plus-square-o’ CARD_ICON,                        ‘u-color-1 ‘CARD_COLOR                              from  cor_dati_mvw                              where  latest=1	select to_char(sum(dimessi_guariti),                     ‘999G999G990’) CARD_TITLE,         ‘Totale Terapia Intensiva’ CARD_SUBTITLE,                           ‘fa-bed’ CARD_ICON,                        ‘u-color-7’ CARD_COLOR                              from  cor_dati_mvw                              where  latest=1

Nelle Aree successive alla prima, se le voglio allineate e non sotto la prima area, dovrò disabilitare l’opzione Inizia nuova riga (come in figura sotto)

Finalmente possiamo vedere il risultato del nostro lavoro:

Grafico a Barre Evoluzione Temporale

Dopo qualche KPI iniziamo a mettere un grafico a barre che illustri la situazione temporale del tempo dei dati che abbiamo a disposizione. Il grafico che ci interessa ha bisogno di:

• DATA da mettere nell’asse delle X
• TIPO MISURA (Positivi, Guariti, ecc) da mettere come serie e in legenda
• VALORE il valore numerico di riferimento

Per fare questo andiamo ad interrogare la base dati aiutandoci con una utilissima funzione di Oracle: UNPIVOT. Non spiegherò qui questa funzione ma è semplice trovarne documentazione in giro. La query diventa:

select data,denominazione_regione,tipo,sum(valore)valore 
     from cor_dati_mvw 
  unpivot(
  valore for
    tipo in
    (
        TOTALE_POSITIVI as 'Positivi',
        RICOVERATI_CON_SINTOMI as 'Ricoverati',
        TERAPIA_INTENSIVA as 'Terapia Intensiva',
        TOTALE_OSPEDALIZZATI as 'Ospedalizzati',
        ISOLAMENTO_DOMICILIARE as 'Isolamento Domiciliare',
        NUOVI_POSITIVI as 'Positivi Giornalieri',
        DIMESSI_GUARITI as 'Guariti',
        DECEDUTI as 'Deceduti',
        TOTALE_CASI as 'Totale Casi',
        TAMPONI as 'Totale'
    )
  )
where tipo in ('Positivi','Guariti','Deceduti') -- questo filtro per decidere quanti tipi visualizzare
group by data,denominazione_regione,tipo
order by data,denominazione_regione

In sostanza la funzione UNPIVOT normalizza la tabella:

anzichè avere ad esempio una tabella con i valori messi su colonne differenti

Data          Regione  Prositivi   Guariti
01/01/2020    Marche          10         5

avrò una tabella normalizzata con i valori in un’unica colonna

Data          Regione  Tipo       Valore
01/01/2020    Marche   Positivi       10
01/01/2020    Marche   Guariti         5

Nella query c’è un where sul campo Tipo, questo perchè potreste non voler graficare tutti i Tipi di valori presenti nei dati (Io ad esempio ne ho scelti solo 3)

Creiamo a questo punto un’area di tipo grafico di nome Evoluzione Temporale, 

[16_gr2.png]

negli attributi definiamo alcune opzioni quali:

TIPO grafico a Barre

LEGENDA sopra al grafico con funzionamento di nascondi e mostra voci abilitato (ridimensiona)

ASSE X di tipo temporale con Zoom abilitato

CURSORE DATI abilitato (per mostrare i dati quando il cursore è sopra il grafico)

nella serie ad essa associata andremo ad inserire la query sopra descritta compilando il Mapping Colonne come segue:

• Nome Serie: TIPO
• Etichetta : DATA
• Valore    : VALORE

Eseguite la pagina ad avrete il seguente risultato:

Potete abilitare e disabilitare le voci in Legenda, avere il valore del dato al passaggio del mouse e avete lo zoom (sotto al grafico) per contrarre o espandere l’asse temporale

Grafico a Barre Evoluzione Temporale

Il grafico a Linee, nella sua costruzione, è esattamente uguale a quello a barre, lascio come esercizio la sua realizzazione (compreso come inserire l’indicatore del rombo su ogni valore del grafico e compreso come affiancare il grafico all’altro anzichè averlo sotto) indicando solo la query di riferimento. Se ad esempio volessimo avere l’evoluzione temporale del numero di positivi per regione avremmo:

select data,
       denominazione_regione Regione,
       sum(TOTALE_POSITIVI )valore 
     from cor_dati_mvw 
group by data,denominazione_regione
order by data,denominazione_regione

Info e fonte dati

Concludiamo con un area dove mettiamo i riferimenti alla base dati e un po’ di autopromozione 🙂

Importante e infine indicare la data dell’ultima elaborazione, utile non tanto per le serie temporali ma per i KPI usati all’inizio.

Creiamo quindi un’Area di tipo Contenuto dinamico PL/SQL con le seguenti caratteristiche:

• MODELLO Alert
• OPZIONI MODELLO come in figura sotto
• CODICE PL/SQL quello sotto

dove andremo a scrivere qualche informazione statica con il comando htp.p()

htp.p('<h5>Elaborazione Dati a cura di <a href="javascript:window.open(''https://www.linkedin.com/in/robertocapancioni'', ''_blank'')">Roberto Capancioni</a></h5>');
htp.p('<h6>Data forniti dalla Protezione Civile</h6>');
htp.p('<h6>https://raw.githubusercontent.com/pcm-dpc/COVID-19/master/dati-json/dpc-covid19-ita-regioni.json</h6>');

Scriveremo poi anche qualche informazione dinamica e cioè la data Ultima Elaborazione

for r1 in (select max(data)data from cor_dati_mvw)
loop
htp.p('<h6>Data Ultima Elaborazione '||to_char(r1.data,'dd/mm/yyyy')||'</h6>');
end loop;

Siamo arrivati alla fine della nostra attività, il risultato è il seguente

Il link al nuovo report è questo

https://apex.oracle.com/pls/apex/rcone/r/report7/home

Il report ed i grafici presentati sono solo un esempio di quello che è possibile fare, si potrebbe continuare aggiungendo dei filtri o facendo maggior uso di funzioni analitiche. Spero comunque di aver incuriosito e aver dato lo spunto per utilizzare APEX anche come ambiente di reporting seppur a livello di base.