Report Definition in Pega – Class Joins & Associations

Irrespective of whatever technology we work in, the database will be a key component to deliver any application.

For example, we all would have used the Red bus ticket booking app to book our travel tickets where we will be able to see the list of buses, available seats in each bus, amenities provided in each bus, and the history of our bookings, etc. These pieces of information are available in the red bus application & are kept up-to-date because the application is connected to a database that helps save all the required information and keeps the data up-to-date.

In the same way, all the applications that we use in our day to day life [Swiggy, BookMyShow, Zomato, Ola, UBER, etc] will be connected to one or more databases which makes the application ready to use.

Database Hierarchy

Below shown is the OSP version of the database hierarchy. Any application we use will be connected to a database server. A database server can have multiple databases in it. Each database in the database server can have multiple tables logically grouped under different schemas.

What is a Database Table?

A database table is a collection of related data/information held in a table format within a database. Each database table will be structured into columns and rows.

In Pega, each database/data table will be represented using a concrete class. Database table data instance is used to link a class and a database table in Pega.

Before getting into the discussion of the Report definition rule in Pega, let’s get to know few basics of SQL to understand the actual context better.

What is SQL?

• SQL stands for Structured Query Language. A language that all databases understand.
• It’s a standard language that any application uses to communicate with the databases. Communications happen in the form of storing, manipulating, and retrieving data from/to the databases.

How to write a SQL Query?

• SQL is a programming language that is used to interact with the database tables to fetch the data as per the business need.

The SQL WHERE Clause,

The WHERE clause is used to filter records that match the specific condition. The WHERE clause is used to retrieve only those records that fulfill the mentioned condition whereas the rest of the records are ignored.

The SQL INNER JOIN,

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. The INNER join is used to fetch only the matching rows between the tables.

The SQL LEFT JOIN,

The LEFT join returns all records from the left table (table 1) and the matched records from the right table (table 2). LEFT join can also be referred to as LEFT Outer Join.

The SQL RIGHT JOIN,

The RIGHT join returns all records from the right table (table 2), and the matched records from the left table (table 1). RIGHT join can also be referred to as RIGHT Outer Join

We hope now the basics of database and SQL are clear. Let’s get into the actual discussion of creating and configuring report definitions in Pega and its relation with SQL.

What is Report Definition?

• Report definition is a type of rule in Pega that is used to interact with the database tables and fetch the information based on the business need.
• Report definitions are rule instances of class Rule-Obj-Report-Definition.
• Report definitions can be found under the Reports category in the Records explorer.

Each report definition rule has 5 main sections to configure,

• Query
• Chart
• Report Viewer
• Parameter
• Data Access

Query

This section of the report definition rule allows us to enter the list of columns to be browsed and the filter criteria (if required).

Chart

This section of the report definition rule allows us to configure the pictorial representation (chart) of the report results. Chart option will be enabled only when the report definition has at least one aggregated column.

We will have a separate post in the series of Reports to discuss the summary reports & chart configuration with real time examples.

Below shown is the sample chart configuration to show the count of claim cases created for each unique Customer ID.

Report Viewer

Report browser is one of the OOTB features in Pega that exposes the reports to end users. Each report developed in Pega can be displayed in the default Report Browser view of the manager’s portal. This section of the report definition rule allows us to configure settings that will be used when showing the report in the report browser.

We will have a separate post in the series of Reports to discuss extensive Report browser configuration.

Parameter

The parameter is a key feature in any coding language that makes our implementation reusable. We write an activity rule in Pega and Parameterize it to ensure that the same activity can be reused for different purposes. For example, SendSimpleEmail is one of the parametrized reusable activity.

In the same way, Report definition filter condition can be parametrized to ensure re-usability. This section of the report definition rule lists all the parameters that needs to be passed to the report definition during its invocation.

Now the same report definition can be used to fetch the list of open claims or the list of resolved claims by passing the claims status value at run-time.

Data Access

Data required for reporting will not be available in one table all the time. In certain situations, we might end up joining information between multiple tables (classes) to satisfy the reporting requirements.

