Aggregazioni definite dall'utente

Le aggregazioni in Power BI possono migliorare le prestazioni delle query su modelli semantici DirectQuery di grandi dimensioni. Usando le aggregazioni, i dati vengono memorizzati nella cache a livello aggregato in memoria. Le aggregazioni in Power BI possono essere configurate manualmente nel modello di dati, come descritto in questo articolo. Per le sottoscrizioni Premium, abilitando automaticamente la funzionalità Aggregazioni automatiche nelle impostazioni del modello.

Creazione di tabelle di aggregazione

A seconda del tipo di origine dati, è possibile creare una tabella delle aggregazioni nell'origine dati come tabella, vista o query nativa. Per ottenere prestazioni ottimali, creare una tabella di aggregazioni come tabella di importazione creata in Power Query. Usare quindi la finestra di dialogo Gestisci aggregazioni in Power BI Desktop per definire le aggregazioni per le colonne di aggregazione con riepilogo, tabella dei dettagli e proprietà delle colonne di dettaglio.

Le origini dati dimensionali, ad esempio data warehouse e data mart, possono usare aggregazioni basate sulle relazioni. Le fonti di Big Data basate su Hadoop spesso si basano su aggregazioni delle colonne GroupBy. Questo articolo descrive le differenze tipiche di modellazione dei dati di Power BI per ogni tipo di origine dati.

Gestire le aggregazioni

Nel riquadro Dati di qualsiasi visualizzazione di Power BI Desktop fare clic con il pulsante destro del mouse sulla tabella delle aggregazioni e quindi scegliere Gestisci aggregazioni.

La finestra di dialogo Gestisci aggregazioni mostra una riga per ogni colonna della tabella, in cui è possibile specificare il comportamento di aggregazione. Nell'esempio seguente le query alla tabella dei dettagli Sales vengono reindirizzate internamente alla tabella di aggregazione Sales Agg .

In questo esempio di aggregazione basata su relazione, le voci GroupBy sono facoltative. Ad eccezione di DISTINCTCOUNT, non influiscono sul comportamento delle aggregazioni e riguardano principalmente la leggibilità. Senza le voci GroupBy, le aggregazioni verrebbero comunque colpite, in base alle relazioni. Questo è diverso dall'esempio di Big Data più avanti in questo articolo, in cui sono necessarie le voci GroupBy.

Validazioni

La finestra di dialogo Gestisci aggregazioni applica le convalide:

• La colonna dettaglio deve avere lo stesso tipo di dati della colonna di aggregazione, ad eccezione delle funzioni di conteggio e riepilogo delle righe della tabella. Le righe della tabella Count e Count sono disponibili solo per le colonne di aggregazione integer e non richiedono un tipo di dati corrispondente.
• Le aggregazioni concatenate che coprono tre o più tabelle non sono consentite. Ad esempio, le aggregazioni nella tabella A non possono fare riferimento a una tabella B con aggregazioni che fanno riferimento a una tabella C.
• Le aggregazioni duplicate, in cui due voci usano la stessa funzione Di riepilogo e fanno riferimento alla stessa tabella dettaglio e colonna dettaglio, non sono consentite.
• La tabella dettagli deve usare la modalità di archiviazione DirectQuery, non l'importazione.
• Il raggruppamento in base a una colonna chiave esterna usata da una relazione inattiva e l'uso della funzione USERELATIONSHIP per i riscontri di aggregazione non è supportato.
• Le aggregazioni basate sulle colonne GroupBy possono usare relazioni tra tabelle di aggregazione, ma la creazione di relazioni tra tabelle di aggregazione non è supportata in Power BI Desktop. Se necessario, è possibile creare relazioni tra tabelle di aggregazione usando uno strumento di terze parti o una soluzione di scripting tramite endpoint XML for Analysis (XMLA).

La maggior parte delle convalide viene applicata disabilitando i valori a discesa e visualizzando il testo esplicativo nella descrizione comando.

Le tabelle di aggregazione sono nascoste

Gli utenti con accesso in sola lettura al modello non possono eseguire query sulle tabelle di aggregazione. L'accesso in sola lettura evita problemi di sicurezza quando usato con la sicurezza a livello di riga (RLS). I consumer e le query fanno riferimento alla tabella dei dettagli, non alla tabella di aggregazione e non devono conoscere la tabella di aggregazione.

