In this post we will see, how we can expose properties in database table.
First let’s get some basics
- Pega uses properties to hold the data.
- We can persist the data in database table.
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 these details in the Work table in a single row that’s corresponds to Case A-1.
Note: Not all the properties exposed as data table columns.
Pega database supports blob storage. If the properties are not exposed, then they get 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?
Here are some pre-requisites you need to have in handy!
- 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
- Create new properties in that work class
I got 3 properties in work class – CustomerName, Age and PhoneNumber.
- Have a case ready with a flow action 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 additionally exposed columns.
Now,I am going to explain how to expose the properties.
There are four ways to expose the properties.
- Using property rule form – Optimize using Other actions button
- Using Modify Schema wizard.
- Directly create a column in database
- Exposing properties using declare index rule – For more info, please visit my previous posts on declare index.
1.Optimize from property form.
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
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 lesson 🙂
Population schedule – Now/Later
If you choose later, you get an option to specify the date and time.
Normally column population job invoves in updating the database table records. So in higher environment, we can schedule to run this job during offline hours.
- 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.
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.
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.
Dynamic system setting – database/AutoDBSchemaChanges should be true
Operator should contain the privilege ‘SchemaPropertyOptimization’ in his access rule.
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.
- Required / Recommended – selecting this will make this property as a candidate to optimize
- 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.
- 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.
Here I am going to expose ‘phonenumber’ property.
Step 1: You can directly create a new column using SQL developer ( may be 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 🙂
Why do we expose a property?
- 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.
- Also keep in mind that, exposing large number of columns also degrades performance. So use it wisely.
We will see more about column population job in my next post 🙂
I will come up with a new post very soon 🙂