Class mappings and database tables

Any Pega class that has instances, such as case types, can be mapped to a database table. For example, when users create cases, the system assigns the case an ID and saves the value as an individual row in a database table. When you generate reports, you are retrieving data from rows in database tables. Reports use class mappings to locate the data from one or more database tables.

When designing reports, you need to know which table has the data and how the data is mapped. For example, you may need to create a report that contains information about Candidate cases. These records are instances in the case work class. In the same report, you may also want to include workbasket information about each candidate case. Workbasket records are instances in a workbasket class. The information for each type of information is stored in separate tables. When you combine the information in a report, you use class names to identify in which tables the information is stored.

Pega uses two records to identify the database table the class is mapped to: Database and Database Table.

• A Database record identifies how Pega connects to a specific database for the named database. The record contains connection information so Pega can access the database. The record establishes an alias that can be referenced elsewhere, such as a database table record. Database records can be configured to use either JNDI or JDBC url for the database connection. By default, Pega uses the following standard databases in a database record:

  • PegaRULES maps to a database where all Pega rules and system data are stored.

  • PegaDATA maps to a database where data and work instances are saved.

• A Database Table record identifies a specific table in a specific database, and specifies the corresponding Pega class. Pega uses this record to identify which table to write case data when a user creates or updates a case.

In some situations, you want to have multiple classes store data in the same table. For example, you might have an application with three case types such as Candidate, Onboarding, and Benefits Enrollment, and you want to report on the work statuses of all cases in the application. Rather than create a database table for each case type, you can designate a class as a class group (also referred to as a work pool). Class groups cause the system to store instances of similar or related case types together in a single database table. If you create a report in a specific case type such as Candidate, your report returns only records in the case type. If you create a report in the class group, the report returns all instances in the classes that belong to the class group.

In Dev Studio, the mappings are displayed in the Database Class Mappings landing page. The following example shows the work classes that map to the class group database table pc_TGB_HRApps_Work.

You commonly generate three types of reports: work, assignment, and history. Each type of report uses properties in classes mapped to a variety of data tables.

Work reports

When a case is created, Pega uses standard properties in the Work- base class to define each case. This Work- base class includes properties that describe the following:

• A case identifier (pyID), the work parties participating in a case (pyWorkParty)
• The customer identifier such as an account number (pyCustomer)
• The work status of the case (pyStatusWork)

For more information about standard property rules, see the Help topic Standard property rules.

Assignment reports

Cases requiring user interaction are assigned to a user during processing. Each time a case is assigned, Pega creates an assignment object. When the case is completed and advances to the next assignment, Pega creates another object. If the assignment is routed to an operator, Pega saves the object to the database table named pc_assign_worklist. If the assignment is routed to a workbasket, Pega saves the object in a database table named pc_assign_workbasket.

Some commonly used properties that are specific to assignments include the operator who has the assignment (pxAssignedOperatorID) or the name of the flow rule for the assignment (pxFlowName).

When creating assignment reports, you often use pxRefObjectKey — this is mapped to pzInsKey. The pxRefObjectKey property allows you to relate the assignment to the case.

For descriptions of many standard properties used in assignment reports, see the Help topic Standard properties in the Assign- base class.

History reports

When a case is being processed, the system automatically captures audit trail data in the history classes. The classes are mapped to the History database tables where the data is saved. For example, the history class History-TGB-HRApps-Work is mapped to pc_History_TGB_HRApps_Work.

History reports use properties in the History- and History-Work- classes. These properties include pyHistory type (identifies the event that caused the history event), or pyPerformer (identifies the operator who completed the event recorded in the history instance).

Properties in history classes can be used to design performance-based reports. For example, you can use pxTaskElapsedTime to report the total time spent on an assignment. If an assignment is routed to multiple users, you can use pyPerformTaskTime to report on the total time spent by all users. If pyPerformTaskTime is significantly lower than pxTaskElapsedTime, then the assignment has been idle for a long time.

For more information about using the statistics for performance reports, see the Help topic Performance statistics.