Usare partizioni di tabelle calde e fredde per ottimizzare modelli di dati di Power BI di grandi dimensioni

Questo articolo descrive come utilizzare le partizioni di tabelle "hot" e "cold" per ottimizzare modelli di dati molto grandi. Le partizioni consentono di dividere i dati di una tabella in subset discreti. Le partizioni non vengono esposte direttamente negli strumenti di modellazione dei dati di Power BI standard, ma è possibile sfruttare i metodi di partizionamento avanzati configurando un criterio di aggiornamento incrementale in Power BI Desktop. L'aggiornamento incrementale si basa sulle partizioni, come illustrato in Aggiornamento incrementale e dati in tempo reale per i set di dati. Tuttavia, la configurazione di partizioni di tabelle a caldo e a freddo va oltre quello che una politica di aggiornamento incrementale può realizzare e presuppone la familiarità con gli schemi di partizionamento delle tabelle tipici e strumenti basati su XMLA.

Prerequisiti

A causa della complessità relativa di questa tecnica di partizionamento, è più adatta per gli utenti avanzati con esperienza nelle aree seguenti:

1. Informazioni sui concetti di partizionamento delle tabelle, sul funzionamento delle partizioni in modalità di importazione, della modalità DirectQuery e della modalità duale .

2. Conoscenza di come creare tabelle ibride usando strumenti basati su XMLA. Le tabelle ibride usano una o più partizioni in modalità importazione e una partizione DirectQuery .

3. Conoscenza dei requisiti delle funzioni DAX che è possibile usare per specificare un oggetto DataCoverageDefinition. Si tratta di una nuova proprietà per le partizioni DirectQuery per descrivere i dati contenuti nella partizione DirectQuery di una tabella ibrida in modo che il motore di Power BI possa escludere questa partizione dall'elaborazione delle query, se appropriato. L'esclusione della partizione DirectQuery consente di evitare query di origine dati non necessarie e migliorare le prestazioni dell'elaborazione di query DAX.

4. Informazioni sulla differenza tra relazioni tra tabelle regolari e limitate. Ad esempio, la funzione RELATED è utile se si desidera definire la copertura dei dati di una partizione di tabella dei fatti in base ai valori di una tabella delle dimensioni della data correlata. Tenere presente che la partizione della tabella dei fatti è una partizione DirectQuery con una possibilità di una relazione limitata alla tabella date in cui la funzione RELATED non riesce a recuperare i valori. In questo scenario RELATED funziona solo se la tabella delle dimensioni date è una tabella doppia. La tabella delle date deve essere in modalità DirectQuery o Dual . Non può essere importazione pura.

Tenere presente che un oggetto definito DataCoverageDefinition in modo non corretto potrebbe causare risultati errati perché Power BI potrebbe escludere erroneamente la partizione DirectQuery dall'elaborazione delle query. Assicurarsi quindi di confrontare i risultati con e senza DataCoverageDefinition per assicurarsi che coincidano.

Quando usare partizioni di tabella "calde" e "fredde"

Ecco un esempio in cui le partizioni ad accesso frequente e sporadico consentono di ottimizzare una tabella ibrida per l'analisi cronologica. Si supponga di avere una sorgente di dati molto grande, accumulata nel corso di molti anni. L'uso principale consiste nell'analizzare i dati più recenti degli ultimi due anni. In alcuni casi, è anche necessario analizzare i dati meno recenti. Forse si è notato un recente aumento delle vendite nel corso dell'anno. È successo prima? È il picco di vendite più alto dall'inizio del rilevamento delle vendite?

Senza il supporto per le partizioni ad accesso frequente e sporadico, questo tipo di analisi cronologica richiederebbe di importare tutti i dati cronologici insieme ai dati più recenti nella tabella dei fatti. Al meglio, si tratta di un uso inefficiente delle risorse perché l'analisi primaria non usa nemmeno nessuno dei dati cronologici meno recenti. Nel peggiore dei casi, il volume di dati è così grande che non può nemmeno essere importato completamente. È necessario passare il modello di dati alla modalità DirectQuery e accettare una riduzione delle prestazioni rispetto alla modalità di importazione oppure è possibile creare modelli separati e forzare gli utenti a passare da un report all'altro. Una tabella ibrida con partizioni calde e fredde offre un'opzione migliore.

