Declarative Index Join – its Configuration, Usage & Execution

Before getting into the discussion, we request you to go through our previous posts on the basics of Report Definition and exposing an aggregate property (Declare Index) in Pega.

Since we had enough discussion about Report Definition joins and Declare Index Rule in our previous posts, let’s get into the actual context of using Declarative Index Join in a Report Definition Rule.

What is Declarative Index Join?

• Declarative Index Join is one of the data access option available in a report definition rule in Pega.
• Declare Indexes are used to expose aggregate properties like Page/List/Group. These exposed properties can be used for reporting using Declarative Index Join.

• Declare Index rule can be used to expose aggregate properties from Data-, Work- & Rule- classes. Hence Declarative Index Join can be configured in the report definitions created in Data-, Work- & Rule- classes.

Why Declarative Index Join?

Unlike other Data- and Work- classes, Index- classes has direct linking with the work instances.

Hence a declare index rule that writes an entry into the Index table can be directly joined using a Declarative Index Join (without a join condition) rather than a class join (with a join condition).

How to Configure a Declarative Index Join?

• Declarative Index join can be found under the Data Access tab in the report definition rule.

• Declarative Index Join configuration looks similar to the Class join configuration with only difference being that, Join condition is in-built in Declarative Index Join.
• A report definition can have any number of declarative index join to fetch data from multiple index class.
• Each declarative index join should be configured using any one of the below join type,

• Data from the Index- Classes can be referred as a select column (or) in the filter criteria (Left-hand side & Right-hand side).

Possible ways of joining with Index- classes

We can join data from the Index- classes using either of the below ways,

• Class Join
• Declarative Index Join

Class Join

• We can use class joins to join & fetch data from the Index- classes. This type of join structure the query with multiple Join conditions.

SELECT Claim.customerid, Product.policynumber FROM pc_OSP_Claims_Work Claim INNER JOIN pr_Index_OSP_Data_Product Product ON  ( Claim.pzinskey = Product.pxinsindexedkey AND Claim.policystatus = Product.policystatus ) 

Declarative Index Join

• We can use Declarative index join to join & fetch data from the Index- classes. This type of join structure the query with a single Join condition and additional filter conditions.

SELECT Claim.customerid, Product.policynumber FROM pc_OSP_Claims_Work Claim INNER JOIN pr_Index_OSP_Data_Product Product ON  ( Claim.pzinskey = Product.pxinsindexedkey ) where Claim.policystatus = Product.policystatus 

Multiple Join conditions using ON clause makes the query execution costly when compared to filter conditions using Where clause. Hence a declarative index join is always recommended to join with Index- classes rather than a class join.

Business Scenario

OSP organization has a claims application to process the reimbursement requests submitted by the Customers. The system creates a work object in the system for each claim request submitted.

The customer should fill in his/her product information and the beneficiary details for each claim that he/she submits. OSP expects a report to view the list of claim requests that are successfully resolved in the system along with the policy information, beneficiary details & the agent who processed the claim.

Implementation

We have used v8.4 for implementing this POC

• As discussed in our previous post, Declare indexes are created to expose the Product and the Beneficiary information inside the claim work object.

• Agent who works on processing the claim request can be added as a Work Party to the claim case.

• Since the Agent information is added as Work Party, we can make use of the OOTB declare index PartyURI to fetch the information for reporting.

• As per the requirement, we need the Product, Beneficiary & Agent information for reporting. Since the information is persisted in multiple data sources (Index– classes), declarative index join should be used to join and fetch the required information.

Execution

• Let’s now create a couple of claim requests by providing all the required information.

• The submission of the claim request should have triggered the declare index execution & populated the respective index tables.
• The report definition configured with the declarative index joins should now fetch the results that satisfies the given requirement.

• Since classes are joined using declarative index joins, system internally joins the table using instance key (pzInsKey).

Pointers to Remember

• Declare Indexes are used to expose aggregate properties like Page/List/Group. These exposed properties can be used for reporting with Declarative Index Join.
• Declarative index join can be implemented in the report definitions that is written in Data-, Work- & Rule- classes.
• Declarative index join does not need a join condition to be mentioned explicitly. It joins the record using Instance key (pzInsKey) by itself.
• OOTB declare indexes namely PartyURI, Skills, etc.. can be reused as such for reporting using declarative index join.
• A report definition can have multiple declarative index joins based on the business need and the application data modeling.

We are glad to publish the article requested by @ Arun, Akhil on “Declarative Index Join”. If you want us to publish any articles of your choice, please fill out this form & we will make sure it gets published.

Stay tuned for our upcoming posts on Function Alias, Report Browser, Aggregate Data Pages in v8.4 😎