Benutzerspezifische Werkzeuge

Pivot-Tabellen-Berichtsgenerator

Pivot-Tabellen erlauben, umfangreiche Datenmengen zu filtern und zu verdichten, um sie aus unterschiedlichen Perspektiven auszuwerten (Daten-Pivot) und die Ergebnisse mit Pivot-Charts zu visualisieren. Ein Pivot-Tabellen-Berichtsgenerator ermöglicht Business Analytics ohne Unterstützung durch eine Zentrale IT (Self-Service Business Intelligence). Die Daten-Eingabe erfolgt über flache Tabellen. Das Schema gleicht einer SQL-View per Star Join im Stern-Schema der Multidimensionalen Datenmodellierung.

Beim Daten-Pivot wählt der Benutzer Attribute (Spalten) der flachen Tabelle, um sie der Zeilen- und Spaltendimension der Pivot-Tabelle, dem Wertfeld oder Berichtsfiltern zuzuordnen. Eine Dimensionsreduktion bedingt eine Datenverdichtung, wobei der Benutzer zwischen diversen Aggregat-Funktionen und Ergebnisdarstellungen wählen kann. Selektive Zeilen- und Spaltenfilter, Berichtsfilter und Hierarchiefilter unterstützen ein Slice & Dice, Drill Down / Roll Up und an variierende Differenzierungsgrade angepasste Ergebnisdarstellungen. Pivot-Tabellen-Berichtsgenerierung und Daten-Pivot werden anhand einer Fallstudie mit dem Excel Add In „Power Pivot“ illustriert.

Tabellarisches Modell

Das Schema der Input-Daten definiert das Tabellarische Modell. Die Berichtsgenerierung basiert auf folgendem Modell:

 Pivot RFS

Die Relation RFS definiert eine flache Tabelle (ohne Fremdschlüssel). Flache Tabellen lassen sich spaltenweise und komprimiert im Columnstore Index speichern.

Das Tabellarische Modell erlaubt per Star Join einen Import von im Multidimensionalen Datenmodell (Stern-Schema) definierten Würfeln, Dimensionen und Hierarchien. Fakten unterschiedlicher Dimension führen zu undefinierten Ausprägungen von Dimensionsattributen (NULL). Der Pivot-Tabellen-Berichtsgenerator erkennt NULL-Felder. Ihre Instanzen lassen sich in der Pivot-Tabelle durch Filter unterdrücken.

Fallstudie

Die Landeshauptstadt Kiel (Schleswig-Holstein) gliedert sich in 30 Stadtteile [Kiel 2017] bzw. 18 Bezirke der Ortsbeiräte [Kiel 2018], wobei sich der Stadtteil Wik in 2 Ortsteile untergliedert. In Anlehnung an die räumliche Gliederung der FHH Hamburg [vgl. RäumGlG 2006] wird die Hierarchie [Anzahl Ausprägungen in Klammern] Ortsteil [2], Stadtteil [30], Bezirk [17], Gesamt [1] unterstellt, jedoch nicht nach Ortsteilen differenziert. Die 17 Bezirke werden 6 Bereichen untergeordnet.

 Stadtteile der Landeshauptstadt Kiel

 Abb. 1: Stadtteile der Landeshauptstadt Kiel [Kiel 2017, S. 3]

Der Stadtplan in Abbildung 1 lässt eine variierende Flächennutzung erkennen. Neben der Einwohnerdichte werden deshalb auch die Siedlungsdichte ermittelt und die 6 Flächennutzungsarten im Statistischen Bericht Nr. 254 [Kiel 2017] den Flächenklassen (1) Siedlung, (2) Nutzfläche und (3) Erholung untergeordnet. Die Einwohnerstatistik differenziert nach (1.) Stadtteil, (2.) Geschlecht (männlich, weiblich) und (3.) hinsichtlich der Demographie nach Zugehörigkeit zu den Soziogruppen Ausländer sowie Deutsche ohne und mit Migrationshintergrund (D [ohne|mit] Mh) [Kiel 2017, S. 4-6].