Come utilizzare le partizioni di tabella hot e cold

Prima di tutto, configurare la tabella delle vendite con una partizione in modalità importazione a caldo per i dati più recenti e mantenere i dati meno recenti in una partizione a DirectQueryfreddo, come illustrato nel diagramma seguente per la tabella FactInternetSales di un modello di dati di esempio AdventureWorks. Tutte le righe con OrderDateKey uguale o superiore a 20200101 vengono importate nel modello di dati tramite la partizione in modalità di importazione rapida. Le righe con OrderDateKey minore di 20200101 sono coperte attraverso la partizione a freddo DirectQuery. Power BI può ora distribuire rapidamente i casi d'uso principali con la modalità di importazione e non è necessario importare grandi volumi di dati cronologici analizzati solo occasionalmente perché la partizione DirectQuery include questo argomento.

Se si ha un data warehouse di esempio AdventureWorks e si vuole seguire questa procedura, ecco i passaggi generali:

1. Creare il set di dati. Usare Power BI Desktop per creare un set di dati e un report AdventureWorks. Includere tutte le tabelle in modalità DirectQuery pura. Convertire quindi tutte le tabelle ad eccezione della FactInternetSales tabella in modalità doppia . Lasciare la FactInternetSales tabella in modalità DirectQuery .

2. Caricare il set di dati. Usare un'area di lavoro ospitata in Power BI Premium con l'endpoint XMLA abilitato per le operazioni di scrittura.

3. Aggiornare il livello di compatibilità. Aprire l'area di lavoro con il set di dati AdventureWorks in SQL Server Management Studio (SSMS). Fare clic con il pulsante destro del mouse sul set di dati AdventureWorks>Script>Script Database asCrea o Sostituisci in, e selezionare Nuova Finestra dell'Editor di Query. Impostare la proprietà compatibilityLevel su 1603 (o versione successiva). Selezionare Esegui o premere F5. Verificare che l'operazione venga completata correttamente.

   

4. Configurare le partizioni della tabella FactInternetSales. Fare clic con il pulsante destro del mouse sul set di dati AdventureWorks>Script>Script Database comeCrea o sostituisci come e selezionare nuova finestra dell'editor di interrogazioni. Sostituire l'intera sezione partizioni con la sezione seguente. Assicurarsi di aggiornare le righe di Sql.Database in modo che punti al database AdventureWorksDW nell'ambiente in uso. Selezionare Esegui o premere F5. Verificare che l'operazione venga completata correttamente.

      "partitions": [ 
       { 
         "name": "FactInternetSales-DQ-Partition", 
         "mode": "directQuery", 
         "dataView": "full", 
         "source": { 
           "type": "m", 
           "expression": [ 
             "let", 
             "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
             "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
             "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
             "in", 
             "    #\"Filtered Rows\"" 
           ] 
         } 
       }, 
       { 
         "name": "FactInternetSales-Import-Partition", 
         "mode": "import", 
         "source": { 
           "type": "m", 
           "expression": [ 
             "let", 
             "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
             "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
             "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
             "in", 
             "    #\"Filtered Rows\"" 
           ] 
         } 
       } 
     ],    
   

5. Elaborare il modello di dati. Nel portale di Power BI aprire l'area di lavoro con il set di dati AdventureWorks ed eseguire un aggiornamento su richiesta del set di dati per caricare la partizione di importazione con i dati.

6. Verificare che i report visualizzino dati recenti e cronologici. Aprire AdventureWorks e verificare che il report sia in grado di visualizzare i risultati per le transazioni di vendita prima e dopo il 1° gennaio 2020, come illustrato nello screenshot seguente.

Screenshot di due report diversi. Uno mostra i dati del 2020 e uno mostra i dati del 2019.

