Die meisten Auswertungen haben einen Zeitbezug. Beispielsweise möchten wir die Summe der Ausgaben für Bestellungen wissen – allerdings nicht die Gesamtsumme als eine Zahl, sondern den zeitlichen Verlauf. So kann man beispielsweise saisonalen Schwankungen auf die Spur kommen.
Beispiel:
In der Datenbank AdventureWorks finden wir Bestellungen in der Tabelle PurchaseOrderHeader: Wir benötigen zwei Felder: OrderDate (das Bestelldatum) und SubTotal (die Summe des Auftrags). Das Feld Monat ist berechnet:
Die Spalte Monat stellt damit das Datum monatsweise im Format yyyyMM dar. Erstellen wir anschließend aus den Daten ein Pivot-Chart als Kurve der Summe der Umsätze über die Zeit (OrderDate), nehmen die Spalte Monat als Datenschnitt (Slicer) und wählen als Monat im Slicer 200804, erhalten wir folgendes Ergebnis: Der x-Achsen-Abstand zwischen dem 01.04.2008 sowie dem 03.04.2008 ist gleich groß wie der Abstand zwischen dem 03.04.2008 und 04.04.2008, obwohl dieser doppelt so breit sein sollte. Der Abstand zwischen dem 07.04.2008 und dem 10.04.2008 müsste dreimal so groß sein.
Das Problem
Excel nimmt sich aus den Daten der Tabelle PurchaseOrderHeader alle verfügbaren OrderDates und trägt diese auf der x-Achse ab. Fehlt ein Datum, weil am entsprechendem Tag keine Ware bestellt wurde, fehlt der Eintrag. Das gleiche würde man erhalten, möchte man die verkaufte Stückzahl aller Produkte wissen, die man im Produktstamm hat. Nimmt man nur die Informationen aus den Verkaufsaufträgen, erhält man nur Ergebnisse für Produkte, die tatsächlich verkauft wurden. Wurde ein Produkt nie verkauft, wird es im Ergebnis fehlen – obwohl man eventuell das Produkt mit Verkaufsmenge 0 sehen möchte. Man würde niemals von Excel verlangen, dass es das fehlende Produkt “errät” und mit “0” anzeigt. Bei Zeitdimensionen ist das gleich: Excel müsste wissen, dass es sich um eine Zeitdimension handelt, und dass der Schritt zwischen zwei Punkten immer ein Tag beträgt. Aber das weiß Excel nicht.
Die Lösung: eine Datumstabelle
Wie bei einer Produkttabelle, die als Produktdimension verwendet wird, erstellen wir für die Zeit ebenfalls eine Tabelle, eine Datumsdimension. Pro gewünschter Zeiteinheit wird ein Satz in der Tabelle angelegt. Möchte man beispielsweise eine Auflösung von einem Tag haben, legt man für jeden Tag einen Datensatz an (möchte man stundengenaue Aussagen muss man entsprechend pro Stunde einen Satz anlegen). Eine solche Datumstabelle könnte wie folgt aussehen: Die Formeln für diese Spalten lauten:
- DateKey: das Datum, keine Formel
- Year: =Jahr(A2)
- Quarter: =AUFRUNDEN(MONAT(A2)/3;0)
- QuarterAndYear: = C2 & "-"& TEXT(G2;"00")
- QuarterName: ="Q" & D2
- Month: =MONAT(A2)
- MonthAndYear: =C2 & "/" & TEXT(G2;"00")
- MonthName: =TEXT(G2;"MMMM")
- MonthNameAndYear: =I2 & " " & C2
- DayOfYear: =A2-DATUM(JAHR(A2);1;0)
- DayOfMonth: =TAG(A2)
- Weekday: =WOCHENTAG(A2;2)
- WeekdayName: =TEXT(WOCHENTAG(A2);"TTTT")
Diese Datumstabelle können Sie im Downloadsbereich herunterladen (01.01.2000 – 31.12.2020). Diese Tabelle wird in Power Pivot importiert. Damit Power Pivot die Datumstabelle als solche erkennt, muss diese bekannt gemacht werden. Dies erfolgt über das Menü über die Schaltfläche Als Datumstabelle markieren: Neben allen anderen Informationen benötigt die Datumstabelle immer ein Feld, in dem das Datum als vollständiges Datum vorhanden ist. Dieses Feld müssen Sie angeben: Dadurch werden in der Pivot-Table / im Pivot-Chart viele Datumsfunktionen angeboten.
Verknüpfen der Datumstabelle
Anschließend muss sie mit der Faktentabelle verknüpft werden. Dies erfolgt über den DateKey. Hinweis: Es gilt als “Best Practice” einen künstlichen Schlüssel sowohl in der Datumstabelle als auch in der Faktentabelle zu erstellen – üblicherweise das Datum im Format yyyyMMdd (also für den 01. April 2013 den Schlüssel 20130401). Über diesen Schlüssel wird dann die Verknüpfung der Tabellen hergestellt. In Power Pivot wird seitens Microsoft explizit davon abgeraten. Die Datumsfelder werden direkt verknüpft. Achtung: Stellen Sie sicher, dass das Datum in Ihrer Faktentabelle keine Uhrzeiten enthält. Siehe dazu den Artikel zur Datums-Bereinigung. Über das Feld DateKey lassen sich die beiden Tabellen jetzt verknüpfen: Unser Pivot-Chart ändern wir jetzt wie folgt: An den Lücken hat sich bis jetzt noch nichts geändert. Das liegt daran, das Excel die Daten jetzt tatsächlich kennt, allerdings in Pivot-Tabellen automatisch Nullwerte unterdrückt werden. Diese können wir sichtbar machen, indem wir in den Pivot-Optionen der zum Pivot-Chart gehörenden Pivot-Tabelle die Anzeige von Nullwerten konfigurieren:
Anschließend verändert sich der Pivot-Chart wie folgt:
Lösung via Measures
Bei Power Pivot führen viele Wege nach Rom. Natürlich gibt es auch die Möglichkeit in der Datumstabelle ein berechnetes Feld anzulegen, welches die SubTotal-Werte der PurchaseOrderHeader-Tabelle addieren. Verwendet man als Fakt dann diese Werte, werden die 0-Werte ebenfalls angezeigt. Das gleiche gilt, wenn man ein Measure definiert, dass Nullwerte explizit umwandelt. Beispielsweise:
Kommentar hinterlassen
Nehmen wir als Alternative eine Personentabelle als Dimension, die einerseits vom Feld CustomerID und andererseits vom Feld DispatcherID referenziert wird. Auch dann muss die Dimensionstabelle "Person" zweimal importiert werden. Datumstabellen verhalten sich nicht anders.