Im Multidimensionalen Datenmodell mit den Dimensionen Geographie, Demographie, Geschlecht und Fläche bilden die Einwohnerzahlen einen 3-dimensionalen Würfel „Fakt1“ und die Flächen einen 2-dimensionalen Würfel „Fakt2“. Tabelle 1 zeigt einen Ausschnitt der flachen Tabelle zur Fallstudie mit der um das Attribut Stadtteilnummer erweiterten Attributzeile [$A$6:$H$6] und den Daten-Zeilen [$A$7:$J$366]; NULL-Felder sind „leer“.

 Kopf- und Inputdaten

Tab. 1: Kopf und Input-Daten (Auszug) der flachen Tabelle zum Fallbeispiel

Pivot-Tabellen-Generierung

Aus einem flachen Schema FS entsteht eine Pivot-Tabelle, indem der Zeilen- und Spaltendimension sowie Berichtsfiltern Dimensionsattribute und dem Wertfeld ein oder mehrere Fakt-Attribute zugewiesen werden. Durch Zuweisung mehrerer Dimensionsattribute zur Zeilen- oder Spaltendimension erhält die Pivot-Tabelle eine hierarchische Struktur, bei Zuweisung mehrerer Fakt-Attribute zum Wertfeld eine spaltenweise Parametrisierung. Die Zeilen- und Spaltenindizierung definiert die Gruppen der Aggregation. Datenaggregation und Berichtsformatierung können durch Filter beeinflusst werden:

  1. Zeilen- und Spaltenfilter: Durch Ausblenden von Instanzen (inkl. „Leer“) eines Zeilen- oder Spaltenattributes wird die Pivot-Tabelle auf einen Subwürfel (Dice) reduziert.
  2. Berichtsfilter: Ein Berichtsfilter definiert einen Datenschnitt (Slice). Er kann auf ALLE (ohne Filter) oder auf spezifische Attributausprägungen (inkl. „Leer“) gesetzt werden.
  3. Hierarchiefilter: In einer hierarchischen Zeilen- oder Spaltendimension können, um die Granularität der Berichtswerte dem Differenzierungsbedarf anzupassen, die einem Aggregat-Knoten untergeordneten Instanzen ausgeblendet werden.

Berichts-, Zeilen- und Spaltenfilter werden in Pull-Down-Menüs gesetzt; sie zeigen die Ausprägungen eines Attributs sowie, falls in der Attributspalte undefinierte Felder existieren, die Ausprägung „Leer“ (NULL) an. Ein Berichtsfilter, der ein oder mehrere Klassifikationsknoten einer Hierarchieebene spezifiziert, unterstützt ein Drill Down, indem er eine Dimension auf die darunter liegenden Klassifikationsstufen (Teilhierarchien) beschränkt.

Bei den Wertfeldeinstellungen unterscheidet man zwischen Aggregat-Funktionen (ANZAHL, SUMME, MITTELWERT etc.) [s. Date 1997] und Wertfeldanzeige, i.e. Darstellung der Wertfeldangaben (absolut, in % der Zeilen- bzw. Spaltensumme, in % der Wertfeldsumme / Fallzahl etc.). Sofern semantisch korrekt, werden in Spalte Gesamt die Zeilen-Summen, in Zeile Gesamt die Spalten-Summen und in Feld „Gesamt von Gesamt“ die Wertfeld-Summe ausgewiesen. Deshalb erübrigt sich eine Modellierung der Hierarchieebene Alle. Bei hierarchisch strukturierten Zeilen oder Spalten werden ebenso die Aggregatwerte der untergeordneten Hierarchiestufen ausgewiesen.