Per questo motivo, le tabelle di aggregazione sono nascoste dalla visualizzazione Report . Se la tabella non è già nascosta, la finestra di dialogo Gestisci aggregazioni lo imposta su nascosto quando si seleziona Applica tutto.

Modalità di archiviazione

La funzionalità di aggregazione interagisce con le modalità di archiviazione a livello di tabella. Le tabelle di Power BI possono usare modalità di archiviazione DirectQuery, Import o Dual Storage. DirectQuery esegue direttamente una query sul back-end, mentre importa i dati in memoria e invia query ai dati memorizzati nella cache. Tutte le origini dati DirectQuery importate e non multidimensionali di Power BI possono funzionare con le aggregazioni.

Per impostare la modalità di archiviazione di una tabella aggregata su Importa per velocizzare le query, selezionare la tabella aggregata nella visualizzazione Modello di Power BI Desktop. Nel riquadro Proprietà, espandere Avanzate, aprire il menu a tendina sotto Modalità di archiviazione e selezionare Importa. La modifica dell'importazione è irreversibile.

Per altre informazioni sulle modalità di archiviazione tabelle, vedere Gestire la modalità di archiviazione in Power BI Desktop.

Sicurezza a livello di riga (RLS) per le aggregazioni

Per funzionare correttamente per le aggregazioni, le espressioni RLS devono filtrare le tabelle di aggregazione e di dettagli.

Nell'esempio seguente l'espressione RLS nella tabella Geography funziona per le aggregazioni, perché Geography si trova sul lato filtro delle relazioni con la tabella Sales e la tabella Sales Agg . Query che raggiungono la tabella di aggregazione e le query a cui non è stata applicata correttamente la sicurezza a livello di riga.

Un'espressione RLS, nella tabella Product, filtra solo la tabella di dettaglio Sales, non la tabella aggregata Sales Agg. Poiché la tabella di aggregazione è un'altra rappresentazione dei dati nella tabella dei dettagli, rispondere alle query dalla tabella di aggregazione sarebbe insicuro se il filtro RLS non può essere applicato. Non è consigliabile filtrare solo la tabella dei dettagli, perché le query utente di questo ruolo non traggono vantaggio dai risultati di aggregazione.

Un'espressione RLS che filtra solo la tabella di aggregazione Sales Agg e non la tabella dei dettagli Vendite non è consentita.

Per le aggregazioni basate sulle colonne GroupBy, è possibile usare un'espressione di sicurezza a livello di riga (RLS) applicata alla tabella dei dettagli per filtrare la tabella di aggregazione, poiché tutte le colonne GroupBy della tabella di aggregazione sono coperte dalla tabella dei dettagli. D'altra parte, un filtro RLS sulla tabella di aggregazione non può essere applicato alla tabella dei dettagli, quindi non è consentito.

Aggregazione basata sulle relazioni

I modelli dimensionali usano in genere aggregazioni basate sulle relazioni. I modelli di Power BI provenienti da data warehouse e data mart sono simili a schemi star/snowflake, con relazioni tra tabelle delle dimensioni e tabelle dei fatti.

Nell'esempio seguente il modello ottiene i dati da una singola origine dati. Le tabelle usano la modalità di archiviazione DirectQuery. La tabella dei fatti Sales contiene miliardi di righe. L'impostazione della modalità di archiviazione di Sales su Import per la memorizzazione nella cache comporta un notevole sovraccarico di memoria e risorse.

Creare invece la tabella di aggregazione Sales Agg . Nella tabella Sales Agg il numero di righe è uguale alla somma di SalesAmount raggruppati per CustomerKey, DateKey e ProductSubcategoryKey. La tabella Sales Agg ha una granularità maggiore rispetto a Sales, quindi invece di miliardi di righe potrebbe contenere milioni di righe, che sono più facili da gestire.

Se le tabelle delle dimensioni seguenti vengono usate più comunemente per le query con valore aziendale elevato, è possibile filtrare Sales Agg, usando relazioni uno-a-molti o molti-a-uno .

• Geografia
• Cliente
• Appuntamento
• Sottocategoria prodotto
• Categoria prodotto

L'immagine seguente mostra questo modello.

La tabella seguente illustra le aggregazioni per la tabella Sales Agg .

La modifica della modalità di archiviazione della tabella Sales Agg aggregata in Importa apre una finestra di dialogo che indica che le tabelle delle dimensioni correlate possono essere impostate sulla modalità di archiviazione Doppia.

