Tuning delle prestazioni delle query sul database multidimensionale

Quando si utilizza un database multidimensionale come origine dati in Oracle Analytics, potrebbero verificarsi problemi di prestazioni, con conseguente generazione di query di espressioni multidimensionali (MDX) non ottimali.

Se si modifica la progettazione, è possibile migliorare le query MDX generate da Oracle Analytics. Oltre che sulle prestazioni dei report, questa operazione può influire notevolmente anche sul volume delle risorse utilizzate nel database. Il modo in cui si utilizzano le funzioni supportate o non supportate influisce notevolmente sulle query MDX generate e, di conseguenza, sulle prestazioni.

Poiché ogni caso d'uso è unico, il team di sviluppo deve rivedere le opzioni, analizzare i log delle query di Oracle Analytics e selezionare la soluzione più adatta per ciascun caso d'uso.

In questo argomento non vengono affrontati i problemi di prestazioni causati dall'infrastruttura, come reti, browser o visualizzazione dei report.

Metodologia

Oracle consiglia di effettuare i task riportati di seguito per migliorare le prestazioni. È importante comprendere la struttura delle query MDX nonché i log delle query generati da Oracle Analytics.

  • Semplificare le query MDX generate.
  • Ridurre il numero di query MDX generate.
  • Accertarsi che nella query MDX siano applicati filtri e selezioni ottimali.
  • Eseguire il tuning delle prestazioni con l'amministratore del database (DBA) sul database multidimensionale e verificare il motivo per cui le prestazioni del database di origine sono ancora mediocri.
  • Modificare l'analisi in base al feedback dell'amministratore del database.

Ottimizzazione dei passi di selezione

Quando si ottimizzano i passi di selezione, è possibile semplificare le query MDX, ridurre il numero di query MDX generate e migliorare le prestazioni.

Nella figura riportata di seguito viene mostrato un esempio di confronto tra i passi di selezione ottimizzati e quelli non ottimizzati.

Segue la descrizione di GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg
.jpg​

Istruzioni CASE

La funzionalità delle istruzioni CASE non è supportata nelle query MDX e deve sempre essere applicata in Oracle Analytics. La logica illustrata in questa sezione in merito alle istruzioni CASE è valida per la maggior parte delle funzioni non supportate nelle query MDX (if null e così via).

L'utilizzo delle istruzioni CASE comporta vantaggi e svantaggi. Quando si includono le istruzioni CASE nelle formule dei report, queste non vengono inserite nella query MDX. In questo modo è possibile semplificare la query MDX e migliorare le prestazioni. Tuttavia, il lato negativo è che l'applicazione dei filtri risulta meno efficace, ossia la query potrebbe restituire più record di quanto necessario.

L'uso della funzionalità dell'istruzione CASE comporta le limitazioni riportate di seguito.

  • Se l'istruzione CASE non combina più membri, la colonna di base utilizzata nell'istruzione deve essere inclusa nella query e nelle viste come colonna separata nascosta.
  • Se l'istruzione CASE combina più membri, la colonna di base non può essere inclusa nella vista senza influire sul livello di aggregazione. In questo caso:
    • Se la regola di aggregazione della misura non è Aggregazione esterna, la colonna di base deve essere esclusa nella query.
    • Se la regola di aggregazione della misura è Aggregazione esterna, la colonna di base deve essere inclusa nella query ed esclusa dalla vista. È necessario modificare l'impostazione predefinita della regola di aggregazione della misura in una regola di aggregazione interna semplice (SUM, MAX, MIN). Questa soluzione funziona solo se la regola di aggregazione interna viene utilizzata per combinare i membri e fornisce risultati corretti.

Funzione FILTER

A differenza della funzionalità dell'istruzione CASE, la funzione FILTER può essere inviata al database per l'esecuzione.

Il principale vantaggio dell'uso della funzione FILTER nelle formule dei report è che la selezione viene applicata nella query MDX e il volume di dati calcolati e recuperati dal database viene ridotto.

