První věc, kterou řeším když začínám dělat report v PWBI je datumová dimenze.
V 99% případech se v reportu nějaké datum vyskytne. Ať již v podobě osy nebo třeba jen k informativním účelům (za jakého dnes/měsíce/kvartálu/roku data jsou). A člověk nikdy neví kdy se mu časová dimenze bude hodit (většinou zadání reportu není beze změny od začátku do konce, vždy se objeví nějaký nový požadavek, nová fíčura).
Pro tyto účely mám připravenou dimenzi v PowerQuery. Určitě by ji šlo vytvořit i v DAX nebo si ji dotáhnout z podkladového datového zdroje (třeba z view v SQL databázi). Já jsem pro PowerQuery, přijde mi flexibilnější a hlavně její podobu mám plně v rukou (podkladová datová vrstva občas není ve vaší správě), nemusím kvůli ní napojovat nový datový zdroj a tak si ušetřím napojování datasetu s datovým zdrojem v online podobě reportu (případně nemusím pro ni řešit gateway).
Tak jdeme na to.
- Otevřete si nový sešit v PWBI desktopu a vytvoříme si nový prázdný datasource.
2. Vytvoříme si dva parametry YearsBack a YearsForward. Ty budou sloužit pro určení začátku a konce dimenze. Použijí se ve skriptíku, který je na konci článku.
3. V PowerQuery Editoru klikněte na Advanced Editor.
4. Do textového editoru vložte skriptík z konce článku.
5. Je hotovo.
A tady je ten skriptík. Popis proměnných tam najdeme a na zbytek přijdete. Pokud chcete mít dimenzi česky, tak stačí přejmenovat.
let
// conf. start
Today=Date.From(DateTime.LocalNow()), // today's date
ActualYear=Date.Year(DateTime.LocalNow()),
StartofFiscalYear=1, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day.Sunday....
// conf. end
FromDate=#date(ActualYear-YearsBack,1,1),
ToDate=#date(ActualYear+YearsForward,12,31),
Source=List.Dates(
FromDate,
Duration.Days(ToDate-FromDate)+1,
#duration(1,0,0,0)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted DateId" = Table.AddColumn(#"Changed Type", "DateId", each Date.ToText([Date],"yyyyMMdd"), Int64.Type),
#"Inserted Year" = Table.AddColumn(#"Inserted DateId", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
#"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date],firstDayofWeek), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
#"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
FiscalMonthBaseIndex=13-StartofFiscalYear,
adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
#"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
#"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
#"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
#"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),
#"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
#"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type4", "Year-Month", each Date.ToText([Date],"MMM yyyy")),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Year-Month Code", each Date.ToText([Date],"yyyyMM")),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom5",{{"Year-Month", type text}, {"Year-Month Code", Int64.Type}})
in
#"Changed Type5"Osobně si datumovou dimenzi vytvořím v samostatném reportu. Report nahraji do online workspace, smažu report část a nechám si tam pouze dataset. Ten pak používám v ostatních reportech a přímo se na něj napojuji. Tedy pokud report jde do toho stejného workspace.
Omezením je, že ve všech reportech kde je dimenze použita, vypadá stejně. Sdílí se všechny její atributy napříč vším a následně i každá změna v datumové dimenzi se projeví ve všech reportech kde je použita.
Někdy příště ukážu datumovou dimenzi řešenou v podkladových datech, v MSSQL.
