Modellazione dimensionale in Microsoft Fabric Warehouse: Tabelle di caricamento

Si applica a:✅ Endpoint sql analitica e Warehouse in Microsoft Fabric

Questo articolo fornisce indicazioni e procedure consigliate per il caricamento di tabelle delle dimensioni e dei fatti in un modello dimensionale. Fornisce indicazioni pratiche per Warehouse in Microsoft Fabric, un'esperienza che supporta molte funzionalità T-SQL, ad esempio la creazione di tabelle e la gestione dei dati nelle tabelle. È quindi possibile controllare completamente la creazione delle tabelle del modello dimensionale e il caricamento con i dati.

Caricare un modello dimensionale

Il caricamento di un modello dimensionale comporta l'esecuzione periodica di un processo ETL (Extract, Transform e Load). Un processo ETL orchestra l'esecuzione di altri processi, che in genere riguardano i dati di origine di staging, 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 sviluppare ed eseguire il 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 sviluppare logica a basso codice per inserire dati da centinaia di origini dati. I flussi di dati supportano la combinazione di dati da più origini, la trasformazione dei dati e il caricamento in una destinazione, 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, tra cui Microsoft Excel e Power BI Desktop.

Orchestrazione

Il flusso di lavoro generale di un processo ETL consiste nel:

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

Le tabelle delle dimensioni devono essere elaborate per prima cosa per assicurarsi di archiviare tutti i membri della dimensione, inclusi quelli aggiunti ai sistemi di origine dall'ultimo processo ETL. Quando sono presenti dipendenze tra le dimensioni, come accade con le dimensioni outrigger, le tabelle delle dimensioni devono essere elaborate in ordine di dipendenza. Ad esempio, una dimensione geography utilizzata da una dimensione customer e una dimensione fornitore devono essere elaborate prima delle altre due dimensioni.

Le tabelle dei fatti possono essere elaborate 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 fase

I dati dell'origine di staging consentono di supportare i requisiti di caricamento e trasformazione dei dati. 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 usato 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.

È consigliabile creare uno schema nel warehouse, possibilmente denominato staging. Le tabelle di staging devono essere simili alle tabelle di origine il più possibile in termini di nomi di colonna e tipi di dati. Il contenuto di ogni tabella deve essere rimosso all'inizio del processo ETL. Si noti tuttavia che le tabelle di Fabric Warehouse non possono essere troncate. È invece possibile eliminare e ricreare ogni tabella di staging prima di caricarla con i dati.

È anche possibile prendere in considerazione le alternative di virtualizzazione dei dati come parte della strategia di gestione temporanea. Puoi usare:

• Mirroring, ovvero una soluzione chiavi in mano a basso costo e a bassa latenza che consente di creare una replica dei dati in OneLake. Per altre informazioni, vedere Why use Mirroring in Fabric?.
• Collegamenti a OneLake, che puntano ad altre posizioni di archiviazione che potrebbero contenere i dati di origine. I collegamenti possono essere usati come tabelle nelle query T-SQL.
• PolyBase in SQL Server, una funzionalità di virtualizzazione dei dati per SQL Server. PolyBase consente alle query T-SQL di unire dati da origini esterne a tabelle relazionali in un'istanza di SQL Server.
• Virtualizzazione dei dati con Istanza gestita di SQL di Azure, che consente di eseguire query T-SQL su file che archiviano dati in formati di dati comuni in Azure Data Lake Storage (ADLS) Gen2 o Archiviazione BLOB di Azure e combinarli con dati relazionali archiviati in locale usando join.

Trasforma dati

La struttura dei dati di origine potrebbe non essere simile 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.

Ecco alcune trasformazioni che il processo ETL potrebbe eseguire.

