Una relazione "molti a molti" si verifica quando più record di una tabella sono associati a più record di un'altra tabella. Tali relazioni, nei modelli tabulari, non sono attualmente supportate. È necessario ricorrere all’implementazione di una soluzione esterna alternativa che possiamo generalizzare in due macro operazioni:
TABELLA[COLONNA] RELAZIONE TABELLA[COLONNA]
DATI_CAMBIO [ COD_SCENARIO_COD_PERIODO_COD_ CONTO_CLUSTER_COD_VALUTA] NaN IMPORTI [COD_SCENARIO_COD_PERIODO_COD_ CONTO_CLUSTER_COD_VALUTA]
1. Modifica strutturale relazionale del modello tabulare affinché vengano gestiti logicamente relazioni molti a molti.
2. Definire in una misura ad-hoc, espressa in linguaggio DAX, il filtraggio e l’aggregazione corretta della tabella dei fatti e delle rispettive tabelle dimensionali.
6.7.2 IMPLEMENTAZIONE DELLA SOLUZIONE
Per evitare l’incompatibilità a livello strutturale, è possibile scomporre la relazione "molti a molti" in due relazioni "uno a molti" utilizzando una terza tabella, detta “Bridge Table” o tabella intermedia. Ogni record della Bridge Table include un campo di confronto che contiene i valori delle chiavi primarie PK delle due tabelle che unisce (nella tabella intermedia, questi campi di
confronto sono chiavi esterne FK).
Tabella 6.9: Relazioni NaN in SSAS Tabular
Nel seguente contesto di sviluppo, sono state riscontrate due relazioni molti a molti (per gestione della valuta e della lunghezza del periodo), presenti in Tabella 6.9, da dover opportunamente risolvere per l’aggregazione e la visualizzazione corretti dei dati in fase di reporting.
La seguente Figura 7.0 mostra l’implementazione strutturale alternativa per relazioni NaN con relazioni di tipo “uno a molti”.
TABELLA[COLONNA] RELAZIONE TABELLA[COLONNA]
DATI_CAMBIO [ COD_SCENARIO_ COD_PERIODO_ COD_CONTO_ CLUSTER_COD_VALUTA] NaN IMPORTI [COD_SCENARIO_ COD_PERIODO_ COD_CONTO_CLUSTER_ COD_VALUTA] SP_LP_C_D [ SCENARIO_PERIODO_CONTO] NaN IMPORTI [SCENARIO_PERIODO_CONTO]
Tabella 7.0: Relazione tra “tabella dei fatti” e tabella “Dati_Cambio” e “SP_LP_CC_PD” in SSAS Tabular.
Successivamente, si definiscono le misure per la corretta interpretazioni dei risultati in fase di reporting, mediante piccole espressioni formulati in linguaggio DAX. Prima di poter proseguire con le relative formulazioni, è opportuno introdurre il termine di “contesto” adottato dal linguaggio. Il contesto consente di eseguire analisi dinamiche e risolvere problemi
riscontrati nelle formule in presenza di relazioni molti a molti così da restituire risultati correttamente filtrati per le sole righe utilizzate.
Si definiscono diversi tipi di contesto:
• contesto di riga o di più righe (Row Context) • contesto di query (Query Context)
• contesto di filtro (Filter Context)
Se si crea una formula in una colonna calcolata, il Row Context per tale formula include i valori di tutte le colonne presenti nella riga corrente. Se la tabella è correlata a un'altra tabella, il contenuto include anche tutti i valori dell'altra tabella che sono correlati alla riga corrente.
Il contesto di riga segue automaticamente le relazioni tra tabelle per
determinare quali righe nelle tabelle correlate sono associate alla riga corrente. In DAX sono inoltre incluse funzioni che iterano i calcoli in una tabella. Queste funzioni possono presentare più righe correnti e più contesti di riga correnti. In termini di programmazione, è possibile creare formule ricorsive in un ciclo interno ed esterno per un “contesto di più righe”.
Il Query Context fa riferimento al subset di dati che viene recuperato in modo implicito per una formulazione query. Quando si inserisce una misura o un altro campo del valore in una cella di una tabella pivot, il motore esamina le intestazioni di riga e colonna, i filtri dei dati e i filtri dei report per determinare il contesto. Quindi, il motore esegue i calcoli necessari per popolare ogni cella nella tabella pivot. Il set di dati recuperato è il contesto di query per ogni cella. Infine, il Filter Context è l'insieme dei valori consentiti in ogni colonna, in base ai vincoli di filtro che sono stati applicati alla riga o che sono definiti da
espressioni di filtro all'interno della formula.
Tale filtro viene applicato quando si specificano i vincoli del filtro, sul set di valori consentiti in una colonna o tabella, utilizzando gli argomenti della sintassi della formula. Il Filter Context può essere applicato ad altri contesti come il contesto di riga o il contesto di query.
Tuttavia, all'interno delle misure o delle colonne calcolate, che vengono aggiunte alla tabella pivot, è possibile specificare espressioni di filtro per controllare i valori utilizzati dalla formula. È inoltre possibile deselezionare i filtri in modo selettivo in colonne specifiche.
Dopo aver introdotto le tipologie di contesti differenti applicabili in una espressione DAX, possiamo ricondurci ad un classico esempio per poter identificare una soluzione generica ed adeguata da adottare in presenza di relazioni molti a molti per il corretto funzionamento delle misure presenti all’interno del modello SSAS Tabulare.
Ipotizziamo il seguente schema tabulare, definito dalla presenza di una tabella dei fatti, da due dimensioni rispettivamente “X” e “Y” e da una misura
“Amount” espressa in Figura 7.1, che esprime la somma cumulativa degli elementi “importi” presenti nella tabella dei fatti.
Figura 7.1: Somma cumulativa degli elementi espressa in una misura DAX
Ipotizziamo che esistano le seguenti relazioni logiche tra i vari elementi: • Un elemento della dimensione X può essere relazionato a più importi
presenti nella Tabella dei fatti (1aN);
• Più elementi della dimensione X possono essere uniti a più membri della dimensione Y (NaN).
Come già osservato, la relazione molti a molti presente tra le dimensioni X e Y, può essere gestita mediante l’introduzione di una tabella intermedia detta “Bridge Table”. In Figura 7.2, è possibile evidenziare tale passaggio risolutivo .
"
"
Figura 7.2: Soluzione strutturale applicata per relazioni NaN non supportate in progettazioni SSAS Tabulari
In questo contesto, se esploriamo gli elementi delle varie dimensioni, effettuando una Pivot Table inserendo su riga tutti gli elementi della
dimensione Y e in colonna la misura “Amount”, otterremo un errore logico durante il calcolo degli importi, poiché sarà riportato lo stesso importo ripetuto per ogni riga (per tutti gli elementi di Y).
Questo errore si manifesta poiché la dimensione Y è collegata indirettamente alla tabella dei fatti attraverso la struttura alternativa molti a molti creata con la Bridge Table.
È opportuno notare le seguenti osservazioni in fase di reporting:
• Se si applica un Filter Context sugli elementi della dimensione Y, questo implica il filtraggio della Bridge Table (BRIDGE TABLE Y) senza la propagazione automatica del filtro, arrestandosi, a causa della presenza della direzione relazionale opposta (X BRIDGE TABLE). Di conseguenza la tabella dei fatti non viene filtrata correttamente restituendo valori errati per la misura “Amount” (FATTI Y)
• Se si applica un Filter Context nella tabella degli elementi di X, di
conseguenza anche la tabella dei fatti viene filtrata correttamente grazie alla propagazione del filtro direzionale, poiché la dimensione X ha una relazione diretta con la tabella dei fatti. (FATTI X)
Per risolvere il seguente problema sarà sufficiente filtrare la dimensione X sfruttando la propagazione automatica verso la tabella dei fatti implementando, all’interno della misura “Amount”, uno script in DAX che svolga le seguenti operazioni:
• Recuperare il Filter Context esistente sulla dimensione Y
• Creare un nuovo filtro sulla dimensione X tale da mostrare i soli elementi di X appartenenti ad un subset di elementi di Y.
Per individuare quali elementi di X sono legati ad elementi di Y pre-selezionati dal Filter Context, è utile controllare, per ogni elemento di X, la presenza di una riga nella Bridge Table relativa a un elemento Y selezionato.
È possibile realizzare una misura ad-hoc mostrata in Figura 7.3 che effettui un conteggio degli elementi di Y.
Figura 7.3: Conteggio di elementi di Y espresso in una misura DAX Counting:= COUNTROWS (BridgeTable)
Eseguendo una Pivot Table, in presenza della nuova misura, con elementi della dimensione X sulle righe e la misura “Counting” in colonna, evidenzieremo per ogni elemento di X, il numero di elementi Y legati ad X.
Applicando un Filter Context sugli elementi della dimensione Y, la Bridge Table viene filtrata per effetto della propagazione automatica ottenendo nella stessa Pivot Table valori differenti per la misura Counting:
• Valori maggiori o uguali a 1 per elementi della dimensione X che
appartengono al set degli elementi di Y preselezionati (precedentemente filtrati dal Filter-Context).
• Nessun valore per elementi di X che non appartengono al set degli elementi di Y preselezionati.
Infine, per spostare il filtro dalla dimensione Y alla dimensione X, bisogna individuare tutti gli elementi di X per i quali la misura “Counting” ha valore maggiore di zero. Tale condizione può essere formulata e incapsulata nella misura “Amount”, in Figura 7.4, utilizzando le funzioni “Calculate()” e “Filter()” presenti nel linguaggio DAX.
Figura 7.4: Somma cumulativa degli elementi espressa in misura DAX
Come è possibile notare dalla Figura 7.4, sulla dimensione X viene creato un Filter Context contenente i soli elementi di X relativi alla misura “Counting” maggiori di zero. In questo modo, la dimensione X verrà filtrata correttamente mostrando i soli elementi relativi ad elementi di Y preselezionati fornendo una aggregazione logica dei dati in fase di reporting corretta.
Counting:= COUNTROWS (BridgeTable) Amount:=
CALCULATE (
È possibile ridefinire una sola misura per il modello “Amount” con la seguente sintassi in Figura 7.5.
Figura 7.5: Somma degli elementi espressa in una unica misura DAX
Per incorporare correttamente la funzione “Counting()” all’interno della misura “Amount” è sufficiente utilizzare un “Calculate()” forzato esplicito presente nella funzione “Filter()” che itera la tabella dei fatti.
La funzione “Calculate()” interno al “Filter()” trasforma il Row Context in un Filter Context, che si propaga nella Bridge Table con la espressione
“Calculate()”, usato nel secondo paramentro della funzione “Filter()”,
ottenendo un filtro bidirezionale nella Bridge Table e favorendo un filtraggio corretto della tabella dei fatti. Per ogni elemento della dimensione X, sarà eseguita, in modo iterativo la funzione COUNTROWS().
In generale, è opportuno spostare il filtro dalla tabella più lontana a quella più vicina rispetto alla tabella dei fatti usufruendo della Bridge Table, utilizzata come ponte, per verificare se le righe della tabella più vicina devono essere rese visibili o meno, a seconda del Filter Context esplicito applicato sulla tabella più lontana.
Tale esempio semplificato e risolutivo è stato utile per formulare una soluzione corretta per il problema relazionale NaN presente nel seguente caso di studio tra “Tabella dei fatti” e le rispettive tabelle “Dati_Cambio” e
“SP_LP_CC_PD”. In Figura 7.2 si definisce la misura finale, derivata dalla misura adottata come soluzione per l’esempio semplificato precedente, così da permettere il filtraggio corretto delle tabelle dei fatti e “SP_LP_CC_PD” per la gestione della lunghezza del periodo.
Amount:=
CALCULATE (SUM (Fact_Table [Amount]),
Figura 7.6: Somma degli elementi espressa in una unica misura DAX per il filtraggio corretto della lunghezza del periodo
IMPORTI_A:= CALCULATE(SUM(DATA[IMPORTO]); FILTER(SCENARIO_PERIODO_CONTO_CLUSTER;CALCULATE(COUNTROWS(SCENARIO_PERIODO_LUNGH EZZA_PERIODO_CONTO_CLUSTER_PERIODO_DELTA)>0)); FILTER(SCENARIO_PERIODO_LUNGHEZZA_PERIODO_CONTO_CLUSTER_PERIODO_DELTA;SCENARIO_P ERIODO_LUNGHEZZA_PERIODO_CONTO_CLUSTER_PERIODO_DELTA[COEFF_PERIODO_DELTA]=1)) IMPORTI_B:= CALCULATE(SUM(DATA[IMPORTO]); FILTER(SCENARIO_PERIODO_CONTO_CLUSTER;CALCULATE(COUNTROWS(SCENARIO_PERIODO_LUNGH EZZA_PERIODO_CONTO_CLUSTER_PERIODO_DELTA)>0)); FILTER(SCENARIO_PERIODO_LUNGHEZZA_PERIODO_CONTO_CLUSTER_PERIODO_DELTA;SCENARIO_P ERIODO_LUNGHEZZA_PERIODO_CONTO_CLUSTER_PERIODO_DELTA[COEFF_PERIODO_DELTA]=-1)) AMOUNT:=[IMPORTI_A]-[IMPORTI_B]
6.8 GESTIONE DELLE GERARCHIE