Abbildung 2a zeigt den Beginn des Dialogs mit dem Pivot-Tabellen-Berichtsgenerator, um die Tabelle in Abbildung 2b zu generieren, und im rechten Teil die zugewiesenen Attribute sowie aktivierte Filter. Abbildung 2b dokumentiert die Filter zusätzlich in Feld [$D$3:$G$3]. Zur Ermittlung der Einwohnerzahlen und Siedlungsflächen wird der Berichtsfilter Fl-Klasse auf Leer und Siedlung beschränkt. Da bei Suchsdorf und Wik Bezirk und Stadtteil übereinstimmen, werden die Stadtteilangaben ausgeblendet und mit „+“ ein Hierarchiefilter angezeigt.

PG1  Pivot Berichtsgenerierung

Abb. 2a: Beginn und Ergebnis einer Pivot-Tabellen-Berichtsgenerierung (Beispiel)

 

 Pivot-Tabelle

Abb. 2b: Pivot-Tabelle mit Zuordnung von Stadtteilen über Bezirke zu Bereichen

Daten-Pivot

Beim Daten-Pivot werden Feldzuweisungen und/oder Filtereinstellungen geändert. Es setzt entweder erneut auf der Inputtabelle auf oder geht von einer erstellten Pivot-Tabelle aus (Evolutionäres Prototyping). Folgende 4 Fälle illustrieren das Daten-Pivot:

(1) Flache Pivot-Tabelle, Hierarchiefilter

In einer flachen Pivot-Tabelle werden die Spalten durch Fakten (Wertattribute) definiert. Abbildung 3 verdichtet und vereint auf Basis der hierarchischen Dimension Geographie die Würfel Einwohnerzahl und Fläche. Abgesehen vom Bereich Zentrum, werden jene Stadtteile selektiert, deren Einwohnerdichte die mittlere Einwohnerdichte von Kiel um 50% oder mehr übersteigt (rot hervorgehoben). Sind Bezirk und Stadtteil identisch, wird der Bezirk nicht untergliedert (rot und Fettdruck). Feld [$D$2:$E26] zeigt die für die Selektion notwendigen Berechnungen an.

 Pivot Ergebnistabelle

Abb. 3: Flache Ergebnistabelle der Selektion von Stadtteilen mit hoher Einwohnerdichte

(2) Slice mit Hierarchiefiltern, Pivot-Chart

Ein Slice des Berichtsfilters bewirkt einen oder mehrere Schnitte durch einen Würfel. In Abbildung 4 beschränkt er die Dimension Geographie auf die Bereiche Außenförde und Nord, um dort die Flächennutzung zu analysieren. Die Hierarchisierung der Spaltendimension erlaubt bei den Nutzungsflächen eine stärkere Differenzierung. Die Pivot-Tabelle in Abbildung 4a zeigt unter der Wertfeld-Einstellung in % der Zeilensumme die Anteile der Flächennutzungsarten. Im Pivot-Chart in Abbildung 4b spiegeln die Beschriftung der horizontalen Achse den Hierarchiefilter der Zeilendimension und die Legende zum Balkendiagramm den Hierarchiefilter der Spaltendimension wider.

 Pivot Flächenklassen

Abb. 4a: Anteile der Flächenklassen und Nutzflächenanteile

 Pivot Chart

Abb. 4b: Pivot-Chart zur Pivot-Tabelle in Abbildung 4a

(3) Varianzanalysen, alternative Wertfeld-Darstellungen

