Seit inzwischen mehr als zwanzig Jahren ist OLAP (Online Analytical Processing) die bevorzugte Datenbanktechnologie für die meisten Business-Intelligence-Systeme. Begründet wurde das Konzept der „dimensionalen“ Datenhaltung maßgeblich durch den US-Akademiker E.F. Codd, der auch als geistiger Vater der relationalen Datenbanken gilt. Nachdem letztere bereits etwa drei Jahrzehnte lang vor allem für transaktionsorientierte Systeme im Praxiseinsatz waren, reifte die Erkenntnis, dass für analytische Fragestellungen andere Ansätze erforderlich sind, die weniger auf die Verarbeitung atomarer Vorgänge ausgerichtet sind, sondern für die flexible und performante Aggregation großer Datenmengen optimiert sind.

Vereinfacht betrachtet, sind OLAP-Datenbanken also Systeme, mit denen beliebige Zahlenwerte (meist bezeichnet als Kennzahlen, KPIs oder Measures) nach beliebigen Merkmalen (Kategorien, Dimensionen) gefiltert, gruppiert, aggregiert oder detailliert werden können – kurzum: mächtige Aggregationsmaschinen. Ein typisches Beispiel aus dem Vertriebscontrolling, einem der häufigsten Anwendungsfelder für BI-Systeme: Die einzelnen Positionen einer Ergebnis- oder Deckungsbeitragsrechnung sollen im zeitlichen Verlauf, entlang der betrieblichen Organisationsstruktur und anhand des Produktsortiments betrachtet werden. Konkretisiert bedeutet dies, tägliche, monatliche, quartalsweise und jährliche Absätze, Umsätze und Kosten nach Kunden, Kundengruppen und Vertriebsregionen sowie nach Artikeln und Warengruppen zu analysieren.

Die „Spielregel“ bei der Modellierung lautet dabei: Man speichere die Daten auf der detailliertesten gewünschten Ebene (im Beispiel: Tag, Kunde und Produkt) und modelliere die gewünschten Analysekategorien, die dann einerseits („aufwärts“) die Aggregationsstufen und andererseits („abwärts“) die Aufrissmöglichkeiten („Drilldown-Pfade“) definieren. Dabei können und sollten Kennzahlen mit gleichen Merkmalen (Dimensionalität) und gleicher Detaillierung (Granularität) zusammengefasst werden. Diese werden als Würfel (Cubes) oder im Falle der führenden OLAP-Technologie Microsoft Analysis Service (MSAS) als Kennzahlengruppen (MeasureGroups) bezeichnet. Dimensionen und MeasureGroups werden aus Tabellen im Data Warehouse versorgt, die als Dimensions- und Faktentabellen bezeichnet werden und gemeinsam je nach Normalisierungsgrad ein Star- oder Snowflakeschema bilden.

Nach welcher Logik aber soll nun die Aggregation erfolgen? Ähnlich wie im althergebrachten SQL kennt OLAP verschiedene vordefinierte Aggregationsfunktionen, die in den meisten Situationen helfen, das gewünschte Verhalten abzubilden. Darüber hinaus gibt es zur Abbildung komplexerer Anforderungen je nach Technologieanbieter entweder weitere Möglichkeiten „out of the box“ oder aber Modellierungstricks. Die wichtigsten Praxisfälle werden im Folgenden in Form einer Übersicht betrachtet. Daher richtet sich dieser Blogbeitrag vor allem an Einsteiger in die Materie. Für Profis existieren diverse weiterführende Blogbeiträge, auf die jeweils verwiesen wird.

Im Allgemeinen…

Normalweise lässt sich pro Measure, aber unabhängig von sämtlichen Dimensionen, eindeutig entscheiden, welche Rechenregel zum fachlich korrekten Ergebnis führt. Erfahrungsgemäß sind rund 90 % aller Measures sogenannte Flussgrößen und damit additiv. Im obigen Vertriebsbeispiel dürfen alle Measures summiert werden. Daher ist auch in MSAS die Standard-Aggregationsfunktion „Sum“ (Summe).

Der zweithäufigste Fall ist der Wunsch nach der Bildung von Durchschnitten. Hier ist zu unterscheiden zwischen dem einfachen Mittelwert und der Gewichtung. Beides bietet MSAS nicht in seinem Standardrepertoire, doch es existieren einfache Lösungen:

