Context: I have gaps in my master data - where I have cost centers in my fact table that are not present in my master data for particular fiscal year - but those cost centers are present in other years. I have developed a scenario to allow to complete the master data - by appending the GetMissingDim to the Dim Table.
When I apply transformation steps (add_dim1FilteredFinal) to the dim.1 table (i.e. the table generated from the merge query with the Dim Query) - the process is extremely slow - it appears to read in data on a row by row (record by record basis) - without or with buffering. Using the code below (GetMissingDim - Alternative 1) - a count of 10K or above will demonstrate the slowness of this approach.
When I apply the same transformation steps (add_dim1FilteredFinal) to the dim.1 table - except that I first expand the dim.1 table and then regroup using all rows, the data does not appear to be read row by row - and as such, the results are extremely fast (GetMissingDim - Alternative 2) - even with a count of 50K. .
My questions:
Why is there such a difference between a table that has been generated from a merger versus a table that has been generated from the allrows in the group by function- as in both cases, I am doing transformation to an inner table?
How can I change Alternative 1 to have performance results like Alternative 2 - so I don't have to expand and re-group?
Below you will find the sample queries that would demonstrate the issues described above:
Count: A seperate query to enter a numeric value to change how big are the tables. Issues start to be apparent with a count of 10K+
DIM: Dimension table with a missing year of data.
let
x=Count,
Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x)}),{"dimCC","dimFY"})
in
Source
GetMissingDim- Alternative 1 - Very slow as it processes row by row
let
x=Count,
Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x)})&List.Zip({{1..x},List.Repeat({"2019-20"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x)}),{"CC","FY"}),
MergeLeftAnti = Table.NestedJoin(Source, {"CC", "FY"}, Dim, {"dimCC", "dimFY"}, "Dim", JoinKind.LeftAnti),
Merged = Table.NestedJoin(MergeLeftAnti, {"CC"}, Dim, {"dimCC"}, "dim.1", JoinKind.LeftOuter),
Add_dim1FilteredFinal = Table.AddColumn(Merged, "dim.1_Final", each let
FYFilter=[FY],
dimFYFiltered=List.Select([dim.1][dimFY], each _>FYFilter),
dimFY_Max=List.Min(dimFYFiltered),
dim1FilteredFinal=Table.SelectRows([dim.1],each [dimFY]=dimFY_Max)
in dim1FilteredFinal),
#"Expanded dim.1_Final" = Table.ExpandTableColumn(Add_dim1FilteredFinal, "dim.1_Final", {"dimCC", "dimFY"}, {"dimCC", "dimFY"})
in
#"Expanded dim.1_Final"
GetMissingDim - Alternative 2- Very quick - Same as Alternative 1 except for a expand Merged step and a Group with all rows:
let
x=Count,
Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x)})&List.Zip({{1..x},List.Repeat({"2019-20"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x)}),{"CC","FY"}),
MergeLeftAnti = Table.NestedJoin(Source, {"CC", "FY"}, Dim, {"dimCC", "dimFY"}, "Dim", JoinKind.LeftAnti),
Merged = Table.NestedJoin(MergeLeftAnti, {"CC"}, Dim, {"dimCC"}, "dim.1", JoinKind.LeftOuter),
#"Expanded dim.1" = Table.ExpandTableColumn(Merged, "dim.1", {"dimCC", "dimFY"}, {"dimCC", "dimFY"}),
#"Grouped Rows" = Table.Group(#"Expanded dim.1", {"CC", "FY"}, {{"dim.1", each _, type table [CC=number, FY=text, Dim=table, dim.1=table]}}),
Add_dim1FilteredFinal = Table.AddColumn(#"Grouped Rows", "dim.1_Final", each let
FYFilter=[FY],
dimFYFiltered=List.Select([dim.1][dimFY], each _>FYFilter),
dimFY_Max=List.Min(dimFYFiltered),
dim1FilteredFinal=Table.SelectRows([dim.1],each [dimFY]=dimFY_Max)
in dim1FilteredFinal),
#"Expanded dim.1_Final" = Table.ExpandTableColumn(Add_dim1FilteredFinal, "dim.1_Final", {"dimCC", "dimFY"}, {"dimCC", "dimFY"})
in
#"Expanded dim.1_Final"
