Understanding Tree and Recursive Hierarchy Process Results

This section discusses tree flattener and tree denormalizer output tables and tree flattener and tree denormalizer results.

Tree Flattener and Tree Denormalizer Output Tables

Running the Tree and Recursive Hierarchy process creates:

A relationship table created by the flattening portion of the Tree and Recursive Hierarchy process.
A hierarchy table (if you run the denormalization portion of the process).

In the hierarchy table:
- The Tree and Recursive Hierarchy process denormalizes each node of a winter tree to the detail level.
- The Tree and Recursive Hierarchy process denormalizes only the leaves in a summer tree to the detail level.
- An unbalanced level, skip level, or both, has a special character infix, which you specified on the Hierarchy Group Definition page, for example ~ , concatenated with the tree node ID in the denormalizer output table TRDN.

Output Relationship Tables

The output table structures of the MDW flattened tree or recursive hierarchy relationship data are similar for all relationship tables, except for the keys, which indicate whether the trees are SetID, business unit, or user-defined based.

Relationship tables capture the parent-child relationship between an entity and its direct or indirect children in a hierarchy. For this reason, relationship tables always have a parent column and a child column. In addition, because the EPM tree and recursive hierarchy process also handles source database trees and recursive hierarchy and all SetID, business unit, and user-defined based trees, the key sets are adjusted according the tree or recursive hierarchy that is being flattened. Also, certain keys are not required because they may not be relevant, depending on the source type, tree, or recursive hierarchy. The flag, NODE_DET_FLAG, is used to indicate whether the entry is a node or a detail in the tree or recursive hierarchy. This field has translate values, where D indicates that the entry is a detail and N indicates that the entry is a node.

The MDW relationship table is also effective-dated. When the source is a tree, the effective date of the relationship data is the tree effective date. When the source is a recursive hierarchy, the effective date of the relationship data is the recursive hierarchy process date.

Relationship tables for recursive hierarchies also store the driver record description. The driver record is the recursive hierarchy table or the underlying data table, in the case of recursive hierarchies that are based on the F0150 table. The record description is obtained from the PeopleSoft record definition table (PSRECDEFN).

Because relationship tables have a description field, the hierarchy processing creates a related language table for the relationship table. This related language table has all of the keys of the base relationship table, plus one additional key, LANGUAGE_CD. (The non-key field that is in the related language table for the relationship table is the description field.)

