Understanding Tree and Recursive Hierarchy Processing
This section discusses:
Trees and recursive hierarchies.
OWE tree flattener versus MDW tree denormalizer.
Hierarchies that are supported by the tree and recursive hierarchy process.
Denormalized tree result balancing.
Skip levels.
Tree and recursive hierarchy source tables.
Multilanguage Support for Relationship and Hierarchy Tables.
Trees and Recursive Hierarchies
PeopleSoft transaction applications store hierarchical structures in the form of trees and recursive hierarchies. In PeopleSoft applications, a recursive hierarchy is a data hierarchy in which all levels of data are from the same data table, and the parent-child relationships between levels are defined in the same source table. That is, recursive hierarchies are generic two-column tables, with the columns representing parent and child.
However, in the MDW, PeopleSoft hierarchical structures, such as trees, recursive hierarchies must be in denormalized form. This enables efficient data query, as well as integration with third-party business intelligence tools. PeopleSoft's tree and recursive hierarchy processing provides the functionality to denormalize trees and recursive hierarchies for multidimensional reporting.
The Tree and Recursive Hierarchy process populates existing relationship and hierarchy tables, which are the source for business intelligence reporting. Unlike the original hierarchy structure—such as tree or recursive hierarchy—that the utility processes, the relationship table contains parent-child relationships within the structure not only to the direct children, but also to the indirect children of a node in the hierarchy. The denormalized structure enables you to use one simple join to access all lower-level entities within a hierarchy that are related directly or indirectly to a particular entity. For this reason, a relationship table is frequently used to facilitate further processing of a fact table, such as aggregation, or to integrate with a third-party reporting tool.
The extract, transform, and load (ETL) process that you use to create input tables for business intelligence reporting combines with the ETL Tree and Recursive Hierarchy process, enabling you to flatten and denormalize your data in a single process. You run the Tree and Recursive Hierarchy process at the same time you that run the ETL process to populate the MDW.
Note: The Tree and Recursive Hierarchy process cannot process some invalid trees. Specifically, it cannot process a tree that refers to a node that does not exist in the node table, as specified in the tree structure definition, and a tree that refers to a leaf that does not exist in the detail table, as specified in the tree structure definition.
OWE Tree Flattener Versus MDW Tree Denormalizer
This section details the differences between the MDW Tree and Recursive Hierarchy ETL utility and the OWE Tree Flattener utility. Understanding the differences in how these two utilities are used can help you understand why two separate tree processing utilities are necessary in EPM.
|
Subject |
MDW Tree Denormalizer |
OWE Tree Flattener |
|---|---|---|
|
Technology Platform |
Based on ETL technology. |
Based on Application Engine technology for seamless integration with application processing that is also based on the Application Engine. |
|
Supported Types of Hierarchical Structures |
EPM and source database trees, recursive hierarchies. |
Only EPM trees. |
|
Usage |
Preparing hierarchical data for MDW reports, as well as facilitating data transformation by ETL maps. Warehouse ETL maps use the Tree and Recursive Hierarchy ETL utility to enable seamless integration. |
Used by Application Engine-based applications to facilitate further data processing. Application Engine-based applications use Application Engine-based tree flattener to enable seamless integration. |
The PeopleSoft Tree and Recursive Hierarchy process has two parts: tree flattener and tree denormalizer. First, the process flattens a tree or recursive hierarchy into a relationship table. Next, the process denormalizes the data further into a hierarchy table. Although the processes are sequential, not all tree or recursive hierarchy tables must be denormalized into a hierarchy table. Thus, this step is optional. For example, you may not need to denormalize a hierarchy if you are not using it for business intelligence reporting, but only to facilitate fact data processing, as in aggregating data.
PeopleSoft trees and recursive hierarchies relate each node in a hierarchy only to its direct parent or child. Data stored in this way makes it difficult to access non-subsequent child notes (the "grandchildren," or further removed generations) of a hierarchy. The relationship table, which is the result of the flattening part of the Tree and Recursive Hierarchy process, makes all generations related to a specific node easily accessible by associating each node in a hierarchy to any of its descendents, direct or indirect.
The output of the denormalization part of the Tree and Recursive Hierarchy process is a hierarchy table. A hierarchy table format associates the lowest level nodes to all of its parents, direct or indirect, in a row of data. That is, the data in a hierarchy table is denormalized such that a node relationship for a particular path within a tree or recursive hierarchy is represented in one row.
The Tree denormalizer process converts trees into a multicolumn data format so that they can be used by your selected business intelligence reporting tool. The output of the tree flattener portion of the process is the input to the tree denormalizer portion of the process. When you process a dimension, you must run the tree flattener and the tree denormalizer in sequential order. When you process a fact, if the fact uses a tree as its source, usually only the tree flattener is required.
You can control the Tree and Recursive Hierarchy process by specifying the hierarchy output table name for each tree or recursive hierarchy. If you do not specify a hierarchy output table name (Hierarchy Record Name), the denormalization process does not run, and the tree or recursive hierarchy is not denormalized.
Note: PeopleSoft Analytic Applications use a different ETL process for flattening hierarchical data. Do not confuse that process with the ETL process for business intelligence reporting described here.
Hierarchies Supported by the Tree and Recursive Hierarchy Process
This section reviews the hierarchies supported by the tree and recursive hierarchy process.
Source Database Tree
Source database trees are trees that exist in the source databases that supply data to the EPM warehouses. The source database tree is different from EPM tree, such that tree processing for a source database tree must consistently use the tree definition and underlying data from the source database that has been mirrored in the EPM OWS layer.
The following table provides a list of source tables in the OWS that contain source database tree definitions:
|
OWS Table Name |
Source Table in Source Database |
Description |
|---|---|---|
|
PS_S_TREESTRCT |
PSTREESTRCT |
Tree Structure table |
|
PS_S_TREEDEFN |
PSTREEDEFN |
Tree Definition table |
|
PS_S_TREENODE |
PSTREENODE |
Tree Node table |
|
PS_S_TREELEAF |
PSTREELEAF |
Tree Leaf table |
|
PS_S_TREE_NODE_TBL |
PS_TREE_NODE_TBL |
Tree Node Definition table |
In addition to the source database tree definition tables that are listed in this list, the underlying data tables for trees are also used as the source for the source database tree processing. You must retrieve the name of the underlying data tables from the tree structure definition table; you will be asked to associate the data table for nodes and leaves when you create your trees. These data tables must already exist in the EPM OWS.
Sometimes the OWS data table name is not the same as the original data table name in the source database. You must refer to metadata console tables PS_MDC_JOB_SRC_REC and PS_MDC_JOB_TGT_REC to associate the OWS table name to its original name as it is found in the source database.
Source Database Recursive Hierarchy
The source table for a relationship or hierarchy table that is based on a recursive hierarchy of the source database data is the OWS table that is the mirror of the source database recursive hierarchy table. One example of the source database recursive hierarchy is the OWS Campaign table: PS_RA_CAMPAIGN.
EPM Tree
EPM trees are typical PeopleSoft trees. They are created within the EPM database and are viewable through the PeopleSoft Tree Manager. The following table provides a list of EPM tables that contain tree definitions:
|
Table Name |
Description |
|---|---|
|
PSTREESTRCT |
Tree Structure table |
|
PSTREEDEFN |
Tree Definition table |
|
PSTREENODE |
Tree Node table |
|
PSTREELEAF |
Tree Leaf table |
|
PS_TREE_NODE_TBL |
Node Definition table |
In addition to the EPM tree definition tables, the underlying data tables for the trees are also used as the source for the EPM tree processing. The name of the underlying data tables can be found in the tree structure definition table.
EPM Recursive Hierarchy
The difference between the EPM recursive hierarchy and the source database recursive hierarchy is that EPM recursive hierarchy stores its recursive hierarchy data in EPM OWE tables, rather than the copy of the source database recursive hierarchy table in the OWS.
Denormalized Tree Result Balancing
A tree is balanced if all of its branches, or paths, are the same length. For example, if one path of a balanced tree is three levels deep, then all of the paths in the tree must be three levels deep. An unbalanced tree has paths of varying length.
Some business intelligence tools, especially ROLAP tools, require that the denormalized dimension tables in the MDW be balanced to use data effectively. If you use a denormalized table for certain third-party business intelligence reporting, you must balance the hierarchy such that no columns contain blanks in the denormalized table. Because not all business intelligence tools require denormalized data to be balanced, the balancing process is optional. Because balancing occurs during denormalization, it has no impact on the tree flattening process.
If you choose to perform balancing, you can select up-balancing or down-balancing. Up-balancing is replicating detail data to a higher level. Down-balancing is propagating the lowest level nodes in a tree down to the node level next to the detail.
As a result of balancing an unbalanced tree, the description field for the newly created nodes contains a specific notation. This notation is <dd>~, where <dd> is the two-digit level number, for example 03 for level three, and ~, which is the special character that you select for the Hierarchy Balancing Infix field on the Hierarchy Group Definition page.
Note: The balanced node IDs remain the same as their original values.
The balancing process requires up to two parameters on the Hierarchy Group Definition:
The flag to indicate that up-balancing, down-balancing, or no balancing process is to be performed.
The special character to indicate that a node is introduced as a result of the balancing process.
If no balancing is required, you do not populate this field.
Balancing Example
To provide an example of the balancing process, consider a simple tree with two levels: a parent node named auto and a child node named car. If the selected special character is ~, then these are the balancing results.
Balancing up:
|
E_ID |
E_Desc |
L31_ID |
L31_Desc |
... |
L2_ID |
L2_Desc |
L1_ID |
L1_Desc |
|---|---|---|---|---|---|---|---|---|
|
C |
Car |
C |
31~Car |
- |
C |
02~Car |
A |
Auto |
Balancing down:
|
E_ID |
E_Desc |
L31_ID |
L31_Desc |
... |
L2_ID |
L2_Desc |
L1_ID |
L1_Desc |
|---|---|---|---|---|---|---|---|---|
|
C |
Car |
A |
31~Auto |
- |
A |
02~Auto |
A |
Auto |
No balancing:
|
E_ID |
E_Desc |
L31_ID |
L31_Desc |
... |
L2_ID |
L2_Desc |
L1_ID |
L1_Desc |
|---|---|---|---|---|---|---|---|---|
|
C |
Car |
- |
- |
- |
- |
- |
A |
Auto |
Skip Levels
Trees with strictly enforced levels require that each path of the tree has the same depth. You can skip a level if a portion of the hierarchy does not have nodes at that level. For example, one path in a tree may have levels A, B, C, and D, and another path may have levels A, C, and D (skipping level B).
Similar to tree balancing, skip level handling produces synthetic, or artificial, node entries to fill the gap in a denormalized table. For some business intelligence tools, especially ROLAP tools, you must close the gap produced by a skipped level to use the denormalized table effectively.
Because not all business intelligence tools require a skipped level to be closed, skipped level handling is optional. You use the same flag that indicates up-balancing or down-balancing for tree balancing to indicate processing of skip levels. The special mark for nodes that result from skip level processing is applied to the description field. The special mark is <dds>, where <dd> is the two-digit level number and <s> is the special character that you enter in the Skip Level Infix field on the Hierarchy Group Definition page.
Note: The special mark templates that you use for skip level balancing and for regular balancing can be the same or different than the other. For example, you can use <ddb> to refer to the result of balancing and <dds> to refer to the result of resolving a skip level, where dd refers to the level number, such as 03, b refers to the balancing infix character, such as ~, and s refers to the skip level infix character, such as #.
Skipping Levels Example
Image: Skip level summer tree
To provide an example of the skip-level process, consider the following summer tree:
The following table represents the tree flattener result:
|
Tree Node |
Entity ID |
Parent Level Number |
Child Level Number |
|---|---|---|---|
|
A |
F |
1 |
0 |
|
A |
G |
1 |
0 |
|
A |
H |
1 |
0 |
|
B |
F |
2 |
0 |
|
B |
G |
2 |
0 |
|
C |
F |
3 |
0 |
|
D |
G |
3 |
0 |
|
E |
H |
3 |
0 |
If the selected special character for balancing is ~, and the selected special character for skip-level handling is #, and the option is down balancing, then the skip level result the following result occurs:
|
- |
Row 1 |
Row 2 |
Row 3 |
|---|---|---|---|
|
Ent_ID |
F |
G |
H |
|
Ent_Dec |
F Description |
G Description |
H Description |
|
L31_ID |
C |
D |
E |
|
L31_Desc |
31~C Description |
31~D Description |
31~E Description |
|
... |
- |
- |
- |
|
L4_ID |
C |
D |
E |
|
L4_Desc |
04~C Description |
04~D Description |
04~E Description |
|
L3_ID |
C |
D |
E |
|
L3_Desc |
C Description |
D Description |
E Description |
|
L2_ID |
B |
B |
A |
|
L2_Desc |
B Description |
B Description |
02#A Description |
|
L1_ID |
A |
A |
A |
|
L1_Desc |
A Description |
A Description |
A Description |
Note: Due to space limitation, this example is rotated 90 degrees, with the table columns appearing in the rows.
Tree and Recursive Hierarchy Source Tables
The Tree and Recursive Hierarchy process can use as its source:
A tree or recursive hierarchy originating in the source database and mirrored in the OWS.
A tree or recursive hierarchy originating from the EPM database OWE.
Note: The Tree and Recursive Hierarchy process cannot process trees that contain a combination of dynamic details and range details. This combination may yield incorrect reporting results when it is used with business intelligence tools.
Tree and Recursive Hierarchy Source Tables
Recursive hierarchy tables are typically data tables; therefore, bringing them into the EPM OWS is similar to the process of bringing any source data table into the OWS using the source to OWS ETL jobs. Before you run the tree denormalizer part of the Tree and Recursive Hierarchy process, if you are using the source database's trees or recursive hierarchies, you must either first bring your source database tree and recursive hierarchy definition and structure tables into EPM OWS, or you must ensure that your EPM trees and recursive hierarchies exist in the EPM database.
This table lists the PeopleSoft tree source tables that you bring into the OWS before running the Tree and Recursive Hierarchy process:
|
Source Database Tree Metadata Records |
EPM OWS Tree Metadata Records |
|---|---|
|
PSTREESTRCT |
S_TREESTRCT |
|
PSTREESTRCTLANG |
S_TREESTRCTLANG |
|
PSTREEDEFN |
S_TREEDEFN |
|
PSTREEDEFNLANG |
S_TREEDEFNLANG |
|
PSTREENODE |
S_TREENODE |
|
PSTREELEAF |
S_TREELEAF |
|
TREE_NODE_TBL |
S_TREE_NODE_TBL |
|
TREE_NODE_LANG |
S_TREE_NODE_LNG |
Multilanguage Support for Relationship and Hierarchy Tables
If your EPM database supports multiple languages, then you may need to apply multilanguage capability in your relationship and hierarchy tables. You must have the language tables and also the corresponding outrigger tables. Language table names have an L prefix. Therefore, the name of your relationship language record is always prefixed with LR_, while the name of your hierarchy language record is always prefixed with LH_. The naming standard for outrigger tables is O prefix; therefore, OR_ is the prefix for your relationship outrigger record, and OH_ is the prefix for your hierarchy outrigger table.
The multilanguage support for relationship and hierarchy tables are not built-in within the predelivered EPM warehouses. You must extend the warehouse by creating maps that populate the language and outrigger tables. You must also modify your reports to use the relationship and hierarchy tables, as well as their language and outrigger tables.