Declare Index in Pega – Understanding

In this blog article, we will talk about  some basics on Declare Index rule.

Think of why the rule is given such a name. We all might have come across the term Index.

What is an Index?

Take a text book, in the first page you will see an index. ‘What is the main purpose of it?’

Yes, you are right. It contains page numbers to access the topics quickly. So instead of going and searching page by page, you can directly use the index and find the topic.

• Quicker access
• Saves time

We have a similar context in Database Indexes.

Important note: Database indexes are totally different than Declare Index. Do not confuse with the names.

In the Databases, when you want to execute a search query on a table, you can always index the where clause columns, to facilitate performance-efficient query.

In the above picture, we see the Player’s table as the main table. We have some SQL queries with a where clause on the Age column. Let’s say the table is huge and so we have some performance issues and we decided to create indexes on the database table – on Age column.

You see the Index table is sorted on the Age column and has a pointer to the disk location of the actual data.

When the query gets executed it goes to the index table to find the exact location of the results and so can be quickly retrieved compared to sequential scans 😊

Okay now it is time to talk about the Declare Index in Pega

We know that Pega can store the data either in the clipboard memory or in the database tables.

Clipboard memory – Some data, we just need for temporary purposes to process the requests

Database memory – Some data, like customer information, can be used in future for reference

Let’s understand the database structure on a very high level.

Database -> Schema -> Database table

With OOTB design, we already saw in the Work party blog article that Pega stores the work party / stackholders of the case within the embedded page list – pyWorkParty within the pyWorkPage.

We can have one or more pyworkparties within the case. Like multiple rows in a database table right?

Is there a way to persist this case record?  Yes, we can.

We use the BLOB column in tables ‘pzPvStream’ to store those aggregate values.

What is BLOB?

– Binary Large Object. Collection of binary data stored under a single entity (column).

– They are efficient and flexible mechanisms used by PRPC to compress the data.

– They can contain any amount of information comprising of simple single-value properties to complex page list structures.

So when we save a case which contains some page properties (like policy, address), all those data get compressed and stored inside BLOB.

Don’t think I am speaking out of syllabus on Declare Index 😉

Those are just basic operations on how Pega stores the data.

Scenario:

Now, say I have a requirement to take a report on the Work party details.

The report column should contain Stackholder name and their role.

Note: We can use report definition to get the data from BLOB at the expense of performance. RDB queries cannot use BLOB Data.

Say, you have got millions of records in the table. You need to decompress heavy data to get the results.

As a result, totally it is time-consuming. You need to sit and wait for a few seconds, while the report gets loaded.

Pega provides a solution for this performance issue, which is ‘DECLARE INDEX’

What is a Declare Index rule?

It comes under SysAdmin category.

Think about how the index page in a book serves us. Like the same, Index tables can be used to store the data that are embedded in aggregate properties.

We can directly refer to that index table, instead of decompressing the blob and checking the details inside the blob.

It helps in creating an index for the properties (pxPartyRoles & pyFullName) embedded in aggregate properties (pyWorkParty).

What do you mean by creating Index here?

We are going to create a new table!!!

Yes. A new table with exposed columns as pxPartyRoles & pyFullName.

You should get a question here.

I can create a DB table manually, do the class mapping and save those details, then ‘Why in the world would we need these declare Index rules?!’

The answer is simple. Declarative processing!

Yes. It updates those tables automatically at the back end. They can delete rows or insert rows in the Index tables.

We will see all these interesting stuff, later in the post/

They improve the database performance (No decompression from BLOB).

For the above use case, Pega already uses Declare Index processing OOTB 😀

Going forward in this blog article, we will explore how Pega uses different rules to support this declarative processing.

Let’s check how Pega is implemented using Declare Index

Step 1: Identify the Index class.

Note: Always the Index classes should inherit from Index- as pattern inheritance.

Open the class ‘Index-WorkPartyUri’ and check the test connection.

You can see the Index-WorkPartyUri class mapped to pc_index_workparty table.

Let’s analyze the Index-WorkPartyUri class:

Class type – Concrete class

Keys – 3 keys

Keys identify unique instances for a particular concrete class.

What is a key?

For reference, let me show the key for the Mortgage work class.

Because every work item (Instance of the work class) is differentiated by keys pyID (Ex: S-1, M-1 etc.,).

So you will get a question now. ‘Why 3 keys for the declare Index class?!’ 🙂