Impostare le tabelle delle dimensioni correlate su Dual consente loro di funzionare come Import o DirectQuery, a seconda della sottoquery. Nell'esempio:

• È possibile restituire dalla cache in memoria le query che aggregano le metriche dalla tabella Sales Agg in modalità importazione e raggruppare per attributi dalle tabelle duali correlate.
• Le query che aggregano le metriche dalla tabella DirectQuery Sales e raggruppano per attributi dalle tabelle Dual correlate possono essere restituite in modalità DirectQuery. La logica di query, inclusa l'operazione GroupBy, viene passata al database di origine.

Per altre informazioni sulla modalità di archiviazione doppia, vedere Gestire la modalità di archiviazione in Power BI Desktop.

Relazioni regolari e limitate

I riscontri di aggregazione basati sulle relazioni richiedono relazioni regolari.

Le relazioni regolari includono le seguenti combinazioni di modalità di archiviazione, in cui entrambe le tabelle provengono da una singola origine:

L'unico caso in cui una relazione tra origini viene considerata normale è se entrambe le tabelle sono impostate su Import. Le relazioni molti a molti sono sempre considerate limitate.

Per le aggregazioni inter-origine che non dipendono dalle relazioni, vedere Aggregazioni basate sulle colonne GroupBy.

Esempi di query di aggregazione basate sulle relazioni

La query seguente attiva l'aggregazione, perché le colonne nella tabella Date hanno la granularità che può attivare l'aggregazione. La colonna SalesAmount utilizza l'aggregazione Sum .

La query seguente non raggiunge l'aggregazione. Nonostante la richiesta della somma di SalesAmount, la query esegue un'operazione GroupBy su una colonna della tabella Product, che non corrisponde alla granularità necessaria per eseguire l'aggregazione. Se si osservano le relazioni nel modello, una sottocategoria di prodotto può avere più righe product . La query non è in grado di determinare il prodotto da aggregare. In questo caso, la query torna a DirectQuery e invia una query SQL all'origine dati.

Le aggregazioni non sono solo per semplici calcoli che eseguono una somma semplice. I calcoli complessi possono anche trarre vantaggio. Concettualmente, un calcolo complesso viene suddiviso in sottoquery per ogni SOMMA, MIN, MAX e COUNT. Ogni sottoquery viene valutata per determinare se può incidere sull'aggregazione. Questa logica non è sempre valida in tutti i casi a causa dell'ottimizzazione del piano di query, ma in generale dovrebbe applicarsi. L'esempio seguente raggiunge l'aggregazione:

La funzione COUNTROWS può trarre vantaggio dalle aggregazioni. La query seguente raggiunge l'aggregazione perché è presente un'aggregazione di righe della tabella Count definita per la tabella Sales .

La funzione AVERAGE può trarre vantaggio dalle aggregazioni. La query seguente raggiunge l'aggregazione perché AVERAGE viene internamente piegato a una SOMMA divisa per un conteggio. Poiché la colonna UnitPrice ha aggregazioni definite sia per SUM che per COUNT, l'aggregazione viene raggiunta.

In alcuni casi, la funzione DISTINCTCOUNT può trarre vantaggio dalle aggregazioni. La query seguente raggiunge l'aggregazione perché è presente una voce GroupBy per CustomerKey, che mantiene l'univocità di CustomerKey nella tabella di aggregazione. Questa tecnica potrebbe comunque raggiungere la soglia delle prestazioni in cui più di due-cinque milioni di valori distinti possono influire sulle prestazioni delle query. Tuttavia, può essere utile negli scenari in cui sono presenti miliardi di righe nella tabella dei dettagli, ma due-cinque milioni di valori distinti nella colonna. In questo caso, DISTINCTCOUNT può eseguire operazioni più veloci rispetto all'analisi della tabella con miliardi di righe, anche se sono state memorizzate nella cache in memoria.

Le funzioni DAX (Data Analysis Expressions) di intelligenza temporale sono consapevoli dell'aggregazione. La query seguente raggiunge l'aggregazione perché la funzione DATESYTD genera una tabella di valori CalendarDay e la tabella di aggregazione ha una granularità che copre le colonne "group-by" nella tabella Date. Questo è un esempio di filtro con valori di tabella per la funzione CALCULATE, che può essere usata con le aggregazioni.