Il principale svantaggio dell'uso della funzione FILTER è che può aumentare il numero di query MDX eseguite. Per impostazione predefinita, viene eseguita una query per ciascuna funzione FILTER utilizzata.

Esempio delle funzioni CASE e FILTER

In questo esempio, un utente richiede un report che mostra i profitti in base al trimestre e all'unità mantenimento scorte (SKU) del prodotto selezionato. Le unità SKU, inoltre, sono raggruppate in 12 categorie. Alla categoria Other Cola sono assegnati i seguenti prodotti del settore di attività: Cola, Diet Cola e Shared Diet Cola.

Segue la descrizione di GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg
.jpg​

Di seguito è riportata la query logica dell'istruzione CASE:

SELECT

   0 s_0,

   CASE when XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola') THEN 'Other Cola' ELSE XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" END s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_3,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_6,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_8,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_9,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_10,

   XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" s_11

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 8 ASC NULLS LAST, 11 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

Non sono presenti raggruppamenti basati sull'istruzione CASE. Viene generata una query MDX semplice con l'istruzione CASE elaborata da Oracle Analytics:

With 
  set [_Product3]  as 'Descendants([Product], [Product].Generations(3), leaves)'
  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'
select 
  { [Measures].[Profit]
  } on columns,
  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties GEN_NUMBER, [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows 
from [Sample.Basic]

L'istruzione CASE viene eseguita su BI Server come indica l'impostazione del database database 0:0,0:

 RqList <<11777451>> [for database 0:0,0]
                            D1.c6 as c6 [for database 0:0,0],
                            D1.c4 as c4 [for database 0:0,0],
                            case  when D1.c7 in ([ 'Cola', 'Diet Cola', 'Shared Diet Cola'] ) then 'Other Cola' else D1.c7 end  as c2 [for database 0:0,0],
                            D1.c5 as c5 [for database 0:0,0],
                            D1.c3 as c3 [for database 0:0,0],
                            D1.c1 as c1 [for database 0:0,0],
                            D1.c7 as c7 [for database 0:0,0],
                            D1.c8 as c8 [for database 0:0,0]

In alternativa, è possibile utilizzare un filtro applicato alla metrica dei profitti per recuperare solo i membri del settore di attività desiderati. In questo scenario vengono create tre metriche con i filtri corrispondenti applicati.

Di seguito è riportata la query logica dell'istruzione FILTER:

SELECT

   0 s_0,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_3,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_6,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_8,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_9,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola')) s_10,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Sasprilla','Birch Beer','Dark Cream')) s_11,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('xxxxx')) s_12

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 8 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

In questo scenario vengono generate tre query, una per ciascun filtro, e si verificano problemi di prestazioni.

Query 1:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "xxxxx")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

Query 2:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Birch Beer") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Dark Cream") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Sasprilla")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

Query 3:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Diet Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Shared Diet Cola")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

Esempio di applicazione del filtro per i prodotti

Un approccio migliore consiste nell'includere la colonna dei prodotti nel report, con un'unica colonna di misura senza alcun filtro. Creare quindi un filtro che includa i prodotti desiderati. Se si desidera raggruppare i prodotti in categorie diverse, utilizzare un'istruzione CASE. In questo scenario viene generata un'unica query MDX con le righe filtrate e, sebbene Oracle Analytics applichi l'istruzione CASE, viene utilizzato il subset di dati e non vengono utilizzati tutti i record.

Di seguito è riportato un altro scenario in cui le istruzioni CASE causano problemi di prestazioni.

Uno sviluppatore applica un'istruzione CASE per rinominare i marchi e un prompt del dashboard consente agli utenti di selezionare il marchio.

Segue la descrizione di GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg
.jpg​

Segue la descrizione di GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg
.jpg​

Poiché l'istruzione CASE non è supportata nella query MDX, il filtro basato su Brand2 non può essere applicato nella query MDX. Tutti i marchi vengono selezionati, per un risultato non ottimizzato.

Segue la descrizione di GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg
.jpg​

In questo tipo di scenario Oracle consiglia di rimuovere l'istruzione CASE e rinominare i membri nel database o creare alias.