Note: Relationship tables always have a prefix R_ to identify them. The following table represents an output relationship table for SetID based trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
SETID	Char	5	K	Y	N	N	None
SRC_SETID	Char	5	K	N	N	N	None
TREE_NAME	Char	18	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
TREE_NODE	Char	30	K	Y	N	N	None
CHILD_NOD_DTL	Char	30	K	Y	N	N	None
TREE_NODE_DESCR	Char	50	-	N	N	N	None
NODE_DET_FLAG	Char	1	-	N	XLAT	N	None
RANGE_FROM	Char	30	-	N	N	N	None
RANGE_TO	Char	50	-	N	N	N	None
TREE_LEVEL_NUM	Number	3	-	N	N	N	None
CHILD_LEVEL_NUM	Number	3	-	N	N	N	None
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output relationship table for business unit based trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
BUSINESS_UNIT	Char	5	K	Y	N	N	None
SRC_BUSINESS_UNIT	Char	5	K	N	N	N	None
TREE_NAME	Char	18	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
TREE_NODE	Char	30	K	Y	N	N	None
CHILD_NOD_DTL	Char	30	K	Y	N	N	None
TREE_NODE_DESCR	Char	50	-	N	N	N	None
NODE_DET_FLAG	Char	1	-	N	XLAT	N	None
RANGE_FROM	Char	30	-	N	N	N	None
RANGE_TO	Char	30	-	N	N	N	None
TREE_LEVEL_NUM	Number	3	-	N	N	N	None
CHILD_LEVEL_NUM	Number	3	-	N	N	N	None
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output relationship table for user-defined (no key) trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
SETCNTRLVALUE	Char	5	K	N	N	N	None
TREE_NAME	Char	18	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
TREE_NODE	Char	30	K	Y	N	N	None
CHILD_NOD_DTL	Char	30	K	Y	N	N	None
TREE_NODE_DESCR	Char	50	-	N	N	N	None
NODE_DET_FLAG	Char	1	-	N	XLAT	N	None
RANGE_FROM	Char	30	-	N	N	N	None
RANGE_TO	Char	30	-	N	N	N	None
TREE_LEVEL_NUM	Number	3	-	N	N	N	None
CHILD_LEVEL_NUM	Number	3	-	N	N	N	None
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output relationship table for SetID based recursive hierarchy trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
SETID	Char	5	K	Y	N	N	None
SRC_SETID	Char	5	K	N	N	N	None
RECORD	Char	30	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
NODE	Char	30	K	Y	N	N	None
CHILD_NOD_DTL	Char	30	K	Y	N	N	None
NODE_DESCR	Char	50	-	N	N	N	None
NODE_DET_FLAG	Char	1	-	N	XLAT	N	None
LEVEL_NUM	Number	3	-	N	N	N	None
CHILD_LEVEL_NUM	Number	3	-	N	N	N	None
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following represents an output relationship table for business unit based recursive hierarchy trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
BUSINESS_UNIT	Char	5	K	Y	N	N	None
SRC_BUSINESS_UNIT	Char	5	K	N	N	N	None
RECORD	Char	30	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
NODE	Char	30	K	Y	N	N	None
CHILD_NOD_DTL	Char	30	K	Y	N	N	None
NODE_DESCR	Char	50	-	N	N	N	None
NODE_DET_FLAG	Char	1	-	N	XLAT	N	None
LEVEL_NUM	Number	3	-	N	N	N	None
CHILD_LEVEL_NUM	Number	3	-	N	N	N	None
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output relationship table for non business unit and non SetID based recursive hierarchy trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
RECORD	Char	30	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
NODE	Char	30	K	Y	N	N	None
CHILD_NOD_DTL	Char	30	K	Y	N	N	None
NODE_DESCR	Char	50	-	N	N	N	None
NODE_DET_FLAG	Char	1	-	N	XLAT	N	None
LEVEL_NUM	Number	3	-	N	N	N	None
CHILD_LEVEL_NUM	Number	3	-	N	N	N	None
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

Output Hierarchy Tables

For hierarchy tables, the keys match the keys of the trees or recursive hierarchy, either SetID, business unit, or user-defined key based. Hierarchy tables capture all of the parent IDs and descriptions of a detailed entity in a hierarchy. For this reason, they always have an entity ID as a key. The prepackaged Tree and Recursive Hierarchy ETL utility supports data processing for a hierarchy that is up to 32 levels deep, including one level for details. Therefore, it has 32 ID and description columns. The ID column is named L<n>_ID, where n is the hierarchy level. The description column is named L<n>_DESC.

Hierarchy tables have a description field for each of the supported levels for denormalization. (PeopleSoft supports 32 levels for a table.) Except for the entity ID and description, which are the lowest level, the ID and description are named L<n>_ID and L<n>_DESCR, where n is between 1 and 31, which is one less than the number of supported levels.

The hierarchical data is also effective-dated. When the source is a tree, the effective date of the hierarchy data is the tree effective date. When the source is a recursive hierarchy, the effective date of the hierarchy data is the recursive hierarchy process date. Like the relationship tables, hierarchy tables for recursive hierarchy also have the record description as a key. The record is the recursive hierarchy driver record. The description is obtained from the PeopleSoft record definition table (PSRECDEFN).

Because a hierarchy table has a description field, the process creates the related language table for the hierarchy table. The related language table for the hierarchy table has all of the keys of the base hierarchy table, plus one additional key called LANGUAGE_CD. (The non-key fields that exist in the related language table of the hierarchy table are the description fields.)

Note: Hierarchy tables are always prefixed with H_ to identify them.

