Power BI – Bequeme Filter in SSAS Tabular Modellen

Hintergrund

Inspiriert durch den äußerst lesenswerten Artikel von Brad Marshall im Rahmen des regelmäßigen Newsletters von „powerpivot(pro)“ habe ich mich direkt einmal rangesetzt und die sogenannten „convenience“ – also bequemen – Filter in ein Beispiel Power BI-Modell eingebaut. Was Brad unter „convenience filters“ versteht, erklärt er im ersten Teil seines Artikels:

„Denken Sie zurück an das Jahr 2012 als Microsoft uns die SQL Server Analysis Services Tabular Models vorstellte. Es war neu und aufregend. PowerPivot-Modelle (verfügbar seit 2010) konnten nun einfach nach SSAS publiziert werden und boten damit eine sichere, zentralisierte und hoch optimierte, unternehmensweit einsetzbare serverbasierte Reporting-Lösung.

In Zeiten vor Power BI Desktop wurde vor allem Excel als Frontend eingesetzt, um mit SSAS-Modellen zu kommunizieren und hochperformante ad-hoc-Abfragen auszuführen. Ganze Dashboards wurden in Excel gebaut, nach SharePoint publiziert und alle waren glücklich.

Dann setzte Ernüchterung ein: Jeden Monat mussten die Berichte im SharePoint ausgechecked, die Filter auf den aktuellen Monat angepasst und die Berichte wieder eingechecked werden. Für z.B. rollierende 12-Monats-Berichte musste auf diesem Wege der neue Monat manuell hinzugefügt und der alte entfernt werden. Und das jeden Monat für jeden Bericht…“

Bequeme Filter sind die Antwort auf dieses Problem

Im Jahre 2013 begann Brad sich Gedanken über dieses nervige Problem zu machen und kam mit etwas zurück, dass der „convenience filters“ nannte.

Was war damit gemeint? In der Regel verwenden „Tabular Models“ eine eigene Datumstabelle, zur Abbildung der Zeit-Dimension. Gewöhnlicherweise enthält diese Tabelle eine Spalte mit dem Tagesdatum und viele weitere Spalten mit Ableitungen anhand dieses Datums: Jahr, Quartal, Monatsname etc. Brads Idee bestand nun darin, noch ein paar weitere Spalten hinzuzufügen, die die Werte „TRUE“ oder „FALSE“ enthalten. Diese Spalten in die Berichte eingebaut und auf „TRUE“ gefiltert – und niemand muss sich mehr darum kümmern.

Ein Beispiel: Es wird in die Datumstabelle eine Spalte „Current Month“ eingefügt. Jeder Eintrag in der Datumstabelle wird nun gegen das aktuelle Datum geprüft. Sofern sich das eingetragene Datum im aktuellen Monat befindet, enthält die Spalte Current Month den Wert „TRUE“, ansonsten „FALSE“. Anstatt nun jeden Monat den Bericht wie oben beschrieben anpassen zu müssen, reicht es nun, die Spalte „Current Month“ als visuellen, Seiten- oder Berichtsfilter im Bericht zu verwenden und auf „TRUE“ zu setzen – fertig!

Power BI schafft hier schon etwas Abhilfe

Die Entwickler von Microsoft haben dieses Problem ebenfalls schon vor einer Weile erkannt und in Power BI den Datumsfilter um die Funktion „relative Datumsfilterung“ ergänzt:

Damit können Datumswerte gefiltert werden, die in einem vom aktuellen Datum abhängigen definierbaren Zeitraum liegen, z.B. „in den letzten 30 Tagen“, oder auch „in den letzten 12 Monaten“, womit wir einen rollierenden Jahresbericht erhalten. Wie gesagt, diese Funktion existiert in Power BI. Anwender, die aber weiterhin Excel verwenden, um auf ihre SSAS-Modelle zuzugreifen, oder gar – Gott bewahre! – Tableau oder Cognos dafür verwenden 😉 – für diese Anwender wäre es hilfreich, die bequemen Filter direkt und zentral im Datenmodell zu verankern und dann unabhängig vom Frontend verwenden zu können.

Wir bauen bequeme Filter

