## Oracle Analytic Functions – Part I

In today’s issue I would like to address a very useful topic in an oracle environment called analytic functions. These functions are extremely useful to build complex calculations in an easy manner. In older oracle versions you had to create compley sub-queries and joins what can be now done by analytic functions.

To give you a brief introduction in this topic we will build YearToDate sums with these functions, that means for our monthly turnovers we want to calculate the YearToMonth turnover (turnover from January until each month) for each month with the help of analytic functions. For this our base table TBL_SALES looks like this:

 MONTH QTY TURNOVER 1 201212 5 500 2 201301 1 100 3 201302 2 150 4 201303 3 250 5 201304 4 450

Fig.1: TBL_SALES

The general syntax fort he analytic functions is like this:

Function(….) Over( Partition by ….. Order by ….. Rows between …. And …..)

There are a lot of functions that can be used with this, but today we will focus on the SUM function, which is also known as aggregate function. The disadvantage of these aggregates is, that they can only work on the grouping of the whole query. But sometimes you need different groupings for different calculations. Maybe we want to list every month with its quantity and then the complete quantity. Without analytic functions one could create a sub query that sums all quantities, maybe like this:

SELECT MONTH,
QTY,
(SELECT SUM(QTY) FROM TBL_SALES) AS TOTAL_QTY
FROM TBL_SALES

With analytical functions the same would look like this:

SELECT MONTH,
QTY,
SUM(QTY) OVER() AS TOTAL_QTY
FROM TBL_SALES

The result in both cases is this:

MONTH               QTY   TOTAL_QTY
———————————————–
201212                  5             15
201304                  4             15
201303                  3             15
201301                  1             15
201302                  2             15

As this example is pretty simple the advantage isn’t that big. But the more complex the aggregates are, the higher is the advantage using analytic functions.

Back to the syntax of analytic functions – In our case above we didn’t use the PARTITION BY neither the ORDER BY clause. The reason is because we wanted to calculate a SUM over everything in the table. We can use the PARTITION BY clause to define on what level the function is working, i.e. on what level the total quantities are calculated. If we want to calculate them for each year then we would define a partition on the year like this:

SELECT MONTH,
QTY,
SUM(QTY) OVER(substr(MONTH,0,4) AS TOTAL_QTY_YEAR
FROM TBL_SALES

The result would be like this:

MONTH               QTY   TOTAL_QTY_YEAR
———————————————–
201212                  5             5
201304                  4             10
201303                  3             10
201301                  1             10
201302                  2             10

We now want to calculate the YearToMonth-Sum for each Month. For this we need to tell oracle that in Jan 2013 he has to summarize just the January qty, in Feb 2013 he has to sum the Jan and Feb quantity and so on. We can give this information with the ROWS BETWEEN …. AND …. Statement. With this we can define a dynamic window. But when we use it we need to order our datasets too. This is done with the ORDER BY clause within the Function. The complete Statement for the YearToMonth SUM would look like this:

SELECT  MONTH,
QTY,
SUM(QTY) OVER(PARTITION BY substr(MONTH,0,4)
ORDER BY MONTH ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTM
FROM TBL_SALES

The result is this:

MONTH               QTY   TOTAL_QTY_YEAR
———————————————–
201212                  5             5
201301                  1             1
201302                  2             3
201303                  3             6
201304                  4             10

In this case UNBOUNDED PRECEDING means that all rows before the current row but within the partition are taken.

This was an example to show the basic principle how these functions work. You can easily do the same on partitions over several columns. And of course you can combine them all. For example you could combine the YTM Calculation and the Total_Qty_Year in one statement.

This article was the first part of the series on the analytic functions. In the next parts I will describe more functions and give you further examples on how to use them correctly.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

## Zeitliche Intervalle filtern in dimensionalen Abfragen

Hallo zusammen,

heute wollen wir in einem Report ein zeitliches Intervall filtern anhand von zwei Parametern, die der User auf der Eingabeaufforderungsseite füllt. Wenn man einen Bericht dimensional entwickelt (egal ob auf DMR oder OLAP) erzeugt man sog. MemberSets, die dann ausgewertet werden. Man erstellt zum Beispiel ein MemberSet für das aktuelle Jahr und kann dann beispielsweise den Umsatz für dieses MemberSet berechnen lassen. Das geht so: total([Umsatz] within set [<MemberSet>]

Das Problem dabei ist, dass man diese Elemente explizit definieren muss. Wenn man z.B. den Februar bis November eines Jahres entsprechend auswerten möchte, dann müssen alle Monate explizit angegeben werden. Das ist im Falle einer Zeit-Dimension unüblich und unpraktisch. Man möchte sicherliche eher nur den Anfangs- und Ende-Monat angeben. Die dazwischenliegenden Monate sollen automatisch mitselektiert werden.

Um dieses zu erreichen kann man nicht den BETWEEN-Operator verwenden (wie bei relationaler Abfrageerstellung), sondern muss sich eine entsprechendes MemberSet mit Hilfe verschiedener Funktionen zusammen bauen. Dazu benötigen wir folgende Funktionen:

• Lastperiods(<Anzahl Perioden>, <Letztes Element>)
• Operator  ->
• Intersect (<Set1>, <Set2>)

Mit lastperiods können wir ein Set definieren, dass mit <Letztes Element> endet und die <Anzahl Perioden> Member davor beinhaltet. Mit lastperiods(12, [Dez. 2013]) würden wir z.B. alle Monate des Jahres 2013 erhalten. Mit dem Operator -> kann ein spezifischer Member rausgepickt werden. Man kann den Operator auch mit Parametern kombinieren, z.B. [Dimensional].[Datum (Monat)]. .[Datum (Monat)].[Monat]->?p_MonatVon?. Man kann beides auch kombinieren, um z.B. 3 Monate vor dem gewählten Monat zu bekommen. Wenn man für <Anzahl Perioden> einen negativen Wert angibt, arbeitet die Funktion vorwärts, liefert also die n folgenden Perioden.

Um unser Intervall definieren zu können, benötigen wir noch eine dritte Funktion: intersect. Hiermit erhält man die Schnittmenge zweier Sets zurück. Z.B.: Set1=(1,2,3) und Set2=(3,4,5). Dann erhält man mit intersect die Menge (3), weil nur dieses Element in beiden Sets vorhanden ist.

Nun können wir hingehen und folgende Sets bauen und danach mit intersect die Schnittmenge bilden:

• Set1 = lastperiods (-120; [Dimensional].[…..].[Monat]->?p_MonatVon?)
• Set2 = lastperiods (120; [Dimensional].[…..].[Monat]->?p_MonatBis?)

Man kann sich das Ganze auf dem Zeitstrahl veranschaulichen:

Auf das Ergebnisset, das man sich mit obigen Funktionen erstellt hat, kann man dann entsprechende Rechenoperationen durchführen.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

## Cognos Makro Funktionen in Report Studio

Hallo zusammen,

das heutige Thema meines BLOGs bezieht sich auf die Cognos Entwicklung im Report Studio. Anwendungsfall war, dass man dynamisch den MUN(=Member Unique Name) für eine drillbare Dimension erzeugen wollte. Hintergrund ist, dass sich ein User aussuchen kann, anhand welcher Dimension der Report strukturiert wird und gedrillt werden kann.

Die beiden relevanten MUNs auf oberster Ebene sahen wie folgt aus:

1. [Dimensional].[OWG].[OWG].[Oberwarengruppe]
2. [Dimensional].[AX].[AX].[Zielgruppe]

Das Ganze wurde mit Hilfe von Makro Funktionen umgesetzt, und zwar mit folgenden:

• Operator +
• Funktion substitute (<Suchmuster>, <Ersetzungstext>, <Zeichenkette>)
• Funktion prompt (<p_name>, <Datentyp>, <Std-Wert>)

Um Makrofunktionen zu verwenden, muss man einen Makroblock definieren. Das macht man mit der Raute:  # … <  Makro Anweisungen > … #. Das Makro erzeugt dann die entsprechende Report-Struktur zur Laufzeit.

Man könnte z.B. in einer Abfrage ein Datenelement erzeugen mit dem Inhalt: # ‘Test1‘+‘ Hallo‘ # Cognos würde zur Laufzeit Test1Hallo in das Datenelement schreiben. Als wenn man es vorher von Hand so da rein schreiben würde. Diese Anweisung würde natürlich erst einmal zu einem Fehler führen, da die Hochkommata fehlen. Um diese um unsere Zeichenkette zu ergänzen, können wir die Funktion sq( <Zeichenkette> ) verwenden: # sq(‘Test1‘ + ‘Hallo‘) #. Jetzt würde in dem Datenelement ‘Test1Hallo‘ stehen und es würde keinen Fehler mehr geben. Der + Operator verbindet übrigens Zeichenketten. 😉

Um auf unser Ausgangsproblem zurückzukommen. Die Funktion substitute ersetzt in <Zeichenkette> die Zeichenfolge <Suchmuster> durch <Ersetzungstext>. Mit prompt können Parameter abgefragt und verwendet werden. (Baut man manchmal in der Form auch in Framework Modelle direkt ein). Die Parameter der Funktion prompt sind selbsterklärend. Wenn es eine Eingabeaufforderungseite mit Parameterauswahl für diesen Parameter gibt, dann wird dieser auch nicht erneut abgefragt sondern prompt gibt das Ergebnis dieser Selektion zurück.

Die Lösung des obigen Problems sieht jetzt folgendermaßen aus:

# ‚[Dimensional].[‚ +  prompt(‚p_prodstruktur‘, ‚token‘ ,’OWG‘)  +  ‚].[‚  +  prompt(‚p_prodstruktur‘, ‚token‘ ,’OWG‘)  +  ‚].[‚  +  substitute(‚AX‘, ‚Zielgruppe‘,  substitute(‚OWG‘, ‚Oberwarengruppe‘, prompt(‚p_prodstruktur‘, ‚token‘ ,’OWG‘)))  +  ‚]‘   #

Der Parameter p_prodstruktur dient zur Auswahl der entsprechenden Struktur (‚OWG‘ oder ‚AX‘). Zunächst wird der fest stehende Anfang als Zeichenkette definiert, dann wird das Ergebnis des Parameters verwendet und zwar zweimal. Das Ergebnis wird jeweils in eckige Klammern eingeschlossen, so dass man damit schon mal folgendes hätten: [Dimensional].[OWG].[OWG].[ im Falle der Auswahl OWG. Nun wird mit Hilfe von substitute geguckt, ob das Suchmuster OWG in dem Produktstrukturparameter enthalten ist (und somit gewählt wurde). Dies wird dann durch Oberwarengruppe ersetzt. Falls nicht, wird der Parameterinhalt vom äußeren substitute geprüft, ob AX drin ist. Dieses wird dann durch Zielgruppe ersetzt. Auf diese Art und Weise kann man eine einfache If- Then- Else- Struktur im Makro abbilden und die gestellt Aufgabe somit lösen.

Das Ganze stellt eine von vielen Anwendungsmöglichkeiten der Cognos-Makro-Funktionen dar. In zukünftigen BLOG-Einträgen werde ich auch auf dieses Thema weiter eingehen.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!