Definire la copertura dei dati della partizione DirectQuery

La soluzione funziona perfettamente sui dati recenti e cronologici. Tuttavia, per impostazione predefinita Power BI esegue query su tutte le partizioni di tabella, perché non conosce i dati coperti da ogni partizione. Di conseguenza, Power BI esegue ancora una query sulla partizione DirectQuery anche per quegli anni che la partizione DirectQuery non copre. I dati di vendita sono facilmente disponibili nella partizione di importazione e la partizione DirectQuery non contribuisce ad alcuna riga, ma questa superflua interrogazione della fonte può comunque causare un carico notevole sulla fonte dei dati e provocare ritardi nell'elaborazione delle query DAX. Per evitare questa query di origine superflua, utilizzare il DataCoverageDefinition.

Come illustrato nello screenshot seguente, il report di Power BI invia ancora diverse query SQL non necessarie per il 2020 all'origine dati perché la query DAX di ogni elemento visivo fa in modo che Power BI esegua una query sulla partizione DirectQuery.

Impostando la proprietà sulla dataCoverageDefinition partizione DirectQuery come nel frammento di codice TMSL seguente, queste query SQL vengono evitate. Tenere presente, tuttavia, che è necessario aggiornare il set di dati dopo l'applicazione o la modifica di una definizione di copertura dei dati. Un processo di ricalcolo è sufficiente per valutare la definizione di copertura dei dati. Se si dimentica questo passaggio, le query che toccano la partizione falliscono con un messaggio di errore che indica "DataCoverageDefinition della partizione DQ della tabella '[Nome tabella]' non è ancora stato calcolato dopo una modifica recente. Deve essere rielaborata".

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        } 

Come accennato in precedenza, la proprietà dataCoverageDefinition aiuta a eliminare il caricamento non necessario dell'origine dati. Migliora anche le prestazioni di analisi per i dati recenti perché ora Power BI può escludere la partizione DirectQuery dall'elaborazione di query DAX, se appropriato. È possibile definire espressioni di copertura dei dati semplici per singoli valori, nonché intervalli con operatori AND, OR e NOT semplici. È anche possibile utilizzare la funzione RELATED per definire la copertura dei dati in base a una colonna di una tabella delle dimensioni con una relazione regolare con la tabella dei fatti. Se un'espressione di copertura dati usa colonne di una tabella dimensionale, assicurarsi che la tabella dimensionale sia in modalità doppia. È anche possibile definire la copertura dei dati in base alle colonne della tabella dei fatti stessa. Vedere la tabella seguente per le operazioni supportate, suddivise in tre gruppi. 

La DataCoverageDefinition proprietà nelle partizioni DirectQuery consente di ottimizzare anche i modelli di dati di Power BI più grandi basati su partizioni ad accesso frequente in modalità di importazione e partizioni ad accesso sporadico in modalità DirectQuery evitando query non necessarie dell'origine dati. Questa riduzione delle query di origine consente di migliorare le prestazioni dei report durante l'analisi dei dati ad accesso frequente. Consente anche di ridurre il carico sull'origine dati e in questo modo consente di ottimizzare la scalabilità dell'origine dati. Tenere tuttavia presente che l'ottimizzazione di un modello di dati usando la dataCoverageDefinition proprietà è ancora uno scenario avanzato. Assicurarsi di verificare attentamente i risultati.

Considerazioni e limitazioni

• Attualmente, la DataCoverageDefinition proprietà nelle partizioni DirectQuery richiede valori statici, ad esempio RELATED('Date'[Year]) = 2020 o RELATED('Date'[Year]) IN {2020, 2021, 2022}. Le assegnazioni dinamiche non sono supportate, ad esempio RELATED('Date'[DateKey]) = TODAY().

• L'aggiornamento incrementale con dati in tempo reale non sfrutta la proprietà DataCoverageDefinition. Se si applica una definizione di copertura dati a una partizione DirectQuery (in tempo reale), l'aggiornamento incrementale elimina la definizione di copertura dati durante la ricreazione della partizione.