• Combinare i dati: i dati di origini diverse possono essere integrati (uniti) in base alle chiavi corrispondenti. Ad esempio, i dati dei prodotti vengono archiviati in diversi sistemi (ad esempio produzione e marketing), ma usano tutti un'unità di mantenimento delle scorte comune (SKU). I dati possono anche essere aggiunti quando condividono una struttura comune. Ad esempio, i dati di vendita vengono archiviati in più sistemi. Un'unione delle vendite di ogni sistema può produrre un superset 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, è possibile concatenare il nome e il cognome per produrre il nome completo. Come un altro esempio, per la tabella dei fatti di vendita, è possibile calcolare i ricavi delle vendite lordi, 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 la modifica cronologica più avanti in questo articolo.
• Dati aggregati: 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 di vendita. Pertanto, un risultato aggregato che raggruppa tutte le chiavi della dimensione può essere usato per archiviare i dati della tabella dei fatti.

Caricare i dati

È possibile caricare tabelle in un'istanza di 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, eseguono 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 data 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 vogliono trasformare e caricare dati da tabelle di staging all'interno della stessa area di lavoro. Si tratta anche di operazioni basate su set, che è probabile che sia il modo più efficiente e rapido per caricare le tabelle del modello dimensionale.

Registrazione

I processi ETL richiedono in genere 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 ogni processo ETL e usarlo per registrare i dettagli su ogni operazione.

Prendere in considerazione la registrazione:

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

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 business. È quindi possibile determinare se i dati di origine rappresentano dati nuovi o modificati. Quando la tabella delle dimensioni si applica al tipo 1 della dimensione a modifica lenta, le modifiche vengono apportate aggiornando le righe della tabella delle dimensioni esistenti. Quando la tabella applica modifiche al tipo 2 di scD, la versione esistente è scaduta e viene inserita una nuova versione.

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

Si consideri il processo della tabella delle Product dimensioni.

• Quando vengono aggiunti nuovi prodotti al sistema di origine, le righe vengono inserite nella tabella delle Product dimensioni.
• Quando i prodotti vengono modificati, le righe esistenti nella tabella delle dimensioni vengono aggiornate o inserite.
  • Quando si applica il tipo 1, vengono eseguiti aggiornamenti alle righe esistenti.
  • Quando si applica il tipo 2, gli aggiornamenti vengono eseguiti per scadere le versioni di riga correnti e vengono inserite nuove righe che rappresentano la versione corrente.
  • Quando si applica il tipo 3 di scD, si verifica un processo simile al tipo 1, aggiornando le righe esistenti senza inserire nuove righe.When SCD type 3 applies, a process similar to SCD type 1, updating the existing rows without inserting new rows.

Chiavi sostitutive

È consigliabile che ogni tabella delle dimensioni abbia una chiave surrogata, che deve usare il tipo di dati Integer più piccolo possibile. Negli ambienti basati su SQL Server in genere eseguiti creando una colonna Identity, questa funzionalità non è tuttavia supportata in Fabric Warehouse. È invece necessario usare una tecnica alternativa che genera identificatori univoci.

Gestire la modifica cronologica

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

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

ScD tipo 1

Quando vengono rilevate modifiche al tipo 1 di scD, usare la logica seguente.When SCD type 1 changes are detected, use the following logic.

1. Aggiornare gli attributi modificati.
2. Se la tabella include la data dell'ultima modifica e l'ultima modifica in base alle colonne, impostare la data e il processo correnti che hanno apportato le modifiche.

ScD tipo 2

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

1. Scadere la versione corrente impostando la colonna di validità della data di fine sulla data di elaborazione ETL (o un timestamp appropriato nel sistema di origine) e il flag corrente su FALSE.
2. Se la tabella include la data dell'ultima modifica e l'ultima modifica in base alle colonne, impostare la data e il processo correnti che hanno apportato le modifiche.
3. Inserire nuovi membri con la colonna di validità della data di inizio impostata sul valore della colonna di validità della data di fine (usato per aggiornare la versione precedente) e con il flag di versione corrente impostato su TRUE.
4. Se la tabella include la data di creazione e la creazione da colonne, impostare la data e il processo correnti che hanno eseguito gli inserimenti.

ScD tipo 3

