Self-joining querying

The goal of this example is to add Manager Name and Manager Title columns to an existing Employee dimension table:

  • The EmployeeKey and ParentEmployeeKey columns of the table are used in expressions to self-join the table.
  1. Create a query that retrieves the key columns of the hierarchy and the attribute columns to be added.
    • The existing table has the key columns, Employee Name, and Title.
Figure 127: Existing Employee Dimension
  • The new staging query references the existing dimension table and selects the required columns.
Figure 128: Manager Staging Expression
  • The ParentEmployeeKey from Figure 128 can be joined to the EmployeeKey of Figure 129 to provide access to the Manager columns.
  • The Manager Name and Manager Title columns could optionally be applied in the staging query via Table.RenameColumns(), but in this example the alias is applied within the merge operation.
Figure 129: Manager Staging Query
  • The query is named Managers and load is disabled.
  1. Join the Manager staging query created in step 1 with the existing Employee table and add the Manager Name and Manager Title columns.
Figure 130: Self Join Syntax
  • The Employees query is referenced as the Left table in a Table.NestedJoin() function and joined to the Managers query via a left outer join.
  • The left join is required to retain all employee rows in this scenario, as the Employee table includes one employee that doesn't have a parent employee key: Chief Executive Officer.
  • Given the join on Parent Employee Key to Employee Key, the Manager columns are renamed in the Table.ExpandTableColumn() expression.
Figure 140: Employee Table with Manager Columns
  • The two rows for Rob Walters are due to a Slowly Changing Dimension (SCD) Type 2 process applied in the source database.
  • With the revised table loaded, it's simple to create a manager-employee hierarchy or use the columns separately in visuals with drill up/down capabilities.
Figure 141: Employee Dimension in Matrix Visual
上一章目录下一章