Property Optimization – Expose Columns in Pega

In this blog article we will check the different ways through which we can expose properties in database table.

First let’s see how the data (for example – Case data) gets saved into database table.

We know Pega uses properties to hold the data.

Say for example, I have created a new amazon sales case, Say A-1 and captured all the customer details.

`CustomerName = “Prem”.

We can persist the property details embedded or captured within the case in the Work table as a single row that’s corresponds to Case A-1.

By Default Not all the properties are exposed as data table columns. Then how do Pega saves every details as a single row entry?

Pega database supports blob storage. If the properties are not exposed, then they get compressed and stored in a blob storage column ‘pzpvstream’

So what do you mean by exposing property?

Exposing means, we will be creating a dedicated  column in the database table and map the corresponding property to the database column.

How to expose a property?

Pre-requisites

There are different ways to expose a property as a database table. Before exploring the options, let’s get the pre-requisites ready.

1. Have a dedicated work table (or any table) with class mapping done already.

Here my work class will be “OIKGGB-MyKnowPega-Work-AmazonSales” and the table I mapped is

“pc_oikggb_myknowpega_Work’

2. Create new properties in that work class

I got 3 properties in work class – CustomerName, Age and PhoneNumber.

3. Have a case ready with a collect information step to capture the above customer details.

Since I have already exposed Customer Name and Age column for other tutorials, I am going to drop those columns.

My drop column query is like below

Alter Table pc_oikggb_myknowpega_Work DROP COLUMN customername, DROP COLUMN phonenumber.

Now my table is fresh. No additional exposed columns.

Now, I am going to explain how to expose the properties.

There are four ways to expose the properties.

1. Using property rule  form or from App Explorer – Optimize using Other actions button

2. Using Modify Schema wizard.

3. Directly create a column in database

4. Exposing properties using declare index rule  – For more info on Declare Index creation, please visit my previous blog articles on declare index.

Procedure 1: Optimize from property form or App explorer

Using this option, Pega do support optimizing single value properties directly on the class layer or embedded on Page properties.

First let’s see how we can optimize a single value property that is directly on the Case class layer.

Step 1: Open the CustomerName property rule

Step 2: Go to ‘Actions’ button and click on the Optimize for reporting from the dropdown menu.

Step 3: You will be landed in the property optimization landing page

Wizard steps

Properties and classes

Here you can get more than one class and the corresponding tables.

Say for example, you have 2 cases in your application and both the cases are mapped to dedicated database table. In such case we can have common properties. When we try exposing those properties, you will get an option to expose those properties in multiple classes and tables.

I selected Amazon sales and click on next.

You also have an option to populate the data.

Populating data – Whenever, we expose a property in later point of time, we can use column population jobs to copy the data from the blob storage to the exposed column. We will see more in detail in separate blog article 🙂

Population schedule – Now/Later

If you choose later, you get an option to specify the date and time.

Normally column population job involves in updating the database table records. So in higher environment, we can schedule to run this job during offline hours.

Eligible classes

You will get a screen with a confirmation warning. It will list all the classes that will be affected.

You can click next to start optimizing.

Optimization

You can also click on the Column population jobs dashboard, to check the job status

Now, let’s check the database table, if a new column is created or not.

Yes 😊. Create a new case and check if the customername is getting populated.

How to control the auto-column creation behaviour?

– Based on the property data type, Pega automatically chooses the right column data type in the DB table.

– Based on property advanced configuration, for example you can set the maximum length as 256, then Pega can automatically create a DB column of varchar(256).

I have created a case A-6. Check in the database table for pyid =’A-6’.

We have successfully optimized the customername property.

Note: Optimise property action is available only for single value property. Also, a when rule ‘pxIsPropertyOptimizationAllowed’ should be true. If you dig the when rule, then you can see two conditions must be satisfied.

1. Dynamic system setting – database/AutoDBSchemaChanges should be true
2. Operator should contain the privilege ‘SchemaPropertyOptimization’ in his access rule.

Also understand that you can optimize the property from App explorer as well as shown below.

Also one final thing. Let’s say you have Customer details embedded into the Case level and you want to expose the single page customer details which are embedded within the case.

You can expand the right embedded page property and click óptimize for reporting’.

This will open a same wizard, which you can finish to complete the optimization.

One important thing to note is, when you expose any property with different column name, you should also make sure the external mapping is done properly.

For us, Pega automatically updated the external mapping tab of the class rule form.

Let’s move on to next procedure to expose a property.

Procedure 2: Optimize using Modify Schema wizard

Here I am going to try optimizing Age property

You can make use of the Modify Schema wizard.

Designer studio -> System -> Database -> Modify Schema

You can see a 6 step wizard.

Step 1: Select a database.

Choose an available database.

I selected PegaDATA , where my work table is available

Step 2: Select a Table

Select the appropriate table from the database.

I selected pc_OIKGGB_MyKnowPega_Work, which is my work table.

Step 3: View table

You can view the contents of the table.

Click on the count of columns link inside the grid to view the properties – Step 5

Step 4: View columns. You can also directly click on view columns or Columns in the table : (92)

Just a read only view and you can come back.

Step 5: View properties.

You can see the exposed properties – Already greyed out

Unexposed (eligible) properties – You have an checkbox option to select those eligible properties.

Eligible refers to column visibility field. We will see in detail.

You should get a question here!. Why Age property is not an eligible property?

Actually you need to check a configuration point in property rule form

Step 1: Open the age property.

In the advanced tab, you have a filed – column inclusion.

You have 3 options.

1. Required / Recommended – selecting this will make this property as a candidate to optimize
2. Optional – this is equal to making this field as empty.

Here I selected –Required.

Now again continue the modify schema wizard.

You will see the set to visible count increased from 102 to 103.

Click on the link to view the properties.

You will the age property. Select the property.

Scroll down.

You can either generate the SQL code or directly generate the database columns.

To generate the database columns, you need the database User ID and password.

If you are using personal edition provide ‘pega’ as user name and password.

Click on create.

You can see the success message.

Now let’s go and check the database table.

You can see a new column Age got created.

Note: The operator should contain ‘SchemaPropertyOptimization’ privilege to use wizard.

Procedure 3: Manually Optimize by creating Columns using SQL query

Here I am going to expose ‘phonenumber’ property.

Step 1: You can directly create a new column using SQL developer (maybe with the help of DB team)

You can run the query and check in the database table.

You can see the newly added column Phonenumber.

Note: After adding a new column, always re-save the database table instance. (This re-save will be handled by the wizard, when we expose the property from designer studio)

Let’s create a new case with valid phonenumber and check the value in database table.

The case I created is A-8.

Let’s check the phonenumber value in database table.

We have successfully exposed 3 different properties via 3 different procedures.

Procedure 4: Optimizing list type properties using Declare Index

This is explained in a separate blog article –

Why do we expose a property?

1. The main aim is to support reporting

Say for example, I need to make a report and display all the customer details like ‘phonenumber, age & name’. here if I don’t expose those columns, then I need to decrypt the Blob and get their values. This is very expensive and degrades performance. Also you cannot use SQL query. SQL query can access only the exposed columns. This is the main reason why we expose the properties.

2. Also keep in mind that, exposing large number of columns also degrades performance. So use it wisely.