Ridurre la cardinalità per migliorare le prestazioni

Completato

Anche con DAX ottimizzato, un modello semantico può essere lento se contiene troppi dati. La cardinalità (il numero di valori univoci in una colonna) influisce direttamente sulle dimensioni del modello, sull'utilizzo della memoria e sulle prestazioni delle query. La riduzione della cardinalità è uno dei modi più efficaci per velocizzare un modello.

Comprendere in che modo la cardinalità influisce sulle prestazioni

I modelli semantici usano un motore di compressione in memoria denominato VertiPaq, che comprime la colonna di dati per colonna. Le colonne con un minor numero di valori univoci comprimono meglio ed eseguono query più velocemente. Una colonna con 10 valori univoci comprime molto più efficientemente di una colonna con 10 milioni di valori univoci.

Le colonne a cardinalità elevata creano due problemi:

• Dimensioni del modello maggiori. Più valori univoci richiedono più spazio di archiviazione, che aumenta l'utilizzo della memoria e può rallentare l'aggiornamento dei dati.
• Query più lente. Il motore deve elaborare valori più distinti durante l'applicazione di filtri, raggruppamenti o aggregazioni. Le colonne con cardinalità elevata nelle relazioni aumentano anche il costo del join di tabelle.

Si pensi a questo modo: se il modello è un dizionario, la cardinalità è il numero di parole univoco. Un dizionario con un milione di voci impiega più tempo per essere cercato rispetto a uno con mille.

Identificare le colonne a cardinalità elevata

Non tutte le colonne contribuiscono allo stesso modo alle dimensioni del modello. Per trovare i più grandi trasgressori, cercare colonne con queste caratteristiche:

• GUID o chiavi surrogate non usati nelle relazioni o nei report. Questi vengono spesso caricati per impostazione predefinita, ma non servono a scopo nel modello.
• Timestamp con alta precisione. Una colonna datetime che archivia i valori al millisecondo ha valori molto più univoci rispetto a una colonna date che archivia i valori a livello di giorno.
• Colonne di testo libero o descrizione. Le colonne di testo con valori univoci per riga (ad esempio le descrizioni delle transazioni o i commenti) comprimono in modo non appropriato.
• Identificatori univoci come numeri di ordine, numeri di fattura o ID sessione non necessari per la creazione di report.

In Power BI Desktop è possibile esaminare le statistiche delle colonne nella visualizzazione modello. L'anteprima dei dati in Power Query mostra anche i conteggi dei valori distinti durante la trasformazione.

Applicare strategie di riduzione

Dopo aver identificato colonne a cardinalità elevata, applicare una o più di queste strategie:

Rimuovere le colonne inutilizzate. Se una colonna non viene usata in relazioni, misure, oggetti visivi o ruoli di sicurezza, non importarla. Questa operazione viene talvolta definita filtro verticale. Esaminare regolarmente il modello per assicurarsi che ogni colonna soddisfi uno scopo.

Ridurre la precisione del tempo. Se la creazione di report richiede solo una granularità giornaliera, troncare le colonne datetime a date in Power Query prima di caricare. Il passaggio da datetime (milioni di valori univoci) a date (poche migliaia) riduce drasticamente la cardinalità.

Valori continui nel bucket. Raggruppare i valori numerici continui in intervalli. Ad esempio, invece di archiviare le età esatte (da 0 a 120), creare fasce di età come "18-25", "26-35" e "36-45". In questo modo si riducono i valori univoci mantenendo il valore analitico.

Rimuovere righe non necessarie. Filtrare i dati cronologici non più necessari per la creazione di report. Se gli utenti analizzano solo gli ultimi due anni, non caricare cinque anni di dati. Si tratta di un filtro orizzontale e riduce sia la cardinalità che il numero complessivo di righe.

Ottimizzare i tipi di dati delle colonne. Il motore VertiPaq usa la codifica dei valori per i dati numerici (altamente efficienti) e la codifica hash per il testo (meno efficiente). Se una colonna come il numero di ordine viene archiviata come testo con un prefisso ,ad esempio "SO123456"), è consigliabile rimuovere il prefisso e archiviarlo come numero.

Tip

Preferire la creazione di colonne calcolate in Power Query rispetto alle colonne calcolate DAX. Le colonne di Power Query vengono elaborate durante il caricamento dei dati e beneficiano di una migliore compressione VertiPaq. Le colonne calcolate DAX vengono valutate dopo il caricamento e in genere vengono compresse in modo meno efficiente.

Valutare i compromessi

La riduzione della cardinalità comporta sempre un compromesso tra granularità e prestazioni. L'arrotondamento dei timestamp al livello del giorno impedisce di analizzare i modelli intragiornalieri. Raggruppare le età in categorie significa che non è possibile filtrare per età esatte. Queste decisioni devono essere guidate dai requisiti aziendali.

Porsi queste domande prima di ridurre la cardinalità:

• Qualsiasi report, filtro dei dati o misura dipende da questo livello di dettaglio?
• Gli utenti possono ottenere i dettagli necessari da una pagina drill-through connessa a una tabella DirectQuery?
• La colonna viene usata nelle regole di sicurezza a livello di riga?

Quando l'azienda richiede prestazioni a livello di riepilogo e accesso a livello di dettaglio, un approccio basato sul modello composito può essere utile. Usare la modalità di archiviazione di importazione per i dati riepilogati (query veloci) e DirectQuery per il drill-through a livello di dettaglio (accesso su richiesta all'origine).