The following represents an output hierarchy table for SetID based trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
SETID	Char	5	K	Y	N	N	None
SRC_SETID	Char	5	K	N	N	N	None
TREE_NAME	Char	18	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
DTL_ID	Char	30	K	Y	N	N	None
DTL_DESC	Char	50	-	N	N	N	None
L<n>_ID	Char	30	-	N	N	N	None
L<n>_DESC	Char	50	-	N	N	N	None
…	-	-	-	-	-	-	-
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output hierarchy table for business unit based trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
BUSINESS_UNIT	Char	5	K	Y	N	N	None
SRC_BUSINESS_UNIT	Char	5	K	N	N	N	None
TREE_NAME	Char	18	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
DTL_ID	Char	30	K	Y	N	N	None
DTL_DESC	Char	50	-	N	N	N	None
L<n>_ID	Char	30	-	N	N	N	None
L<n>_DESC	Char	50	-	N	N	N	None
…	-	-	-	-	-	-	-
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output hierarchy table for user defined (no key) trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
SETCNTRLVALUE	Char	5	K	N	N	N	None
TREE_NAME	Char	18	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
DTL_ID	Char	30	K	Y	N	N	None
DTL_DESC	Char	50	-	N	N	N	None
L<n>_ID	Char	30	-	N	N	N	None
L<n>_DESC	Char	50	-	N	N	N	None
…	-	-	-	-	-	-	-
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output hierarchy table for SetID based recursive hierarchy trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
SETID	Char	5	K	Y	N	N	None
SRC_SETID	Char	5	K	N	N	N	None
RECORD	Char	30	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
DTL_ID	Char	30	K	Y	N	N	None
DTL_DESC	Char	50	-	N	N	N	None
L<n>_ID	Char	30	-	N	N	N	None
L<n>_DESC	Char	50	-	N	N	N	None
…	-	-	-	-	-	-	-
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output hierarchy table for business unit based recursive hierarchy trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
BUSINESS_UNIT	Char	5	K	Y	N	N	None
SRC_BUSINESS_UNIT	Char	5	K	N	N	N	None
RECORD	Char	30	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
DTL_ID	Char	30	K	Y	N	N	None
DTL_DESC	Char	50	-	N	N	N	None
L<n>_ID	Char	30	-	N	N	N	None
L<n>_DESC	Char	50	-	N	N	N	None
…	-	-	-	-	-	-	-
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

The following table represents an output hierarchy table for non business unit and non SetID based recursive hierarchy trees:

Field Name	Type	Length	Key	Required	Edit	Prompt	Default
RECORD	Char	30	K	Y	N	N	None
SRC_SYS_ID	Char	5	K	Y	N	N	None
EFFDT	Date	10	K	Y	N	N	None
DTL_ID	Char	30	K	Y	N	N	None
DTL_DESC	Char	50	-	N	N	N	None
L<n>_ID	Char	30	-	N	N	N	None
L<n>_DESC	Char	50	-	N	N	N	None
…	-	-	-	-	-	-	-
DATA_SRC_SYS_ID	Char	5	-	Y	N	N	None

Related Language Tables

You use flattened (relationship) tables and denormalized (hierarchy) tables for business intelligence reporting; therefore, the tables have description fields. Thus, both types of tables have related language tables for multilanguage reporting. If your company does not require multilanguage processing, you do not have to populate the related language tables on the Relationship Record Definition and Hierarchy Record Definition pages. However, you must still define the relationship record name on the Relationship Record Definition page, and if you are running the denormalization part of the process, you must also define the hierarchy record name on the Hierarchy Record Definition page.

The Tree and Recursive Hierarchy process populates related language tables only if you specify a relationship language and outrigger record on the Relationship Record Definition page or a hierarchy language and outrigger record on the Hierarchy Record Definition page.

See Understanding Multilanguage Processing.

Tree Flattener and Denormalizer Results

The output of flattening and denormalizing trees depends on the type of tree: summer or winter, balanced or unbalanced, skip level, and so on.

Summer Tree

Image: Example of a summer tree before processing

This graphic shows an example of a summer tree before processing and without any balancing:

Processing of this tree results in the following relationship table:

Tree Node	Entity ID	Parent Level Number
A	D	1
A	E	1
A	F	1
A	G	1
B	D	2
B	E	2
C	F	3
C	G	3

In a summer tree, the relationship table does not contain a tree node to itself or other node. Tree nodes only relate to the leaves. In addition, the child level numbers are always set to 0.

The following table shows the hierarchy table, without balancing:

Entity	L31	L30	...	L2	L1
D	-	-	-	B	A
E	-	-	-	B	A
F	-	-	-	C	A
G	-	-	-	C	A

Note: In the previous example, the columns for levels 3 through 31 are not populated because the balancing option is turned off. If the balancing option were turned on, levels 3 through 31 would also be populated.

