How to configure Declare index in Pega – step by step tutorial

Introduction

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

http://myknowpega.com/2017/06/16/what-is-declare-index-in-pega/

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.

Submit it.

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

Good 🙂

Requirement 

  • 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

Set,

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?

  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.

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

pXInsIndexedInskey, pxIndexcount & pxIndexPurpose are the three key properties

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

Create those properties under Index class.

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

If you need to know the other declarative rules, check out the below category 🙂

http://myknowpega.com/category/declarative-rules

See ya in next post 🙂

43 thoughts on “How to configure Declare index in Pega – step by step tutorial

    1. Hi Mastan,
      You can find the answer on your own :). Open you application skin -> Component styles -> Dynamic layouts -> Formats ( Inherited)
      Check both stacked and default. You will see some difference in width and height.

      Note: Columns per row is 1 in both the formats. Its like Default behaves like a stacked format.
      Hope you are clear now :)……. By the way, I am Prem, not Praveen 😉

    1. exposing properties – You can either get the help from DBA team to create new columns in DB table ( if you dont have access) else you can manually create new columns. You need to save the data table instance once. Now the mapping is done.

      There is another simple option, in the property rule Other action button -> Optimize. On completing the wizard, a new column will be created in the corresponsing DB table.

    1. Oh, I’m so sorry for the delay, Swapna. I’ll try to post something new within this week. I’m a little busy, but I’ll definitely post a new post soon. Stay tuned.

    1. You are most welcome, Abdul. 🙂
      I’m feeling elated on reading your comment. 🙂
      Thank you so much for your appreciation. 🙂

    1. Hi Raji, i used personal edition as well in the tutorial. pega personal edition comes with postgres database.
      It will be within your downloaded package. Run the .exe file. Add a new connector providing username -postgres ; password – postgres;
      You can access the database

  1. Hi Prem,

    Recently only i have started reading all the post provided by you. Your way of explanation is superb. It clears all the doubts.

  2. thanks for helping us with ur posts bro..can you do a topic on decision rules and how to configure them?It would be very very helpful for all of ur followers.

    1. You are most welcome, Mohammed Warish. 😀
      Okay bro, I’ve taken note of that topic. I’ll post about it soon. Stay tuned !

Leave a Reply

Your email address will not be published. Required fields are marked *