Condividi tramite

Modellazione dimensionale in Microsoft Fabric Warehouse: caricare le tabelle

  • Articolo

Si applica a:✅ endpoint di SQL analitica e Warehouse in Microsoft Fabric

Nota

Questo articolo fa parte della serie di articoli sulla modellazione dimensionale. Questa serie si basa su materiale sussidiario e procedure consigliate per la progettazione relativa alla modellazione dimensionale in Microsoft Fabric Warehouse.

Questo articolo fornisce materiale sussidiario e procedure consigliate per il caricamento di tabelle dei fatti in un modello dimensionale. Fornisce una guida pratica per Warehouse in Microsoft Fabric, un'esperienza che supporta molte funzionalità T-SQL, come la creazione di tabelle e la gestione dei dati nelle tabelle. Si ha, quindi, il pieno controllo sulla creazione delle tabelle del modello dimensionale e sul caricamento dei dati.

Nota

In questo articolo il termine data warehouse si riferisce a un data warehouse aziendale, che fornisce un'integrazione completa dei dati critici nell'organizzazione. Al contrario, il termine warehouse da solo si riferisce a un Fabric Warehouse, ovvero un database relazionale SaaS (Software as a Service) che è possibile usare per implementare un data warehouse. Per maggiore chiarezza, in questo articolo verrà usato il termine Fabric Warehouse.

Suggerimento

Se si è inesperti con la modellazione dimensionale, prendere in considerazione questa serie di articoli come primo passo. Non è pensato per fornire una discussione completa sulla progettazione di modellazione dimensionale. Per altre informazioni, vedere direttamente il contenuto pubblicato ampiamente adottato, ad esempio The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (terza edizione, 2013) di Ralph Kimball e altri.

Caricare un modello dimensionale

Il caricamento di un modello dimensionale comporta l'esecuzione periodica di un processo di estrazione, trasformazione e caricamento (ETL). Un processo ETL coordina l'esecuzione di altri processi, che in genere riguardano i dati di origine di gestione temporanea, la sincronizzazione dei dati delle dimensioni, l'inserimento di righe nelle tabelle dei fatti e la registrazione di dati ed errori di controllo.

Per una soluzione Fabric Warehouse, è possibile usare Data Factory per lo sviluppo e l'esecuzione del processo ETL. Il processo può preparare, trasformare e caricare i dati di origine nelle tabelle del modello dimensionale.

In particolare, è possibile:

  • Usare le pipeline di dati per creare flussi di lavoro per orchestrare il processo ETL. Le pipeline di dati possono eseguire script SQL, stored procedure e altro ancora.
  • Usare i flussi di dati per lo sviluppo di logiche a basso codice per inserire dati da centinaia di origine dati. I flussi di dati supportano la combinazione di dati da una molteplicità di fonti, la trasformazione dei dati e il caricamento in una meta, ad esempio una tabella del modello dimensionale. I flussi di dati vengono compilati usando l'esperienza familiare di Power Query attualmente disponibile in molti prodotti Microsoft, inclusi Microsoft Excel e Power BI Desktop.

Nota

Lo sviluppo ETL può essere complesso e rappresentare una sfida. Si stima che il 60-80% del lavoro richiesto dallo sviluppo del data warehouse sia dedicato al processo ETL.

Orchestrazione

Il flusso di lavoro generale di un processo ETL include:

  1. Facoltativamente, caricamento delle tabelle di staging.
  2. Elaborazione delle tabelle delle dimensioni.
  3. Elaborazione delle tabelle dei fatti.
  4. Facoltativamente, esecuzione di attività di post-elaborazione, ad esempio l'attivazione dell'aggiornamento del contenuto dell'infrastruttura dipendente (ad esempio un modello semantico).

Il diagramma mostra i quattro passaggi del processo ETL, come descritto nel paragrafo precedente.

Le tabelle delle dimensioni devono essere elaborate per prime per garantire che contengano tutti i membri di dimensione, inclusi quelli aggiunti ai sistemi di origine dall'ultimo processo ETL. Quando sono presenti dipendenze tra le dimensioni, come nel caso delle dimensioni outrigger, le tabelle delle dimensioni devono essere elaborate in ordine di dipendenza. Ad esempio, una dimensione geografica utilizzata da una dimensione cliente e da una dimensione fornitore deve essere elaborata prima delle altre due dimensioni.

