Index Suggestions

Designing indexes for PS/nVision can be difficult because different reports can have different criteria. The following table lists the index columns and suggestions.

Index Column	Suggestions
Fiscal_Year	When using a TimeSpan (required for ledger reporting but optional for queries), PS/nVision always generates an equality for fiscal year (for example, FISCAL_YEAR=1996). If the TimeSpan requires data from multiple fiscal years, then PS/nVision generates multiple Select statements, one for each fiscal year, which makes Fiscal_Year a good candidate for the first column in a multi-column index.
Ledger	When accessing ledger data, PS/nVision retrieves data from only one ledger at a time, so this column is guaranteed to have an equality. Thus, Ledger is a good choice as the second column in a multi-column index.
Business_Unit	If you use the report request option to retrieve data from the requesting business unit only, PS/nVision generates an equality (for example, BUSINESS_UNIT=‘M04’) for this column. If this is the most common way of requesting reports or if you use a scope to get instances by business unit, then use business unit as the second or third column in a multi-column index, especially if you have many business units in the same ledger table. If most of your reporting accesses multiple business units in a single instance, position it in the index as you would any other field.
Accounting_Period	When using TimeSpans, accounting period is specified using either an equality (ACCOUNTING_PERIOD=12) or a range (ACCOUNTING_PERIOD BETWEEN 10 AND 12). If you do a lot of reporting for the current period or other single periods (such as current period a year ago), performance may be improved using an alternate index beginning with accounting period. A good optimizer uses this index only when accounting period has an equality.
Account	In many companies, Account is the field with the highest cardinality. It also has criteria in almost all ledger reports, in part because PS/nVision enforces this rule. Use it as the next index column following the columns that you expect to have equalities.