Question
WIPRO
IN
Last activity: 15 Feb 2023 10:47 EST
Data Model for a complex REST Request API
Hi - I want to host a service-REST where request from the consumer will be very huge. I have two basic questions with this.
1. How to generate the request/response structure?
2. I have few reporting requirements and would like to expose some properties (Roughly around 20 properties). I understand its not wise to expose them in work table. Also BIX is not an option because I have to use these properties in a search screen as well. Can I create a separate data type to store the columns I need for reporting and leave the rest in the work table?
Pegasystems Inc.
IN
Hi Yes it is always recommended to model data first into appropriate data types, leverage savable data pages and save it and maintain only references of the data in case. follow single source of truth and data reference pattern.
advantages is case is light weight and helpful for reporting without using BIX or any other.
WIPRO
IN
@SriHarsha Anika Thanks for the response.
Are you suggesting to store every object within JSON into its own table or only selectively? In my request JSON, there are 500+ attributes and some are like paragraph kind of attributes. Should I still store them in a dedicated columns? Is it possible to store them in a BLOB under a data type?
Lventur
IN
Yes, it is possible. The values which you want to use in report for that fields alone have a dedicated column in table.
No need to hold the JSON value in exposed column and it will not be recommended approach. It will consume huge space in DB.
While creating data table by default schema will be customerdata, instead of customerdata schema select pegadata schema.
If you are selecting pegadata schema when creating data table, then blob will be available for the data table and in some property you can hold the JSON value.
Note: For customerdata schema table blob will not be available only exposed columns alone will be there.
WIPRO
IN
@Gunasekaran Baskaran Thank you. I will try this approach
Pegasystems Inc.
IN
@MOHANRAJS7274 Hello for such huge set of data. other than fields what you want to leverage for search, other properties can very remain as json itself in a database column of type JSON.
advantage is you can query JSON as well on demand - as enterprise products are moving towards NoSQL databases (Pega also soon) it will be more helpful in retrieval. a blob extraction is always a costly job. as it involves file compression, encryption and decryption. Querying data is not a easy task - if you want to re-optimize one more property later in future - you have to resave all instances to re-populate data and run column population jobs. Need to control BIX to stop extracting old data given pxupdatedatetime, pxcommitdatetime will be updated one time activities which performs resave of data into optimized columns (worktable/ index table) so you have to control declarative rules and lot many ( on changes/ triggers). a data type is safer to with respective maintenance, updates to date, optimizing the column with out engaging pega engine and without touching pega cases you can handle it. agreed database size will grow - but this is not a drastic range. average cost per gb for cloud database storage is 0.023$ per month. (you don't get a cookies either for this rate). It's trade off between database cost vs long running system maintenance with less efforts and easy to handle.
@MOHANRAJS7274 Hello for such huge set of data. other than fields what you want to leverage for search, other properties can very remain as json itself in a database column of type JSON.
advantage is you can query JSON as well on demand - as enterprise products are moving towards NoSQL databases (Pega also soon) it will be more helpful in retrieval. a blob extraction is always a costly job. as it involves file compression, encryption and decryption. Querying data is not a easy task - if you want to re-optimize one more property later in future - you have to resave all instances to re-populate data and run column population jobs. Need to control BIX to stop extracting old data given pxupdatedatetime, pxcommitdatetime will be updated one time activities which performs resave of data into optimized columns (worktable/ index table) so you have to control declarative rules and lot many ( on changes/ triggers). a data type is safer to with respective maintenance, updates to date, optimizing the column with out engaging pega engine and without touching pega cases you can handle it. agreed database size will grow - but this is not a drastic range. average cost per gb for cloud database storage is 0.023$ per month. (you don't get a cookies either for this rate). It's trade off between database cost vs long running system maintenance with less efforts and easy to handle.
to develop this model - development efforts are little higher end - but maintenance is at lower side. for any client they want less maintenance cost with lesser impacts, loosely coupled systems. this model is loosely coupled with pega engine for db DDL, DML, CRUD maintenance activities.
WIPRO
IN
@SriHarsha Anika Thank you very much for your Insights. Extremely helpful