Le tabelle dei fatti possono essere elaborate solo dopo l'elaborazione di tutte le tabelle delle dimensioni.

Quando vengono elaborate tutte le tabelle dei modelli dimensionali, è possibile attivare l'aggiornamento dei modelli semantici dipendenti. È anche consigliabile inviare una notifica al personale pertinente per informarli del risultato del processo ETL.

Dati di gestione temporanea

I dati di origine di gestione temporanea consentono di supportare i requisiti di caricamento e trasformazione dei dati. Ciò comporta l'estrazione dei dati del sistema di origine e il caricamento in tabelle di staging, create per supportare il processo ETL. È consigliabile preparare i dati di origine perché possono:

  • Ridurre al minimo l'impatto sui sistemi operativi.
  • Essere usati per facilitare e ottimizzare l'elaborazione ETL.
  • Consente di riavviare il processo ETL, senza dover ricaricare i dati dai sistemi di origine.

I dati nelle tabelle di staging non devono mai essere resi disponibili agli utenti aziendali. È rilevante solo per il processo ETL.

Nota

Quando i dati vengono archiviati in unFabric Lakehouse, potrebbe non essere necessario eseguire la gestione temporanea dei dati nel data warehouse. Se viene implementata un'architettura medallion, è possibile recuperarne i dati dai livelli bronzo, argento o oro.

Si consiglia di creare uno schema nel warehouse, possibilmente denominato staging. Le tabelle di staging deve somigliare il più possibile alle tabelle di origine in termini di nomi di colonna e tipo di dati. Il contenuto di ogni tabella deve essere rimosso all'inizio del processo ETL. Tuttavia, si noti che le tabelle di Fabric Warehouse non possono essere troncate. In alternativa, è possibile eliminare e ricreare ogni tabella di staging prima di caricarla con i dati.

Si possono anche valutare alternative di virtualizzazione dei dati come parte della strategia di gestione temporanea. Puoi usare:

Trasforma dati

La struttura dei dati di origine potrebbe non corrispondere alle strutture di destinazione delle tabelle del modello dimensionale. Il processo ETL deve, quindi, rimodellare i dati di origine per allinearli alla struttura delle tabelle del modello dimensionale.

Inoltre, il data warehouse deve fornire dati puliti e conformi, pertanto potrebbe essere necessario trasformare i dati di origine per garantire qualità e coerenza.

Nota

Il concetto di garbage in, garbage out si applica certamente all'archiviazione dati; pertanto, evitare il caricamento di dati di garbage (bassa qualità) nelle tabelle del modello dimensionale.

Ecco alcune trasformazioni che il processo ETL potrebbe eseguire.

  • Combinare i dati: i dati provenienti da origini diverse possono essere integrati (uniti) in base a chiavi corrispondenti. Ad esempio, i dati di prodotto vengono archiviati in diversi sistemi (ad esempio produzione e marketing), ma usano tutti un'unità di stockkeeping delle scorte (SKU) comune. I dati possono anche essere aggiunti quando condividono una struttura comune. Ad esempio, i dati di vendita vengono archiviati in una molteplicità di sistemi. Un'unione delle vendite di ciascun sistema può produrre un insieme completo di tutti i dati di vendita.
  • Convertire i tipi di dati: i tipi di dati possono essere convertiti in quelli definiti nelle tabelle del modello dimensionale.
  • Calcoli: i calcoli possono essere eseguiti per produrre valori per le tabelle del modello dimensionale. Ad esempio, per una tabella delle dimensioni dipendente, si potrebbe concatenare il nome e il cognome per ottenere il nome completo. Un altro esempio: per la tabella dei fatti di vendita, si potrebbe calcolare il ricavo lordo delle vendite, ovvero il prodotto del prezzo unitario e della quantità.
  • Rilevare e gestire le modifiche cronologiche: è possibile rilevare e archiviare in modo appropriato le modifiche nelle tabelle delle dimensioni. Per altre informazioni, vedere Gestire le modifiche cronologiche di seguito in questo articolo.
  • Aggregare i dati: l'aggregazione può essere usata per ridurre la dimensionalità della tabella dei fatti e/o per aumentare la granularità dei fatti. Ad esempio, la tabella dei fatti di vendita non deve archiviare i numeri degli ordini cliente. Pertanto, è possibile usare un risultato aggregato che raggruppa tutte le chiavi di dimensione per archiviare i dati della tabella dei fatti.

