Power BI e i Database Relazionali

In questo post parleremo dei database relazionali, della loro struttura e delle regole fondamentali che è utile conoscere per imparare a importare e gestire correttamente i dati in Power BI. Comprendere come funzionano i database relazionali è infatti il primo passo per utilizzare al meglio Power BI, perché questo strumento si basa su molte delle stesse logiche che guidano la progettazione e l’organizzazione dei dati nei sistemi relazionali tradizionali. In altre parole, imparare i principi dei database relazionali — come le tabelle, le relazioni e le chiavi — ci aiuta a capire come Power BI struttura e collega i dati al suo interno, e quindi a costruire modelli più coerenti, performanti e facili da analizzare. Lo scopo di questa pagina è offrire una panoramica di alto livello, chiara e accessibile, dei concetti fondamentali che ci interessano, senza entrare nei dettagli tecnici più complessi.

Cos’è un Database Relazionale

Un database relazionale (Relational DataBase Management System, o RDBMS) è un sistema multi-utente, usato per memorizzare e gestire grandi quantità di dati in modo centralizzato, che organizza le informazioni in tabelle, poste in relazione logica l’una con l’altra. Rispetto ad uno strumento più semplice come Excel, un database relazionale evita errori e duplicazioni dati, permette di memorizzare e cercare informazioni con facilità, generare reportistica, controllare l’accesso alle informazioni, e si integra facilmente con altri sistemi informativi quali ERP o CRM.

Nelle grandi aziende, i database relazionali — come ad esempio Microsoft SQL Server, Oracle, IBM DB2, PostgreSQL — sono tra le fonti dati più usate, sia per Power BI, che per le applicazioni di Business Intelligence in generale. I database relazionali sono alla base di moltissime applicazioni informatiche, come la registrazione delle vendite, la gestione dell’inventario dei prodotti, o la conservazione delle informazioni sui clienti aziendali.

Le Tabelle di un Database Relazionale

In un database relazionale, tutte le informazioni vengono memorizzate all’interno di tabelle, che rappresentano il modo più ordinato e strutturato per organizzare i dati.

Quando si progetta un database, l’obiettivo principale è tradurre in forma logica le regole e i flussi di lavoro che descrivono un determinato contesto o caso di business. Per farlo, è necessario innanzitutto identificare le entità principali coinvolte nel processo, ossia gli elementi fondamentali del mondo reale di cui vogliamo tenere traccia. Ogni entità può essere rappresentata da una tabella del database (ad esempio un cliente, un prodotto, una vendita, un pagamento o un fornitore). In questo modo, il modello relazionale diventa una rappresentazione logica e coerente della realtà operativa.

In un database relazionale, una tabella può essere paragonata a un foglio di calcolo ben organizzato: ogni riga rappresenta un record, ovvero un singolo elemento, oggetto o evento di cui si vogliono conservare le informazioni — ad esempio un cliente, una vendita o un prodotto. Le colonne definiscono gli attributi di questi elementi: ad esempio, una tabella “Prodotti” potrebbe includere colonne come “prodotto_id”, “nome”, “prezzo”, “colore” e “peso”:

In questo esempio, prodotto_id è la colonna che definisce la chiave primaria della tabella, un elemento che può essere utilizzato per creare relazioni logiche con altre tabelle.

Chiavi Primarie, Chiavi Esterne e Relazioni tra Tabelle

In un database relazionale, due tabelle possono essere collegate tra loro attraverso una relazione definita tramite una coppia di chiavi: la chiave primaria (primary key) e la chiave esterna (foreign key). Una chiave è un valore univoco che permette di identificare in modo preciso un determinata riga all’interno di una tabella, garantendo così l’integrità e la coerenza dei dati.

La chiave primaria (primary key) è uno degli elementi più importanti della tabella. Si tratta di un campo — o, in alcuni casi, di un insieme di campi — che ha il compito di identificare in modo univoco ogni record presente in essa.

Grazie alla chiave primaria, il database può distinguere chiaramente un elemento da un altro, evitando duplicazioni o ambiguità nei dati. Inoltre, la presenza di una chiave primaria consente al sistema di ricercare e accedere rapidamente a un’informazione specifica, migliorando l’efficienza delle operazioni. In altre parole, una chiave primaria è come il codice fiscale che identifica in modo univoco una persona, o come la targa che distingue una specifica automobile.

E’ buona pratica definire sempre una chiave primaria per ciascuna tabella del database. Ad esempio, nella tabella Prodotti, la colonna prodotto_id svolge questa funzione: ogni valore di prodotto_id è unico e permette di identificare senza possibilità di errore un determinato prodotto all’interno della base dati.

