Related link: Overview of Tree Formats
This transform can join several tree-based sources to one common tree, enabling hierarchical data from different source systems to be joined easily. For example, relational data can be converted into a tree using a TreeFH transform with a Jedox dimension coming directly from a Dimension extract. The attributes of the different tree sources are also joined.
For each tree source, it is possible to define a root element and a weight. If this element does not exist in any of the tree sources, it will be created in the root element, and the tree source will be inserted under this root element. Basically, a consolidation is created from this element to the root element(s) of the respective tree source with the given weight.
In many cases, it is not necessary to define such root elements, because the TreeJoin transform detects matching elements of the different input sources. If there is an element with an identical name in both sources, one tree is inserted automatically underneath another tree. If the element names of the sources are disjunct, then parallel hierarchies are created with separate root element(s) for each tree.
Note: ensure that no circular references are created when the data sources are joined. If this is the case, the tree cannot be created.
Example 1: joining two trees with matching elements on one level
A tree join is defined with two input sources. The two sources have the following format:
| Input source 1 | Input Source 2 | |||||||||||||||||
|
|
Since there are matching element names on one level, the trees are joined at this level. The result is a tree in this format:
| Year | Qtr.1 | Jan |
| Year | Qtr.1 | Feb |
| Year | Qtr.1 | Mar |
| Year | Qtr.2 | Apr |
| Year | Qtr.2 | May |
| Year | Qtr.2 | Jun |
Example 2: joining elements with a defined root element
The "Months" tree is to be joined with the "Years" tree:
Note: when the input is coming dynamically from the Source or Transform, but the "Field name" is left empty, the name of the source column/ function is used for the Field name. When the target is manually defined as constant, then the name "constant" is chosen instead of the value from the "Input" field. Adding more than one constant column results in error, because there would be two columns with the name "constant."
Updated July 8, 2024