Caricare i dati

È possibile caricare tabelle in Fabric Warehouse usando le opzioni di inserimento dati seguenti.

  • COPY INTO (T-SQL): questa opzione è utile quando i dati di origine sono file Parquet o CSV archiviati in un account di archiviazione di Azure esterno, ad esempio ADLS Gen2 o Archiviazione BLOB di Azure.
  • Pipeline di dati: oltre a orchestrare il processo ETL, le pipeline di dati possono includere attività che eseguono istruzioni T-SQL, effettuano ricerche o copiano dati da un'origine dati a una destinazione.
  • Flussi di dati: in alternativa alle pipeline di dati, i flussi di dati offrono un'esperienza senza codice per trasformare e pulire i dati.
  • Inserimento tra warehouse: quando i dati vengono archiviati nella stessa area di lavoro, l'inserimento tra data warehouse consente di unire tabelle warehouse o lakehouse diverse. Supporta comandi T-SQL come INSERT…SELECT, SELECT INTOe CREATE TABLE AS SELECT (CTAS). Questi comandi sono particolarmente utili quando si desidera trasformare e caricare dati da tabelle di staging all'interno della stessa area di lavoro. Si tratta, inoltre, di operazioni basate sui set, che rappresentano con ogni probabilità il modo più efficiente e veloce per caricare le tabelle del modello dimensionale..

Suggerimento

Per una spiegazione completa di queste opzioni di inserimento dati, incluse le procedure consigliate, vedere Inserire i dati nel warehouse.

Registrazione

In genere, i processi ETL richiedono monitoraggio e manutenzione dedicati. Per questi motivi, è consigliabile registrare i risultati del processo ETL in tabelle del modello non dimensionali nel magazzino. È necessario generare un ID univoco per ciascun processo ETL e usarlo per registrare i dettagli su ogni operazione.

Prendere in considerazione la registrazione:

  • Processo ETL:
    • Un ID univoco per ogni esecuzione ETL
    • Ora di inizio e fine
    • Stato (esito positivo o negativo)
    • Eventuali errori riscontrati
  • Ogni tabella di gestione temporanea e del modello dimensionale:
    • Ora di inizio e fine
    • Stato (esito positivo o negativo)
    • Righe inserite, aggiornate ed eliminate
    • Conteggio finale delle righe della tabella
    • Eventuali errori riscontrati
  • Altre operazioni:
    • Ora di inizio e di fine delle operazioni di aggiornamento del modello semantico

Suggerimento

È possibile creare un modello semantico dedicato al monitoraggio e all'analisi dei processi ETL. Le durate dei processi consentono di identificare i colli di bottiglia che potrebbero trarre vantaggio dalla revisione e dall'ottimizzazione. I conteggi delle righe possono consentire di comprendere le dimensioni del carico incrementale ogni volta che viene eseguita l'ETL e di prevedere le dimensioni future del data warehouse (e quando aumentare la capacità di Fabric, se appropriato).

Elaborare le tabelle delle dimensioni

L'elaborazione di una tabella delle dimensioni comporta la sincronizzazione dei dati del data warehouse con i sistemi di origine. I dati di origine vengono prima trasformati e preparati per il caricamento nella relativa tabella delle dimensioni. Questi dati vengono quindi confrontati con i dati della tabella delle dimensioni esistenti unendo le chiavi aziendali. È quindi possibile determinare se i dati di origine rappresentano dati nuovi o modificati. Quando la tabella delle dimensioni applica una dimensione a modifica lenta (SCD) di tipo 1, le modifiche vengono apportate aggiornando le righe della tabella delle dimensioni esistenti. Quando la tabella applica modifiche SCD di tipo 2, la versione esistente viene considerata scaduta e viene inserita una nuova versione.

Il diagramma seguente illustra la logica usata per elaborare una tabella delle dimensioni.

Il diagramma illustra un flusso che descrive come vengono caricate le righe di origine nuove e modificate in una tabella delle dimensioni, come descritto nel paragrafo seguente.

