schema types data warehouse modeling star snowflake schema
Questo tutorial spiega vari tipi di schemi di data warehouse. Scopri cos'è lo schema a stella e lo schema a fiocco di neve e la differenza tra lo schema a stella e lo schema a fiocco di neve:
In questo Esercitazioni su Data Warehouse per principianti , abbiamo esaminato in modo approfondito Modello di dati dimensionali in Data Warehouse nel nostro precedente tutorial.
In questo tutorial, impareremo tutto sugli schemi di data warehouse utilizzati per strutturare i data mart (o) tabelle di data warehouse.
come eseguire un file json
Iniziamo!!
Destinatari
- Sviluppatori e tester di data warehouse / ETL.
- Professionisti di database con conoscenza di base dei concetti di database.
- Amministratori di database / esperti di big data che desiderano comprendere le aree del data warehouse / ETL.
- Laureati / matricole che cercano lavori nel data warehouse.
Cosa imparerai:
Schema del data warehouse
In un data warehouse, uno schema viene utilizzato per definire il modo di organizzare il sistema con tutte le entità del database (tabelle dei fatti, tabelle delle dimensioni) e la loro associazione logica.
Ecco i diversi tipi di schemi in DW:
- Star Schedule
- Schema SnowFlake
- Galaxy Schema
- Schema dell'ammasso stellare
# 1) Programma delle stelle
Questo è lo schema più semplice ed efficace in un data warehouse. Una tabella dei fatti al centro circondata da più tabelle delle dimensioni assomiglia a una stella nel modello Star Schema.
La tabella dei fatti mantiene relazioni uno-a-molti con tutte le tabelle delle dimensioni. Ogni riga in una tabella dei fatti è associata alle righe della tabella delle dimensioni con un riferimento di chiave esterna.
Per questo motivo, la navigazione tra le tabelle in questo modello è facile per eseguire query sui dati aggregati. Un utente finale può facilmente comprendere questa struttura. Quindi tutti gli strumenti di Business Intelligence (BI) supportano notevolmente il modello di schema Star.
Durante la progettazione degli schemi a stella, le tabelle delle dimensioni vengono volutamente denormalizzate. Sono ampi con molti attributi per memorizzare i dati contestuali per una migliore analisi e reporting.
Vantaggi dello schema a stella
- Le query utilizzano join molto semplici durante il recupero dei dati e quindi le prestazioni delle query sono migliorate.
- È semplice recuperare i dati per i rapporti, in qualsiasi momento per qualsiasi periodo.
Svantaggi di Star Schema
- Se sono presenti molti cambiamenti nei requisiti, si sconsiglia di modificare e riutilizzare lo schema a stella esistente a lungo termine.
- La ridondanza dei dati è maggiore poiché le tabelle non sono divise gerarchicamente.
Di seguito viene fornito un esempio di schema a stella.
Interrogazione di uno schema a stella
Un utente finale può richiedere un report utilizzando gli strumenti di Business Intelligence. Tutte queste richieste verranno elaborate creando internamente una catena di 'query SELECT'. Le prestazioni di queste query avranno un impatto sul tempo di esecuzione del report.
Dall'esempio dello schema Star sopra, se un utente aziendale desidera sapere quanti romanzi e DVD sono stati venduti nello stato del Kerala nel gennaio 2018, è possibile applicare la query come segue sulle tabelle dello schema Star:
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Product pdim, Sales sfact, Store sdim, Date ddim WHERE sfact.product_id = pdim.product_id AND sfact.store_id = sdim.store_id AND sfact.date_id = ddim.date_id AND sdim.state = 'Kerala' AND ddim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Risultati:
| Nome del prodotto | Quantità_Venduta | |
|---|---|---|
| 7 | Chiunque può facilmente comprendere e progettare lo schema. | È difficile capire e progettare lo schema. |
| Romanzi | 12.702 | |
| DVD | 32.919 |
Spero che tu abbia capito quanto sia facile interrogare uno schema a stella.
# 2) Schema SnowFlake
Lo schema a stella funge da input per progettare uno schema SnowFlake. Lo sfaldamento della neve è un processo che normalizza completamente tutte le tabelle delle dimensioni da uno schema a stella.
La disposizione di una tabella dei fatti al centro circondata da più gerarchie di tabelle delle dimensioni ha l'aspetto di uno SnowFlake nel modello di schema SnowFlake. Ogni riga della tabella dei fatti è associata alle sue righe della tabella delle dimensioni con un riferimento di chiave esterna.
Durante la progettazione degli schemi SnowFlake, le tabelle delle dimensioni vengono normalizzate in modo mirato. Le chiavi esterne verranno aggiunte a ogni livello delle tabelle delle dimensioni per collegarle al relativo attributo padre. La complessità dello schema SnowFlake è direttamente proporzionale ai livelli gerarchici delle tabelle delle dimensioni.
Vantaggi dello schema SnowFlake:
- La ridondanza dei dati viene completamente rimossa creando nuove tabelle delle dimensioni.
- Se confrontato con lo schema a stella, viene utilizzato meno spazio di archiviazione dalle tabelle delle dimensioni Snow Flaking.
- È facile aggiornare (o) mantenere le tabelle Snow Flaking.
Svantaggi dello schema SnowFlake:
- A causa delle tabelle dimensionali normalizzate, il sistema ETL deve caricare il numero di tabelle.
- Potrebbero essere necessari join complessi per eseguire una query a causa del numero di tabelle aggiunte. Quindi le prestazioni delle query saranno ridotte.
Di seguito viene fornito un esempio di schema SnowFlake.
Le tabelle delle dimensioni nel diagramma SnowFlake sopra sono normalizzate come spiegato di seguito:
- La dimensione della data viene normalizzata nelle tabelle trimestrali, mensili e settimanali lasciando gli ID delle chiavi esterne nella tabella delle date.
- La dimensione del negozio viene normalizzata per comprendere la tabella per lo stato.
- La dimensione del prodotto viene normalizzata in Brand.
- Nella dimensione Cliente, gli attributi connessi alla città vengono spostati nella nuova tabella Città lasciando un ID chiave esterna nella tabella Cliente.
Allo stesso modo, una singola dimensione può mantenere più livelli di gerarchia.
Diversi livelli di gerarchie dal diagramma sopra possono essere indicati come segue:
- ID trimestrale, ID mensile e ID settimanali sono le nuove chiavi surrogate create per le gerarchie di dimensioni Data e quelle sono state aggiunte come chiavi esterne nella tabella delle dimensioni Data.
- State id è la nuova chiave surrogata creata per la gerarchia della dimensione Store ed è stata aggiunta come chiave esterna nella tabella delle dimensioni Store.
- Brand id è la nuova chiave surrogata creata per la gerarchia della dimensione Product ed è stata aggiunta come chiave esterna nella tabella delle dimensioni Product.
- City id è la nuova chiave surrogata creata per la gerarchia della dimensione del cliente ed è stata aggiunta come chiave esterna nella tabella delle dimensioni del cliente.
Interrogazione di uno schema a fiocco di neve
Possiamo generare lo stesso tipo di report per gli utenti finali come quello delle strutture dello schema a stella con gli schemi SnowFlake. Ma le domande sono un po 'complicate qui.
Dall'esempio di schema SnowFlake sopra, genereremo la stessa query che abbiamo progettato durante l'esempio di query dello schema Star.
Ciò significa che se un utente aziendale desidera sapere quanti romanzi e DVD sono stati venduti nello stato del Kerala nel gennaio 2018, è possibile applicare la query come segue alle tabelle dello schema SnowFlake.
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Sales sfact INNER JOIN Product pdim ON sfact.product_id = pdim.product_id INNER JOIN Store sdim ON sfact.store_id = sdim.store_id INNER JOIN State stdim ON sdim.state_id = stdim.state_id INNER JOIN Date ddim ON sfact.date_id = ddim.date_id INNER JOIN Month mdim ON ddim.month_id = mdim.month_id WHERE stdim.state = 'Kerala' AND mdim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Risultati:
| Nome del prodotto | Quantità_Venduta |
|---|---|
| Romanzi | 12.702 |
| DVD | 32.919 |
Punti da ricordare durante l'interrogazione delle tabelle dello schema Star (o) SnowFlake
Qualsiasi query può essere progettata con la struttura seguente:
Clausola SELECT:
- Gli attributi specificati nella clausola select vengono visualizzati nei risultati della query.
- L'istruzione Select utilizza anche i gruppi per trovare i valori aggregati e quindi dobbiamo utilizzare la clausola group by nella condizione where.
Clausola FROM:
- Tutte le tabelle dei fatti essenziali e le tabelle dimensionali devono essere scelte in base al contesto.
Dove la clausola:
- Gli attributi di dimensione appropriati sono menzionati nella clausola where unendosi agli attributi della tabella dei fatti. Le chiavi surrogate delle tabelle delle dimensioni vengono unite alle rispettive chiavi esterne delle tabelle dei fatti per fissare l'intervallo di dati da interrogare. Fare riferimento all'esempio di query dello schema a stella scritto sopra per capirlo. Puoi anche filtrare i dati nella clausola from stessa se nel caso in cui stai utilizzando join interni / esterni lì, come scritto nell'esempio dello schema SnowFlake.
- Gli attributi di dimensione sono anche menzionati come vincoli sui dati nella clausola where.
- Filtrando i dati con tutti i passaggi precedenti, vengono restituiti i dati appropriati per i rapporti.
In base alle esigenze aziendali, è possibile aggiungere (o) rimuovere fatti, dimensioni, attributi e vincoli a una query di schema a stella (o) schema SnowFlake seguendo la struttura sopra. Puoi anche aggiungere sottoquery (o) unire risultati di query diversi per generare dati per rapporti complessi.
#3) Galaxy Schema
Uno schema galattico è anche noto come schema di costellazione dei fatti. In questo schema, più tabelle dei fatti condividono le stesse tabelle delle dimensioni. La disposizione delle tabelle dei fatti e delle tabelle delle dimensioni sembra una raccolta di stelle nel modello di schema Galaxy.
Le dimensioni condivise in questo modello sono note come dimensioni conformi.
Questo tipo di schema viene utilizzato per requisiti sofisticati e per tabelle dei fatti aggregate che sono più complesse da supportare dallo schema Star (o) SnowFlake. Questo schema è difficile da mantenere a causa della sua complessità.
Di seguito viene fornito un esempio di Galaxy Schema.
# 4) Schema dell'ammasso stellare
Uno schema SnowFlake con molte tabelle delle dimensioni potrebbe richiedere join più complessi durante la query. Uno schema a stella con un numero inferiore di tabelle delle dimensioni può avere più ridondanza. Quindi, uno schema di ammasso stellare è entrato in scena combinando le caratteristiche dei due schemi precedenti.
Lo schema a stella è la base per progettare uno schema a grappolo di stelle e poche tabelle di dimensioni essenziali dallo schema a stella sono fiocchi di neve e questo, a sua volta, forma una struttura dello schema più stabile.
Di seguito viene fornito un esempio di schema di ammasso stellare.
Qual è il migliore schema a fiocco di neve o schema a stella?
La piattaforma del data warehouse e gli strumenti di BI utilizzati nel sistema DW giocheranno un ruolo fondamentale nel decidere lo schema adatto da progettare. Star e SnowFlake sono gli schemi utilizzati più di frequente in DW.
Lo schema a stella è preferibile se gli strumenti BI consentono agli utenti aziendali di interagire facilmente con le strutture delle tabelle con semplici query. Lo schema SnowFlake è preferibile se gli strumenti BI sono più complicati per gli utenti aziendali per interagire direttamente con le strutture della tabella a causa di più join e query complesse.
Puoi andare avanti con lo schema SnowFlake se vuoi risparmiare un po 'di spazio di archiviazione o se il tuo sistema DW ha strumenti ottimizzati per progettare questo schema.
Schema a stella vs schema a fiocco di neve
Di seguito sono riportate le differenze chiave tra lo schema Star e lo schema SnowFlake.
| S.No | Star Schedule | Schema del fiocco di neve |
|---|---|---|
| uno | La ridondanza dei dati è di più. | La ridondanza dei dati è inferiore. |
| Due | Lo spazio di archiviazione per le tabelle delle dimensioni è più. | Lo spazio di archiviazione per le tabelle delle dimensioni è relativamente inferiore. |
| 3 | Contiene tabelle delle dimensioni denormalizzate. | Contiene tabelle delle dimensioni normalizzate. |
| 4 | La singola tabella dei fatti è circondata da più tabelle delle dimensioni. | La singola tabella dei fatti è circondata da più gerarchie di tabelle delle dimensioni. |
| 5 | Le query utilizzano join diretti tra fatti e dimensioni per recuperare i dati. | Le query utilizzano join complessi tra fatti e dimensioni per recuperare i dati. |
| 6 | Il tempo di esecuzione della query è inferiore. | Il tempo di esecuzione della query è maggiore. |
| 8 | Utilizza l'approccio dall'alto verso il basso. | Utilizza l'approccio dal basso verso l'alto. |
Conclusione
Ci auguriamo che tu abbia una buona conoscenza dei diversi tipi di schemi di data warehouse, insieme ai loro vantaggi e svantaggi di questo tutorial.
Abbiamo anche imparato come interrogare Star Schema e SnowFlake Schema e quale schema deve scegliere tra questi due insieme alle loro differenze.
Resta sintonizzato sul nostro prossimo tutorial per saperne di più sul Data Mart in ETL !!
=> Guarda qui la serie di formazione sul data warehousing semplice.
Lettura consigliata
- Tipi di dati Python
- Tipi di dati C ++
- Esercitazione sul test del data warehouse con esempi | Guida al test ETL
- I 10 principali strumenti di data warehouse e tecnologie di test più diffusi
- Modello di dati dimensionale nel data warehouse - Tutorial con esempi
- Esercitazione sul test del data warehouse di test ETL (una guida completa)
- Che cos'è il processo ETL (Extract, Transform, Load) in Data Warehouse?
- Data mining: processo, tecniche e problemi principali nell'analisi dei dati