If your relationship or hierarchy tables require multilanguage support, then you must create the outrigger tables for the relationship and hierarchy tables.

Dynamic Summer Tree

Image: Dynamic summer tree

This graphic shows an example of a dynamic summer tree (with relationships between departments and employees) before processing:

The following table represents the Department database table:

Department ID	Department Name
A	HR
B	Benefit
C	Payroll

The following table represents the Employee table:

Employee ID	Department ID	Employee Name
D	B	Jane Doe
E	B	Joe Bloe
F	C	John Who

Processing of this tree results in the following relationship table:

Tree Node	Entity ID	Parent Level Number
A	D	1
A	E	1
A	F	1
B	D	2
B	E	2
C	F	2

The following table shows the hierarchy table, without balancing:

Entity	L31	L30	...	L2	L1
D	-	-	-	B	A
E	-	-	-	B	A
F	-	-	-	C	A

Winter Tree

Image: Example of a winter tree before processing

This graphic shows an example of a winter tree before processing:

This table shows the winter tree relationship table after tree flattening:

Tree Node	Entity ID	Parent Level Number	Child Level Number
A	A	1	1
A	B	1	2
A	C	1	2
A	D	1	3
A	E	1	3
A	F	1	3
A	G	1	3
B	B	2	2
B	D	2	3
B	E	2	3
C	C	2	3
C	F	2	3
C	G	2	3
D	D	3	3
E	E	3	3
F	F	3	3
G	G	3	3

In the relationship table, every node in a winter tree is associated with itself, as well as any nodes that are directly or indirectly under it.

This table shows the winter tree hierarchy after tree denormalizing:

Entity	L3	L2	L1
A			A
B		B	A
C		C	A
D	D	B	A
E	E	B	A
F	F	C	A
G	G	C	A

Recursive Hierarchy

The following table provides an example of a recursive hierarchy table:

Entity ID	Entity Parent ID
1	0
2	1
3	1
4	2
5	2

In a PeopleSoft recursive hierarchy, an Entity Parent ID = 0 implies that the entity is at the top of the hierarchy, if the entity is of numeric field. If the entity is a character field, then the highest-level entity will have Entity Parent ID = blank (that is, a space).

Because the lowest level entities in the hierarchy are of the same type as the parents, we can think of a recursive hierarchy like a winter tree. Therefore, the relationship and hierarchy table output from the Tree and Recursive Hierarchy ETL utility resembles the output format for a winter tree. That is, any node in the recursive hierarchy is associated with itself, as well as any nodes directly or indirectly under it.

Parent Node	Entity	Parent Level Number	Child Level Number
1	1	1	1
1	2	1	2
1	3	1	2
1	4	1	3
1	5	1	3
2	2	2	2
2	4	2	3
2	5	2	3
3	3	2	2
4	4	3	3
5	5	3	3

The following table shows the hierarchy table without balancing:

Entity	L31	L30	...	L3	L2	L1
1	-	-	-	-	-	1
2	-	-	-	-	2	1
3	-	-	-	-	3	1
4	-	-	-	4	2	1
5	-	-	-	5	3	1

Tree Node	Entity ID	Parent Level Number	Child Level Number
A	A	1	1
A	B	1	2
A	C	1	2
A	D	1	3
A	E	1	3
A	F	1	3
A	G	1	3
B	B	2	2
B	D	2	3
B	E	2	3
C	C	2	3
C	F	2	3
C	G	2	3
D	D	3	3
E	E	3	3
F	F	3	3
G	G	3	3

Tree Node	Entity ID	Parent Level Number	Child Level Number
A	A	1	1
A	B	1	2
A	C	1	2
A	D	1	3
A	E	1	3
A	F	1	3
A	G	1	3
B	B	2	2
B	D	2	3
B	E	2	3
C	C	2	3
C	F	2	3
C	G	2	3
D	D	3	3
E	E	3	3
F	F	3	3
G	G	3	3

Tree Node	Entity ID	Parent Level Number	Child Level Number
A	A	1	1
A	B	1	2
A	C	1	2
A	D	1	3
A	E	1	3
A	F	1	3
A	G	1	3
B	B	2	2
B	D	2	3
B	E	2	3
C	C	2	3
C	F	2	3
C	G	2	3
D	D	3	3
E	E	3	3
F	F	3	3
G	G	3	3