Posted: 21 Dec 2020 1:10 EST Last activity: 8 Mar 2021 22:38 EST
Pega BIX -How to get incremental data in BIX Extract without pr_extract_time table in Source DB
Hi all I have a requirement to extract only delta data from a production backup database. So initial extract works fine however, after every nightly backup, the table pr_extract_time which has the pxLastUpdateTime column is overwritten.
Is there a way to get Delta updates without having this table in source Database?
***Edited by Moderator: Pooja Gadige to add product details tag, add platform capability tags***
When Pega runs the BIX extract rules for the first time it extracts all records for the cases types you have selected. On the second run it will check for the last run date time, and only extract records which were modified after the last extraction date time. After extraction rule is run it will set the new date time as last extraction date time for next run. This is how BIX works
Pega uses pr_extract_time for it's own understanding to track when the last time the extract rule was run and logs all the extraction related details here
In the extract rule itself you can go to the Filter criteria tab and Select the Use Last Updated Time as Start check box to extract all data that was created or updated since the last time the Extract rule was run. This field is not relevant until the Extract rule has run at least once.
pyLastUpdateTime in the table pr_extract_time is considered for every run of BIX extract. Under the Filter criteria tab of the BIX extract rule, we have the option "Use Last Updated Time as Start". This plays important role in extracting the data.
Extract Full Load of Data Daily: In this scenario "Use Last Updated Time as Start" should be unchecked. Irrespective of pyLastUpdateTime in the table pr_extract_time table daily full load of data will be extracted.
Incremental Extraction of Data: In this scenario "Use Last Updated Time as Start" should be checked. For the first time when the extract is executed as there, won't be ins-key of the extract in the table Pega will extract a full load of data, and once the extraction is done it will insert a record with pyLastUpdateTime with extract execution completed time. From the next extraction onwards Pega will compare pxCommitDateTime of the source table with pyLastUpdateTime in the table pr_extract_time table and the delta data that is committed after pyLastUpdateTime will be retrieved.
In your scenario whenever we take a backup of production db there won't be any impact on this table. You can configure the extract with a second option and automatically the delta data will be extracted.