Pega played wisely here

As we can see in this pyWorkParty example, tell me how you will identify the warnings.

`1. Workparty will always belong to a case instance.

So Pega made the Rule pzInsKey as one of the keys  – pxInsIndexedKey

2. But we know that each case instance can contain more than 1 workparties (so what, pyWorkParty is a page group property).

So Pega introduced another key – pxIndexCount

3. Do you think these 2 keys are enough to uniquely identify an Index row. NO. There may be some situations, where you need to expose more than one aggregate property in a particular class.

Spoiler: Pega index the Custom-Fields too in the rule class. It means there are two aggregate properties Indexed in the rule class. So you need an additional key to differentiate both. So Pega introduced 3 rd key – pxIndexPurpose ( this will be inherited from declare index rule name )

Step 2: Identify the database table instance

Search Index-WorkPartyUri in the search box and open the database table instance.

You can see Pega made the database table instance mapped to pc_index_workparty table.

Step 3: Identify the database table pc_index_workparty in the database.

Pega exposed the required columns needed for reporting purposes.

You can see the first two rows belongs to same rule. They are differentiated by pxIndexCount

Step 4: Identify the declare Index rule

Search and open the declare index rule ‘PartyURI

You have a single main tab here.

Indexes tab

Source page context – Specify the page or page list property we need to expose.

Note: For Pagelist omit the subscript. You can see how pega used in this rule. pyWorkParty()

Source page context class – Specify the page definition of embedded property.

In our example, you can see the page definition for ‘pyWorkParty page list as Data-Party.

Index class to write – Specify the Index class we saw before.

Pega used Index-WorkPartyUri class.

Properties for indexing and mapping – before you need to create all the single value properties, we are going to expose in the index tables.

You can see the properties in Index-WorkPartyUri class.

After creating all properties in Index class, we need to map those properties with values from the source class

Properties in Source class —————–> Properties in Index class

(Embed-Warning)                                              (Index-Warning)

So, now you can visualize the role of Declare index from the 4 steps.

Yes, Declare Index rule helps in updating the Index table with values from the source class.

Here the declare index updates the workparty table with the value from case workparties.

Indexing condition –

You can use a when rule and enable it.

When true – New indexes get added. Normal behaviour as if no conditions added

When false – No new indexes are added. It also deletes existing indexes.

Note: You can leave this blank for normal behavior

Index even if source class property is blank –

Example – consider address as source values. If the user provided only city value and Street & state are blank

Checked – Index gets created even if a few source properties are blank. In the above case, it will fire

Unchecked – index gets created only when all the source property has value. In the above case,e it will not fire

All the rules are ready now.

When the declare rules will be triggered?

Creation – First think about when the workparties can be added to the Index table.

Yes, you should add an entry whenever a workparty is added to the case and the case is committed to the database.

Deletion – Then what will happen, If the case gets deleted/purged?

The Workparties should also be cleared from the Index table.

Update – What happens, If we update the workparties in the case instance?

The Workparties should be updated in the Index table

So It’s basically a forward chaining.

Whenever an instance is committed saved/deleted, then the declare Index will fire and update the index table.

Note: Declare Index will not update, If none of the source properties change. There is no need right.

Don’t worry about these triggering, Pega declarative engine masters it.

It is time to test what we learned

Step 1: Create a new Mortgage case. M-1

Step 2: In the clipboard, you should see a workparty added with the case.

Step 3: Open the database and write down the query to browse the pr_Index_workparty table to get the workparties available with the case.

Yes :). We got the workparty committed in the Index table.

Step 4: If you also trace with declare index settings enabled, you should see the declare index getting fired for the workparty.

In this blog article we understood in detail about Pega OOTB uses the Declare Index processing.

Okay now with all these learnings, recollect what are the steps needed to implement a new Declare Index?

1. Create a new Index table.

If you don’t have the privilege to create a new table, seek DBA team advice and get it done.

2. Create a new Index class – Include 3 key properties

pXInsIndexedInskey, pxIndexcount & pxIndexPurpose are the three key properties

3. Create new properties that we need to expose in Index table

Create those properties under Index class.

4. Create a new database table instance and map the Class with the Index table created in DB.
5. Finally create declare Index rule and configure all the details.

Pega also can automate all these steps when exposing an embedded page list property.

I will end the article here.

How to code a new declare index rule and use it? Visit my next post on step-by-step tutorial.