Usare partizioni di tabelle ad accesso frequente e sporadico per ottimizzare modelli di dati di Power BI di grandi dimensioni

Questo articolo descrive come usare le partizioni di tabella ad accesso frequente e sporadico per ottimizzare i modelli di dati di grandi dimensioni. 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 ad accesso frequente e sporadico va oltre il risultato che un criterio di aggiornamento incrementale può eseguire e presuppone la familiarità con i tipici schemi di partizionamento delle tabelle e gli strumenti basati su XMLA.

Prerequisiti

A causa della complessità relativa di questa tecnica di partizionamento, è più adatta agli 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à doppia .

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 data correlata. Tenere presente che la partizione di tabella dei fatti è una partizione DirectQuery con possibilità di una relazione limitata alla tabella date in cui la funzione RELATED non può recuperare i valori. In questo scenario RELATED funziona solo se la tabella delle dimensioni date è una tabella doppia. La tabella data deve essere in modalità DirectQuery o Doppia . Non può essere un'importazione pura.

Tenere presente che un elemento 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 per DataCoverageDefinition assicurarsi che vengano aggiunti.

Quando usare partizioni di tabella ad accesso frequente e sporadico

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 un'origine dati molto grande, accumulata in 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 nell'anno. È mai 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 ad accesso frequente e sporadico offre un'opzione migliore.

Come usare partizioni di tabella ad accesso frequente e sporadico

Configurare prima di tutto la tabella sales con una partizione in modalità di importazione ad accesso frequente per i dati più recenti e mantenere i dati meno recenti in una partizione DirectQuery a freddo, come illustrato nel diagramma seguente per la tabella FactInternetSales di un modello di dati di esempio AdventureWorks. Le righe con orderDateKey maggiore o uguale a 20200101 vengono importate nel modello di dati tramite la partizione in modalità importazione ad accesso frequente. Le righe con orderDateKey minore di 20200101 vengono coperte tramite la partizione DirectQuery a freddo. 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 ha questo argomento trattato.

Se si dispone di 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 databasescript> script del set di dati> AdventureWorkscomecrea 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 databasescript> script del set di dati> AdventureWorkscomecrea o sostituisci in e selezionare nuova finestra dell'editor di query. 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 mostrino 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.

Definire la copertura dei dati della partizione DirectQuery

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

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

Impostando la dataCoverageDefinition proprietà sulla 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 ricalcolo di processo è sufficiente per valutare la definizione di copertura dei dati. Se si dimentica questo passaggio, le query che toccano la partizione hanno esito negativo con un messaggio di errore che indica "DataCoverageDefinition della partizione DQ nella tabella '[Nome tabella]' non viene ancora 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à consente di eliminare il caricamento dell'origine dataCoverageDefinition dati non necessario. 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 code coverage dei dati usa colonne di una tabella delle dimensioni, assicurarsi che la tabella delle dimensioni sia in modalità doppia . È anche possibile definire la copertura dei dati in base alle colonne della tabella dei fatti stessa. Fare riferimento alla tabella seguente per le operazioni supportate, suddivise in tre gruppi. 

Tipo	Commenti	Esempio
Predicato singolo (basato su valore)	Operatori di uguaglianza, disuguaglianza e IN Supportare sia le tabelle delle dimensioni che delle tabelle dei fatti	RELATED('Date'[Year]) = 2020 NOT RELATED('Date'[Year]) = 2020 RELATED('Date'[Year]) IN {2020, 2021, 2022} InternetSales'[SalesAmt] = CURRENCY(100.0) NOT InternetSales'[SalesAmt] = CURRENCY(100.0) InternetSales'[SalesAmt] IN {CURRENCY(100.0), CURRENCY(200.0)}
Predicato singolo (basato su intervallo)	Può essere un operatore di confronto come >, , <>=, <= Richiedere che la tabella delle dimensioni sia in modalità doppia	RELATED('Date'[Year]) > 2020 RELATED('Date'[Year]) <= 2020
Predicati multipli	Uguaglianza, disuguaglianza e confronto Non supporta l'operatore IN Limitato a una singola tabella delle dimensioni in modalità doppia	RELATED('Date'[Year]) > 2010 && RELATED('Date'[Year]) > 2020 RELATED('Date'[Year]) = 2020 && RELATED('Date'[Calendar Quarter]) = 1 RELATED('Date'[Year]) > 2020 && NOT RELATED('Date'[Calendar Quarter]) = 1 RELATED('Date'[Year]) > 2020 && RELATED('Date'[Calendar Quarter]) < 3 RELATED('Date'[Year]) > 2020 && (RELATED('Date'[Calendar Quarter]) = 1 || RELATED('Date'[Calendar Quarter]) = 2)

La DataCoverageDefinition proprietà nelle partizioni DirectQuery consente di ottimizzare anche i modelli di dati di Power BI più grandi in base alle partizioni ad accesso frequente in modalità di importazione e alle partizioni ad accesso sporadico in modalità DirectQuery evitando l'esecuzione di query non necessarie dell'origine dati. Questa riduzione delle query di origine consente di migliorare le prestazioni del 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 tramite 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 DataCoverageDefinition proprietà . Se si applica una definizione di copertura dati a una partizione DirectQuery (in tempo reale), l'aggiornamento incrementale elimina la definizione di copertura dei dati durante la ricreazione della partizione.