Building Org Structure - Parent_Child Structure

[cvo]

Greetings,

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

Repeat table as iterate table

Greetings, with the iterate table, we can operate transformations on a reference table. Could we have the same with repeat. To reproduce a ‘with recursive’ SQL query in EM, we currently need to save the reference table as a dset file and import it into the repeated module. the possibility to transfer a table would make process cleaner and maybe improve performance as with the iterate table transformation. Regards

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 levels.zip (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

Regards