Fatti sui fatti: Organizzare le tabelle dei fatti nei sistemi di Data Warehouse

Il processo di definizione del tuo sistema di data warehousing (DWH) è iniziato. Hai delineato le tabelle delle dimensioni rilevanti, che si legano ai requisiti di business. Queste tabelle definiscono ciò che pesiamo, osserviamo e scaliamo. Ora abbiamo bisogno di definire come misuriamo.

Le tabelle dei fatti sono dove memorizziamo queste misure. Contengono dati di business che possono essere aggregati attraverso combinazioni di dimensioni. Ma il fatto è che le tabelle fattuali non sono così facilmente descritte – hanno sapori propri. In questo articolo, risponderemo ad alcune domande di base sulle tabelle dei fatti, ed esamineremo i pro e i contro di ogni tipo.

Cosa sono le tabelle dei fatti?

Nel senso più generale, le tabelle dei fatti sono le misure di un processo aziendale. Contengono per lo più dati numerici e corrispondono a un evento piuttosto che a una particolare relazione.

La caratteristica più importante di una tabella dei fatti, oltre alle misure, è la grana. La grana definisce quale livello di dettaglio viene osservato per un particolare evento. (Confronta questo con la frase “controlli a grana fine”, che significa che gli utenti possono controllare dettagli molto piccoli per il loro conto).

Più dettagliata è la grana della tabella dei fatti, meglio gestirà i requisiti aziendali imprevedibili. Lo svantaggio di una grana maggiore è che è richiesto più spazio fisico per la memorizzazione dei dati. Può anche causare prestazioni più lente.

Come sono strutturate le tabelle dei fatti?

Una tabella dei fatti generale consiste di due gruppi principali di attributi:

• Chiavi esterne alle tabelle dimensionali
• Misure

Le chiavi esterne sono autoesplicative; anche le dimensioni degenerate appartengono a questo gruppo. Una dimensione degenerata è una chiave di dimensione senza tabella di dimensione madre. Si verificano quando tutte le informazioni importanti sulla dimensione sono già nella tabella dei fatti. Gli esempi includono vari numeri di intestazione di controllo, numeri di biglietti, numeri d’ordine, ecc.

Misure (cioè metriche o fatti aziendali) in una tabella dei fatti possono essere:

• Additive: sommabili in qualsiasi dimensione
• Semi-additive: sommabili in alcune dimensioni
• Non-additive: non sommabili (es. vari rapporti)

Oltre alle misure e alle chiavi esterne, ci possono essere molte colonne tecniche. Le colonne tecniche sono utili per l’auditing e la manutenzione di basso livello del modello. I timestamp, che sono usati per segnare quando avvengono inserimenti o aggiornamenti nella tabella dei fatti, sono un esempio comune di colonna tecnica.

Per illustrare una generica tabella dei fatti, guardiamo uno schema a stella molto semplice:

In questo modello, abbiamo una singola tabella dei fatti circondata da tre tabelle di dimensione. Le nostre chiavi esterne per la tabella dei fatti includono:

• time_id – si riferisce alla tabella della dimensione tempo (dim_time)
• product_id – si riferisce alla tabella della dimensione prodotto (dim_product)
• store_id – si riferisce alla tabella della dimensione negozio (dim_store)
• pos_transaction – una dimensione degenerata. Queste sono tutte le informazioni su POS di cui abbiamo bisogno, quindi possiamo memorizzarle qui. Non c’è motivo di costruire un’altra tabella di dimensione.

Il nostro gruppo di chiavi di misura consiste in:

• sales_quantity – Le vendite di un prodotto in un momento in un negozio. Questa misura è additiva.
• sales_price – Il prezzo di un articolo venduto nel negozio. Questa è una misura non additiva.

C’è una colonna tecnica, time_inserted, che memorizza il tempo in cui viene inserita una riga.

Ora che abbiamo un’idea generale delle tabelle dei fatti, ci addentreremo nei loro diversi gusti. Ci sono quattro tipi di tabelle di fatti: transazione, istantanea periodica, istantanea accumulata e tabelle di fatti senza fatti.

Ogni gusto serve uno scopo nel rappresentare il business sottostante che il sistema di data warehousing supporta. Tuttavia, prima di addentrarci in ciò che fanno queste diverse tabelle dei fatti, parliamo di un importante fattore comune: la sparsità, o la quantità proporzionale di dati memorizzati in una tabella dei fatti. La sparsità è legata alla grana e ha un effetto sulle prestazioni delle query.

Che cos’è la sparsità delle tabelle dei fatti?

Quando progettiamo una tabella dei fatti, consideriamo la sua sparsità – cioè il numero di righe della tabella che sono popolate rispetto a quante sono vuote. Se riempiamo le tabelle dei fatti da molte tabelle sottostanti, è saggio stimare la sparsità. Facciamo la stima in base al livello di granularità della tabella dei fatti. Se la vostra tabella dei fatti corrisponde alla tabella di origine sottostante, potete usare la cardinalità della tabella sottostante per calcolare la sparsità.