Una chiave esterna (foreign key) è un campo, o talvolta un insieme di campi, che serve a collegare una tabella ad un’altra tabella. Essa rappresenta il legame logico tra due insiemi di dati che, pur appartenendo a tabelle diverse, sono connessi da una relazione significativa nel mondo reale.

La chiave esterna è strettamente collegata al concetto di chiave primaria: il suo valore, infatti, fa riferimento alla chiave primaria di un’altra tabella, stabilendo così un vincolo che mantiene coerenza e integrità tra le informazioni. Grazie a questo meccanismo, il database è in grado di mettere in relazione i dati, garantendo che ogni riferimento punti sempre a un elemento realmente esistente. Ad esempio, nel nostro database, potremmo avere una tabella “Vendite”, definita nel modo seguente:

dove:

• il campo (o colonna) vendita_id è la chiave primaria di Vendite,
• il campo prodotto_id è una chiave esterna di “Vendite”, che punta ad un singolo record della tabella Prodotti, referenziando la chiave primaria di tale tabella.

Con questa struttura tabellare, ogni vendita viene collegata in modo esplicito al prodotto corrispondente, e il database può assicurarsi che non vengano registrate vendite di prodotti inesistenti. Usando un client database come SQL Server Management Studio, possiamo vedere graficamente la relazione tra le due tabelle in questo modo:

In particolare:

• i campi prodotto_id e vendita_id delle rispettive tabelle sono identificati come chiave primaria tramite l’iconcina a forma di chiave;
• la relazione tra le due tabelle è raffigurata da un segmento di retta che unisce le due tabelle;
• la chiave posta in cima al segmento, a lato della tabella Prodotti, indica che la chiave primaria prodotto_id “migra” dalla tabella Prodotti alla tabella Vendite come chiave esterna;
• ogni record della tabella Vendite utilizza il campo prodotto_id come referenza al record della tabella Prodotti associato ad essa.

Ad esempio, considerando la struttura del database precedente, se volessimo sapere i dettagli del prodotto associato a una specifica vendita, potremmo scrivere una query di questo tipo:

-- select all data from the product associated to the current sales
-- (let's say we have vendita_id = 101)
SELECT
    vendite.data as 'data_vendita',
    prodotti.nome,
    prodotti.prezzo,
    prodotti.colore,
    prodotti.peso
FROM
    vendite
        INNER JOIN prodotti
            ON vendite.prodotto_id = prodotti.prodotto_id
WHERE
    vendite.vendita_id = 101

che ritornerebbe una riga con dati simili a questi:

Qui il comando SQL INNER JOIN sfrutta la relazione tra le due tabelle per combinare i dati. Il database esamina il record della tabella Vendite che ha chiave = 101, legge il valore del suo campo prodotto_id, e cerca nella tabella Prodotti il record corrispondente. Se esso esiste, il database unisce le informazioni richieste delle due tabelle in un’unica riga, che viene ritornata all’utente.

Aggiungendo una tabella Clienti, otteniamo uno schema di questo tipo:

In questo caso, la tabella Vendite contiene due chiavi esterne: prodotto_id, e la nuova cliente_id, che referenzia la tabella Clienti. Ogni record di Vendite utilizza quindi i campi prodotto_id e cliente_id per referenziare le informazioni relative al prodotto e al cliente associato a tale transazione. Se volessimo recuperare i dati del prodotto e del cliente che lo ha acquistato, potremmo usare una query simile alla precedente:

-- select all data from the product associated to the current sales,
-- and some customer's information
SELECT
    vendite.data as 'data_vendita',
    prodotti.nome as 'prodotto',
    prodotti.prezzo,
    prodotti.colore,
    prodotti.peso,
    clienti.nome as 'cliente_nome',
    clienti.cognome as 'cliente_cognome',
    codice_fiscale
FROM
    vendite
        INNER JOIN prodotti
            ON vendite.prodotto_id = prodotti.prodotto_id
        INNER JOIN clienti
            ON vendite.cliente_id = clienti.cliente_id
WHERE
    vendite.vendita_id = 101

Tipi di Relazione

Esistono tre tipi principali di relazioni, che si distingono per la loro cardinalità, la quale indica quanti elementi di una tabella possono essere associati a quanti elementi dell’altra — cioè il “numero massimo” di corrispondenze possibili. Questi tre tipi di relazione sono: “Uno a Uno”, “Uno a Molti” e “Molti a Molti”.

Relazione “Uno a Uno”

In una relazione “Uno a Uno” (“One to One”, o “1:1”), un record di una tabella corrisponde a un solo record dell’altra tabella, e viceversa. Una delle due tabelle rappresenta l’estensione logica dell’altra:

Di solito, in una relazione 1:1, entrambe le tabelle condividono lo stesso valore di chiave primaria. In questo esempio, la chiave primaria cliente_id della tabella Clienti_Profilo è anche marcata come chiave esterna della tabella Clienti.

Da un punto di vista logico, una relazione 1:1 dovrebbe sempre essere unita in un’unica tabella. Tuttavia, in alcuni casi potrebbe essere utile mantenere le due tabelle separate: ad esempio, per motivi di sicurezza, se una delle due contiene dati sensibili, oppure per ragioni di performance o di organizzazione, quando si vuole alleggerire la struttura principale o isolare dati utilizzati di rado.

Relazione “Uno a Molti”

In una relazione “Uno a Molti” (“One to Many”, o “1:*”), un record della prima tabella può essere collegato a più record della seconda, mentre ciascun record della seconda si collega a uno solo della prima:

In una relazione di questo tipo, la tabella sul lato “Molti” deve avere una chiave esterna che punti alla chiave primaria della tabella sul lato “Uno”. Questo tipo di relazione è quella più comune nei database relazionali. Ad esempio, considerando la Figura 4:

• un prodotto può essere venduto più volte;
• una vendita referenzia un solo prodotto.

Relazione “Molti a Molti”

In una relazione “Molti a Molti” (“Many to Many “, o “*:*”), un record di una tabella può essere collegato a più record dell’altra — e viceversa.

Ad esempio:

• Uno studente può iscriversi a molti corsi;
• Ogni corso può avere molti studenti.

Nei database relazionali, per gestire una relazione “Molti a Molti”, bisogna creare una tabella intermedia, detta anche “tabella ponte” o “tabella di giunzione”. Nel nostro esempio, la tabella Studenti_Corsi serve a rappresentare le relazioni effettive tra le due tabelle principali, Studenti e Corsi.

La tabella Studenti_Corsi utilizza come chiave primaria composta le due chiavi provenienti dalle tabelle collegate (studente_id e corso_id), che insieme identificano in modo univoco ogni relazione. Oltre a queste chiavi, la tabella può contenere altri campi che descrivono gli attributi della relazione — ad esempio, data_iscrizione, che indica quando lo studente si è iscritto al corso.

Relazioni e Integrità Referenziale

Un’altra caratteristica interessante dei database relazionali è l’applicazione dell’integrità referenziale, una regola di coerenza dei dati che assicura che le relazioni tra le tabelle restino sempre valide. Consideriamo l’esempio di Vendite e Prodotti raffigurato in Figura 1: tramite la loro relazione, l’integrità referenziale garantisce che non possano esistere vendite collegate a prodotti inesistenti.

Tecnicamente, mediante la chiave esterna Vendite.prodotto_id che referenzia Prodotti.prodotto_id, il database controlla automaticamente che ogni valore del campo Vendite.prodotto_id esista davvero come chiave primaria di Prodotti. Se provassimo ad aggiornare o a creare un record nella tabella Vendite con un valore Vendite.prodotto_id inesistente, il database annullerebbe l’operazione, restituendo un errore di questo tipo:

In Excel, invece, non esiste un vero concetto di chiave primaria e chiave esterna. Quindi, le “relazioni” (simulate con formule o tabelle pivot) non sono controllate automaticamente. Possiamo scrivere qualsiasi valore in una cella usata come chiave, anche se esso non corrisponde a un dato “valido” in un altro foglio. Per questo motivo, in Excel possiamo creare facilmente errori o incongruenze (vendite con prodotti inesistenti, chiavi primarie duplicate, ecc.), e la qualità dei dati dipende completamente dai controlli dell’utente. Con l’aumentare delle informazioni, mantenere la coerenza diventa molto difficile e rischioso.

Dati Normalizzati

Ma perché mai dovremmo complicarci la vita utilizzando più tabelle, quando potremmo inserire tutto in un’unica, grande tabella — proprio come facciamo normalmente in un foglio di calcolo? La risposta è semplice: nei database relazionali, questa scelta serve per evitare la duplicazione dei dati, e le potenziali incoerenze associate a tale copia. Con il termine “incoerenza”, intendiamo la situazione in cui una stessa informazione risulta registrata in modi diversi o con valori contrastanti in più parti del database.

Immagina un semplice archivio clienti e vendite in Excel:

Qui abbiamo una sola tabella — sembra comoda, ma è denormalizzata, ovvero è una tabella unica che contiene dati provenienti da più entità. In altre parole, è una versione “tutto in uno” dei dati, dove le informazioni vengono ripetute per rendere più facile la consultazione o l’analisi. In questo contesto:

• il nome e l’email del cliente si ripetono per ogni vendita;
• se Anna cambia email, dovremo aggiornare più righe;
• se cancelliamo per errore una vendita, potremmo perdere anche le informazioni del cliente.

Il modello relazionale risolve tutti questi problemi strutturando il database in forma normale, ovvero separando i dati in più tabelle — ciascuna dedicata a un’entità logica — e collegandole tramite relazioni definite dalla coppia (chiave primaria, chiave esterna). Il modello relazionale elimina la ridondanza, e mantiene la coerenza dei dati. La normalizzazione serve a garantire che:

• ogni tabella contenga un solo tipo di informazione (una sola “entità”);
• ogni informazione sia archiviata una sola volta;
• i collegamenti tra le tabelle siano gestiti in modo logico.

Un database in forma normale (cioè progettato secondo le regole della normalizzazione) presenta solitamente uno schema complesso, composto da molte tabelle e da numerose relazioni tra di esse. Ogni tabella è dedicata a un singolo tipo di informazione, e, in linea teorica, può essere collegata a qualsiasi altra tabella attraverso chiavi e relazioni logiche. Questa elevata articolazione rende il modello molto flessibile, ma allo stesso tempo più difficile da interpretare visivamente: non è sempre immediato individuare una tabella centrale o più importante rispetto alle altre. In molti casi, infatti, lo schema può contenere più tabelle “principali”, ognuna rappresentante un’area funzionale del sistema Ad esempio:

Dati Denormalizzati

Come abbiamo visto, in un database relazionale normalizzato, i dati sono distribuiti in molte tabelle collegate tra loro. Questa situazione è ideale per garantire la coerenza dei dati, evitare duplicazioni e ottimizzare le scritture. Tuttavia, quando vogliamo leggere e analizzare i dati per creare dei report, avere le informazioni sparse su molte tabelle può diventare inefficiente. Ecco allora la necessità di avere una vista “denormalizzata” dei dati, cioè una struttura che riunisca in una o in poche tabelle tutte le informazioni provenienti da più tabelle normalizzate, dove privilegiamo la velocità di lettura, rispetto alla pulizia logica. In sintesi:

Quando abbiamo necessità di avere dati denormalizzati, abbiamo almeno due opzioni per gestire questa necessità:

1. dentro al database transazionale:
   usare delle view costruite con delle query di join su tabelle normalizzate;
2. fuori dal database transazionale:
   usare un nuovo database di tipo Data Warehouse, che usa tabelle fisiche denormalizzate, e viene caricato con i dati del database OLTP mediante un processo ETL (Extract, Transform, Load).

Uso delle View

Le View sono tabelle virtuali¹ costruite con query SQL, che uniscono i dati provenienti da più tabelle fisiche, o da altre View, spesso usando operazioni di join e condizioni di filtro. Le view non duplicano fisicamente i dati: vengono calcolate “al volo” ogni volta che vengono interrogate. Sono perfette quando serve una vista semplificata dei dati operativi, senza modificare la struttura del database esistente.

Le View sono molto utili come strumento intermedio quando esiste la necessità di creare della reportistica o delle query che richiedono dati già integrati, “denormalizzati” per comodità di lettura, nascondendo la complessità delle relazioni tra le tabelle sorgenti. In altre parole, una View fornisce una “finestra virtuale” sui dati transazionali, utile per creare elenchi, report o controlli operativi di base.

Ad esempio, possiamo creare una View chiamata View_Vendite che unisce i dati provenienti dalle singole tabelle Clienti, Prodotti e Vendite, tramite questo comando SQL:

-- questa dichiarazione di creazione della view è riportata solo per completezza,
-- ma non ci interessa entrare nei suoi dettagli implementativi.
CREATE VIEW [dbo].[View_Vendite]
AS
   SELECT dbo.clienti.nome    AS nome_cliente,
          dbo.clienti.cognome AS cognome_cliente,
          dbo.prodotti.nome   AS nome_prodotto,
          dbo.prodotti.prezzo,
          dbo.vendite.quantita,
          dbo.vendite.data    AS data_vendita
   FROM   dbo.clienti
          -- unione clienti -> vendite
          INNER JOIN dbo.vendite
                  ON dbo.clienti.cliente_id = dbo.vendite.cliente_id
          -- unione prodotti -> vendite
          INNER JOIN dbo.prodotti
                  ON dbo.vendite.prodotto_id = dbo.prodotti.prodotto_id
GO

Definita la View, possiamo recuperare i dati con una semplice query di questo tipo:

select * from [dbo].[View_Vendite] where cliente_id = 1

e quello che otteniamo, è una tabella denormalizzata, “in stile Excel”, facile da leggere e analizzare, dove ogni riga contiene tutti i dati di nostro interesse:

Tuttavia, in un database OLTP, le View non sono progettate per gestire grandi volumi di letture analitiche o aggregazioni complesse. Ogni volta che una View viene interrogata, il database esegue in tempo reale la query che la definisce. Se questa query contiene molte join, filtri o calcoli, l’impatto sulle prestazioni può diventare significativo. In un sistema OLTP, dove il carico principale è costituito da inserimenti, aggiornamenti e cancellazioni rapidi, questo tipo di “peso computazionale” non è ideale, poichè potrebbe rallentare le transazioni operative e compromettere la reattività del sistema. In questo caso, meglio usare un database di tipo Data Warehouse.

Uso di un Database Data Warehouse

Un Data Warehouse (DW) è un database relazionale progettato specificamente per l’analisi dei dati e ottimizzato per garantire alte prestazioni nelle query analitiche. Non sostituisce il database OLTP, ma lo affianca, e viene alimentato attraverso processi ETL (Extract, Transform, Load), durante i quali i dati provenienti dai sistemi transazionali vengono estratti, trasformati, integrati e denormalizzati. Il risultato è uno schema pensato per la lettura e l’analisi aggregata, in cui le informazioni più utilizzate sono facilmente accessibili, riducendo la necessità di complesse operazioni di join tipiche dei database transazionali.

Lo schema a stella (Star Schema) solitamente impiegato in un database Data Warehouse è molto più semplice e intuitivo rispetto allo schema complesso di un database OLTP. Nel modello a stella, i dati sono organizzati attorno a una tabella centrale (Fact Table), normalizzata, che contiene i dati quantitativi, come vendite o ordini. Questa tabella centrale è collegata a un numero limitato di tabelle descrittive denormalizzate (Dimension Table), che forniscono i dati di contesto, come ad esempio: prodotto, cliente, area geografica, ecc. Questa struttura è chiara, facilmente leggibile e ottimizzata per le analisi, poichè le query, per recuperare i dati, richiedono meno join, e quindi sono più veloci da eseguire:

Un Data Warehouse può anche usare uno schema a “fiocco di neve” (Snowflake Schema), che riprende il concetto dello schema a stella, ma può usare Dimension Table normalizzate, dove le informazioni descrittive possono venire separate in tabelle aggiuntive. Queste Dimension Table formano una struttura “ramificata”, che assomiglia appunto a un fiocco di neve. Ad esempio:

Questo schema si avvicina un po’ di più alla logica dei database OLTP, pur restando nel contesto analitico di un Data Warehouse. Può essere usato per avere maggiore flessibilità nella gestione di tabelle Dimension di grandi dimensioni (con molti campi o molte righe), o che rappresentano strutture gerarchiche (ad esempio: Città → Regione → Paese, oppure Categoria → Sottocategoria).

Ricapitolando

Power BI è uno strumento di analisi e visualizzazione dei dati, pensato per leggere, aggregare e correlare informazioni in modo veloce. Per funzionare al meglio, Power BI ha bisogno che i dati ricevuti in fase di importazione siano già organizzati in modo analitico, cioè in una forma simile a quella di un Data Warehouse.

Di contro, quando usiamo come fonte dati un database OLTP, Power BI riceve informazioni da un sistema che è altamente normalizzato, contiene molte tabelle e relazioni complesse, ed è progettato per registrare transazioni, ma non è ottimizzato per analizzare dati.

In questo scenario, è necessario “insegnare” a Power BI — attraverso il Power Query Editor — a eseguire tutte le trasformazioni, unioni e denormalizzazioni necessarie per costruire uno schema analitico che si avvicini il più possibile a uno schema a stella o, in alcuni casi, a uno schema a fiocco di neve. In sostanza, Power BI finisce per comportarsi come un piccolo data warehouse, ricreando al proprio interno la logica e le trasformazioni concettuali che un vero Data Warehouse esterno avrebbe già dovuto svolgere prima della fase di analisi.