In diesem Blogbeitrag betrachten wir mehrere Varianten, um ein Datenset zwischen Kontenmodell und Kennzahlenmodell zu transformieren bzw. zu pivotieren/unpivotieren. Dafür veranschaulichen und erläutern wir zunächst die Begriffe des Kontomodells bzw. Kennzahlenmodells und zeigen im Anschluss die Möglichkeiten zur Transformation, die SAP HANA bietet.
Der geneigte SAP Business Warehouse (BW) User wird mit den Begriffen Kontenmodell und Kennzahlenmodell bereits vertraut sein.
Account Model
Die obige Tabelle visualisiert eine Datenmodellierung nach dem Kontenmodell. Diese enthält eine Spalte mit der Bezeichnung der Kennzahl und eine zweite Spalte mit dem entsprechenden Wert. Unabhängig von der Anzahl der Kennzahlen werden hier nur zwei Spalten verwendet.
Key Figure Model
Die obige Tabelle zeigt dagegen das Kennzahlenmodell.
So ist für jede Kennzahl eine eigene Spalte modelliert. Dabei ist die Bezeichnung der Kennzahl entsprechend auch die Bezeichnung der Spalte und die Ausprägungen der Spalte enthalten die jeweiligen Werte der Kennzahl.
Falls für die Kennzahl eines Datensatzes keine Ausprägung vorhanden ist, wird diese mit einem Standardwert, z.B. null oder 0, gefüllt.
Es ergeben sich eine Reihe an Vor- und Nachteilen:
- Im Kontenmodell werden keine unnötigen Werte gesichert (null bzw. 0 im Kennzahlenmodell)
- Im Kontenmodell kann eine neue Kennzahl modelliert werden ohne das Datenmodell zu verändern
- Das Kennzahlenmodell erleichtert die Analyse und wird daher im Frontend/Reporting vorzugsweise verwendet - daher muss u. U. eine Transformation von Kontenmodell zu Kennzahlenmodell im Backend erfolgen
- Für reihenbasierte Tabellen ist die Performance des Kontenmodells stärker
Historisch gesehen war die Nutzung beider dieser Modelle essenziell, da eine Modellierung nach dem Kontenmodell eine bessere Performance des BW Systems möglich machte, das Kennzahlenmodell jedoch für das Reporting essenziell war (und weiterhin ist). Mit der Einführung der spaltenbasierten Architektur der Tabellen in der SAP HANA Datenbank ist die Nutzung des Kontenmodells nicht mehr performancerelevant, jedoch unter Umständen aus architektonischen Gründen noch vertreten.
Performance-Boost dank SQLScript -
Whitepaper jetzt herunterladen!
Von Konten- zu Kennzahlenmodell (Pivot)
Nun betrachten wir ein Szenario, bei dem wir im HANA Kontext ein Datenmodell nach dem Kontenprinzip in das Kennzahlenmodell transformieren möchten. Architektonisch haben wir die Wahl, diese Transformation entweder grafisch in einer Calculation View zu ermöglichen oder als Teil eines scriptbasierten HANA Objekts, z. B. einer Table Function, mittels SQLScript zu implementieren.
Im SQLScript basierten Ansatz wird dabei für jede zu modellierende Kennzahl ein CASE-WHEN Statement verwendet:
-- Account Model to Key Figure Model
SELECT
ID,
SUM (CASE
WHEN "FIELD" = 'SALES'
THEN "VALUE"
ELSE 0
END) as "SALES",
SUM (CASE
WHEN "FIELD" = 'QUANTITY'
THEN "VALUE"
ELSE 0
END) as "QUANTITY",
SUM (CASE
WHEN "FIELD" = 'ORDERS'
THEN "VALUE"
ELSE 0
END) as "ORDERS"
FROM
"IRN"."ACCOUNTMODEL"
GROUP BY
ID
ORDER BY
ID;
Da hier abgesehen vom CASE-WHEN Gerüst keine außergewöhnliche SQL Logik nötig ist, lässt sich dieselbe Funktionalität auch mittels einer Calculation View implementieren. Dafür werden Calculated Columns verwendet. Jedes CASE-WHEN Statement entspricht dabei äquivalent einer Calculated Column:
Pivot with Calculation View
Von Kennzahlen- zu Kontenmodell (Unpivot)
Auch bei der Transformation von Kennzahlen- zum Kontenmodell ist eine Lösung sowohl mittels Calculation View als auch via SQLScript möglich.
Für die grafische Lösung wird zunächst für jede Kennzahl eine Projection Node angelegt und anschließend mit einer Union Node verbunden. Danach bearbeiten wir die Union Node, indem wir:
- Alle bereits u. U. automatisch eingetragenen Mappings der Kennzahlen entfernen.
- Über das “+” Zeichen im “Target” Bereich der Union Node zwei custom Zielfelder erstellen - eins für die Feldbezeichnungen und eins für die Feldwerte.
- Über Rechtsklick auf das custom Feld für Feldbezeichnungen => Manage Mappings die Projection Nodes mit jeweils einer Constant Value auf das Feld für die Feldbezeichnungen mappen.
- Jeweils eine Kennzahl aus jeder Projection Node auf das Feld für die Feldwerte mappen (entsprechend der vorher definierten Constant Value zu Projection Node Paare).
Unpivot with Calculation View
Analog lässt sich dieses Prinzip auch via SQLScript anwenden. So wird für jede Kennzahl ein Subselect geschrieben und diese Subselects werden jeweils mittels UNION ALL verbunden:
-- UNION ALL: Key Figure Model to Account Model
SELECT
ID, 'SALES' as "FIELD", "SALES" as "VALUE"
FROM "IRN"."KPIMODEL"
UNION ALL
SELECT
ID, 'QUANTITY' as "FIELD", "QUANTITY" as "VALUE"
FROM "IRN"."KPIMODEL"
UNION ALL
SELECT
ID, 'ORDERS' as "FIELD", "ORDERS" as "VALUE"
FROM "IRN"."KPIMODEL";
Alternativ bietet HANA auch die Möglichkeit der Kombination von zwei integrierten Funktionen - MAP() und SERIES_GENERATE_INTEGER(). Die letztere generiert automatisch eine Tabelle, die eine Zahlenreihe enthält - für uns ist die generierte Spalte “ELEMENT_NUMBER” interessant. Die MAP() Funktion ermöglicht es, eine Reihe an Ausgangswerten jeweils einem Zielwert zuzuordnen. Wir generieren also eine Zahlenreihe mittels SERIES_GENERATE_INTEGER(), die der Anzahl unserer Kennzahlen entspricht und formen einen Cross Join mit der Tabelle, die unser kennzahlenbasiertes Datenmodell enthält. Anschließend nutzen wir MAP(), um die zwei Felder für die Feldbezeichnung und den Feldwert im SELECT korrekt zu befüllen:
-- MAP(): Key Figure Model to Account Model
SELECT
ID,
MAP(element_number,
1, 'SALES',
2, 'QUANTITY',
3, 'ORDERS')
as "FIELD",
MAP(element_number,
1, "SALES",
2, "QUANTITY",
3, "ORDERS")
as "VALUE"
FROM "IRN"."KPIMODEL"
CROSS JOIN SERIES_GENERATE_INTEGER(1,1,4);
Haben Sie Fragen zu diesem oder anderen Themen? Versuchen Sie das nötige Know-How in Ihrer Abteilung aufzubauen oder benötigen Sie Unterstützung bei einer konkreten Fragestellung? Wir helfen Ihnen gerne dabei. Fordern Sie noch heute ein unverbindliches Beratungsangebot an.