Per calcolare la sparsità, prima troviamo il rapporto di riempimento – cioè il numero di celle non vuote sul totale delle celle. Per calcolare il rapporto di riempimento, dividete il numero di righe nella tabella di origine per il numero di righe distinte in ogni tabella di dimensione. La sparsità è quindi calcolata come 1-(f), dove f = rapporto di riempimento.

Esempio:

Supponiamo di riempire la tabella fact_retail_sale da due fonti: una tabella di database con 100.000 righe e un foglio elettronico con 20.000 righe. Queste righe sono riempite su base annuale. Le tabelle di dimensione sono: date (365 righe), prodotti (100 righe) e negozi (1.000 righe).

Calcoliamo la sparsità come:

1-(100.000 + 20.000)/(365*100*1.000) = 0,99671

Questa tabella dei fatti è sparsa, perché meno dell’1% delle sue righe hanno valori non nulli.

Una buona regola empirica è che consideriamo qualsiasi cosa sotto il 2% molto rada; qualsiasi cosa sopra non è così rada.

Tabelle di fatti transazione

Questo è il tipo più semplice e comune di tabella di fatti. La grana di questo tipo è una riga per transazione, o una riga per riga su una transazione. La grana di una tabella dei fatti delle transazioni è un punto nello spazio e nel tempo. Esse contengono i più piccoli dettagli di business.

Quando una transazione avviene, viene catturato un ampio contesto su di essa. Questo contesto crea un sacco di dettagli nelle tabelle delle dimensioni, quindi ce ne aspettiamo molti.

Una volta che inseriamo una riga in una tabella delle transazioni essa viene raramente, se non mai, rivisitata. Questo ci porta a considerare tecniche specifiche per popolare questi tipi di tabelle. Nessun aggiornamento significa un processo ETL (estrazione, trasformazione e caricamento) molto più semplice.

Pro: Una maggiore granularità permette il monitoraggio di attività di business dettagliate.

Cons: Problemi di performance nelle query. Difficoltà nell’interpretare i comportamenti di tendenza in punti fissi nel tempo.

Un esempio di una Transaction Fact Table

Guardiamo un tipico schema finanziario a stella:

Registriamo ogni singola azione del conto nella tabella dei fatti. I dati di esempio di questa tabella includono:

Nota: time_date è rappresentato come è mostrato nella dimensione dim_time, e la colonna tecnica è ignorata.

Come il proprietario del conto completa le transazioni, come depositare e prelevare denaro, registriamo ogni azione.

Con questa struttura possiamo rispondere a domande come: Quante transazioni al giorno elabora il nostro business? Qual è l’importo medio prelevato in un giorno? ecc.

Tabelle di fatti istantanee periodiche

Le tabelle di istantanee periodiche registrano la performance cumulativa del business in periodi di tempo predefiniti. Un intervallo predeterminato per prendere le istantanee è la chiave: giornaliero, settimanale, mensile, ecc. I risultati sono salvati nella tabella dei fatti dell’istantanea periodica.

Questo tipo di tabella dei fatti ci dà un sacco di libertà: possiamo incorporare qualsiasi informazione che descrive l’attività in un periodo di tempo.

Spostare da una transazione a una tabella periodica è possibile. (Ci sono alcuni algoritmi ordinati in SQL che si occupano di questo, come vedremo nei prossimi articoli). Si tratta solo di sommare le transazioni da uno snapshot all’altro. Proprio come le tabelle dei fatti delle transazioni hanno molte dimensioni, lo snapshot di solito ha meno dimensioni.

Pros: Guadagno di performance, visione longitudinale del business, compattezza fisica.

Cons: Grana inferiore.

Un esempio di una tabella fatti istantanea periodica

Consideriamo un altro schema finanziario a stella:

Prevediamo di aver costruito questa tabella fatti e il suo data mart dalla stessa fonte dell’esempio precedente. Potremmo anche aver costruito questa tabella dei fatti dai dati della tabella dei fatti precedente.

Abbiamo bisogno di mostrare il balance_amount ad ogni data di snapshot; in questo caso, la granularità dello snapshot è un giorno. Aggreghiamo tutte le transazioni in uscita su base giornaliera, e il tipo di deposito è l’aggiunta e il ritiro è la sottrazione. Il risultato è:

Nota: presentiamo time_date come è mostrato nella dimensione dim_time e la colonna tecnica è ignorata.

Ora possiamo rispondere ad alcune comuni domande aziendali, come: Quanti giorni un cliente ha un saldo positivo del conto in un mese? Qual è il saldo medio di uno specifico tipo di cliente ogni mese? ecc.

Riorganizzazione di una tabella di fatti istantanea periodica

