Data storage in Pega

Any Pega Platform class with concrete instances, such as case types, can be mapped to a database table. For example, when users create cases, the system assigns an ID to the case and saves the value as an individual row in a database table. When you generate reports, you retrieve data from rows in database tables. Reports use class mappings to locate the data from one or more database tables. For example, when a user creates a case, Pega Platform assigns a unique case ID by using the class mapping to save the instance as a row in the correct database table. Similarly, when accessing a class instance, for example, opening a case or running a report, Pega Platform uses the class mapping to retrieve data from the associated database table. 

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 work queue information about each Candidate case. Work queue records are instances in a workbasket class. The data for each type of information is stored in separate tables. When you combine the data in a report, you use class names to identify the tables where the information is stored.

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

• Database records identify how Pega Platform connects to specific databases and contains the connection information for Pega Platform to access the databases. This record is an alias that can be referenced elsewhere, such as in a Database Table record. Database records can be configured to use either JNDI or JDBC URL for the database connection. By default, Pega Platform always contains the following database records:

  • PegaRULES – Maps to the database where all Pega Platform rules and system data are saved.

  • PegaDATA – Maps to the database where data and work instances are saved.

• Database Table records exist for every Pega Platform class and identify the corresponding database and table. Pega Platform uses this record to identify which table to write case data when a user creates or updates a case or data instance.

In some situations, you may want to save instances of several classes in the same table. For example, in an application with three case types, Candidate (class TGB-HR-Apps-Work-Candidate), Onboarding (class TGB-HR-Apps-Work-Onboarding), and Benefits Enrollment (class TGB-HR-Apps-Work-BenefitsEnrollment), you need to report on the work status of all cases in the application.

Rather than create a database table for each case type, you designate a class, usually the parent class (TGB-HR-Apps-Work), 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. A report created in a specific case type, such as Candidate, returns only records in the case type. A report created in the class group returns all instances in the classes that belong to the class group.

The following example shows the work classes that map to the class group database table pc_TGB_HRApps_Work.

Check your knowledge with the following interaction.

You commonly generate reports that include properties from three different classes: work, assignment, and history. Each type of report uses properties from classes that are mapped to different database tables.

Work reports

When a case is created, the properties from the work class and the inherited classes (pattern and directed) are used to generate reports. Some commonly used properties are:

• pyID – Case identifier
• pyWorkParty – Work parties participating
• pxUpdateOperator – Last user to update the case
• pxUpdateDateTime – Time of the last update
• pyStatusWork – Work status of the case

Work reports are created in the appropriate work class (such as TGB-HRApp-Work) and the class mapping specifies which database table is used to source the required data. To optimize performance, properties referenced in reports must be exposed as columns in the database table. To determine if a column is exposed, use the Database Class Mappings landing page, and then click the Columns value for the class to show all the columns in the table.

Assignment reports

Assignments are created during a case life cycle, typically where some form of user interaction is required. The two main types of assignment objects created are worklist (class Assign-Worklist) and workbasket (class Assign-WorkBasket), which are mapped to the database tables pc_assign_worklist and pc_assign_workbasket respectively by using the associated class mappings. When an assignment is completed, the assignment object is deleted and another is created following the processing in the steps and stages of a case type.

Assignment reports are displayed on a user's portal to list assignments in their worklist and any work queues to which they have access. There are many standard assignment reports (such as pyAssignmentWorklist), and these reports are often specialized to meet specific business requirements (for example, a join to the class of a case type to return associated case data). When joining to the class of a case type, the assignment class pxRefObjectKey is used to match with the pzInsKey of the case type. 

History reports

You can use properties in history classes to create performance reports. For example, the property pxTaskElapsedTime saves the total time spent on an assignment. If an assignment is routed to multiple users, the property pyPerformTaskTime records the total time spent by all users on that assignment. If pyPerformTaskTime is significantly lower than pxTaskElapsedTime, that value indicates the assignment has been idle for a long time.

Any Pega Platform class that has instances and can be persisted, such as a case, is concrete and must be saved to the associated database table. To save to the associated database table, a class mapping (Data-Admin-DB-Table rule) associates the concrete class with a database table. 

In the following image, click the + icons to review the three common types of class mappings.