This section of the report definition rule allows us to define the join between classes (tables). We can join information between classes in more than one ways,

• Class Joins
• Declare Index Joins
• Association
• Sub-reports

We will discuss Class Joins and Associations in this post. Declare Index Joins and Sub-Reports will be covered in a separate post in the series of Reports.

Class Join

• Class join is a data access option available in a report definition rule which allows us to join the current table (class) with a different table (class) using a join condition.
• Class join in a report definition rule is similar to writing a Join query in SQL.
• Each joined class with have a prefix and the prefix will be used to identify the column source during the execution.

Business Scenario

Let’s consider the scenario where OSP organization has a claims application, Customers can raise a claim anytime in the system. Each Claim request raised in the Pega will have the customer identification number within it. Customer information namely Customer name, email & address information of the customer namely address type, city, etc.. will be available in Pega as a separate data type.

OSP organization wants a report which will pull all the open claim cases along with the customer information (Customer Name, Email) & address information (Address Type, City) of each customer for whom the claim is raised.

Implementation

• Customer information (OSP-Data-Customer) are maintained in a separate data type in Pega.

• Address information (OSP-Data-Address) of the customers are maintained in a separate data type in Pega. Each customer can have more than one address of different types (Home, Office, etc..). There can even be a scenario where a customer can exist in the system without any address information.

• Each claim request is created as a work object in Pega & class OSP-Claims-Work-Request maintains all the claim request raised in Pega.

• Since the report needs all the open claims information along with the customer information & address information, the class join needs to be configured between OSP-Claims-Work-Request, OSP-Data-Address & OSP-Data-Customer to fetch the required information.

• Since the customer table contains information about each customer in the system, Claim request work class should be joined with the customer table using the customer identifier as the joining key to fetch the customer-related information.

• Type column in the class join defines the type of join that is used to fetch information.

• Since the address table contains information about each customer’s addresses in the system, Claim request work class should be joined with the address table using the customer identifier as the joining key to fetch the address-related information.
• As per the requirement, a customer can exist in the system without any address. Hence using INNER join (Only include matching rows) will exclude the claim cases created for customers who don’t have any address. Hence LEFT Join (Include all rows in this class) is used to fetch all the claim cases even if the address detail doesn’t exist for the customer.

• Query tab of the report definition rule has the list of columns to be retrieved and the filter criteria to be used.

Execution

• Let’s verify the above configured report definition by creating claim cases for the different customers (Cus-1, Cus-2, Cus-3)

• Let’s now run the report definition rule to verify the results for the created claim cases.

Association Join

• As we all know, re-usability is one of the key measures in Pega. Yes, association rules in Pega are similar to class join that helps us join the information between one or more classes (tables). But unlike Class joins, they are not defined in each report definition rule rather association as such is a rule type in Pega that can be created and referenced in the report definitions whenever needed.
• Association rules are instances of the class Rule-Obj-Association.
• Association rules can be found under the Data Model category in the Records explorer.

Configuring an Association Rule

• Configuring an association rule is similar to the class join, but the join condition is configured in the association rule rather than the report definition rule.

• The association rule name and the prefix of the joining class should always be the same. In our scenario, it is CustomerInfo.

Referring an Association Rule

• When we have an association rule created to join two classes, it need not be again mentioned in the class join in the data access tab of the report definition rule.
• Open the report definition where we want to refer the association rule and directly access the columns using the association rule name as shown below.

• When an association rule is referenced in the report, system automatically defines the association rule in the Association Join section of the data access tab.

Business Scenario

Let’s try to implement the same scenario discussed above for class join using the association rule join.

Implementation

• As discussed above, Customer information (OSP-Data-Customer) are maintained in a separate data type in Pega.

• As discussed above, Address information (OSP-Data-Address) of the customers are maintained in a separate data type in Pega. Each customer can have more than one address of different types (Home, Office, etc..). There can be a scenario where a customer can exist in the system without any address information.

• Each claim request is created as a work object in Pega & class OSP-Claims-Work-Request maintains all the claim request raised in Pega.

• Since the report needs all the open claims information along with the customer information & address information, the association join needs to be configured between OSP-Claims-Work-Request, OSP-Data-Address & OSP-Data-Customer to fetch the required information.