Aggregazione basata sulle colonne GroupBy

I modelli big data basati su Hadoop hanno caratteristiche diverse rispetto ai modelli dimensionali. Per evitare join tra tabelle di grandi dimensioni, i modelli Big Data spesso non usano relazioni, ma denormalizzare gli attributi delle dimensioni alle tabelle dei fatti. È possibile sbloccare questi modelli di Big Data per l'analisi interattiva usando aggregazioni basate su colonne GroupBy.

La tabella seguente contiene la colonna numerica Movement da aggregare. Tutte le altre colonne sono attributi da usare per raggruppare. La tabella contiene dati IoT e un numero elevato di righe. La modalità di archiviazione è DirectQuery. Query sull'origine dei dati che aggregano l'intero modello sono lente a causa dell'enorme volume.

Per abilitare l'analisi interattiva su questo modello, è possibile aggiungere una tabella di aggregazione che raggruppa la maggior parte degli attributi, ma esclude gli attributi di cardinalità elevata, ad esempio longitudine e latitudine. Questo riduce notevolmente il numero di righe e è abbastanza piccolo da adattarsi comodamente a una cache in memoria.

È possibile definire i mapping di aggregazione per la tabella Driver Activity Agg nella finestra di dialogo Gestisci aggregazioni .

Nelle aggregazioni basate sulle colonne GroupBy le voci GroupBy non sono facoltative. Senza di esse, le aggregazioni non vengono colpite. Ciò è diverso dall'uso delle aggregazioni in base alle relazioni, in cui le voci GroupBy sono facoltative.

La tabella seguente illustra le aggregazioni per la tabella Driver Activity Agg .

È possibile impostare la modalità di archiviazione della tabella Agg dell'attività del driver aggregata su Importa.

Un esempio di query di aggregazione GroupBy

La query seguente raggiunge l'aggregazione perché la colonna Data attività è coperta dalla tabella di aggregazione. La funzione COUNTROWS utilizza l'aggregazione righe della tabella conteggiate.

In particolare per i modelli che contengono attributi di filtro nelle tabelle dei fatti, è consigliabile usare aggregazioni per conteggiare le righe della tabella. Power BI può inviare query al modello usando COUNTROWS nei casi in cui non è richiesta in modo esplicito dall'utente. Ad esempio, la finestra di dialogo filtro mostra il numero di righe per ogni valore.

Tecniche di aggregazione combinate

È possibile combinare le relazioni e le tecniche delle colonne GroupBy per le aggregazioni. Le aggregazioni basate sulle relazioni possono richiedere la suddivisione delle tabelle delle dimensioni denormalizzate in più tabelle. Se questo è costoso o poco pratico per determinate tabelle delle dimensioni, è possibile replicare gli attributi necessari nella tabella di aggregazione per tali dimensioni e utilizzare le relazioni per altre.

Ad esempio, il modello seguente replica Month, Quarter, Semester e Year nella tabella Sales Agg . Non esiste alcuna relazione tra Sales Agg e la tabella Date , ma esistono relazioni con Customer e Product Subcategory. La modalità di archiviazione di Sales Agg è Import.

La tabella seguente mostra le voci impostate nella finestra di dialogo Gestisci aggregazioni per la tabella Sales Agg . Le voci GroupBy in cui Date è la tabella dei dettagli sono obbligatorie per raggiungere le aggregazioni per le query raggruppate in base agli attributi Date . Come nell'esempio precedente, le voci GroupBy per CustomerKey e ProductSubcategoryKey non influiscono sui riscontri di aggregazione, ad eccezione di DISTINCTCOUNT, a causa della presenza di relazioni.

Esempi di query di aggregazione combinate

La query seguente raggiunge l'aggregazione, perché la tabella di aggregazione copre CalendarMonth e CategoryName è accessibile tramite relazioni uno-a-molti. SalesAmount usa l'aggregazione SUM .

La query seguente non raggiunge l'aggregazione perché la tabella di aggregazione non copre CalendarDay.

La seguente query di intelligenza temporale non tocca l'aggregazione, perché la funzione DATESYTD genera una tabella di valori CalendarDay e la tabella di aggregazione non copre CalendarDay.

La precedenza dell'aggregazione

La precedenza di aggregazione consente di considerare più tabelle di aggregazione tramite una singola sottoquery.

L'esempio seguente è un modello composito contenente più origini:

