Power BI lässt es zu, dass verschiedene Quellen im Sinne eines ETL-Vorgangs zu einem Dataset zusammengeführt werden können. Unabhängig vom eigentlichen Ausgangspunkt, also wo die Data ursprünglich herkommen, bieten sich verschiede Szenarien an, um eine neue Dimension einzubauen, die unabhängig von der ursprünglichen Datenquelle ist.
In diesem Blog widme ich mich der Variante mittels Query M, da diese sehr vorteilhaft zu sein scheint, weil dies außerhalb der Ebene DAX geschieht. Gemeint ist, dass vor dem Laden der Daten die Kalendertabelle erstellt wird und ins Datenmodell beim Speichern und Laden übernommen wird. Somit geschieht es ohne den Einsatz von DAX (Data Analysis Expression). Abschließend wird dieser Vorgang noch bewertet, ob es aus der Perspektive der Performance vorteilhaft ist oder nicht.
Das Script schaut folgendermaßen aus:
let
fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable
Dieses Script wird im sog. Power Query-Editor mit Hilfe einer leeren Abfrage via „Erweiterter Editor“ hinzugefügt.
Es führt dazu, dass wir nun dem Klick auf „Fertig“ eine Eingabemaske für die Eingabe von Parametern erhalten.
Ich habe in diesem Beispiel einen Datumsbereich vom 01.01.1996 bis 31.12.2010 gewählt. Abschließend klicke ich auf „Speichern und übernehmen“, damit wird die neue per Language M generierte Dimensionstabelle in den Arbeitsspeicher geladen und im tabellarischen Datenmodell von Power BI gespeichert. Die ausgeführte Aktion hat etwa 5 Sekunden gedauert, bis die Daten aktualisiert worden sind, es sind insgesamt 5478 Zeilen.
Jetzt stellt sich die Frage, ob dieses Verfahren ggf. nachteilig ist, ggü. einer vergleichsweise ähnlichen Vorgehensweise, wenn diese Dimensionstabelle mit einem DAX Ausdruck erstellt worden wäre. Dazu habe ich folgende DAX-Formel erstellt. Sie liefert zwar 4 Spalten weniger, jedoch die gleiche Anzahl an Zeilen.
Calendar = ADDCOLUMNS ( CALENDAR (DATE(1996;1;1); DATE(2010;12;31)); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "MonthNo"; FORMAT ( [Date]; "MM" ); "YearMonthNo"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonth"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthShort"; FORMAT ( [Date]; "mmm" ); "MonthLong"; FORMAT ( [Date]; "mmmm" ); "WeekNo"; WEEKDAY ( [Date] ); "WeekDay"; FORMAT ( [Date]; "ddd" ); "WeekDayShort"; FORMAT ( [Date]; "dddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ))
Diese Antwort erhalten wir im DAX Studio. Es ist ein wunderbares Werkzeug, um das unter die Lupe zu nehmen. Nähere Informationen zu DAX Studio inkl. Free Download findest du hier: DAX Studio.
Im DAX Studio wird die PBIX Datei geöffnet und über den Reiter „Advanced“ -> „View Metrics“ liefert uns die Ansicht „Tables“ die Antwort.
Im DAX Studio habe ich mir abschließend die Werte angeschaut, um folgendes festzustellen. Die DAX generierte Tabelle ist trotz weniger Spalten, insgesamt 13 (4 Spalten weniger erstellt als mit der Language „M“) dennoch deutlich größer.
Der Unterschied beläuft sich auf 11,2 MB Tabellengröße der DAX Tabelle, im Gegensatz zu 8 MB der Query Language „M“, obwohl es hier 4 Spalten mehr sind.
Fazit: Hieran ist deutlich zu erkennen, dass DAX mehr Speicher verbraucht als „M“, ob dies nun aber große Auswirkungen auf die Performance hat ist recht fraglich. Ja es sind ca. 3 MB Unterschied, und es wird ggf. eine größere Rolle spielen, wenn es nicht 5000 Zeilen sondern 5 Mio. Zeilen sind.