Si consideri il processo della tabella delle dimensioni Product.

  • Quando vengono aggiunti nuovi prodotti al sistema di origine, le righe vengono inserite nella tabella delle dimensioni Product.
  • Quando i prodotti vengono modificati, le righe esistenti nella tabella delle dimensioni vengono aggiornate o inserite nuovamente.
    • Quando si applica l'SCD di tipo 1, vengono effettuati aggiornamenti sulle righe esistenti.
    • Quando si applica l'SCD di tipo 2, gli aggiornamenti vengono effettuati in modo da far scadere le versioni di riga correnti e vengono inserite nuove righe che rappresentano la versione corrente.
    • Quando si applica l'SCD di tipo 3, si verifica un processo simile all'SCD di tipo 1, aggiornando le righe esistenti senza inserire nuove righe.

Chiavi sostitutive

È consigliabile che ogni tabella delle dimensioni abbia una chiave sostitutiva, che deve usare il tipo di dati Integer più piccolo possibile. In genere, negli ambienti basati su SQL Server ciò avviene creando una colonna Identity; tuttavia, questa funzionalità non è supportata in Fabric Warehouse. Pertanto, sarà necessario usare una tecnica alternativa che genera identificatori univoci.

Importante

Quando una tabella delle dimensioni include chiavi sostitutive generate automaticamente, è consigliabile non eseguire mai un troncamento e ricaricamento completo. Questo perché invaliderebbe i dati caricati nelle tabelle dei fatti che usano quella dimensione. Inoltre, se la tabella delle dimensioni supporta le modifiche all' SCD DI tipo 2, potrebbe non essere possibile rigenerare le versioni cronologiche.

Gestire le modifiche cronologiche

Quando una tabella delle dimensioni deve archiviare la modifica cronologica, è necessario implementare una dimensione a modifica lenta (SCD).

Nota

Se la riga della tabella delle dimensioni è un membro inferito (inserito da un processo di caricamento dei fatti), è consigliabile considerare eventuali modifiche come dettagli delle dimensioni in arrivo in ritardo, anziché come una modifica SCD. In questo caso, qualsiasi attributo modificato deve essere aggiornato e la colonna flag del membro inferito impostata su FALSE.

È possibile che una dimensione supporti le modifiche SCD di tipo 1 e/o SCD di tipo 2.

SCD di tipo 1

Quando vengono rilevate modifiche SCD di tipo 1, usare la logica seguente.

  1. Aggiornare tutti attributi modificati.
  2. Se la tabella include le colonne data ultima modifica e ultima modifica da, impostare la data corrente e il processo che ha apportato le modifiche.

SCD di tipo 2

Quando vengono rilevate modifiche SCD di tipo 2, usare la logica seguente.

  1. Far scadere la versione corrente impostando la colonna della data di fine della validità sulla data di elaborazione ETL (o su data e ora appropriate nel sistema di origine) e il flag corrente su FALSE.
  2. Se la tabella include le colonne data ultima modifica e ultima modifica da, impostare la data corrente e il processo che ha apportato le modifiche.
  3. Inserire nuovi membri con la colonna della data di inizio validità impostata sul valore della colonna della data di fine validità (usata per aggiornare la versione precedente) e con il flag della versione corrente impostato su TRUE.
  4. Se la tabella include le colonne data di creazione e creato da, impostare la data corrente e il processo che ha effettuato gli inserimenti.

SCD di tipo 3

Quando vengono rilevate modifiche SCD di tipo 3, aggiornare gli attributi usando una logica simile a quella di elaborazione di SCD di tipo 1.

Eliminazioni dei membri della dimensione

Prestare attenzione se i dati di origine indicano che i membri di dimensione sono stati eliminati (perché non vengono recuperati dal sistema di origine o sono stati contrassegnati come eliminati). Non è consigliabile sincronizzare le eliminazioni con la tabella delle dimensioni, a meno che i membri di dimensione non siano stati creati per errore e non siano presenti record di fatti correlati.

Il modo appropriato per gestire le eliminazioni di origine consiste nel registrarli come eliminazione temporanea. Un'eliminazione temporanea contrassegna un membro di dimensione come non più attivo o valido. Per supportare questo caso, la tabella delle dimensioni deve includere un attributo booleano con il tipo di dati bit, ad esempio IsDeleted. Aggiornare questa colonna per i membri delle dimensioni eliminati in TRUE (1). La versione corrente più recente di un membro di dimensione potrebbe essere contrassegnata in modo analogo con un valore booleano (bit) nelle colonne IsCurrent o IsActive. Tutte le query di report e i modelli semantici di Power BI devono filtrare i record che sono eliminazioni temporanea.

