In this post, we will learn how to configure declare index in Pega. Its highly recommend to visit my previous post on declare index rule
Here let us see the tutorial how to implement the declare Index in Pega.
What are the pre-requisites to configure declare index in Pega?
- Imagine the below scenario, address details are captured in a page property ‘Address’
- we need the expose Street, City & state in separate table for reporting purpose.
You can see the property structure below
1. Create new properties ( 7 properties)
Single value properties – Customer name, Phone number and Age created in work class
Page property – Address created in work class
- Street, City & State created in Address page definition ‘MyKnowPega-Data-Address’.
Here we added the properties under Address page property.
2. Create a simple starter flow
3. In the flow action create a simple screen to hold the all six values.
Note: For address, Refer the fields as .Address.Street, .Address.City & .Address.State 🙂
Now let’s run the flow and enter valid details.
Check the same in database worktable for C-1
- You can see the details get persisted in DB.
Age, Customer Name, Phone Number are populated in exposed columns.
- The address details are compressed in BLOB.
Note: I already exposed those 3 columns explicitly in worktable
- User wants to report on the customer details including the address values.
- Since the address values are stored in BLOB, it affects reporting performance.
So we decided to create Index for address properties and expose it as separate columns in Index table.
How do we start configuring Declare Index?
Step 1: Create an Index table.
Table name – Index_Address
Note: I am using Postgres SQL. I got the admin rights to create table. If there is privilege restriction, kindly consult with DBA team to create a new Index table along with columns.
Don’t forget to include the main key columns and pzInskey 🙂
- I have successfully created a new table. You can verify it below
New table with 10 columns.
Step 2: create a new index class in Pega
- Provide the required properties as keys. You can visit my previous post to get the complete information about Keys.
Step 3: Create a new datatable instance
Note: Remember you can also use database class mapping wizard for mapping.
For more info, please visit my post on database class mapping wizard.
Here I am going to do manually.
- Set the database name as ‘PegaDATA’ & table name as Index_Address
Step 4: Create the three properties in Index-address class.
- single value properties – Street, City, State.
Step 5: create a new declare Index rule
Source page context – .Address
Source page context – MyKnowPega-Data-Address
Index class to write – Index-Address
Properties for Indexing and mapping –
Set the source property from address page in left hand side and Index property on right hand side.
How simple is this. We have successfully configured declare index in Pega.
Let’s test it.
Step 1: Run the starter flow.
Step 2: Provide some valid values in the customer details screen.
Submit the screen.
Step 3: open the DB and verify the record in work table. ( C-7)
- Customer Name, Age, phone number values are set correctly.
Now let’s check out index table.
Step 4: Open the Index-Address table and check the address values.
Guess what !! 🙂
Now let us test it differently 🙂
Step 1: Create test activity.
Step 2: In the first step – Open the C-7 work item
Step 3: In the second step – Property-set on address values
Step 4: In the last step – Obj-save with commit.
So what we are doing here? We are manually updating the address values. When we save the work item, Index table should be updated with new address values right? Let’s check it 🙂
Step 5: Run the activity from other actions button.
Step 6: Check the record in the index table. Pakka 😀
Step 7: Let’s do one thing,
- Update the test activity to delete the workitem, but don’t commit now.
- Leave immediate unchecked
This is just a one step activity.
If you see both the work table & index table record still persists and not deleted.
The reason is declare index fired only when the DB action is committed in database
Step 8: Check immediate & run the same activity.
Step 9: Check in the Index-Address table
It’s gone…… disappeared. 🙂
what are the things to remember when configuring a new Declare Index in Pega?
- 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.
- Create a new Index class – Include 3 key properties
pXInsIndexedInskey, pxIndexcount & pxIndexPurpose are the three key properties
- Create new properties that we need to expose in Index table
Create those properties under Index class.
- Create a new database table instance and map the Class with the Index table created in DB.
- Finally create declare Index rule and configure all the details
If you need to know the other declarative rules, check out the below category 🙂
See ya in next post 🙂