Quando vengono rilevate modifiche al tipo 3 di scD, aggiornare gli attributi usando una logica simile all'elaborazione del tipo SCD 1.

Eliminazioni dei membri della dimensione

Prestare attenzione se i dati di origine indicano che i membri della 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 della dimensione non siano stati creati in 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 della 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 della dimensione potrebbe essere contrassegnata in modo analogo con un valore booleano (bit) nelle IsCurrent colonne o IsActive . Tutte le query di report e i modelli semantici di Power BI devono filtrare i record che sono eliminazioni temporanea.

Dimensione data

Le dimensioni del calendario e dell'ora 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 della data all'inizio di ogni nuovo anno per estendere le righe a un numero specifico di anni prima. Potrebbero essere presenti altri dati aziendali, ad esempio dati dell'anno fiscale, festività, numeri di settimana da aggiornare regolarmente.

Quando la tabella delle dimensioni della data include attributi di offset relativi, il processo ETL deve essere eseguito ogni giorno per aggiornare i valori degli attributi di offset in base alla 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 surrogata da archiviare nella riga dei fatti. Quando una dimensione supporta il tipo SCD 2, deve essere recuperata la chiave surrogata per la versione corrente del membro della dimensione.

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 della dimensione speciale (ad esempio Sconosciuto). Questo approccio richiede un aggiornamento successivo per assegnare correttamente il valore della chiave della dimensione reale, se noto.

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

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

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.

Si spera che sia possibile rilevare in modo efficiente nuovi fatti basandosi su identificatori del sistema di origine o timestamp. Ad esempio, quando un sistema di origine registra in modo affidabile gli ordini di vendita 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 di vendita per recuperare gli ordini di vendita appena creati e, di nuovo, archiviare il numero di ordine di vendita più recente recuperato per l'uso dal 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 in modo efficiente nuovi fatti, potrebbe essere possibile basarsi su 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à denominata 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à denominata rilevamento delle 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 chiavi di record di tabella inseriti, aggiornati o eliminati.

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

Membri della dimensione dedotti

Quando un processo di caricamento dei fatti inserisce un nuovo membro della dimensione, è noto come membro dedotto. Ad esempio, quando un ospite dell'hotel effettua il check-in, viene chiesto di unirsi alla catena di hotel come membro fedeltà. Un numero di appartenenza viene emesso immediatamente, ma i dettagli dell'ospite potrebbero non seguire fino a quando il documento non viene inviato dal guest (se mai).

Tutto ciò che è noto sul membro della dimensione è la sua chiave naturale. Il processo di caricamento dei fatti deve creare un nuovo membro della dimensione usando i valori di attributo Unknown . Importante, è necessario impostare l'attributo IsInferredMember di controllo su TRUE. In questo modo, quando vengono originati i dettagli in ritardo, il processo di caricamento delle dimensioni può apportare gli aggiornamenti necessari alla riga della dimensione. Per altre informazioni, vedere Gestire la modifica cronologica in questo articolo.

Aggiornamenti o eliminazioni dei fatti

Potrebbe essere necessario aggiornare o eliminare i dati dei fatti. Ad esempio, quando un ordine di vendita viene annullato o viene modificata una quantità di ordine. Come descritto in precedenza per il caricamento delle tabelle dei fatti, è necessario rilevare in modo efficiente le modifiche ed eseguire le modifiche appropriate ai dati dei fatti. In questo esempio per l'ordine annullato, è probabile che lo stato dell'ordine di vendita cambierebbe da Open a Canceled. Tale modifica richiede un aggiornamento dei dati 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. Questa strategia di utilizzo delle eliminazioni soft mantiene 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 filtrare i record che sono eliminazioni temporanea.

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

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

Contenuto correlato

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

• Inserisci i dati nel warehouse
• Inserire dati nel warehouse usando pipeline di dati
• Inserire dati nel warehouse usando l'istruzione COPY
• Inserire dati in Warehouse usando Transact-SQL
• Esercitazione: Configurare dbt per Fabric Warehouse