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 o per le sottoscrizioni Premium, abilitando automaticamente la funzionalità Aggregazioni automatiche nel modello Impostazioni.

Creazione di tabelle di aggregazione

A seconda del tipo di origine dati, è possibile creare una tabella di aggregazioni nell'origine dati come tabella o vista, come query nativa o per ottenere prestazioni ottimali, 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 origini Big Data basate su Hadoop spesso si basano sulle aggregazioni sulle 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 Campi 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 raggiunti, in base alle relazioni. Questo è diverso dall'esempio di Big Data più avanti in questo articolo, in cui sono necessarie le voci GroupBy.

Convalide

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 Conteggio e Conteggio righe di riepilogo delle tabelle. 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 concatenati 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 U edizione Standard RELATIONSHIP per i riscontri di aggregazione non è supportato.
• Le aggregazioni basate sulle colonne GroupBy possono sfruttare le 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 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. In questo modo si evitano problemi di sicurezza quando vengono usati con la sicurezza a livello di riga. 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à 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, a discesa la selezione in modalità Archiviazione e selezionare Importa. Si noti che questa azione è irreversibile.

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

Sicurezza a livello di riga per le aggregazioni

Per funzionare correttamente per le aggregazioni, le espressioni di sicurezza a livello di riga devono filtrare sia la tabella di aggregazione che la tabella dei 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. Le query che raggiungono la tabella di aggregazione e quelle che non hanno entrambi la sicurezza a livello di riga sono state applicate correttamente.

Un'espressione di sicurezza a livello di riga nella tabella Product filtra solo la tabella dettagli Sales, non la tabella Sales Agg aggregata. Poiché la tabella di aggregazione è un'altra rappresentazione dei dati nella tabella dei dettagli, sarebbe insicura rispondere alle query della tabella di aggregazione se il filtro di sicurezza a livello di riga non può essere applicato. Non è consigliabile filtrare solo la tabella dei dettagli, perché le query utente di questo ruolo non trarranno vantaggio dai riscontri di aggregazione.

Espressione di sicurezza a livello di riga che filtra solo la tabella di aggregazione Sales Agg e non la tabella dettagli Vendite non è consentita.

Per le aggregazioni basate sulle colonne GroupBy, è possibile usare un'espressione di sicurezza a livello di riga applicata alla tabella dei dettagli per filtrare la tabella di aggregazione, perché tutte le colonne GroupBy della tabella di aggregazione sono coperte dalla tabella dei dettagli. D'altra parte, non è possibile applicare un filtro di sicurezza a livello di riga sulla tabella di aggregazione alla tabella dei dettagli, pertanto 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 molto più facili da gestire.

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

• Geografia
• Customer
• Data
• Product Subcategory
• Product Category

L'immagine seguente mostra questo modello.

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

Nota

La tabella Sales Agg , come qualsiasi tabella, offre la flessibilità di essere caricata in diversi modi. L'aggregazione può essere eseguita nel database di origine usando processi ETL/ELT o dall'espressione M per la tabella. La tabella aggregata può usare la modalità di archiviazione import, con o senza aggiornamento incrementale per i modelli semantici oppure può usare DirectQuery ed essere ottimizzata per query veloci usando indici columnstore. Questa flessibilità consente architetture bilanciate che possono distribuire il carico di query per evitare colli di bottiglia.

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.

L'impostazione delle tabelle delle dimensioni correlate su Dual consente di agire 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:

Tabella sui molti lati	Tabella sul lato 1
Doppio	Doppio
Import	Importare o duale
DirectQuery	DirectQuery o Doppia

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 i riscontri di aggregazione tra origini che non dipendono dalle relazioni, vedere Aggregazioni basate sulle colonne GroupBy.

Esempi di query di aggregazione basate sulle relazioni

La query seguente raggiunge l'aggregazione, perché le colonne nella tabella Date hanno la granularità che può raggiungere 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à che può raggiungere 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 e ogni sottoquery viene valutata per determinare se può raggiungere l'aggregazione. Questa logica non mantiene true in tutti i casi a causa dell'ottimizzazione del piano di query, ma in generale dovrebbe essere applicata. 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 di business intelligence per le gerarchie temporali DAX sono consapevoli delle aggregazioni. La query seguente raggiunge l'aggregazione perché la funzione DATESYTD genera una tabella di valori CalendarDay e la tabella di aggregazione ha una granularità coperta per 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 per il raggruppamento. La tabella contiene dati IoT e un numero elevato di righe. La modalità di archiviazione è DirectQuery. Le query sull'origine dati che aggregano l'intero modello sono lente a causa del volume di dimensioni ridotte.

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 essi, le aggregazioni non verranno 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.

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 usa l'aggregazione Count table rows .

In particolare per i modelli che contengono attributi di filtro nelle tabelle dei fatti, è consigliabile usare le aggregazioni conteggio righe di tabella. Power BI può inviare query al modello usando COUNTROWS nei casi in cui non viene 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 query di Business Intelligence per le gerarchie temporali seguente non raggiunge l'aggregazione, perché la funzione DATESYTD genera una tabella di valori CalendarDay e la tabella di aggregazione non copre CalendarDay.

Precedenza aggregazione

La precedenza di aggregazione consente di considerare più tabelle di aggregazione da 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. Vengono eseguite query drill-through 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 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 un dashboard esecutivo ad alto profilo, quindi le query che fanno riferimento a tali attributi devono essere il più velocemente possibile.

Nota

Le tabelle di aggregazione DirectQuery che usano un'origine dati diversa dalla tabella dei dettagli sono supportate solo se la tabella di aggregazione proviene da un'origine SQL Server, Azure SQL o Azure Synapse Analytics (in precedenza SQL Data Warehouse).

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 Gestisci aggregazioni 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 considereranno prima Driver Activity Agg2 . Le sottoquery che non sono in corrispondenza della granularità a cui è possibile rispondere da Driver Activity Agg2 considereranno invece Driver Activity Agg . Le query di dettaglio a cui non è possibile rispondere da una tabella di aggregazione verranno indirizzate 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 .

Rilevare se le query hanno raggiunto o perso le aggregazioni

SQL Profiler può rilevare se le query vengono restituite dal motore di archiviazione della cache in memoria o sottoposte a push nell'origine dati da 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 nella tabella di aggregazione a cui è stato eseguito il mapping.

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 tenterà 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 i parametri di query M dinamici.

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

Community

Power BI ha una vivace community in cui MVP, professionisti bi e peer 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