Setting Tree Performance Options
PS/nVision and PeopleSoft Tree Manager each provide a number of techniques and tuning options that can dramatically improve reporting performance. These performance enhancement techniques apply to both query-based and ledger-based layouts.
To change the technique used for a given tree, you can specify the technique using the Tree Performance Options dialog box in PeopleSoft Tree Manager. To override any Tree Manager settings and specify the technique used for a particular report layout, you specify the technique using the Tree Performance tab on the Layout Options dialog box in PS/nVision.
The tree performance options enable you to control the database access techniques PS/nVision uses to implement tree criteria for your report. These options can have a dramatic effect on how fast your reports run.
In setting these options, work with your database administrator to determine the best options, and to ensure that indexes are tuned for the SQL techniques selected. You should be prepared for some trial and error to find the best settings for your data and reporting requirements.
Note: The performance-tuning information presented here is intended for database administrators and advanced PS/nVision users who understand how PS/nVision accesses relational databases. The techniques discussed are not useful for casual users or for customizing performance on a workstation-by-workstation basis.
Access the Tree Performance tab from Excel, select ; and then select the Tree Performance tab.
Image: PeopleSoft nVision Layout Options dialog box: Tree Performance tab
This example illustrates the fields and controls on the PeopleSoft nVision Layout Options dialog box: Tree Performance tab.
Tree performance options are saved in the sheets named NvsTree.treename for each specified tree.
You are setting performance options for a specific layout, one tree at a time. Optimum performance often is achieved using different techniques for different trees, depending on the nature of those trees and the way each tree is used in the report.
If you do not specify the tree performance options for a tree used in a report layout, and no performance options are defined in Tree Manager for that tree, PS/nVision uses the same SQL techniques used in the past on your database platform.
|
Field or Control |
Definition |
|---|---|
| Join to tree selector |
Select to include the tree selector table in the From clause and use join criteria to select the appropriate rows from the fact table. This method is sometimes used by PS/nVision even when another method is specified, if tree node information is needed to produce the report. |
| Suppress join; use literal values |
Select to eliminate a SQL join by retrieving the detail ranges associated with the selected node and coding them in the Select statement. If you select the suppress join technique, but PS/nVision cannot use it because of the need to group results by tree node, it will automatically use the join method you select (that is, either static or dynamic). However, if PS/nVision can use the suppress join technique, it ignores the selector options for this tree. Therefore, pick a selector technique in addition to selecting the suppress join option. Note: This option is not available for use with winter trees. |
| Sub-SELECT tree selector |
Select to add the tree selector to the From list of the main query. The tree selector criteria and its relation to the data in the main query is within an Exists clause in the Where portion of the main query. |
| Static Selectors |
Select to build a selector that represents the entire tree and remains valid until the tree changes. |
| Dynamic Selectors |
Select to creates a new tree selector for use in a section of a single report. The dynamic selector represents just the requested nodes. |
| Single values |
Used only with dynamic selectors. Select to cause PS/nVision or PeopleSoft Query to build a selector using the individual detail values (from the detail table specified in the tree structure) that fall within the detail ranges of the selected nodes. |
| Range of values (>= <=) |
For a tree with ranges of values, select to make the selectors more compact (fewer rows) and less likely to become obsolete as detail values are added. For some database optimizers, the syntax “fieldname >= RANGE_FROM_nn AND fieldname <= RANGE_TO_nn” gets a better access plan than BETWEEN. Note: If you specify one of the range syntax options, but the tree has no ranges, PS/nVision uses the single-value syntax (field = L.RANGE_FROM_nn). |
| Range of Values (BETWEEN) |
Select to use the syntax “fieldname BETWEEN RANGE_FROM_nn AND RANGE_TO_nn”. This choice is best for ranged selectors on most database platforms. |
| Non-specific node criteria (above 2 billion) |
Select to prevent the optimizer from selecting the driving criteria field based on how inclusive the node number criteria are. This option has been used on DB2 when criteria for multiple trees were present. Unless you are a DB2 customer who has tuned your database around this extra criteria, we recommend that you not use this option. |
Note: Trees with a mixture of dynamic detail and range detail are not supported by nVision. Reports generated using such trees may not be accurate.
In certain cases, PS/nVision may override the specified tree SQL technique. The dynamic selector technique is not used when a field has tree criteria in multiple dimensions (for example, both row and column), or when a field has the same tree criteria in multiple places (for example, in both sheet level and row level). This can also happen at DrillDown time if criteria for a field are inherited from multiple dimensions (for example, the scope and column) of the parent report. With the dynamic selector technique unavailable, PS/nVision uses either the suppress join technique (if requested and if feasible) or the static selector technique.
You cannot select the single value option with static selectors because the static selector remains unchanged until the tree changes. However, the addition of single values to the fact table, which the tree detail is based on, doesn’t affect a tree change. The single-value options exist for dynamic selectors and for enabling you to control the syntax used with ranged selectors.
PS/nVision ignores the suppress join technique if specified for a tree with summary ChartField node criteria. Summary ChartField nodes, or detail values in summary trees, are tree nodes from a detail tree rather than values from a database field. In addition, PS/nVision does not support translation of summary ChartField nodes when drilling down to the detail ledger, so we recommend that you use the summary tree criteria.
The suppress join technique is available for reports based on summary trees, as long as tree node information is not needed to group the result. It may be possible to recode some reports that use detail value criteria for summary trees for performance reasons.
These performance-enhancement techniques are not used when retrieving labels (such as account descriptions). Labels for detail fields associated with tree criteria are retrieved using static selectors. The SQL code used to retrieve labels is defined at a different level from the SQL used to retrieve amounts, so it isn’t possible to use the same dynamic selectors for both.
Because criteria from multiple rows and columns are combined with the instance (scope) criteria in a single Select statement, SQL statements generated by PS/nVision can be long and complex. While current releases of PS/nVision no longer enforce a statement size limit, every database platform has a maximum statement size, and even statements shorter than the maximum may be inefficient. You control statement size through judicious use of the performance options.
Here are the common causes of oversized SQL statements:
Use of the suppress join technique on a tree (or trees) from which nodes representing too many detail ranges are requested.
Suppressing a join can be very useful, but is recommended only when criteria from a given tree require a relatively short list of detail values or ranges.
Use of static selectors with a very long list of nodes.
PS/nVision combines node number ranges for sibling nodes where possible, so it takes lots of nodes to exceed the limit. Use of dynamic selectors makes the SQL much shorter.
An extremely long list of detail values.
The messages that indicate a statement is too long vary from platform to platform. For statements made long by tree criteria, the most successful solution is generally to use the dynamic selectors technique on one or more of the trees involved.
If you use the dynamic selector technique heavily for certain criteria fields, you should try an index on that field’s selector table that is optimized around this technique. For example, a ACCOUNT is a six-character field (meaning its selector table is PSTREESELECT06) and you plan to use dynamic selectors, with single values, for the ACCOUNT trees on most of your production reports. You should create an index on PSTREESELECT06 on SELECTOR_NUM and RANGE_FROM_06, since these are the only fields that will appear in the Where clause with single-value dynamic selectors. But also note that:
Other fields that are the same size may use the same selector table, so you might not want to eliminate an index if removing it would penalize those reports.
Although only SELECTOR_NUM and RANGE_FROM_06 will appear in the Where clause, TREE_NODE_NUM may appear in the Select list (if PS/nVision needs to Group By tree node).
An index that includes this field as well would enable index-only access (that is, access with no need to read the data table) when using this selector.
Index use varies depending on the optimizer and the volume and distribution of data, so experiment to get optimum results.