Datumsdimension in Power BI dynamisch mit M und DAX erzeugen

Publiziert von

Die Ausarbeitung einer firmenspezifischen und möglichst universell nutzbaren Datumsdimension ist eine Kernaufgabe in der Implementierung eines analytischen Datenmodells in Power BI, Power Pivot und SQL Server Tabular Model.

Natürlich kann eine firmenspezifische Datumsdimension auch als Tabelle in einem Datawarehouse oder als Excel Table bereitgestellt werden. Der Vorteil einer dynamisch erzeugten Datumsdimension besteht jedoch darin, daß einerseits der Spaltenaufbau für einzelne Anwendungen leicht angepaßt werden kann und andererseits der Zeitraum der Datumsdimension dynamisch festgelegt werden kann.

Die Umsetzung kann sowohl mit der Formelsprache M in der Komponente Get Data/Power Query oder in der Formelsprache DAX in der Komponente Datamodel/Power Pivot erfolgen, mit vielen Parallelen aber auch einigen Unterschieden im Detail. Vielen Dank an Peter Rynesch für den Impuls und die wertvollen Inputs zu diesem Blogbeitrag.

1. Umsetzung mit der Formelsprache M (Get Data/Power Query)

Die folgende Methode kann in Power BI Desktop, in Power Query ab Excel 2010 und in Tabular Model ab SQL Server 2017 angewendet werden.

Als erstes wird mit dem Befehl Home -> Get Data -> Blank Query eine leere Query angelegt, um die M-Statements für den dynamischen Aufbau der Datumsdimension einsetzen zu können:

Im jetzt bereits geöffneten Query Editor wird im nächsten Schritt zur Dynamisierung des Zeitraums der Datumsdimension im Menü Home -> Manage Parameters -> New Parameter ein Parameter erstellt …

… und folgendermaßen unter dem Namen Startdatum konfiguriert:

Den Kern der Lösung bildet das folgende M-Statement:

  • = List.Dates(Startdatum, Number.From(DateTime.LocalNow())- Number.From(Startdatum) ,#duration(1,0,0,0))

Es wird also eine Liste mit fortlaufenden Datumseinträgen erzeugt, beginnend beim (leicht veränderbaren) Parameter Startdatum bis zum heutigen Tag:

Anstelle des dynamischen Parameters könnte alternativ das Startdatum im ersten Step Source mit einem M-Statement hart-codiert werden und das List.Dates-Statement als zweiter Step Liste angelegt werden (anstelle des Bezugs auf den Parameter Startdatum lautet der Bezug dann auf Source).

  • Source =#date(2015,1,1)
  • Liste = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))

Als nächstes wird die Liste mit dem Befehl List Tools -> Transform -> To Table in eine Tabelle umgewandelt, die Spalte von Date auf Datum umbenannt, der Datentyp dieser Spalte auf Datum geändert und die Query selbst von Query1 auf DIM Datum (M) umbenannt:

Im nächsten Schritt wird mit den Time Intelligence Funktionen von Power Query das Year 

… sowie das Quarter und Month erzeugt. Dabei muß jeweils das Feld Datum markiert sein, da sich die Time Intelligence Funktionen immer auf ein Datumsfeld beziehen müssen. Hier das vorläufige Resultat:

Jetzt können die neuen Felder auf Jahr, Quartal und Monat SORT umbenannt werden. Im letzten und abschließenden Schritt wird über den Befehl Add Column -> Custom Column ein Feld für den Monatsnamen mit folgendem M-Statement erzeugt:

  • Monat = Date.ToText([Datum], „MMMM“, „de-AT“)

Sehr wertvoll ist hier das dritte Argument zur aktiven Steuerung der angewendeten Sprache – unabhängig von der Systemsprache von Power BI Desktop oder des Betriebssystems. Die Liste der Language Codes ist hier zu finden.

Abschließend wird mit der Time Intelligence Funktion Day of Week das Feld Wochentag SORT eingefügt und wiederum mit einer Custom Column das Feld Wochentag:

  • Wochentag = Date.ToText([Datum], „dddd“, „de-AT“)

Abschließend werden als Best Practice die Power Query Applied Steps sprechend benannt und die finale Datumsdimension kritisch geprüft:

Im Menüpunkt View -> Advanced Editor kann das fertige M-Statement für die gesamte Datumsdimension gesichert werden und in jede andere Power BI Desktop Anwendung übertragen werden:

In der Ziel-Anwendung muß natürlich ein gleichnamiger Parameter vorhanden sein, am besten es wird auch das M-Statement des Parameters auf die gleiche Art und Weise gesichert bzw. übernommen.

Die datengetriebene Dynamisierung kann durch eine Erweiterung um zwei vorgelagerte M-Statements für das Start- und das Enddatum erfolgen, die das MIN- und das MAX-Datum aus dem Datumsfeld einer Faktentabelle abfragen (hier: FACT InternetSales aus der AdventureWorks Datenbank).

  • StartDatum = List.Min(Table.Column(FACTInternetSales,“OrderDate“)),
  • EndDatum = List.Max(Table.Column(FACTInternetSales,“OrderDate“)),
  • Source = List.Dates(StartDatum, Number.From(EndDatum)- Number.From(StartDatum) ,#duration(1,0,0,0)),

Wichtig ist für die Funktionsweise dieser M-Statements, daß das Feld OrderDate in der Faktentabelle das Format Date hat und nicht etwa DateTime oder Ganzzahl (da in diesen Fällen noch eine Formatkonvertierung notwendig wäre).

Der Parameter wird in dieser Ausbaustufe nicht mehr benötigt und wird daher entfernt, da dieser zumindest nicht gleichnamig zur neuen Variable StartDatum weiterbestehen kann. Denkbar wäre auch eine Dynamisierung der Datumsdimension unter Beibehaltung des Parameters durch Verwendung der Query-Option in der Parameter-Definition, in unseren Tests ist uns das aber nicht auf Anhieb gelungen.

Quellen:

www.powerquery.training/portfolio/dynamic-calendar-table/

blog.crossjoin.co.uk/2016/08/30/data-driven-power-bi-desktop-parameters-using-list-queries/

community.powerbi.com/t5/Desktop/Date-Dimension-Table-that-Dynamically-Pulls-Start-and-End-dates/td-p/76870

2. Umsetzung mit der Formelsprache DAX (Datamodel/Power Pivot)

Die folgende Methode kann in Power BI Desktop und in Tabular Model ab SQL Server 2016 angewendet werden, nicht jedoch in Excel Power Pivot, da es dort bis dato leider keine Calculated Tables gibt.

Wir starten mit dem Befehl Modelling -> New Table

… und setzen das folgende DAX-Statement ein:

  • DIM Datum (DAX) = CALENDAR(Date(2015;1;1);Now())

Die DAX-Funktion CALENDAR erzeugt automatisch eine Tabelle mit einer Inkrement von 1 Tag. Eine Dynamisierung mittels Parameter ist im Datamodel eher nicht möglich, dafür zeigen wir am Ende dieses Beitrags die datengetriebene Dynamisierung der Datumsdimension. Mit dem DAX-Statement wird übrigens der Name des Calculated Tables festgelegt, das erzeugte Feld wird von Power BI Desktop mit Date benannt …

… das wir im nächsten Schritt gleich auf Datum umbenennen. Jetzt werden in der neuen Tabelle 6 Calculated Columns angelegt:

  • Jahr = Year(‚DIM Datum (DAX)'[Datum])
  • Quartal = Roundup(Month(‚DIM Datum (DAX)'[Datum])/3;0)
  • Monat SORT = Month(‚DIM Datum (DAX)'[Datum])
  • Monat = Format(‚DIM Datum (DAX)'[Datum];“MMMM“)
  • Wochentag SORT = WEEKDAY(‚DIM Datum (DAX)'[Datum];3)
  • Wochentag = Format(‚DIM Datum (DAX)'[Datum];“DDDD“)

Ein großer Nachteil der DAX-Funktion FORMAT gegenüber der M-Funktion Date.ToText ist, daß das anzuwendende Länderschema nicht bestimmt werden kann (siehe Screenshot). Diese Inhalte hängen damit vom Deployment (Power BI Desktop, PowerBI.com, SQL Server) ab und können damit variieren, ein Filterbezug auf diese Felder ist damit nicht mehr stabil möglich. Wir raten daher von der Verwendung der FORMAT-Funktion ab, wenn die Sprache des Deployments nicht kontrolliert werden kann.

Offen ist jetzt noch die Dynamisierung. Grundsätzlich wäre eine Dynamisierung mittels What-If Parameter bzw. noch weiter reduziert mit einem Disconnected Table, der nur aus einer Spalte und einer Zeile besteht und damit als skalare Variable verwendet werden kann. Diese Lösung erscheint aber wenig elegant, daher raten wir eher davon ab. Dies auch, weil die Von/Bis-Parameter der CALENDAR Funktion sehr einfach mittels MIN- und MAX-Statements auf das Datumsfeld einer Faktentabelle dynamisiert werden können:

  • DIM Datum (DAX) = CALENDAR(
    Min(‚FACT InternetSales'[OrderDate]);
    Max(‚FACT InternetSales'[OrderDate])

Auch zwei Datumsfelder können noch recht einfach mit den DAX-Funktionen MINX und MAXX verarbeitet werden, ab drei Datumsfeldern (in einer oder mehreren Faktentabellen) ist eine kreative DAX Lösung notwendig, das wäre dann Stoff für einen eigenen Blogbeitrag.

Ein großer Nachteil der bisher gezeigten Lösung ist, daß die DAX-Statements nicht – wie die M-Statements – in Bausch und Bogen in eine zweite Anwendung übertragen werden können sondern die Statements für den Calculated Table und für jede Calculated Column einzeln übertragen werden müssten. Dieser Nachteil kann durch eine Bündelung der einzelnen Statements mit der DAX-Funktion ADDCOLUMNS nach folgendem Muster überwunden werden:

  • DIM Datum (DAX ADDCOLUMNS) =
    ADDCOLUMNS (
    CALENDAR(Min(‚FACT InternetSales'[OrderDate]);Max(‚FACT InternetSales'[OrderDate]));
    „Jahr“; YEAR ( [Date] );
    „Quartal“; ROUNDUP(MONTH([Date])/3;0);
    „Monat SORT“; MONTH([Date]);
    „Monat“; FORMAT([Date];“MMMM“);
    „Wochentag SORT“; WEEKDAY([Date];3);
    „Wochentag“; FORMAT([Date];“DDDD“)
    )

Die Tabelle muß ggfs. einmalig nach der Datumsspalte sortiert werden (was aber keine Wirkung auf das Datamodel hat sondern nur auf die optische Darstellung in der Datenansicht). Das Quellfeld Date kann nach der Erzeugung des Calculated Tables umbenannt werden, die DAX-Formeln im ADDCOLUMNS-Statement behalten jedoch den Bezug zum ursprünglichen Feldnamen Date. Beachten Sie bitte die empfohlene Optimierung mit den DAX Funktionen GENERATE und ROW (siehe Quellenangabe).

Quellen:

www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/

Fazit

Im Ergebnis sehen die beiden Datumsdimensionen fast gleich aus, sowohl die M- als auch die DAX-Statements können in einem Zug mittels Copy & Paste in weitere Anwendungen übertragen werden und in beiden Varianten ist eine datengetriebene Dynamisierung leicht umsetzbar:

Die Umsetzung in Power Query hat den Vorteil, daß die Funktion Date.ToText sprachspezifisch gesteuert werden kann, das ist der einzige echte Funktionsvorteil (die Time Intelligence Funktionen in Power Query sind praktisch, aber nicht notwendig). Ansonsten wird die Toolauswahl weitgehend von den persönlichen Präferenzen bestimmt – ich etwa bevorzuge immer DAX gegenüber M – und von den technologischen Rahmenbedingungen: in Excel (2010 bis 2016) steht nur die M-Methode zur Verfügung und in Tabular Model SQL Server 2016  nur die DAX-Methode (in SQL 2017 dann beide, bis SQL 2014 gar keine). Möglicherweise gibt es auch Performanceunterschiede zwischen den beiden Methoden, diese haben wir im Rahmen dieses Blogbeitrags jedoch nicht beleuchtet.

Unabhängig davon, wie die Datumsdimension in das Datamodel gekommen ist, die semantische Finalisierung muß mit den folgenden Teilschritten (leider jedes Mal aufs Neue) manuell durchgeführt werden:

  • Formatierung des Feldes Datum auf das Format dd.mm.yyyyy
  • Aggregationsmethode für die Wertfelder Jahr, Quartal, Monat SORT und Wochentag SORT auf Do not Summarize umstellen
  • Sortierung der Felder Monat und Wochentag mittels Sort-by-Column definieren und ausblenden der SORT-Felder mit dem Befehl Hide in Report View

Weiterführend

Für alle, die eine Einführung in die Formelsprache M gebrauchen können, hier ein sehr guter Blogbeitrag von Reza Rad:

radacad.com/basics-of-m-power-query-formula-language

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Analyse, Dashboarding, Power BI im Team nutzen, Power Pivot nutzen, Reporting, SQL Server als DWH nutzen