When to use — and when not to use — unoptimized properties in reports
This article describes how and when to use references to unoptimized properties in Report Definition reports.
PRPC stores object information in tables in the PegaRULES database, consolidated into the value of a BLOB (binary large object) column called pzPVStream. To improve performance when retrieving data, PRPC also allows properties to be stored and indexed in separate database columns; the column name is the property name. Data stored in such individual columns is described as being "optimized" or "exposed". PRPC automatically optimizes a small set of standard properties, and developers and DBAs can optimize additional properties as needed to serve reporting functions.
Using the BLOB to store case and work data is highly optimized for performance and flexibility in operations on individual cases or work items.
In PRPC 6.2 SP2 you can reference both optimized and unoptimized properties in Report Definition rules. However, proceed with care when using references to unoptimized properties in reports, because using them can cause system performance to become significantly worse than when you reference only optimized properties.
Suggested Approach
- How to enable using unoptimized properties in reports
- How to reference unoptimized properties in reports
- How to display unoptimized properties as selectable options
- In a single report
- For all new reports in a class
- When it's okay to reference unoptimized properties
- When to avoid referencing unoptimized properties
- The Maximum Elapsed Time control
How to enable using unoptimized properties in reports
If your V6.2 SP2 system is newly installed (not an upgrade), no configuration changes are required.
Report Definitions with references to unoptimized BLOB properties generate SQL statements that call built-in functions (known by Oracle and other database vendors as "user defined functions", or UDFs) that open and access property values directly from the BLOBs stored in the DBMS.
These functions — written, tested, and fully supported by Pegasystems — are automatically installed in the DBMS as you install PRPC V6.2 SP2, or when you upgrade to V6.2 SP2 from an earlier version.
For upgrades, some changes to DBMS permissions may be required to allow the UDFs to work. Check the Upgrade Guide for details, or consult your Pega representative.
How to reference unoptimized properties in reports
Your Report Definition rules can reference unoptimized properties..
You can create and edit the Report Definition rule directly in the Designer Studio, or edit it in the Report Editor in the Case Manager portal.
In either editing mode, when manual entry of property references is available (for instance, when entering the parameters for a calculation or a filter condition), unoptimized properties do not appear (by design) among the entry field's AutoComplete suggestions. However, you can manually enter a valid property reference in the field.
References must be to scalar values: provide an index or page name for each Page List or Page Group in the property reference. For example, to reference the product number for the first line item on a purchase order, you might enter LineItems(1).ProductSKU.
When you save a rule that references an unoptimized property, a warning appears at the top of the form, warning that using unoptimized properties in a report can cause serious performance issues.
How to display unoptimized properties as selectable options
You can have unoptimized properties display in the Data Explorer in the Report Editor, so you can select and use them to populate columns as you do with optimized properties.
For a single report
You can control the display of unoptimized properties in the Data Explorer for any report by editing its rule form in the Designer Studio:
- On the Data Access tab, in the General Data Source Settings section, locate and check the Display Unoptimized Properties in Data Explorer checkbox:
- On the Design tab, make sure the Display in Report Browser checkbox is checked.
- Save the rule.
Unoptimized properties now appear in the Report Editor for this report, in the All Matches column of the Data Explorer. Add a column using an unoptimized property as you would any property or calculation (in V6, SP2, for instance, right-click on the property and follow the dialog to place the column where you want it).
When you save a report that references unoptimized properties, a save confirmation appears to remind you that poor performance may result:
For all new reports in a class
You can enable or disable displaying unoptimized properties for all new reports in a class by modifying the class template reports.
When you create a new report, the system makes a copy of one of two report templates for that class, pyDefaultReport and pyDefaultSummaryReport. The Display Unoptimized Properties in Data Explorer checkbox is unchecked by default in the template rules. Save copies of these template rules for any class and check the checkbox in your copies to enable display of unoptimized properties for all new reports.
When it's okay to reference unoptimized properties
In general, you can use unoptimized properties in reports with modest performance impact under these conditions:
- In a development system, you are designing a report and are not sure yet which properties the report needs.
- In development and demonstration systems with a limited amount of data.
- In a production system, you are working with a report on a class containing a small set of data (no more than 200 rows).
- in reports where the unoptimized data populates columns or is used in calculations, but is not used in filter conditions; and where existing filter conditions clearly limit the result to a few hundred instances.
When to avoid referencing unoptimized properties
Poor performance is likely if you use unoptimized properties:
- in production systems, where the amount of data the report returns can grow large
- as sorting or grouping columns in reports
- in filter conditions
- in reports on classes with more than a few hundred instances
- in reports with joins where the joined result set contains more than a few hundred instances
It is a best practice to optimize properties that are used in reports that may search through or return large amounts of data. The performance "hit" when your report runs affects not just you, but everyone using that instance of PRPC.
The Maximum Elapsed Time control
The resource governor on elapsed time for Report Definitions (the Maximum Elapsed Time in Seconds control on the Data Access tab of the rule form) should terminate report queries with BLOB UDF calls if their execution time within the DBMS exceeds the specified number of seconds. The elapsed time limit works by setting a timeout on the DBMS execute statement. Calls to the UDFs, and their processing time, count against the timeout limit.
However, there is a documented Oracle issue in list reports, where UDF calls in the SELECT clause of generated SQL may not count against the timeout limit on the DBMS. This Oracle issue could cause such reports to run unexpectedly long if the report uses large page sizes and accesses large, complex BLOB columns.