Die Pivot-Tabellen in Abbildung 5 analysieren mit den Wertfeld-Einstellungen

  1. in % der Zeilensumme die Bevölkerungsstrukturen innerhalb der Organisationseinheiten (Bereichs-, Bezirks- bzw. Stadtteilsoziogramm)
  2. in % der Spaltensumme die Verteilung der den Soziogruppen angehörenden Einwohner auf die Organisationseinheiten (Soziogruppenverteilungen über Bereiche, Bezirke, Stadtteile.

Hierarchiefilter in der Dimension Geographie erlauben einen variablen Differenzierungsgrad. Der Spaltenfilter unterdrückt NULL-Spalten. Der Berichtsfilter erlaubt geschlechtsspezifische Analysen.

 Zeilen-Soziogramm

Abb. 5: Zeilen-Soziogramm (oben), spaltenweise Soziogruppenverteilungen (unten)

(4) Drill Down, Referenzwerte

Ein Drill Down dient der Verfeinerung von Analysen. Abbildung 6 zeigt ein Drill Down auf die in Abbildung 5 mit roten Wertangaben markierten signifikanten Abweichungen mit zusätzlicher Geschlechtsspezifizierung. Gegenüber den Absolut-Werten in der Pivot-Tabelle liefert das gestapelte Balkendiagramm im Pivot-Chart die Soziogramme der gemäß Zeilenfilter selektierten Bezirke in %-Angaben (Düsternbrook zum Vergleich).

Pivot Drill Down 1

Pivot Drill Down 2

Abb. 6: Drill Down auf Bezirke mit geschlechtsspezfischer Differenzierung in der Spaltendimension

Schlussbemerkungen

Mit wachsendem BI-Reifegrad erheben Benutzer den Anspruch, Daten selbst, d.h. unabhängig von der Zentralen IT, zu erschließen und individuelle Analysen und Reports zu erstellen. Sie erwarten, dass sie bei Verfolgung ihrer Explorationsziele von der BI-Infrastruktur optimal unterstützt werden. Dies schließt eine Anpassung der Anforderungsdefinition und Untersuchungsziele ein.

Self-Service Business Intelligence (SSBI) erfordert Erfahrung im Umgang mit Daten, Business Analytics und BI-Services. Die Spezifikation der Analysen und Berichte sollte in einer BI-Server-Umgebung auf funktionalen Query-Definitionen in SQL-92 basieren [vgl. Date 1997, TPC-H-Benchmark 2017]. Für Analysen auf Basis des Multidimensionalen Datenmodells im Stern-Schema wurde die „MultiDimensional eXpression Language“ (MDX) eingeführt. Analysen im Tabellarischen Modell werden von der „Data Analysis eXpression Language“ (DAX) unterstützt; bei ihr tritt an die Stelle der WHERE-Klausel in SQL-92 die FILTER-Funktion.

Literatur

Angler, M.W.: Einstieg in Business Intelligence mit Sharepoint 2010. Microsoft Press, Heidelberg 12/2011.

Date, C.J.: A Guide to the SQL standard. A users guide to the standard database language SQL, 4th Ed., Addison Wesley, USA 1997.

Jelen, B.; Alexander, M.: Excel 2016 Pivot Table Data Crunching. Indianapolis, Indiana 46240 USA, 2016.

Landeshauptstadt Kiel (Hrsg.): Die Kieler Stadtteile. Statistischer Bericht Nr. 254, Kiel 2017. https://www.kiel.de/de/kiel_zukunft/statistik_kieler_zahlen/_kleinraeumige_berichte/Statistischer_Bericht_Nr._254_-_Kieler_Stadtteile_2016.pdf (Abruf 15.01.2019)

Landeshauptstadt Kiel (Hrsg.): Ortsteildaten 2017. Kiel 2018. https://www.kiel.de/de/gesundheit_soziales/sozialplanung_berichte_konferenzen/sozialbericht/_dokumente_ortsteile/2017/Daten_Ortsteile_insgesamt.pdf (Abruf 15.01.2019)

RäumGlG: Gesetz über die räumliche Gliederung der Freien und Hansestadt Hamburg. Hamburg, 6.07.2006.

Transaction Processing Performance Council (TPC) Ed.: TPC BenchmarkTM H (Decision Support) Standard Specification, Revision 2.17.3. San Francisco, CA 94129-0920, 2017.

Warren, N.; Campbell, J.; Misner, St.; Neto, M.: PowerPivot for Excel and Sharepoint. Microsoft Press, Heidelberg, 04/

Zuletzt bearbeitet: 27.02.2019 14:56
Letzter Abruf: 18.07.2019 06:42
Artikelaktionen