PS/nVision SQL Basics

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.