Dimensione data

Il Calendario e le dimensioni temporali sono casi speciali perché in genere non dispongono di dati di origine. Vengono, invece, generati usando la logica fissa.

È necessario caricare la tabella delle dimensioni data all'inizio di ogni nuovo anno per estendere le righe a un numero specifico di anni successivi. Potrebbero essere presenti altri dati aziendali, ad esempio dati dell'anno fiscale, festività, numeri della settimana da aggiornare regolarmente.

Quando la tabella delle dimensioni data include attributi di offset relativi, il processo ETL deve essere eseguito ogni giorno per aggiornare i valori degli attributi di offset basati sulla data corrente (oggi).

È consigliabile che la logica per estendere o aggiornare la tabella delle dimensioni data venga scritta in T-SQL e incapsulata in una stored procedure.

Elaborare le tabelle dei fatti

L'elaborazione di una tabella dei fatti comporta la sincronizzazione dei dati del data warehouse con i fatti del sistema di origine. I dati di origine vengono prima trasformati e preparati per il caricamento nella relativa tabella dei fatti. Quindi, per ogni chiave della dimensione, una ricerca determina il valore della chiave sostitutiva da archiviare nella riga dei fatti. Quando una dimensione supporta l'SCD di tipo 2, deve essere recuperata la chiave sostitutiva per la versione corrente del membro di dimensione.

Nota

In genere, la chiave sostitutiva può essere calcolata per le dimensioni di data e ora perché devono usare il formato YYYYMMDD o HHMM. Per altre informazioni, vedere Calendario e orario.

Se la ricerca di una chiave di dimensione ha esito negativo, potrebbe indicare un problema di integrità con il sistema di origine. In questo caso, la riga dei fatti deve comunque essere inserita nella tabella dei fatti. È comunque necessario archiviare una chiave di dimensione valida. Un approccio consiste nell'archiviare un membro di dimensione speciale (ad esempio Sconosciuto). Questo approccio richiede un aggiornamento successivo per assegnare correttamente il valore reale della chiave di dimensione, se noto.

Importante

Poiché Fabric Warehouse non applica chiavi esterne, è fondamentale che il processo ETL verifichi l'integrità quando carica i dati nelle tabelle dei fatti.

Un altro approccio, rilevante quando si è certi che la chiave naturale sia valida, consiste nell'inserire un nuovo membro di dimensione e quindi archiviare il suo valore di chiave sostitutiva. Per altre informazioni, vedere Membri di dimensioni inferiti più avanti in questa sezione.

Il diagramma seguente illustra la logica usata per elaborare una tabella dei fatti.

Il diagramma mostra un flusso che descrive come vengono caricate le nuove righe di origine in una tabella dei fatti, come descritto nei paragrafi precedenti.

Quando possibile, una tabella dei fatti deve essere caricata in modo incrementale, ovvero vengono rilevati e inseriti nuovi fatti. Una strategia di caricamento incrementale è più scalabile e riduce il carico di lavoro sia per i sistemi di origine che per i sistemi di destinazione.

Importante

Soprattutto per una tabella dei fatti di grandi dimensioni, troncare e ricaricare una tabella di fatti dovrebbe essere l'ultima risorsa. Questo approccio è dispendioso in termini di tempo di processo, risorse di calcolo e possibile interruzione dei sistemi di origine. Inoltre, comporta una certa complessità quando le dimensioni della tabella dei fatti applicano l'SCD di tipo 2. Ciò è dovuto al fatto che le ricerche chiave delle dimensioni dovranno essere eseguite entro il periodo di validità delle versioni dei membri di dimensione.

Idealmente, è possibile rilevare nuovi fatti in modo efficiente basandosi su identificatori o data e ora dei sistemi di origine. Ad esempio, quando un sistema di origine registra in modo affidabile gli ordini cliente in sequenza, è possibile archiviare il numero di ordine di vendita più recente recuperato (noto come limite massimo). Il processo successivo può usare tale numero di ordine cliente per recuperare gli ordini cliente appena creati e, di nuovo, archiviare il numero di ordine cliente più recente recuperato per utilizzarlo nel processo successivo. Potrebbe anche essere possibile usare una colonna di data di creazione per rilevare in modo affidabile nuovi ordini.