• Since the customer table contains information about each customer in the system, Claim request work class is joined with the customer table using the association rule CustomerInfo.

• Since the address table contains information about each customer’s addresses in the system, Claim request work class is joined with the address table using the association rule AddressInfo.
• As per the requirement, a customer can exist in the system without any address. Hence using INNER join (Only include matching rows) will exclude the claim cases created for customers who don’t have any address. Hence LEFT Join (Include all rows in this class) is used to fetch all the claim cases even if the address detail doesn’t exist for the customer.

• Query tab of the report definition rule has the list of columns to be retrieved using the association join.

Execution

• Let’s verify the configured report definition by using the claim cases created for the different customers (Cus-1, Cus-2, Cus-3)

• Let’s now run the report definition rule which has the association join to verify the results.

Possible ways of Referring a Report Definition Rule

Report definition rule once created can be referenced in more than one way. Below is the list of invocation points of a report definition rule,

• Report definition rule can be used to source a List structure data pages.

• Report definition rule can be used to source complex UI controls like Dropdowns, Autocomplete, Multi-Select lists, Radio buttons, etc..

• Report definition rule can be used to source repeating layouts like Table layout, etc..

• Report definition rule can be executed from an activity rule and the results of the execution can be saved into a page list.

• Report definition rule can be referenced in the short cut rule in Pega. Category -> Short-Cut-> Report Definition exposes a report definition to be displayed in the report browser.

How to Trace & Debug Report Definition Rule?

• Report definition rules execution can be seen in tracer only if the below setting are enabled in the tracer.

• Each report definition execution can be examined in the clipboard under pyReportContentPage in the Standard thread.

Best Practices when using Report Definition Rule

There are certain guidelines or best practices to be followed when configuring the report definition rule in Pega to ensure the maximum throughput in terms of DB performance.

• Always use columns in Report Definition’s filter criteria that are indexed (database index).
• Report Definition’s filter criteria that use function alias or ignore case matches will not use the database index even if it exists. Hence these usages can be reduced unless and until it’s required.
• Use null if empty should be selected in filter criteria whenever possible, failing which makes the query expensive when filter value is blank.
• Maximum rows to retrieve should never be 0 in the report definition. It affects performance as it fetches all/ all matching records from the table
• Using Outer join [Include All Rows] in Report definition or having multiple join conditions in the report makes the query expensive. PEGA recommends having a maximum of 4 join conditions in a report.
• Applying Sort or any aggregate operations on more number of columns will result in poor performance.
• Report definition’s filter criteria with match conditions other than “Is EQUAL” & wildcard operators like “CONTAINS”, “STARTS WITH”, “ENDS WITH” results in poor performance.
• The auto vacuum factor should be set properly if the tables to be used for reporting grows bigger. Dead Tuples / Dead records should be removed as part of the vacuum process more frequently.
• Frequent checks on the long-running queries/Idle Transactions are always advisable. We can kill these queries using DB specific command / adjust timeout for idle transactions at DB level.
• The number of Indexes in the table should always be less than 5. Recommended no of the index for a table is 5 and the maximum no of the index for a table is 999. More number of database indexes results in larger DB size.
• The primary key of the tables will be auto indexed. Hence duplicate indexes should not be created for primary keys.

Pointers to Remember

• Report Definitions are configurable versions of the Connect-SQL/SQL query in Pega that can be used to fetch records from the database.
• Using report definition we can join information from more than one table.
• The class joins in report definition can be preferred when the joining key differs from one report definition to the other.
• The association joins in the report definition can be preferred when the joining key remains the same and can be referenced using association rule rather than a different class joins.
• Careful consideration is required when configuring select columns, filter logic, joins in a report definition rule. Failing which will make the DB query more expensive.
• Guidelines or best practices summarized by Pega should be ensured before deploying a report definition rule to the production environment.

We are now at the end of the post. We hope the basics of report definition rule in Pega along with the best practices to be followed when we join information from multiple tables are clear.

Stay tuned for lot more posts on Reports 😎