Understanding PS/nVision SQL

PS/nVision produces a great variety of reports from multiple database tables. The SQL statements it generates are not overly complex but are sensitive to the performance of the underlying database, especially in the following areas:

• Large tables (ledgers often have millions of rows) make efficient use of essential indexes.

• The use of trees and reporting (security) views causes multiple tables to be joined. The database’s efficiency in processing these joins dictates most PS/nVision performance.

• Most PS/nVision aggregate queries are defined with minimal built-in criteria and could tire your database server if executed without the added criteria of a PS/nVision layout.

Unlike traditional batch-reporting tools, PS/nVision supports interactive, focused reporting using a probing or querying approach to database access. PS/nVision queries tend to be more numerous than traditional report writers but also more focused on the specific data the user needs to see.

Much of these topics focus on the performance aspects of retrieving information from ledgers for financial reporting. However, most of the information is equally applicable to other types of fact tables, particularly when trees are used to retrieve the data.

PS/nVision often relates tree node criteria to data tables by joining the data table to a tree selector table. This selector table contains a row for every detail range defined for the tree in PeopleSoft Tree Manager and is keyed by SELECTOR_NUM (a system-generated constant number for all the rows representing a particular tree) and the tree node number. Because some database platforms join tables efficiently only if the field sizes match, we use up to 30 selector tables, one for each supported field length. Each selector table has RANGE_FROM_nn and RANGE_TO_nn columns matching the corresponding field size.

The following is a typical Select statement for selection via nodes on a single tree.

SELECT L.TREE_NODE_NUM, SUM(POSTED_TOTAL_AMT)
FROM PS_LEDGER A, PSTREESELECT06 L
WHERE A.LEDGER='ACTUALS'
  AND A.FISCAL_YEAR=1991
  AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 9
  AND A.ACCOUNT>=L.RANGE_FROM_06
  AND A.ACCOUNT<=L.RANGE_TO_06
  AND L.SELECTOR_NUM=198
  AND (L.TREE_NODE_NUM BETWEEN 1612345 AND 3098765
      OR L.TREE_NODE_NUM BETWEEN 3512345 AND 4098765)
  GROUP BY TREE_NODE_NUM

The bold part of this statement accomplishes the tree criteria selection. If the report had tree criteria for other fields, their selector tables would be added to the From list and similar Join criteria to the Where clause. The Group By clause returns an answer row for each node that has a detail range attached to it; these node numbers are used to post amounts from the answer set into the appropriate rows or columns of the report.

PS/nVision tries to retrieve the data for each report instance with as few Select statements as possible. It examines all row and column criteria to determine which can be combined. It then builds a Select statement to retrieve each intersection of a combined group of rows with a combined group of columns. The following built-in rules should be understood when you design indexes:

• Different ledgers cannot be combined.

• Different TimeSpans cannot be combined.

• nPloded rows or columns cannot be combined with non-nPloded rows or columns.

• To be combined, multiple rows or columns must have criteria for the same set of fields, and each field’s criteria must be of the same type. (For example, selected tree nodes cannot be combined with selected detail values).

• If criteria for a field are specified by tree node, they can be combined only if they use the same tree.

• If the combined rows or columns have identical criteria for a particular field, the criteria are included in the Where clause, but a Group By clause on that field is not required. But if different rows or columns in the group have different criteria, PS/nVision adds this field (or the corresponding tree node number) to the Group By clause to retrieve a value for use in posting the answer set to the appropriate rows or columns of the report.

• A single Select statement can retrieve amounts for multiple combined rows and columns.

• Different scope instances are retrieved with separate Select statements.

Some additional rules apply for layouts defined using queries rather than ledgers:

• Different queries are not combined.

• References to different answer columns in the same query can be retrieved with a single Select statement if they meet the above tests.

To examine the SQL produced by PS/nVision, you can capture the statements in one of two ways:

• Use the Show Report SQL option in the PeopleSoft nVision Options dialog box. This option causes PS/nVision to display each Select statement used to retrieve labels or amounts in a dialog box. You can select the text with the mouse, then copy (CTRL+C) it to the Clipboard, then paste (CTRL+V) the text into another application, such as Notepad, WordPad, an interactive SQL tool, or a text editor. You can then save the text to a file or work with it within the application.

  Note: To capture the SQL without waiting for it to execute, you can also select the Suppress Amount Retrieval option. PS/nVision generates all the SQL but does not execute Select statements for amounts.

• Turn on the PeopleTools SQL trace through the Trace tab on the Configuration Manager. The SQL statements executed by PeopleTools will be written to a file that you specify.