Nicht nur für Plan- und Ist-Werte gilt: Zahlen aus Geschäftsprozessen, die eigentlich inhaltlich zusammengehören, können auf mehrere Fakttabellen verteilt sein. Dafür stellen wir in DeltaMaster einen nützlichen Kennzahltyp bereit: die Vereinigungswerte. Sie erleichtern die integrierende Berichterstellung.
Der Analysewerttyp “Vereinigungswerte” ist in unserer Business-Intelligence-Software DeltaMaster bereits mit dem Release 6.3.1.1 erschienen, hat aber bisher in der Forschungsblog-Reihe – unverdientermaßen! – noch keine Erwähnung gefunden. Außerdem gibt es im kommenden Release 6.3.6 (Februar 2021) Neuerungen, die die Vereinigungswerte betreffen.
Vereinigungswerte existieren nur für relationale Anwendungen und sollen gewisse Konzepte, die aus der multidimensionalen Modellierung bekannt sind, auch für relationale Anwendungen bereitstellen. Falls eine bestimmte Kennzahl in mehreren Fakttabellen (manche sagen auch Faktentabellen, engl. fact tables) auftaucht, können wir in DeltaMaster über einen Vereinigungswert einen nach außen hin sichtbaren “Stellvertreter” für alle Ausprägungen dieser Kennzahl definieren, der – je nach Kontext – intern die passenden Kennzahlwerte zuordnen wird. Normalerweise ist es in relationalen Anwendungen nämlich nicht so ohne weiteres möglich, Kennzahlen mit gleicher inhaltlicher Bedeutung aus verschiedenen Fakttabellen in einem Bericht vernünftig darzustellen. Mit dem Konzept des Vereinigungswertes durchbrechen wir diese Schranke.
Beispielszenario
Schraubenhersteller
Aber wie sehen Konstellationen aus, bei denen Vereinigungswerte einen Nutzen stiften – und auch eingesetzt werden dürfen? Schauen wir dazu auf ein einfaches Beispiel. Hier haben wir es mit einem Schraubenhersteller zu tun, der der Einfachheit halber nur zwei Produkte produziert (Holz- und Metallschrauben). Diese vertreibt er in größeren Liefereinheiten, in denen die Absatzzahlen gemessen werden, an Baumärkte. In Wirklichkeit sind es natürlich Hunderte von Kunden, aber wir nehmen hier wieder nur zwei Baumärkte an, um auch die Detailzahlen vollständig anzeigen zu können.
Ist-Absatzzahlen im Jahr 2020 nach Monaten, Produkten und Kunden in einer Grafischen Tabelle
Die fiktiven Daten zu diesem Beispiel liegen als Tabellenblätter in Excel vor. Ich bin hier mit dem Selfservice SQL für Excel unterwegs, der unter der Haube mit SQLite arbeitet. Genauso gut könnten Sie aber auch beispielsweise Microsoft SQL Server oder etwa SAP HANA einsetzen. Die Datenbank muss Common Table Expressions (CTE) unterstützen.
Sollte Ihre relationale Datenbank CTE und somit Vereinigungswerte wider Erwarten nicht unterstützen, bieten wir die Option zum Anlegen erst gar nicht an. Das gilt natürlich auch, wenn Sie nur eine einzige Fakttabelle verwenden – dann gibt es nichts zu vereinigen.
Eine der Hauptanwendungen für Vereinigungswerte ist in der Planung zu finden: Planwerte unterscheiden sich normalerweise in der Granularität von den Istwerten und werden deshalb in einer eigenen Fakttabelle gespeichert. Unser Schraubenhersteller plant jahresweise Absätze nur nach Produkttyp, aber nicht nach Kunden. Er kommt deshalb mit einer sehr kurzen Plan-Fakttabelle aus – dazu später mehr.
Wir beginnen aber mit einem einfacheren Fall, bei dem die eingesetzten Fakttabellen strukturell identisch aufgebaut sind.
Horizontale Partitionierung
Der Schraubenhersteller hat sich – vielleicht aus historischen Gründen – angewöhnt, die Ist-Absatzzahlen für jedes Jahr in einer eigenen Fakttabelle abzuspeichern. Eine solche horizontale Partitionierung der eigentlich strukturell homogenen Daten trifft man häufiger an. Hier existiert ein diskriminierendes Kriterium in Gestalt des Jahres, das für die Zuordnung zu den Fakttabellen verantwortlich ist.
Ein solches Kriterium muss übrigens nicht zwingend vorliegen, um Vereinigungswerte anwenden zu können. Auch eine sehr lange Fakttabelle könnte beispielsweise auf kürzere Fakttabellen aufgeteilt werden, ohne dass ein aufteilendes Kriterium vorhanden sein muss. Hier müsste aber zusätzlich angenommen werden, dass der Aggregationstyp der Kennzahl durch die Summe gegeben ist.
Schaut man per SQL-Durchgriff auf die zugehörige Ist-Fakttabelle des Jahres 2020, fängt sie folgendermaßen an:
Die Fakttabelle zu den Ist-Absatzzahlen im Jahr 2020
Die Ist-Absatzzahlen liegen hier monatlich vor, zusätzlich gibt es noch die Kennzahlen Umsatz und Rabatt. Alle 48 (2x2x12) auftretenden Absatzzahlen sind auch in der Grafischen Tabelle zu sehen.
Struktur des einfachen Modells
Die vorhandenen Fremdschlüssel-IDs für Kunden, Produkt, Zeit und Wertart in der Fakttabelle haben wir mit den passenden Primärschlüsseln der Dimensionstabellen verknüpft. Lassen wir zunächst einmal die Planzahlen außen vor, so sieht das in der Struktur-Ansicht folgendermaßen aus:
Fakttabelle nur für 2020 und Dimensionstabellen in der Struktur-Ansicht
Die Dimensionstabellen enthalten zu jeder ID noch eine verständliche Bezeichnung, die wir als Elementeigenschaft modellieren.
Mehrere Fakttabellen mit identischer Struktur
Nehmen wir nun an, dass wir die Absatzzahlen mit denen des Vorjahres vergleichen wollen. Diese liegen aber – wie bereits erwähnt – in einer weiteren Fakttabelle vor. Diese besitzt die gleiche Granularität, d. h., der Aufbau der Tabelle bezüglich der verwendeten Dimensionen und Kennzahlen ist absolut identisch, nur stammen die Zahlen nun aus 2019.
Binden wir diese analog zur ersten Fakttabelle an und erweitern die Dimensionstabelle der Zeit um die Vorjahresmonate, ergibt sich das folgende Bild in der Struktur-Ansicht:
Mehrere Fakttabellen für 2019 und 2020 und Dimensionstabellen in der Struktur-Ansicht
Deklarieren wir die Kennzahlen als Analysewerte, so gäbe es eigentlich zunächst zwei Kennzahlen mit gleichlautenden Namen “Absatz”, die wir hier aber zur besseren Unterscheidung in “Absatz Ist” und “Absatz Ist 2019” umbenannt haben.
Nun ist es möglich, eine Grafische Tabelle analog zur oben gegebenen für 2020 auch für 2019 anzulegen, wobei wir mit der Kennzahl “Absatz Ist 2019” arbeiten:
Ist-Absatzzahlen im Jahr 2019 nach Monaten, Produkten und Kunden in einer Grafischen Tabelle
Es wird uns aber (noch) nicht so richtig gelingen, Werte aus 2019 und 2020 in einem Bericht darzustellen, da sie ja aus verschiedenen Fakttabellen stammen. Jetzt kommt der Vereinigungswert ins Spiel!
Anlegen eines Vereinigungswertes
Wir legen einen neuen Vereinigungswert “Absatz” an:
Der Menüpunkt Vereinigungswerte erscheint nur, wenn es etwas zu vereinigen gibt
“Absatz” soll sich natürlich auf die beiden Basisanalysewerte “Absatz Ist” und “Absatz Ist 2019” beziehen:
Absatz speist sich nun aus mehreren Fakttabellen
Hier haben wir mit dem Release 6.3.6 die Menge der erlaubten Typen für Basisanalysewerte erweitert. Neben Typ Simple sind jetzt auch eigens in DeltaMaster angelegte Analysewerte wie z. B. Quotientenwerte möglich. Vereinigungswerte selbst (dies gilt auch für Filterwerte) dürfen aber nicht als Basisanalysewerte verwendet werden.
Wir sehen nun auch, dass “Absatz” den Typ “Union” erhält. Außerdem ist ersichtlich, dass der Vereinigungswert nicht mehr einer einzigen, festen Fakttabelle zugeordnet ist.
Mögliche Zuordnungen zu vorhandenen Fakttabellen entstehen implizit durch die zugewiesenen Basisanalysewerte. Wird nun ein Vereinigungswert in einem Bericht verwendet, geht DeltaMaster die Basisanalysewerte, bzw. die zugehörigen Fakttabellen durch. Es wird geschaut, ob der aktive Filter im Bericht zu Treffern innerhalb der Fakttabelle führt. DeltaMaster addiert die Teilergebnisse schließlich zum angezeigten Wert auf.
Hier in unserem Beispiel steuert bei einem einzigen ausgewählten Element der Zeit – wie etwa August 2020 oder Jahr 2019 – immer maximal eine Fakttabelle effektiv einen Wert bei. Nur wenn das All-Element der Zeit aktiv ist, tragen im Normalfall beide Fakttabellen zum ausgegebenen Absatzwert bei.
Möchte man übrigens zusätzlich wissen, welche Fakttabelle wie viel beiträgt, muss eine entsprechende diskriminierende Variable explizit in jeder Fakttabelle vorliegen. Notfalls kann pro Fakttabelle eine SQL-Abfrage definiert werden, die diese Variable hinzufügt.
Bei unserem Beispiel erlaubt die Zeit in Form der Spalte “MonatID” bereits die eindeutige Zuordnung.
Grafische Tabelle mit Magischen Knöpfen
Jetzt haben wir die Möglichkeit, Vorjahresvergleiche für den Absatz im Dezember 2020 durchzuführen. Die Magischen Knöpfe funktionieren im Editiermodus nun wie gewohnt und auch die Sparklines-Historie steht zur Verfügung und reicht hier in das Vorjahr hinein:
Ein Vorjahresvergleich über mehrere Fakttabellen hinweg
Der Absatz von Holzschrauben an Baumarkt B sieht im Dezember 2020 im Vergleich zum Vorjahr etwas mau aus!
Mehrere Fakttabellen: Plan vs. Ist
Unterschiedliche Granularität
Schauen wir nun einmal auf eine weitere, bereits angedeutete Anwendung: Wir möchten für das Jahr 2020 Plan- mit den Istzahlen vergleichen. Die Fakttabelle der Planung ist in unserem Beispiel sehr übersichtlich. Im SQL-Durchgriff sehen wir den folgenden Zweizeiler:
Geplante Absatzzahlen 2020
Im Unterschied zu den Ist-Zahlen, die monatlich für Produkt und Kunde vorliegen und auch Umsatz und Rabatt beinhalten, wurden in der Planung nur Gesamtjahreswerte für den Absatz der Produkte geplant, d. h., in unserem einfachen Szenario hat sich die Absatzplanung mit zwei Zahlen erledigt. Wir schauen uns einmal die Anbindung in der Struktur-Ansicht an:
Anbindung der Fakttabelle Plan Produkt an die Periodendimension
Hier ist übrigens eine Erweiterung aus dem Release 6.3.1.1 zu sehen: Die Ebene Jahr aus der Planung kann in diesem Star-Schema direkt mit der höheren Ebene Jahr der Periodendimension verbunden werden. Vorher war es notwendig, ein Snowflake-Schema einzusetzen, also in diesem Fall eine weitere Tabelle nur für die Jahresebene vorzuhalten.
Die Absatz-Kennzahl aus der Planung haben wir hier zu “Absatz Plan” umbenannt.
Istzahlen sind bei unserem Beispiel in jeder Hinsicht detaillierter als die Planzahlen. Es ist somit möglich, durch Aggregation der Istzahlen auf den gröberen Detaillierungsgrad der Planzahlen zu gelangen. Auch ohne Vereinigungswerte könnte man in einem Multiples-Bericht die Istzahlen der Produkte den zugehörigen Planzahlen immerhin in eigenen Fenstern gegenüberstellen:
Ist- und Planzahlen 2020 nach Produkten
Die Grafischen Tabellen “leben” in diesem Moment aber noch auf ihren jeweils zugeordneten Fakttabellen.
Bevor wir den passenden Vereinigungswert definieren, schauen wir noch auf ein Problem, das durch die unterschiedliche Struktur und Granularität entsteht.
Ignore Unrelated Dimensions
Die Eigenschaft “IgnoreUnrelatedDimensions” ist in der multidimensionalen Modellierung der Analysis-Services-Welt einer MeasureGroup zugeordnet. Es gibt einen bekannten kurzen Blogbeitrag von Benny Austin zu diesem Thema.
Wir benutzen die Logik dieses Konzepts auch in der relationalen Modellierung.
Es geht um die Frage, was mit einer Kennzahl passieren soll, wenn eine gewählte Dimensionsebene des Berichts für diese Kennzahl nicht angebunden ist oder die zugehörige Dimension in der Fakttabelle gar nicht auftaucht.
Dieses Problem der “Unrelated Dimensions” sehen wir am einfachsten, wenn wir einmal versuchen, die Planzahlen auf dem Detaillierungsgrad der Istzahlen (Monate, Kunden, Produkte) zu zeigen. Werden nur Zahlen aufgeführt, die tatsächlich abgeleitet werden können, sehen wir – wie der obere Teil des folgenden Multiples-Berichts zeigt – nichts!
Der Einfluss des Schalters “IgnoreUnrelatedDimensions”
In der Planung haben wir keine Werte für einzelne Kunden oder Monate hinterlegt, deshalb bleiben hier die Felder leer.
Im unteren Bericht mit “IgnoreUnrelatedDimensions=True” werden Elemente aus nicht zugeordneten Ebenen durch höhere, bekannte Elemente in der Hierachie ersetzt (sofern vorhanden!).
Deshalb wird ein Monat aus 2020 intern durch das ganze Jahr 2020 ersetzt und einzelne Kunden durch “Alle Kunden”. Das führt letztendlich dazu, dass wir die zwei Werte aus der Planung hier wiederholt sehen. Das irritiert! Bei einem additiven Wert wie dem Absatz bietet es sich an, die Option auszuschalten, dann werden nur existierende und ableitbare Summen gezeigt.
Der Schalter selbst befindet sich beginnend mit dem aktuell kommenden Release 6.3.6 auf dem Reiter “System” eines Analysewertes. Er ist nur für die Basisanalysewerte sichtbar, aber nicht für daraus ableitbare Analysewerte wie Vereinigungswerte oder auch Filterwerte.
IgnoreUnrelatedDimensions: Aktivieren oder deaktivieren?
Definition des Vereinigungswertes
Deaktivieren wir sicherheitshalber die Option “IgnoreUnrelatedDimensions” für die beiden Kennzahlen “Absatz Ist” und “Absatz Plan” und erstellen einen Vereinigungswert AbsatzP, der diese zwei Basisanalysewerte verwendet.
Nun lässt sich wieder eine Grafische Tabelle mit einer Plan-Ist-Abweichungsanalyse erstellen, die auf mehreren Faktentabellen basiert. Hier sind es die zwei Tabellen “Fakt Plan Produkt” und “Fakt Ist”:
Plan-Ist-Abweichung, aus 2 Fakttabellen gespeist.
Sollte es sinnvoll sein, Absatzzahlen von Holz- und Metallschrauben addieren zu können, da es sich um einheitliche Losgrößen handelt, können wir auch mit allen Schrauben starten und nach Produkten navigieren:
Auch eine Navigation ist prinzipiell möglich
Rabattquoten
Nehmen wir zum Schluss an, dass wir Rabattquoten (also Rabatt/Umsatz) – diesmal für Kunden – geplant haben, also somit zwei Werte festlegen:
Die geplanten Rabattquoten für die zwei Baumärkte
Wir binden die Tabelle wieder in unser Modell ein und ergänzen den Namen der Rabattquote mit dem Zusatz “Plan”. Auf der Fakttabelle Ist berechnen wir einen Quotientenwert “Rabattquote Ist” aus den Werten “Rabatt Ist” und “Umsatz Ist”.
Diesmal aktivieren wir die Option “IgnoreUnrelatedDimensions” bei der “Rabattquote Plan”, bei der “Rabattquote Ist” macht es keinen Unterschied, da sie über alle Dimensionen definiert ist. Wir legen mit diesen beiden Rabattquoten einen Vereinigungswert an.
Die Rabattquoten sind nicht additiv, aber da immer genau eine Fakttabelle über die Wertart aktiviert ist, werden sie wie erwartet funktionieren. Wenn also eine Dimensionsebene existiert, bei der ein einzelnes Element die zugehörige Fakttabelle eindeutig bestimmt, und diese Dimension – wie hier die Wertart – in den Berichten auf jeden Fall enthalten sein wird, verhält sich der Vereinigungswert eher wie ein Auswahlwert.
Gebe ich nun in einer Grafischen Tabelle einen Monat oder ein Produkt an, – für beide waren ja Werte in der Planung gar nicht definiert! -, wird de facto der geplante Wert des Jahres 2020 für den zugehörigen Kunden eingesetzt. Das konkrete Produkt wird für “Plan” intern durch “Alle Produkte” ersetzt:
Rabattquotenanalyse
Die Ist-Rabattquoten liegen ja sowieso vor und nun ist es uns möglich, Abweichungen zwischen geplanten und tatsächlichen Rabattquoten auch für einzelne Monate oder Produkte zu berechnen. Hier haben wir die Historie in Form von Säulen-Sparklines aktiviert, aber wir könnten auch Small Multiples einsetzen:
Rabattquotenanalyse als Small Multiples über die Monate
Die einzige Nebenbedingung, die hier erfüllt sein muss, ist, dass die Kundenebene in die Tabelle eingebunden wird. Auf dem All-Element aller Kunden ist die geplante Rabattquote nicht sauber definiert, da sie von den nicht geplanten Umsätzen bzw. Rabatten pro Kunde abhängen würde.
Fazit
Mit dem Vereinigungswert lassen sich somit Analysewerte mit prinzipiell gleicher Bedeutung aus mehreren Fakttabellen einfach zusammensetzen. Es gibt einige wenige Einschränkungen: Zum Beispiel können Berichte, die auf den Achsen Parent-Child-Hierarchien oder virtuelle Zeithierarchien einsetzen, nicht zusammen mit Vereinigungswerten verwendet werden.
Eine andere kleinere Einschränkung entsteht durch unverbundene Dimensionsebenen: Eine Element- oder Ebenenauswahl, die sich gleichzeitig sowohl auf verbundene als auch auf unverbundene Ebenen bezieht, ist bei Vereinigungswerten nicht erlaubt. Es ist hingegen möglich, sich aus einer verbundenen Auswahl auf unverbundene Ebenen hinunterzudrillen.
