In meinem letzten Post habe ich Euch das Erstellen von Gruppen (sog. Named Sets) erklärt, um asymmetrische Berichte mit Power Pivot zu erstellen. In diesem Post wird es darum gehen, solche Gruppen derart aufzubauen, dass sie für den Nutzer einfach und komfortabel über Datenschnitte gesteuert werden können. Dafür sind einige Kniffe und Kenntnisse notwendig, aber es erhöht erheblich die Nutzerfreundlichkeit und macht das Browsen durch die Datenflut um einiges angenehmer. Es lohnt sich...
Der folgende Screenshot zeigt, wie ich mir das ganze vorstelle.
Wählt hier das Monatsfeld der Hilfstabelle aus und bestätigt mit OK.
Der folgende Screenshot zeigt, wie ich mir das ganze vorstelle.
Die linke Pivot zeigt nach dem Abschluss des Monats
Februar die Monate Januar und Februar einzeln auf, gefolgt von einer Kumulation
der beiden Monate (ein sog. Year-to-Date-Wert, kurz YTD). Danach folgt
ebenfalls ein YTD-Wert, jedoch der des Vorjahres, gefolgt von einer
Verhältniskennzahl, die ausdrückt, wie sich der Wert des aktuellen YTD-Wertes
im Vergleich zu dem YTD-Wertes des Vorjahres verhält.
Jetzt zeige ich Euch in sechs Schritten, wie Ihr den Aufbau der Pivot
über die Datenschnitte steuern könnt.
1. Die monatlichen Gruppen (Sets) anlegen
Damit die
Gruppen durch die Auswahl des Datenschnittes auch ausgewählt werden können, müssen
sie zunächst erstellt werden. Wie Ihr dies macht, könnt ihr meinem letzten Post detailliert
entnehmen. An dieser Stelle nur folgender Hinsweis: Arbeitet mit dem Feld Anzeigeordner
(optional). Dadurch könnt Ihr alle Gruppen (Sets) inhaltlich vernünftig
gruppieren und somit Ordnung in Eure Gruppen bringen. Glaubt mir, es werden
viele werden. Da dieses Feld ein Freitextfeld ist, ist hier besondere Obacht
geboten, die Namen auch richtig zu schreiben.
2. Hilfstabelle für das Filtern erzeugen
Im letzen Post habt Ihr ja bereits lesen können, dass
es nicht zulässig ist einerseits eine Gruppe (Set) aufzubauen, die auf einer
bestimmten Dimension basiert und andererseits diese Dimension als Datenschnitt
zum Filtern zu benutzen... Ihr erinnert euch an diese Fehlermeldung?!
Wenn Ihr aber nunmal über eine Monatsauswahl festlegen
wollt, welchen Aufbau Eure Pivot haben soll, dann müßt Ihr zuvor eine
Hilfstabelle erstellen. Folgender Screenshot zeigt meine Hilfstabelle:
Spalte A beinhaltet eine Aufstellung von Monaten, auf
deren Basis ich später meinen Pivotaufbau bestimmen will. Spalte B greift auf
Spalte A zu und stellt den entsprechenden Wert lediglich als Zahl (also ohne
Unterstrich) dar. Wofür dies notwendig ist, seht Ihr im weiteren Verlauf.
Noch ein wichtiger Hinweis, bevor wir mit
dem nächsten Schritt weiter machen:
Macht aus
dieser Hilfstabelle eine intelligente Tabelle, indem Ihr in Excel auf Einfügen >> Tabelle geht.
Anschließend klickt in die neu erstellte intelligente Tabelle und
geht in den Reiter Entwurf. Unter Tabellenname könnt ihr dieser
Tabelle einen sprechenden Namen geben. Meine heißt tblSetSteuerung.
Dies ist von großem Vorteil für die weitere Arbeit im Datenmodell von Power
Pivot.
3. Hilfstabelle ins Datenmodell integrieren
Nachdem
die Hilfstabelle angelegt ist, kann diese, sofern Ihr die Tabelle aktiviert
habt, über POWERPIVOT >> Tabellen >> Zu
Datenmodell hinzufügen ins Datenmodell eingebunden werden. Das
Datenmodell sieht jetzt wie folgt aus:
Beachtet, dass die Hilfstabelle keine Verbindung zu
den anderen Tabellen hat. Dies ist kein Fehler. Genauso soll es sein.
4. Hinzufügen eines Datenschnitts auf Basis der neuen Hilfstabelle
Der einzige Zweck der Hilfstabelle ist es, als
Datenschnitt für die Steuerung des Pivotaufbaus zu fungieren. Das nennt man auch Parametertabelle. Dementsprechend
wird die Hilfstabelle jetzt als Datenschnitt eingebaut. Geht hierfür zurück in
die Excelumgebung und klickt in die bestehende Pivottabelle. Sobald die Pivot
aktiviert ist, erscheint am oberen Rand des Excel-Fensters das kontextsensitive
Menü PIVOTTABLE-TOOLS. Klickt jetzt im Reiter ANALYSIEREN auf Datenschnitt
einfügen. Im Anschluss erscheint ein Auswahlfenster wie folgendes:
Wählt hier das Monatsfeld der Hilfstabelle aus und bestätigt mit OK.
Mit einem Rechtsklick auf den Datenschnitt könnt Ihr diesem unter Datenschnitteinstellungen noch einen sprechenden Namen geben. In meinem Fall lautet die Beschriftung Gewünschten Berichtsmonat auswählen.
Das
Ergebnis sieht dann wie folgt aus:
Allerdings besteht noch ein wesentlicher Mangel: Noch
steuert der Datenschnitt leider überhaupt nichts. Dafür sorgen wir in den
nächsten Schritten.
5. Das steuernde Measure anlegen
Damit der Aufbau der Pivottabelle über die Auswahl im
Datenschnitt bestimmt werden kann, muss ermittelt werden können, welcher Monat
im Datenschnitt überhaupt ausgewählt wurde. Hierfür werden wird das
Measure MMaxSetSteuerung erstellen. Da bei einem Datenschnitt
nicht vordefiniert werden kann, dass nur ein einzelner Wert ausgewählt werden
darf (single select), muss über das Measure definiert werden, welcher Wert bei
einer Mehrfachselektion im Datenschnitt zurück gegeben wird.
Beispielsweise soll bei Auswahl der Monate 2014_01, 2014_02 und 2014_03 nur
ein einzelner Monat als ausgewählt gelten, in unserem Falle (per Definition) der letzte
Monat. Die Formel unseres Measures lautet demnach:
MMaxSetSteuerung:=MAX(tblSetSteuerung[JJJJ_MM])
Diese Formel liefert jedoch leider einen Fehler, da
die MAX-Funktion nur mit numerischen Werten arbeiten kann. Bei der Spalte tblSetSteuerung[JJJJ_MM] handelt
es sich jedoch um den Datentyp Text. Jetzt kommt die vorhin erstellte Spalte B
MonatsZAHL ins Spiel. Ich hatte in der Formel für die Erstellung der Werte in
Spalte B extra mit 1 multipliziert, um den Datentyp Text in einen Zahlenwert zu
konvertieren (siehe Formel):
Auf Basis dieses Wertes kann nun das funktionierende
Measure für die Identifikation des selektierten Wertes im Datenschnitt erstellt
werden. Die Formel lautet wie folgt:
MMaxSetSteuerung:=MAX(tblSetSteuerung[MonatsZAHL])
Was jetzt noch fehlt ist, der Pivot beizubringen, auf
den selektierten Wert im Datenschnitt hin ihren Aufbau zu ändern. Dies machen
wir im nächsten Abschnitt via MDX.
6. Eine dynamische Gruppe (Set) via MDX aufbauen
Bei MDX (Multidimensional
eXpressions) handelt es sich um eine Datenbanksprache für Multidimensionale
Datenbanken (z. B. SQL Server Analysis Services). Nun kann man sich fragen, was
MDX jetzt mit Power Pivot zu tun hat, aber jeder von Euch, der bereits eine
Gruppe (Named Set) erstellt hat, hat auch bereits MDX benutzt. Intern wird eine
solche Gruppe immer mittels MDX definiert. Der folgende Screenshot zeigt, wie
Ihr Euch die erstellten Gruppen (Sets) als MDX ansehen könnt:
In Schritt 1 sagt Ihr, dass Ihr die Gruppe (Set) mit MDX bearbeiten
wollt. Im Anschluss erfolgt eine Warnmeldung, die darauf hinweist, dass nachdem
Änderungen via MDX vorgenommen wurden, anschließend keine Änderungen mehr über
die gewohnte Nutzeroberfläche, wie in Schritt 1, gemacht werden können. Dies
liegt daran, dass MDX einen größeren Handlungsumfang zur Verfügung stellt, als
die gewöhnliche Oberfläche zur Erstellung der Gruppen anbietet. Sobald Ihr bei Schritt 2
also auf OK klickt und in Schritt 3 ebenfalls mit OK bestätigt,
geht beim nächsten Bearbeiten dieser Gruppe sofort das Fenster von Schritt 3 auf.
Sofern Ihr in Schritt 3 jedoch gleich wieder auf Abbrechen geht,
ist nichts passiert.
Was MDX für Power Pivot angeht kenne ich nur einen einzigen Artikel im
Netz, der darauf eingeht. Die gute Nachricht hierbei ist, dass dieser Artikel
sehr ausführlich geschrieben und mit vielen Beispielen versehen ist. Die
vielleicht weniger gute Nachricht ist, dass er auf Englisch verfasst wurde.
Chris Webb ist der Autor der fünfteiligen Reihe Introduction to MDX for PowerPivot
Users und zum ersten Artikel geht's hier. (Kleine
Randinformation: Chris ist unter anderem auch Autor des derzeit einzigen Buches
zum Thema Power
Query for Power BI & Excel)
Chris' Artikelreihe hatte ich bereits vor über einem Jahr
gelesen und es fiel mir zugegebenermaßen schwer zu verstehen, in wie weit mich das bei
Power Pivot weiterbringen würde. Doch dann las ich folgenden Artikel auf Rob
Collies' Seite Powerpivotpro.com.
Der wesentliche Punkt hierbei war, dass MDX DAX-Measures lesen und
interpretieren kann. Ist das nicht der Wahnsinn?! Was bedeutet das jetzt?
Wenn ich ...
1. mit MDX den Aufbau einer Pivot bestimmen kann (jede Gruppe
tut dies) und
2. MDX den Wert von einem DAX-Measure lesen kann, und
ich
3. wiederum über einen Datenschnitt den Wert eines DAX-Measures
bestimmen kann (denkt an unsere Hilfstabelle), ...
dann kann ich über einen Datenschnitt auch den Aufbau einer Pivot
bestimmen.
Wie geil ist das denn :)
Die Lösung meines Problems sah
schlussendlich wie folgt aus:
Die IIF-Funktion gibt in Abhängigkeit der Erfüllung einer logischen Prüfung (z. B. [Measures].[MMaxSetSteuerung]=201401) das Objekt A, oder B zurück.
Übersetzt auf unseren Fall bedeutet das, dass dieses MDX-Statement prüft, ob im Datenschnitt der Monat 2014_01 ausgewählt wurde und gibt in diesem Fall die Gruppe [2014_01] zurück. Falls dies nicht der Fall ist, prüft das MDX-Statement darauf, ob Monat 2014_02 ausgewählt wurde und so weiter.
Besonders wichtig ist, das rot markierte Feld Set bei jedem Aktualisieren neu berechnen anzuhaken. Nur dann wird die Gruppe (Set) in Abhängigkeit der Auswahl im Datenschnitt den Aufbau der Pivot korrekt bestimmt. Andernfalls ändert sich die Pivot überhaupt nicht.
Der letzte Schritt liegt quasi auf der Hand: Die eben erstellte Gruppe (Set) muss nun für die aktuelle Pivot in der Feldliste noch als Spaltenüberschrift ausgewählt werden.
Damit ist unser Ziel erreicht... Der Aufbau der Pivot läßt sich nun nach belieben über den Datenschnitt bestimmen.
Wer jetzt noch nicht mit rauchendem Kopf da sitzt, dem ist vielleicht aufgefallen, dass das MDX-Statement noch nicht optimal geschrieben ist. Schließlich haben wir über verschachtelte IIF-Funktionen erst 6 Fälle (nämlich die Monate 2014_01 bis 2014_06) behandelt. Für jeden weiteren Monat müßte jeweils eine weitere IIF-Funktion hinzugefügt werden. Wie man dies eleganter und wartungsfreier bewerkstelligen kann, erkläre ich in meinem nächsten Post.
Viel Spaß beim Testen und bis zum nächsten Mal...
Lars