Die technische Umsetzung der bequemen Filter ist abhängig von der verwendeten Technologie. Die Datumstabelle kann bei Ihnen als physische Tabelle abgebildet sein oder als Datenbank-View. In anderen Fällen ist sie vielleicht mit Hilfe von DAX-Formeln im Modell generiert worden. In allen Fällen ist die Technik der bequemen Filter die gleicht, weicht nur in ihrer Umsetzung etwas ab.

Datums-Tabelle im SQL-Server

Jeder, der die bequemen Filter direkt in der Datenbank-Tabelle oder -View anlegen möchte, kommt mit SQL-Befehlen weiter. Angenommen, unsere Datumsspalte nennt sich „PK_Date“, sehen die SQL-Befehle folgendermaßen aus:

Current Month – CASE WHEN YEAR([PK_Date]) = YEAR(getdate()) And MONTH(PK_Date) = MONTH(getdate()) then ‘TRUE’ else ‘FALSE’ end as Current_Month

Current Year – CASE WHEN YEAR([PK_Date]) = YEAR(getdate()) THEN ‘TRUE’ ELSE ‘FALSE’ END as Curent_Year

Rolling Three Months – CASE WHEN DATEDIFF(month, PK_Date, getdate()) BETWEEN 0 AND 2 THEN ‘TRUE’ ELSE ‘FALSE’ END AS RollingThreeMonths

Datums-Tabelle im DAX-Modell

Für Anwender, die ihre Datumstabelle mit DAX-Formeln erzeugen, ergeben sich folgende Code-Zeilen

CurrentMonth =

IF (

   YEAR (Dates[PK_Date] ) = YEAR ( NOW () )

&& MONTH ( Dates[PK_Date] ) = MONTH ( TODAY () ),

   “TRUE”,

   “FALSE”

)

 

CurrentYear =

IF (YEAR (Dates[PK_Date] ) = YEAR ( NOW () ), “TRUE “, ” FALSE” )

 

Rolling 3 Months =

IF (

Dates[PK_Date] > EDATE ( TODAY (), -3 )

&& (Dates[PK_Date] <= EDATE ( TODAY (), 0 ) ),

   “TRUE”,

   “FALSE”

)

 

Weitere Beispiele und Ideen dazu finden Sie im ganz oben erwähnten Originaltitel.

Bequeme Filter abhängig vom letzten Ladedatum

Brad hat seine bequemen Filter immer abhängig vom aktuellen Tagesdatum erzeugt. In meinem konkreten Fall war das jedoch nicht gut, da wir in unserem Projekt eine monatliche Beladung haben und z.B. sich der Current Month bzw. die letzten 12 Monate vom letzten beladenen Monat ableiten müssen. Zusätzlich sollte sich die Zeit-Dimension abhängig vom Beladungsstand anpassen, also Startdatum = MIN(Buchungstag) aus den Bewegungsdaten sowie Endedatum = MAX(Buchungstag). Dazu waren im Modell ein paar kleine Tricks erforderlich… Da ich meine Datumstabelle mit DAX-Formeln aufgebaut habe, beschränke ich mich ab hier auf die Umsetzung mit DAX.

Start- und Endedatum der Datumstabelle festlegen

Zuerst ist im Power Query-Editor eine neue, leere Abfrage zu erstellen. Name der Abfrage ist auf „Kalender“ zu ändern und im ersten angewendeten Schritt die folgende Formel anzuwenden:

List.Min(Table.Column(Buchungen_Import,“Buchungstag“))

Diesen Schritt kann man dann anschließend in „StartDatum“ umbenennen. Hierbei wird der kleinste Datumswert aus der Spalte „Buchungstag“ unserer Bewegungsdatentabelle „Buchungen_Import“ ermittelt und als Startwert unserer Kalender-Tabelle festgelegt, die wiederum später als unsere Zeit-Dimensionstabelle dienen soll.

Nun fügen wir noch einen weiteren Schritt über den Editor ein, der das Endedatum mit dem größten Datumswert aus unseren Buchungen festlegen soll:

List.Max(Table.Column(Buchungen_Import,“Buchungstag“))

Diesen Schritt benennen wir der Übersichtlichkeit noch um in „EndeDatum“

Damit die Datumstabelle nun automatisch mit allen Tagen zwischen dem Start- und dem Endedatum aufgefüllt wird, dient der folgende Schritt:

