IndexCol-Funktion
In Oracle Analytics werden häufig Case-Anweisungen verwendet, wenn eine Berechnung basierend auf einem Variablenwert "verzweigt" wird. Wenn eine Variable in einer Case-Anweisung referenziert wird, sollten Sie stattdessen die IndexCol-Funktion verwenden, um die Effizienz des generierten SQL-Codes zu verbessern. In diesem Thema werden die IndexCol-Funktion und ihr Einsatz beschrieben.
IndexCol-Funktion
Sie verwenden die IndexCol-Funktion, wenn die Spalten oder Werte in einer Berechnung je nach Wert einer Session, eines Repositorys oder einer Präsentationsvariable variieren.
Syntax der IndexCol-Funktion:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Dabei wird das erste Argument als Ganzzahl aufgelöst, und die Elemente von <<expr_list>> entsprechen der Anzahl an möglichen Werten des ersten Arguments. Eines dieser Elemente wird dann basierend auf dem Wert des ersten Arguments in der SQL-Anweisung verwendet.
Beispiel: Wenn das Argument <<integer_literal>> drei mögliche Werte aufweist, muss das Argument <<expr_list>> drei Argumente enthalten, eines für jeden möglichen Wert von <<integer_literal>>.
Das erste Argument basiert oft auf dem Wert einer Sessionvariable oder einer Case-Anweisung mit Variablenreferenzierung. Sie können die IndexCol-Funktion in der Repository-Datei (.rpd) oder direkt in einer Berichtsspalte modellieren. Sie können mehrere IndexCol-Funktionen in einer einzelnen Anweisung verschachteln.
Vorteile der Funktion
Eine Berechnung mit einer <<case when>>-Anweisung wird komplett an den physischen SQL-Code übertragen. Die IndexCol-Funktion übergibt dagegen nur die erforderliche Spalte oder den Ausdruck an die Datenbank. Grund dafür ist, dass die IndexCol-Funktion bewertet wird, bevor der physische SQL-Code generiert wird.
In Kombination mit Variablen-Prompts, die eine Auswahl in einer Werteliste zulassen, können Sie wesentliche Änderungen an der Berichtsstruktur ohne Beeinträchtigung der Performance vornehmen.
Ein Nachteil der IndexCol-Funktion besteht darin, dass Sie sie nicht mit "like" in Ganzzahlberechnungen verwenden können. Sie können aber "like" in der Ausdrucksliste verwenden. Wenn eine Ganzzahlberechnung ein "like"-Element erfordert, müssen Sie stattdessen eine Case-Anweisung verwenden.
Beispiel:
Angenommen, Sie verwenden die Sessionvariable PREFERRED_CURRENCY, die die bevorzugte Währung für einen Benutzer festlegt. Dann wird der Umsatz basierend auf dem Wert der Sessionvariable in der vom Benutzer angegebenen Währung angezeigt.
Zwei Berechnungen wurden erstellt, um die richtige Währung basierend auf dem Wert der Sessionvariable zurückzugeben.
Die erste verwendet eine Case-Anweisung, wie in diesem Beispiel gezeigt:
CASE WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'USD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd" WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'EUR' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur" WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'AUD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud" ELSE NULL END
Die zweite verwendet die IndexCol-Funktion, wie in diesem Beispiel gezeigt:
INDEXCOL( CASE VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") WHEN 'USD' THEN 0 WHEN 'EUR' THEN 1 WHEN 'AUD' THEN 2 END , "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud")
Da das erste Argument der IndexCol-Funktion als Ganzzahl aufgelöst werden muss, wird eine Case-Anweisung für die Auflösung verwendet.
Wenn eine Abfrage mit der Case-Anweisungsberechnung ausgeführt wird, wird die komplette Case-Anweisung an die Datenbank übertragen, da sie zur Laufzeit ausgewertet wird. In einigen Fällen verursacht das Probleme mit dem Optimizer.
WITH
SAWITH0 AS (select sum(case when 'USD' = 'USD' then T42437.Revenue_Usd when 'EUR' = 'USD' then T42437.Revenue_Eur when 'AUD' = 'USD' then T42437.Revenue_Aud else NULL end ) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
from
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c2 ) D1
The same query run using the IndexCol function pushes down only the expression needed to satisfy the query, because the IndexCol function is resolved prior to SQL generation. This helps avoid issues with the Optimizer.
WITH
SAWITH0 AS (select sum(T42437.Revenue_Usd) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
from
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c2 ) D1Arbeitsmappen und INDEXCOL
Anhand der nun verfügbaren Parameter kann die IndexCol-Funktion jetzt in Arbeitsmappen genutzt werden.
In diesem Beispiel wird die IndexCol-Funktion verwendet, um die Zeitraumgranularität in einer Visualisierung zu ändern.
- Erstellen Sie einen Parameter, der als Spaltenselektor zur Auswahl der Zeitraumgranularität verwendet wird, in diesem Fall entweder "Month" oder "Quarter".
- Erstellen Sie anschließend eine benutzerdefinierte Berechnung zum Ausführen der
IndexCol-Funktion. Berechnung für dieses Beispiel:indexcol(case when @parameter("Time Selector Value")('Month')='Month' then 0 else 1 end, "HCM - Workforce Core"."Time"."Month Name", "HCM - Workforce Core"."Time"."Quarter") - Fügen Sie den Parameter in die Filterleiste einer Arbeitsmappe ein. Benutzer können die Granularität eines Berichts ändern, indem sie entweder "Month" oder "Quarter" im Spaltenselektorfilter auswählen.