• La tabella Driver Activity DirectQuery contiene più di un trilione di righe di dati IoT originate da un sistema Big Data. Si utilizzano query dettagliate per visualizzare le singole letture IoT in contesti di filtro controllati.
• La tabella Driver Activity Agg è una tabella di aggregazione intermedia in modalità DirectQuery. Contiene più di un miliardo di righe in Azure Synapse Analytics (in precedenza SQL Data Warehouse) ed è ottimizzato nell'origine usando indici columnstore.
• La tabella Driver Activity Agg2 Import ha una granularità elevata, perché gli attributi group-by sono pochi e di bassa cardinalità. Il numero di righe può essere inferiore a migliaia, quindi può essere facilmente inserito in una cache in memoria. Questi attributi vengono usati da una dashboard esecutiva di alto profilo, quindi le query che fanno riferimento a tali attributi devono essere il più rapide possibile.

Il footprint di memoria di questo modello è relativamente piccolo, ma sblocca un modello enorme. Rappresenta un'architettura bilanciata perché distribuisce il carico di query tra i componenti dell'architettura, usandoli in base ai punti di forza.

La finestra di dialogo Aggregazioni gestite per Driver Activity Agg2 imposta il campo Precedenza su 10, che è superiore a per Driver Activity Agg. L'impostazione di precedenza superiore indica che le query che usano le aggregazioni considerano prima Driver Activity Agg2 . Le sottoquery che non sono alla granularità che può essere gestita da Driver Activity Agg2 possono considerare invece Driver Activity Agg. Le query di dettaglio a cui non è possibile rispondere da una tabella di aggregazione possono essere dirette all'attività del driver.

La tabella specificata nella colonna Tabella dettagli è Driver Activity, non Driver Activity Agg, perché le aggregazioni concatenati non sono consentite.

La tabella seguente illustra le aggregazioni per la tabella Driver Activity Agg2 .

Individuare se le query hanno colpito o mancato le aggregazioni

SQL Profiler può rilevare se le query vengono restituite dal motore di archiviazione della cache in memoria o inviate all'origine dati tramite DirectQuery. È possibile usare lo stesso processo per rilevare se vengono rilevate aggregazioni. Per altre informazioni, vedere Query che hanno raggiunto o perso la cache.

SQL Profiler fornisce anche l'evento Query Processing\Aggregate Table Rewrite Query esteso.

Il frammento JSON seguente mostra un esempio dell'output dell'evento quando viene usata un'aggregazione.

• matchingResult indica che la sottoquery ha usato un'aggregazione.
• dataRequest mostra le colonne GroupBy e le colonne aggregate usate dalla sottoquery.
• Il mapping mostra le colonne della tabella di aggregazione che sono state mappate.

Mantenere sincronizzate le cache

Le aggregazioni che combinano modalità di archiviazione DirectQuery, Import e/o Dual possono restituire dati diversi, a meno che la cache in memoria non venga mantenuta sincronizzata con i dati di origine. Ad esempio, l'esecuzione di query non tenta di mascherare i problemi di dati filtrando i risultati di DirectQuery in modo che corrispondano ai valori memorizzati nella cache. Esistono tecniche stabilite per gestire tali problemi all'origine, se necessario. Le ottimizzazioni delle prestazioni devono essere usate solo in modi che non comprometteno la capacità di soddisfare i requisiti aziendali. È responsabilità dell'utente conoscere i flussi di dati e progettare di conseguenza.

Considerazioni e limitazioni

• Le aggregazioni non supportano parametri di query M dinamici.

• A partire da agosto 2022, a causa delle modifiche apportate alle funzionalità, Power BI ignora le tabelle di aggregazione in modalità importazione con origini dati abilitate per l'accesso Single Sign-On (SSO) a causa di potenziali rischi per la sicurezza. Per garantire prestazioni ottimali delle query con le aggregazioni, è consigliabile disabilitare l'accesso Single Sign-On (SSO) per queste origini dati.

Comunità

Power BI ha una vivace community in cui MVP, professionisti BI e colleghi condividono competenze in gruppi di discussione, video, blog e altro ancora. Quando si apprendono informazioni sulle aggregazioni, assicurarsi di controllare queste risorse aggiuntive:

• Community di Power BI
• Cercare "Aggregazioni di Power BI" in Bing

Contenuto correlato

• Aggregazioni automatiche
• Modelli compositi