1. Implementing Enterprise Structures and General Ledger
  2. Guidelines for Improving the Performance of a Tree Structure

Guidelines for Improving the Performance of a Tree Structure

Tree structures are heavily loaded with data. As a tree management guideline, use the following settings to improve performance of data rendering and retrieval.

  • Row Flattening

  • Column Flattening

  • Column Flattened Entity Objects

  • BI View Objects

Caution: Force flattening handles both row and column flattening. This is applicable for all modes of force flattening, such as, Schedule Force Flattening, Online Force Flattening, and Offline Force Flattening. You must not run row flattening and column flattening separately using force flattening. Running two flattening processes in parallel for the same tree will lead to inconsistent results.

Row Flattening

Row flattening optimizes parent-child information for run-time performance by storing additional rows in a table for instantly finding all descendants of a parent without initiating a CONNECT BY query. Row flattening eliminates recursive queries, which allows operations to perform across an entire subtree more efficiently.

To store row flattened data for the specific tree structure, users can either use the central FND_TREE_NODE_RF table or they can register their own row flattened table. For example, in a table, if Corporation is the parent of Sales Division (Corporation-Sales Division), and Sales Division is the parent of Region (Sales Division-Region), a row-flattened table contains an additional row with Corporation directly being the parent of Region (Corporation-Region).

Column Flattening

Column flattening optimizes parent-child information for runtime performance by storing an additional column in a table for all parents of a child.

To store column flattened data for the specific tree structure, users can either use the central FND_TREE_NODE_CF table or they can register their own column flattened table. For example, in a table, if Corporation is the parent of Sales Division (Corporation-Sales Division), and Sales Division is the parent of Region (Sales Division-Region), a flattened table in addition to these columns, contains three new columns: Region, Sales Division, and Corporation. Although positioned next to each other, the column Region functions at the lower level and Corporation at the higher level, retaining the data hierarchy.

Column Flattened Entity Object

In the absence of a column-flattened table, if you need to generate the business component view objects for your tree structure for the flattened table, use the tree management infrastructure to correctly provide the fully qualified name of the entity object for the column flattened table.

BI View Object

View objects from Business Intelligence can be used as data sources, eliminating the need to create new types of data sources. This field is to store the fully qualified name for the BI view object generated by the tree management for business intelligence reporting and usage The BI view object is a combination of the tree data source and column flattened entity. Using this option prevents data redundancy and promotes greater reuse of existing data, thereby improving the performance of the tree structure.

Search View Object

Specify the full name of the view object for the tree node to ensure that search operations performed on the tree node are efficient.