Layout_Combined AddKids(Layout_Combined L, base_kids R) := TRANSFORM SELF.Accounts := L.Accounts + ROW({R.Account,R.OpenDate,R.IndustryCode, R.AcctType,R.AcctRate,R.Code1, R.Code2,R.HighCredit,R.Balance}, Layout_Accounts); SELF := L; END; base_combined := DENORMALIZE( base_people_dist, base_kids_dist, LEFT.PersonID = RIGHT.PersonID, AddKids(LEFT, RIGHT));
Now that we have separate recordsets of parent and child records, the next step is to combine them into a single dataset with each parent's child data nested within the same physical record as the parent. The reason for nesting the child data this way is to allow easy parent-child queries in the Data Refinery and Rapid data Delivery Engine without requiring the use of separate JOIN steps to make the links between the parent and child records.
To build the nested child dataset requires the DENORMALIZE operation. This operation finds the links between the parent records and their associated children, calling the TRANSFORM function as many times as there are child records for each parent. The interesting technique here is the use of the ROW function to construct each additional nested child record. This is done to eliminate the linking field (PersonID) from each child record stored in the combined dataset, since it is the same value as contained in the parent record's PersonID field.