• Der Mittelwert berechnet sich in jeder Konstellation korrekt, indem man die Rohdaten summiert, zusätzlich einen Zähler (eine Measure mit dem Wert 1 pro relevantem Datensatz) mitführt und im Cube oder im Frontend die Summe durch die Anzahl teilt.
  Beispiel: Der Preis von Produkt A beträgt 2 EUR, der von Produkt B 4 EUR.
  Berechnung des Durchschnittspreises: (2+4)/2=3.
• Gewichtungen bildet man ab, indem man in den Rohdaten (d.h. relational, also in der Faktentabelle) ein Summenprodukt aus dem Produkt von Basiswert und dem Gewichtungsfaktor bildet und im Cube bzw. Frontend wiederum die Summe der Summenprodukte durch die Summe des Gewichtungsfaktors teilt. Kompliziert? Nein!
  Gleiches Beispiel (angenommen, A wurde zehnmal, B aber nur viermal verkauft):
  Berechnung des gewichteten Durchschnittspreises: (2*10+3*4)/(10+4)=32/14=2,29.

Der dritthäufigste Fall ist der einer Bestandslogik. Grundidee ist dabei die einer Fortschreibung sowie die Übernahme von zeitlichen Basisdaten (z.B. Tageswerten) auf höhere Hierarchiestufen (Monat, Quartal und Jahr). Anwendungsfälle sind Lagerbestände (v.a. bei Stichprobeninventuren, d.h. nicht alle Lager ermitteln durchgängig den Bestand aller Artikel), Headcounts oder Bilanzsalden. Abstrahiert lautet die Logik: Die betroffenen Measures sollen über alle Dimensionen außer der Zeitachse aggregiert werden; hinsichtlich der Zeit gilt der letzte verfügbare Wert.

Beispiel: Lager Nord meldet im Januar 100 Stück von Produkt A und 50 Stück von Produkt B (d.h. Gesamtbestand 150), Lager Süd im Februar 40 Stück von Produkt A und 20 Stück von Produkt B (Gesamtbestand 60).

Aufgabenstellung: Was ist der Bestand zum Quartalsende für die einzelnen Produkte und insgesamt? Lösung bzw. Rechenweg: Ermittle die jeweils letzte bekannte Kombination von Lager und Artikel, schreibe diese fort und addiere sämtliche Zwischenergebnisse!

Das klingt reichlich abstrakt und komplex. Doch ruhig Blut: MSAS bietet hierfür (wichtig: nur in der BI- bzw. Enterprise Edition!) unter dem Marketingbegriff „Extended Business Intelligence“ das Feature der sogenannten Semiadditivität in Form eines Sets zusätzlicher Aggregatoren, von denen vor allem Last-NonEmpty (LNE) von Bedeutung ist, da er genau den oben beschriebenen Fall behandelt. Leider muss ergänzt werden, dass LNE nicht in allen Datenkonstellationen erwartungsgemäß arbeitet. Als Workaround ist die relationale Fortschreibung der Basisdaten in der Faktentabelle empfohlen, wobei dies zu einer beträchtlichen Erhöhung des Datenvolumens und entsprechenden Performanceeinbußen sowohl bei der Verarbeitung des Cubes als auch bei der Datenabfrage führen kann. Zur Vertiefung sei auf zwei Blogbeiträge verwiesen:

• „Die LastNonEmpty-Aggregation“ (13.08.2010)
• „Was nicht passt, wird passend gemacht“ (12.08.2011)

Weniger häufig in typischen „kaufmännischen“ BI-Anwendungen, aber dennoch einer Erwähnung wert, sind die weiteren MSAS-Standardaggregatoren

• Anzahl (Count),
• Minimum (Min),
• Maximum (Max) und
• keine Aggregation (None).

Manchmal kann es sinnvoll sein, Measures mehrfach mit unterschiedlichen Aggregatoren abzubilden. Ein Beispiel dafür sind Produktionsmessdaten wie Ausschüsse, Maschinenausfälle, Stillstandszeiten etc., für die entlang aller Dimensionen Summen, Mittelwerte, Minima und Maxima berechnet werden sollen.

… und im Besonderen

Was aber ist zu tun, wenn die oben aufgestellte Hypothese, dass Measures stets über alle Merkmale (Dimensionen) und Merkmalsausprägungen (Elemente, Ebenen, Hierarchiezweige etc.) nach derselben Regel aggregiert werden dürfen, nicht gilt, sondern Ausnahmen und Detaildefinitionen existieren? Glücklicherweise hat MSAS auch hierfür einige Trümpfe zu bieten, die der versierte OLAP-Modellierer im Bedarfsfall aus dem Ärmel schütteln kann:

1) Elementweise Aggregation

Mit unären Operatoren (Unary Operators) kann in Parent-Child-Dimensionen, die zur stücklistenartigen Abbildung asymmetrischer Strukturen (d.h. „schiefe“ Hierarchien mit unterschiedlicher Merkmalstiefe pro Zweig) eingesetzt werden, pro Dimensionselement festgelegt werden, ob auf dem Weg vom Child zum Parent (also aufwärts innerhalb der betroffenen Hierarchie) addiert, subtrahiert, multipliziert, dividiert, quotal oder gar nicht aggregiert werden soll. Die Definition erfolgt mit Hilfe eines zusätzlichen Attributs, das für jedes Dimensionselement den unären Operator speichert.

Das Konzept der CustomRollups ist auch in regulären (symmetrischen) Dimensionen verfügbar und erlaubt die Verwendung individueller Rechenregeln, die elementweise in Form von MDX-Ausdrücken definiert werden. Wichtiger Unterschied zur Logik der unären Operatoren ist, dass die CustomRollupFormula für die Berechnung des Elements selbst gilt, nicht für dessen Aggregation auf die nächsthöhere Stufe. Analog zu oben wird auch hier die Rechenregel pro Dimensionselement durch ein separates Attribut festgelegt, in dem der gewünschte MDX-Ausdruck gespeichert wird.

Details zu beiden Funktionalitäten beschreibt der Blogbeitrag „2011-02-04 MAN UnaryOperators und CustomRollupOperators”.

2) Individuelles Überschreiben

Für einzelne Elemente, Ebenen, Hierarchiezweige oder Elementkombinationen (Zelladressen/Tupel) können mit Hilfe des MDX-Scope-Befehls im Cubeskript individuelle Rechenregeln spezifiziert werden. Die allgemeine Logik bzw. Syntax lautet bei Scope:

SCOPE (<Gültigkeitsbereich>);
THIS = ;
END SCOPE;

So könnte im eingangs skizzierten Vertriebsszenario innerhalb des Cubes der Umsatz ermittelt werden, indem pro Monat, Produkt und Kunde der Absatz mit dem gültigen Preis multipliziert wird. Dazu sind im Scope-Header die gewünschten Elemente/Dimensionen sowie die betroffene „Zielmeasure“ anzugeben und im Body die eigentliche Multiplikationsformel:

SCOPE	(	[Kunde].[Kunde].Members,
[Produkt].[Produkt].Members,
[Measures].[Umsatz]
); 
THIS = [Measures].[Absatz] * [Measures].[Preis]; 
END SCOPE;

Genial ist dabei, dass MSAS „oberhalb“ des Gültigkeitsbereichs mit den berechneten Zwischenergebnissen automatisch wie gewohnt, d.h. nach dem Aggregator der Zielmeasure, weiterrechnet. Wichtig: Dies gilt und funktioniert nur, sofern die Zielmeasure im Cube physisch definiert ist, also nicht nur vorher im Cubeskript per Create Measure virtuell erzeugt wurde.

Dieses im Kern einfache, aber überaus flexible Konzept bietet eine Vielzahl von Anwendungsmöglichkeiten. Abschließend sei jedoch erwähnt, dass es nur aufgrund seiner Mächtigkeit nicht unüberlegt über Gebühr genutzt werden sollte, da seine Verwendung die der OLAP-Technologie inhärente Logik „zuerst aggregieren, dann Formeln berechnen“ unterbindet und damit mitunter zu massiven Performanceeinbußen führen kann. In vielen Fällen ist eine Abbildung der gewünschten Logik in vorgelagerten Prozessstufen empfehlenswert: Das obige Beispiel dient explizit nur der Veranschaulichung der Grundlogik. Effizienter wäre hier die datensatzweise Multiplikation von Absatz und Preis schon in der relationalen Faktentabelle. Damit könnten Absatz und Umsatz als additive Flussgrößen mit dem Default-Operator Sum definiert und anschließend der durchschnittliche Stückpreis im Cubeskript per Division zurückgerechnet werden – womit sich der Kreis schließt…

Und nun viel Spaß und Erfolg bei der Aggregation!