Se non è possibile basarsi sui dati del sistema di origine per rilevare nuovi fatti in modo efficiente, si potrebbe sfruttare una funzionalità del sistema di origine per eseguire un carico incrementale. Ad esempio, SQL Server e Istanza gestita di SQL di Azure dispongono di una funzionalità chiamata Change Data Capture (CDC), che consente di tenere traccia delle modifiche apportate a ogni riga di una tabella. Inoltre, SQL Server, Istanza gestita di SQL di Azure e database SQL di Azure hanno una funzionalità chiamata rilevamento modifiche, che consente di identificare le righe modificate. Se abilitata, consente di rilevare in modo efficiente i dati nuovi o modificati in qualsiasi tabella di database. È anche possibile aggiungere trigger a tabelle relazionali in cui sono archiviate le chiavi dei record inseriti, aggiornati o eliminati.

Infine, è possibile correlare i dati di origine alla tabella dei fatti usando attributi. Ad esempio, il numero ordine cliente e il numero di riga ordine di vendita. Tuttavia, per tabelle dei fatti di grandi dimensioni, rilevare fatti nuovi, modificati o eliminati potrebbe essere un'operazione molto costosa. Potrebbe essere problematico anche quando il sistema di origine archivia i dati operativi.

Membri di dimensione inferiti

Quando un processo di caricamento dei fatti inserisce un nuovo membro di dimensione, questo è noto come membro inferito. Ad esempio, quando un ospite in un hotel effettua il check-in, gli viene chiesto di unirsi alla catena di hotel come membro del programma fedeltà. Viene immediatamente emesso un numero di adesione, ma i dettagli dell'ospite potrebbero non essere disponibili fino a quando l'ospite non fornisce la documentazione (se mai lo farà).

Tutto ciò che è noto sul membro di dimensione è la sua chiave naturale. Il processo di caricamento dei fatti deve creare un nuovo membro di dimensione usando i valori dell'attributo Sconosciuto. È importante impostare l'attributo di controllo IsInferredMember su TRUE. In questo modo, quando i dettagli in arrivo in ritardo vengono originati, il processo di caricamento delle dimensioni può effettuare gli aggiornamenti necessari alla riga di dimensione. Per altre informazioni, vedere Gestire le modifiche cronologiche in questo articolo.

Aggiornamenti o eliminazioni dei fatti

Potrebbe essere necessario aggiornare o eliminare i dati della tabella dei fatti. Ad esempio, quando un ordine cliente viene annullato o se viene modificata una quantità di ordine. Come descritto in precedenza per il caricamento delle tabelle dei fatti, è necessario rilevare in modo efficiente i cambiamenti ed eseguire le modifiche appropriate ai dati dei fatti. Nel caso di un ordine annullato, lo stato dell'ordine cliente potrebbe passare da Aperto a Annullato. Tale modifica richiede un aggiornamento dei dati della tabella dei fatti e non l'eliminazione di una riga. Per la modifica della quantità, sarebbe necessario un aggiornamento della misura della quantità di righe dei fatti. Utilizzare questa strategia di eliminazioni temporanee aiuta a preservare la cronologia. Un'eliminazione temporanea contrassegna una riga come non più attiva o valida e tutte le query di report e i modelli semantici di Power BI devono escludere i record sottoposti a eliminazione temporanea.

Quando si prevedono aggiornamenti o eliminazioni dei fatti, è necessario includere attributi (ad esempio un numero di ordine cliente e il numero di riga ordine vendita) nella tabella dei fatti per aiutare a identificare le righe dei fatti da modificare. Assicurarsi di indicizzare queste colonne per supportare operazioni di modifica efficienti.

Infine, se i dati della tabella dei fatti sono stati inseriti usando un membro di dimensione speciale (ad esempio Sconosciuto), sarà necessario eseguire periodicamente un processo che recupera i dati di origine correnti per tali righe di fatti e aggiornare le chiavi di dimensione con valori validi.

Contenuto correlato

Per altre informazioni sul caricamento dei dati in un Fabric Warehouse, vedere: