Building Org Structure - Parent_Child Structure


Parent-child structures are quite tricky in EM, but you can get very powerful results.
You can even simulate a reccursive outer-join.
If you use the Repeat transformation and make a self join in the loop, you'll get a relative path because you make a self join on the result of the previous iteration.
If you want a "classical" structure, you need to join the result of the repeat iteration with the source table

This is explained in the project shared on this link

it uses the accounts tree from adventure works DWH

I made a few changes in this one to get the flattened dimension by levels
EM repeat with table expanded (7.5 KB)

You can customized with filters, tests or calculation within the loops. This is where the power of the Repeat transformation appears. You can customize a DFS with selecting the path that gives flatter structure or make a bottom-up then top down exploration:

  • A -> B -> C
  • E -> D -> C
  • F -> B
  • A -> B -> C -> D -> E
  • A -> B -> F


1 Like