Potreste aver notato una certa ridondanza nei dati visualizzati nell’esempio precedente. A volte anche la tabella dei fatti dell’istantanea è troppo grande e le prestazioni crollano. Cosa fare allora? Possiamo riorganizzare la nostra snapshot fact table in qualcosa di più compatto.

Abbiamo aggiunto due nuove colonne, date_from e date_to. Esse rappresentano il periodo di tempo in cui il balance_amount era valido.

I dati in questa tabella sarebbero così:

Ogni volta che il balance_amount cambia, chiudiamo il periodo di tempo e ne apriamo uno nuovo.

Nota che la colonna date_to contiene un delimitatore temporale non inclusivo. Dobbiamo tenerne conto quando interroghiamo i dati. Troviamo anche una data interessante – 31/12/9999. Questa data dimostra che l’ultima riga è l’ultimo saldo conosciuto. La aggiorneremo quando il saldo cambierà.

Di seguito, possiamo vedere l’SQL che usiamo per interrogare un’istantanea ad una data particolare (:date).

SELECT ft.*FROMfact_account_snapshot ftWHERE date_from 
Accumulating Snapshot Fact Tables
The accumulating snapshot fact table is the least common type, but it's also by far the most interesting and challenging. We use this table type when we represent processes that have a definite beginning and end.They are most appropriate when users must perform pipeline and workflow analysis.
Accumulating snapshot tables usually have multiple date foreign keys, each corresponding to a major milestone in the workflow. And while the processes captured in this type of table have beginnings and ends, intermediate steps are recorded as well.
Transaction fact tables may have several dates associated with each row; the accumulating fact table allows for many of the date values to be unknown when the row is first loaded. This is unique among fact tables, and it can lead to anomalies – incomplete dimensions – in dimensional modeling.
We solve this by adding a default value to the missing fields in the fact table and the appropriate dimension table. As we revisit the row containing specific workflows, we must implement a different type of ETL for working with these tables.
Pros: Enables complex analysis of tables and workflow analysis.
Cons: High ETL complexity.
An Example of an Accumulating Snapshot Fact Table
In this example, imagine that we have to analyze the effectiveness of our loan approval process, from initial application to final funding. Obviously, we first need to prepare our data!

  

Our loan approval process has four steps:
 
Loan application from a bank customer
 
Submission of complete documentation needed for loan approval (appraisal, credit status etc.)
 
Approval of the loan application from a higher-ranking bank employee
 
Funding the loan
 
One row in our fact table represents one instance of this process.
As we see in the data table, not all our workflows are complete; future actions will make us revisit the fact table and update our data. This is the main difference between this and other types of fact table. 
Some questions that we can answer with this type of fact table are: Where is the bottleneck in the loan approval process? What is causing it? What is the average time needed for end-to-end approval? etc.
 Let's look at the data from the fact table. We'll split the display into two tables:


Looking at the data, we see a strange date: 01.01.1900. This date represents a missing or "not yet filled" date. It is more practical to have a default value for missing key data so we don't have abnormalities when we do a roll-up. 
We can also draw some conclusions from this data. Bigger loans take more time to process. This is normal because they need more documentation and safety checks. Smaller loans complete the process much faster.
Factless Fact Tables
This inconspicuous fact table can also be found in the data warehouse modeling world. The factless fact table does not have any measurements; it only holds foreign keys to dimensional tables. This information is enough to answer relevant business questions.
Pros: Simplicity
Cons: Limited usefulness
An Example of a Factless Fact Table
In the following example, we see a star schema featuring a factless fact table. The schema is used for tracking student attendance, with four dimensional tables representing courses, facilities, dates, and departments.


With this schema, we can find answers to questions like: How many students attended a class last month? What course has the lowest attendance this year? What facility is not being used? etc.
A simple query can answer the last question. The code would be:
SELECT course_id, count(*) number of attendanceFROMfact_student_attendance ftJOIN dim_course dc ON (dc.course_id = ft.course_id)JOIN dim_time dt ON (dt.time_id = ft.time_id)WHERE dt.time_year = 2016GROUP BY course_idORDER BY count(*) desc
La nostra risposta viene restituita nella prima riga della query.
Le tabelle dei fatti sono la carne del mondo del data warehousing. La loro dimensione può essere pari a molti terabyte e occupano la maggior parte dello spazio in un data warehouse. Un errore all'inizio del processo di progettazione delle tabelle dei fatti genera molti problemi, che non diventano più facili da risolvere man mano che il magazzino si evolve! 
È possibile riorganizzare i dati da un tipo di fact table ad un altro. Esploreremo le tecniche per farlo in un prossimo post. Che mi dici delle tue esperienze con diversi tipi di tabelle di fatti? Siete riusciti a mescolarle in qualche modo nuovo ed eccitante? Se ci siete riusciti, fatemelo sapere. 
 .