Zeitanalyseelemente einsetzen und damit den Traum eines jeden Controllers wahr werden lassen: Berichte werden nur einmal angelegt und bleiben dann mit geringem oder sogar ohne jeglichen Aufwand stets auf dem aktuellen Stand. Das neueste Release hat wieder einige nützliche Erweiterungen zu bieten, um diesen Wunsch noch einfacher und flexibler umsetzen zu können!
Zeitanalyseelemente in relationalen Anwendungen einsetzen
Neues bei den Zeitanalyseelementen
In vorhergehenden Releases – vor allem in den Versionen 6.3.3 und 6.3.7 – wurden in unserer Business-Intelligence-Software DeltaMaster die Anwendungsmöglichkeiten der Zeitanalyseelemente für relationale Anwendungen deutlich erweitert. In den Blogbeiträgen Selfservice: Nur eine Frage der Zeitanalyseelemente und Dynamische Berichte in relationalen Modellen hatte ich mich ausführlich den jeweiligen Neuerungen gewidmet.
Die Periodenhierarchie
Berichte, die Zeitanalyseelemente einsetzen, folgen einem grundsätzlichen Ansatz: Zu einer ausgewählten Periode im Berichtsfilter – wie zum Beispiel einem konkreten Monat – werden nicht nur die Kennzahlen der aktuellen Periode angezeigt, sondern auch die Werte von Vergleichsperioden, etwa vom Vorjahresmonat oder vom Vormonat, oder kumulierte Werte bis zum aktuellen Monat oder auch Änderungen zum Vorjahr oder Vormonat. Anstelle eines Monats könnte hier auch ein Tag oder ein Quartal stehen.
Das neueste Release DeltaMaster 6.3.9 hat noch einmal die Möglichkeiten von Referenzen auf andere Elemente erweitert. Weiterhin können nun berechnete Elemente erstellt werden, die benutzerdefinierte Ausdrücke direkt verwenden können, aber dazu später mehr!
Zu Illustrationszwecken verwenden wir eine mit Microsoft Excel/SQL Selfservice angelegte Demo-Anwendung mit der oben gezeigten Periodenhierarchie. Diese geht – aus Platzgründen für die Grafik – nur bis auf die Monats- und nicht auf die Tagesebene hinunter.
Haben wir später vor, berechnete Elemente in der Zeitdimension anzulegen, müssen die Ebenen Quartal und Jahr der Zeitdimension für die Modellierung explizit vorhanden sein. Werden nur Zeitanalyseelemente benötigt, genügt auch eine virtuelle Zeitdimension, bei der DeltaMaster Quartals- und Jahresebene aus der gegebenen Monatsebene automatisch erstellt.
Zeitanalyseelemente einsetzen, um andere Perioden zu referenzieren
Wir konzentrieren uns auf die Zeitanalyseelemente, die über einen benutzerdefinierten Ausdruck bestimmt werden. Im Modellieren-Modus in der Zeitdimension unter “Ich möchte…Zeitanalyseelement hinzufügen” kann der Editor aufgerufen werden. Ganz unten im Editor befindet sich dann der Punkt “Benutzerdefinierter Ausdruck”, unter dem ein Feld zur Eingabe der Befehle bereitsteht.
Ein benutzerdefinierter Ausdruck besteht normalerweise aus bis zu drei logischen Abschnitten: Zunächst beziehen wir uns mittels einer in Kürze erläuterten Syntax auf ein anderes Periodenelement. Dann ermitteln wir optional eine Menge von Perioden, die an der ausgewählten Periode verankert ist (zum Beispiel die letzten 12 Monate oder die Monate seit Jahresbeginn) und schließlich wird – im Falle einer Menge – optional eine Aggregationsvorschrift angegeben, um zu einer einzigen Kennzahl zu gelangen.
In diesem Beitrag soll einmal der erste Schritt – also Referenzen auf Elemente – im Vordergrund stehen und ausführlicher beschrieben werden.
Schauen wir somit zunächst auf die Tabelle der Ausdrücke, die ein Element zurückgeben. Die folgende Tabelle stammt aus den Release Notes 6.3.1 und zeigt bezüglich der referenzierten Elemente den Stand vor dem Release 6.3.9:
Ausdrücke, die ein Element erzeugen
Die meisten Befehle sind selbsterklärend, aber gehen wir sie ruhig einmal der Reihe nach durch. Um die sichtbaren Werte besser nachvollziehen zu können, habe ich die Werte der Kennzahl für die Monate im Jahr 2020 mit 1 bis 12 angesetzt und im Jahr 2021 mit 101 bis 112:
Werte der Beispielanwendung
Nehmen wir einmal an, dass im Berichtsfilter der September 2021 ausgewählt ist. Schauen wir uns zunächst die Befehle an, bei denen wir nur in der Ebene des aktuell gewählten Elements, also hier der Monate navigieren.
Current, Lag und Lead
Über einen benutzerdefinierten Ausdruck “current” geben wir direkt das ausgewählte Periodenelement (hier orangefarben hinterlegt) wieder. Mit lag(n) gehen wir n Schritte in die Vergangenheit, lead(n) führt uns n Schritte in die Zukunft:
Mit lag und lead bewegen wir uns in der Ebene der ausgewählten Periode
In der Grafischen Tabelle sieht das dann so aus:
Die benutzerdefinierten Ausdrücke referenzieren die gewünschten Werte
Die erste Zeile der Spaltennamen gibt hier zur Illustration den verwendeten benutzerdefinierten Ausdruck wieder. Die zweite Zeile setzt die “{}”-Schreibweise aus der Tabelle der Elemente ein, um das referenzierte Element anzuzeigen, z. B. {pp8} im Falle des Ausdrucks lag(8).
“Previous” ist ein längeres Synonym für lag(1) und “Next” ein kürzerer Ausdruck für lead(1). Sollte man den Datenbereich verlassen, z. B. mit lead(4) landete man beim nicht vorhandenen Januar 2022, wird ein NULL generiert.
Ancestor, Parent und Year (Quarter, Month, Week)
Die nächste Gruppe von Referenzen beschreibt Beziehungen zu Vorgängern in der Baumstruktur der Periodendimension:
Referenzen auf Vorgänger in der Baumstruktur sind möglich
Die Ebenen sind von oben nach unten aufsteigend durchnummeriert, beginnend mit der 0 für das all-Element, dann mit der 1 für die Jahresebene, der 2 für Quartale und schließlich mit der 3 für die Monatsebene. Mit ancestor(2) verweisen wir hier auf das übergeordnete Quartal und mit ancestor(1) auf das Jahr des ausgewählten Monats.
Wir können die Ebenen aber auch mit Namen ansprechen, zum Beispiel mit quarter für die Quartalsebene – unabhängig davon, welcher Levelindex vorliegt.
Schließlich gibt es noch die Möglichkeit, über “parent” auf den direkten Vorgänger zu verweisen. Da sich die Befehle auch verketten lassen, referenziert man mit “parent.parent” den Vorgänger des Vorgängers usw. Somit gibt es stets mehrere Möglichkeiten, dasselbe Zielelement zu erreichen:
Viele Wege führen nach Rom
Bisher: Keine Referenz nach unten!
Wichtig ist hier vor allem, dass die Zielebene auf der gleichen Höhe des Elements im Berichtsfilter oder höher liegen muss. Steht man im Berichtsfilter auf dem 3. Quartal, führen Verweise auf die Monatsebene zu NULL-Werten:
Ausgehend vom Quartal ist die Monatsreferenz nicht definiert
Bei der zweiten Zeile wären nun außerdem einige Korrekturen in den Bezeichnern nötig: Das “parent” ist nun in der Jahresebene angesiedelt und der richtige Bezeichner wäre {ap1} anstelle von {ap2}, ebenso müsste parent.parent auf {ap0} umgestellt werden. Eine dreifache Anwendung von parent ist nicht mehr möglich und erzeugt einen NULL-Wert. Bezeichner von Verweisen, die auf parent-Konstruktionen basieren, passen somit immer nur für Berichtsfilterelemente einer bestimmten Ebene.
Solange das Modell die Ebenen Jahr/Quartal/Monat aufweist, ist es unerheblich, ob wir bspw. ancestor(1) oder year verwenden.
Fügen wir eine weitere Ebene der Halbjahre zwischen Jahr und Quartal ein, dann wird mit quarter immer noch die richtige Ebene angesprochen, während ancestor(2) nun nicht mehr auf Quartale, sondern auf die Halbjahre verweisen würde.
Eine Anpassung der sprechenden Namen von {ap2} auf {ap3} wäre aber auch hier für quarter nötig, da ja die Quartalsebene vom 2. Level auf das 3. Level wandert.
Cousin
Nun verbleibt noch die Referenz Cousin(<level index>,<offset>): Cousin springt auf den Vorgänger A des im Berichtsfilter ausgewählten Elements in Höhe des Levels <level index>. Von dort geht es <offset> Schritte in die Vergangenheit zu einem Element B. Von diesem Element B wird dasjenige Nachfolgeelement gewählt, das in der gleichen Beziehung zu B steht wie das Startelement zum Vorgänger A.
<offset> kann auch negativ sein, dann geht es die entsprechende Anzahl von Schritten in die Zukunft.
Steht der Berichtsfilter wieder auf September 2021, so bewirkt cousin(2,3) die folgenden Schritte:
Funktionsweise von Cousin
Von September 2021 springen wir auf das Level 2, also auf Quartalsebene, zum 3. Quartal 2021. Dann geht es drei Schritte zurück zum 4. Quartal 2020. Hier steht Dezember 2020 im gleichen Verhältnis zum 4. Quartal 2020 wie September 2021 zum 3. Quartal 2021.
Cousin in der Grafischen Tabelle
Cousin(1,1) geht zunächst auf das 1. Level der Jahre, geht dann 1 Jahr zurück auf 2020 und wählt dann September 2020 aus, da sich September 2020 zu 2020 genauso verhält wie September 2021 zu 2021. PeriodToYearAgo(1) verhält sich wie cousin(1,1), bloß wird das Jahreslevel logisch abgeleitet und nicht aus dem Levelindex ausgelesen. pya(1) ist dann schließlich eine abkürzende Schreibweise für PeriodToYearAgo(1).
Alternative Interpretation von Cousin
Alternativ könnte man sich bei der Interpretation des Cousin-Befehls auch vorstellen, dass wir vom Ausgangsmonat eine bestimmte Anzahl von Schritten zurückgehen, also eigentlich lag(<offset>*#Nachfahren) verwenden. Hier bezeichnet #Nachfahren die Anzahl der Elemente aus der Ebene der Periode im Berichtsfilter unterhalb des Elements der Ebene, zu der wir springen.
Im Fall eines Quartals gibt es 3 Monate unterhalb des Quartals, bei einem Jahr sind es 12 Monate. Cousin(2,3) entspricht hier lag(9) und Cousin(1,1) verhält sich im vorliegenden Fall wie lag(12).
Cousin(2,5) verhielte sich bei einem Monat im Berichtsfilter wie lag(15) und Cousin(1,-2) wie lead(24). Wäre aber ein Quartal im Berichtsfilter ausgewählt, z. B. 3. Quartal 2021, so entspräche Cousin(2,5) nur einem lag(5) und Cousin(1,-2) könnte durch ein lead(8) ersetzt werden – unter der Annahme eines Datenmodells, das auch die Folgejahre 2023 und 2024 enthält.
Zeitanalyseelemente einsetzen mit firstchild und lastchild
Nachdem nun die bereits bestehenden, umfangreichen Möglichkeiten beschrieben wurden, ist es Zeit, die Neuerung aus dem Release 6.3.9 anzusprechen.
Es sollte aufgefallen sein, dass sich die bisherigen referenzierten Elemente nie auf einer Ebene unterhalb des Ausgangselements befinden. Dies liegt darin, dass Eindeutigkeit nur auf dem Weg nach oben, aber nicht nach unten vorliegt.
Mit den neuen benutzerdefinierten Ausdrücken firstchild und lastchild heben wir nun diese Beschränkung auf! Die Namen der Befehle sind an die Gegenstücke in der OLAP-Welt angelehnt, in der in MDX entsprechende Befehle vordefiniert sind.
Sei zunächst das 3. Quartal 2021 im Berichtsfilter ausgewählt. Mit den Befehlen firstchild und lastchild springt man zu den Kindern; das erste Kind ist in diesem Fall der Juli 2021 und das letzte Kind September 2021:
Firstchild des Jahres 2021 wäre das 1. Quartal 2021 und lastchild ergäbe das 4. Quartal.
Schauen wir nun auf einige Anwendungen, die in dieser oder ähnlicher Form häufiger auftreten können.
Zeitanalyseelemente einsetzen: Der erste Monat des aktuellen Jahres
Wir möchten nun immer auf den Januar des aktuellen Jahres verweisen. Nehmen wir hier wieder an, dass wir im Berichtsfilter September 2021 ausgewählt haben.
Eine Lösung lag(8) wäre nur für den September 2021 passend, führte aber bereits im folgenden Monat Oktober 2021 zu einem falschen Verweis auf Februar 2021. Würden wir Januar 2021 fest verdrahten, müssten wir das im folgenden Jahr 2022 händisch anpassen.
Eine von mehreren möglichen Lösungen ist im folgenden Diagramm zu sehen:
Benutzerdefinierter Ausdruck für Januar des aktuellen Jahres
Mit year.firstchild.firstchild springt man zunächst zum aktuellen Jahr und wandert durch zweimalige Anwendung von firstchild über das 1. Quartal zum Januar. Diese Lösung würde nebenbei bemerkt auch noch dann funktionieren, wenn anstelle eines Monats ein Quartal oder das Jahr selbst im Berichtsfilter ausgewählt ist.
Der August des aktuellen Jahres
Wie geht man vor, wenn ein bestimmter Monat des aktuellen Jahres wie zum Beispiel der August referenziert werden soll?
Benutzerdefinierter Ausdruck für August des aktuellen Jahres
Wir können das Ergebnis für Januar übernehmen und mit einem einfachen Anfügen von lead(7) erreichen wir den August des aktuellen Jahres. Hier lautet der benutzerdefinierte Ausdruck year.firstchild.firstchild.lead(7), alternativ könnten wir uns beispielsweise aber auch mit year.lastchild.lastchild.lag(4) über das 4. Quartal 2021 vom Dezember aus dem August nähern.
Der August des letzten Jahres
Möchte man immer den August des letzten Jahres sehen, muss man nur von Januar nicht sieben Schritte in die Zukunft, sondern einfach mit lag(5) fünf Schritte in die Vergangenheit gehen:
Benutzerdefinierter Ausdruck für August des letzten Jahres
Mit year.firstchild.firstchild.lag(5) landen wir immer beim August des Vorjahres. Auch mit dem etwas längeren Befehl year.lag(1).firstchild.firstchild.lead(7) kämen wir zum Ziel.
Die neuen Befehle firstchild und lastchild sorgen dafür, dass man effektiv Referenzen auf jede beliebige, in einem bestimmten relativen Verhältnis stehende Periode bilden kann, egal, ob sich diese ober- oder unterhalb der Berichtsfilterperiode oder auf der gleichen Ebene befindet.
Es gibt im Normalfall immer mehrere Ausdrücke, die zum gewünschten Ergebnis führen.
Der letzte vollständige August
Die letzte Lösung hat die Eigenschaft, für jeden Monat des Jahres 2021 den August 2020 zu generieren. Wir hätten nun gerne, dass der jeweils letzte, garantiert vollständige August angezeigt werden soll, also zum Beispiel bis August 2021 auf den August 2020 verwiesen wird und ab September 2021 auf den August 2021.
Das sieht auf dem ersten Blick nach einem Problem aus, das nach einer Fallunterscheidung verlangt, aber nach etwas Nachdenken ergibt sich eine direkte Lösung!
Über lag(8) sorgen wir dafür, dass wir ausgehend vom Monat im Berichtsfilter erst einmal auf einem Monat im gewünschten Jahr landen. Dann wenden wir auf diesen Monat das bisherige Prozedere an.
Benutzerdefinierter Ausdruck für letzten vollständigen August
Starten wir z. B. mit September 2021, führt lag(8) zum Januar 2021, über year ergibt sich somit das Jahr 2021. Befinden wir uns hingegen im Juli 2021, so landen wir mit lag(8) im November 2020 und year ergibt nun das Jahr 2020. Mit dem Befehl lag(8).year.firstchild.firstchild.lead(7) erhalten wir somit den jeweils gewünschten, garantiert vollständigen August.
Die folgenden Grafische Tabellen zeigen die in den letzten Abschnitten definierten Zeitanalyseelemente mit benutzerdefinierten Ausdrücken, einmal für September 2021, einmal für Juli 2021:
Die referenzierten Periodenelemente für September und Juli 2021
Die Werte der vom aktuellen Monat aus referenzierten Elemente unterscheiden sich nur für den letzten vollständigen August.
Hier steht nur für current mit {cp} ein {}-Ausdruck zur Verfügung, der den Namen der zugehörigen Periode dynamisch einsetzt. Für die anderen Elemente existieren solche Ausdrücke nicht.
Bis hierhin hätte man die Zeitdimension auch als virtuelle Hierarchie anlegen können. DeltaMaster konstruiert in diesem Fall sämtliche Ebenen allein aus den Monaten. Die Zeitanalyseelemente mit benutzerdefinierten Ausdrücken funktionieren auch in einer virtuellen Zeitdimension.
Nun kommen wir zu der zweiten versprochenen Neuerung, die das Anlegen von berechneten Elementen betrifft.
Mehr Komfort bei berechneten Elementen
Benutzerdefinierte Ausdrücke in berechneten Elementen
Wenn Berechnungen mit Zeitanalyseelementen durchgeführt werden sollen, kommen berechnete Elemente zum Einsatz.
Nehmen wir einmal an, dass wir in unserem Beispiel für den aktuellen Monat die Änderung zum letzten vollständigen August ausweisen wollen.
Bisher hätten wir dazu zwei Zeitanalyseelemente anlegen müssen, eines mit dem Namen “aktuell” (mit dem benutzerdefinierten Ausdruck “current”) und ein zweites “Letzter vollständiger August” (mit der Definition “lag(8).year.firstchild.firstchild.lead(7)”). Auf diese beiden Zeitanalyseelemente können wir in einem neu angelegten berechneten Element verweisen. Eine Abweichung lässt sich einfach definieren:
Definition einer Differenz zweier Zeitanalyseelemente in einem berechneten Element
Neu ist nun, dass die vorherige Anlage von Zeitanalyseelementen nicht zwingend erforderlich ist. DeltaMaster versteht nun die benutzerdefinierten Ausdrücke der Zeitanalyseelemente auch direkt im berechneten Element. Es ist somit eine direkte Definition der folgenden Form möglich:
Direkte Definition im berechneten Element
Natürlich führen beide Ansätze zum identischen Ergebnis:
Zwei Möglichkeiten, das Delta zu berechnen
Komplexere Berechnungen
Die Ausdrücke können auch mit SQL-Konstrukten gemischt werden. Für die zahlreichen Möglichkeiten, die hier vor allem auch mit Mengen in benutzerdefinierten Ausdrücken entstehen, reserviere ich aber lieber einen eigenen Blogbeitrag. Hier sei nur als Teaser ein Ausdruck für ein berechnetes Element gegeben, das die Summe seit dem letzten August (exkl.) bis zum aktuellen Monat ergibt:
Ein komplexerer Ausdruck im berechneten Element
Der in diesem Beitrag nicht ausführlich behandelte Mengenbefehl periodstodate(0) auf dem All-Level 0 liefert alle bisherigen Monate bis zum aktuellen Monat, nicht nur die des laufenden Jahres. Auf diesen Monaten wird dann die Summe gebildet.
Ist der aktuelle Monat September oder später, liegt lag(8) noch im laufenden Jahr und es wird die gesamte kumulierte Summe bis zum August des laufenden Jahres wieder abgezogen. Für Monate vor dem September gerät man mit lag(8) ins Vorjahr und es wird die gesamte kumulierte Summe bis zum August des Vorjahres subtrahiert.
In beiden Fällen bleibt die Summe der Monate ab dem letzten August (exkl.) übrig.
Das berechnete Element im Einsatz
Hier sieht man beide Varianten im Einsatz: Im September 2021 fällt nur der Septemberwert mit 109 an. Ist Januar 2021 im Berichtsfilter, wird die Summe ab September des Vorjahres gerechnet: 9 + 10 + 11 + 12 + 101 = 143
Noch mehr Automatisierung
Für eine vollständige Automatisierung kann der im Berichtsfilter eingestellte Monat auch aus dem Tagesdatum beispielsweise über den SQL-Befehl “current_date” und einer anschließenden Stringbearbeitung als Defaultauswahl eines Berichtes eingestellt werden.
In diesem Fall muss anschließend an einem solchen Bericht nichts mehr nachjustiert oder verändert werden, er ist mit dem Öffnen der Sitzung immer automatisch auf dem passenden Stand. Das genaue Vorgehen hängt hier aber von der Modellierung der Zeitdimension und der verwendeten Datenbank ab. Wenden Sie sich vertrauensvoll an unseren Support, falls Sie hier Hilfe benötigen!