List.Dates(StartDatum, Number.From(EndeDatum) – Number.From(StartDatum) ,#duration(1,0,0,0))

Anschließend umbenennen nach „DatumFüllen“. Damit die erzeugte Liste auch als Tabelle erkannt wird, muss diese konvertiert werden:
Table.FromList(DatumFüllen, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

Auch dieser Schritt erhält natürlich einen sprechenden Namen „In Tabelle konvertieren“. In den nächsten beiden Schritten („Geänderter Typ“ und „Column1 nach Datum umbenennen“) legen wir den Datentyp unseres Datums als „date“ fest und benennen die Spalte um nach „Datum“

Table.TransformColumnTypes(#“In Tabelle konvertieren“,{{„Column1“, type date}}) à „Geänderter Typ“

Table.RenameColumns(#“Geänderter Typ“,{{„Column1“, „Datum“}}) à „Column1 nach Datum umbenennen“

Damit sind die Vorbereitungen fast abgeschlossen.

Datumstabelle vervollständigen

Abgeleitet vom Datum in der Spalte Datum werden nun die typischen Attribute der Datumstabelle ergänzt:

Table.AddColumn(#“Column1 nach Datum umbenennen“, „Jahr“, each Date.Year([Datum]), Int64.Type) à „Jahr eingefügt“

Table.AddColumn(#“Jahr eingefügt“, „Monat“, each Date.Month([Datum]), Int64.Type) à „Monat eingefügt“

Table.AddColumn(#“Monat eingefügt“, „Tag“, each Date.Day([Datum]), Int64.Type) à „Tag eingefügt“

Table.AddColumn(#“Tag eingefügt“, „Quartal“, each Date.QuarterOfYear([Datum]), Int64.Type) à „Quartal eingefügt“

Table.AddColumn(#“Quartal eingefügt“, „Name des Tags“, each Date.DayOfWeekName([Datum]), type text) à „Name des Tages eingefügt“

Table.AddColumn(#“Name des Tags eingefügt“, „YYYY-MM“, each Date.ToText([Datum], „yyyy-MM“)) à „YYYY-MM eingefügt“

Auf diese Weise können nun viele weitere Attribute ergänzt werden und wir haben unsere Arbeiten im Power Query-Editor abgeschlossen.

… und bequeme Filter einbauen

Wir gehen zurück in unseren Power BI Desktop, nehmen die ausstehenden Änderungen am Datenmodell an und wechseln in den Datenmodus (Tabellensymbol in der linken Leiste anklicken) – und ergänzen jetzt die bequemen Filter als zusätzliche Spalten in unserer Kalender-Tabelle.

Der Current Month soll in unserem Fall der letzte beladene Monat sein. Somit zielen wir auf das maximale Datum in der zuvor dynamisch angelegten Datumstabelle „Kalender“ ab. Wir fügen also über den Menüpunkt „Modellierung“, „Neue Spalte“ eine weitere Spalte hinzu und geben als Funktion folgende DAX-Formel ein:

CurrentMonth = IF (

   Kalender[Jahr] = YEAR(MAX ( Kalender[Datum] )) && Kalender[Monat] = MONTH (MAX ( Kalender[Datum] ));

   „TRUE“;

   „FALSE“

)

 

Gleiches Prinzip auch bei den „Rolling 12 Month“:

Rolling 12 Month =

VAR Date12MBefore = EDATE ( MAX(Kalender[Datum]); -12 )

RETURN

IF (

       Kalender[Datum] >= DATE( YEAR(Date12MBefore); MONTH(Date12MBefore)+1; 1);

          „TRUE“;

           „FALSE“

)

 

…und auch bei den „Rolling 3 Month“:

Rolling 3 Month =

VAR Date3MBefore = EDATE ( MAX(Kalender[Datum]); -3 )

RETURN

IF (

       Kalender[Datum] >= DATE( YEAR(Date3MBefore); MONTH(Date3MBefore)+1; 1);

           „TRUE“;

           „FALSE“

)

 

Nun können diese neu eingefügten Spalten als Filter in unseren Power BI-Berichten kinderleicht verwendet werden und mit jeder weiteren Monatsbeladung schiebt sich unser Rolling 12 Month-Bericht um einen weiteren Monat nach vorne.

Robert Wienroth v. Riehm

Controller mit Herz und Liebe zum Detail. Mag das Spiel mit Zahlen - vor allem mit intuitiven Tools und kurzen Wartezeiten.