Please see attached document

Context

Service User (SU) aka “customer” is a person whose management is the primary objective of the application Pega is trying to build. Service user or the customer main system of record (SOR) is a sql server database external to the main Pega application.

Main Pega application has been built on Customer Services strategic application.

Reference data updates

Pega application needs to manage SU’s personal details – like multiple addresses, multiple emails, aliases etc. and update the same in the Sql server SOR database. These cases are mostly single step and involve sending multiple records in a single submit on the screen.

Reporting data updates

Pega application also needs to be record additional reporting data for a Service User (SU) based on business needs. For an example, during the progression of a case for a SU in Pega we need to write some business data for that SU in the Sql server SOR database. This data is written for reporting purposes and can be written in a synchronous operation within the process or asynchronous (via standard agent).

Messaging data updates

From time to time we also need to relay some messages to Sql server SOR database. Quite simply we update Sql server database with SU data and identify that to be a message for an external consumer.

Assumption: Sql server team have built a sophisticated mechanism to take these specific updates and then invoke SOAP/REST web services to push the messages on to the external consumer.

Technical approach of updating Sql server SOR Database from Pega

Proposed Implementation assuming updates required for customer entity in sql server database

1. Pega creates a xml payload based on functional requirements
   1. XSD supplied by SQL SERVER DBA
   2. Pega imports the XSD supplied by SQL SERVER DBA
   3. Pega constructs XML based on XSD
   4. XSD has various entities with minOccurs as 0

i.Same XSD can support multiple addresses, multiple emails, multiple aliases for the same customer with minOccurs clause set to 0

ii.Pega developers fill in the structure they want based on requirements. For example on address update screen we will only fill in addresses structure.

1. Xml is passed as a string argument to a generic sproc
   1. Sproc validates against xsd in sql server
2. Validated xml passed to a dedicated sproc, based on the identified payload
3. Sproc executes against the database
   1. For example if we are sending address XML structure a dedicated stored proc for addresses will apply Create/Update against relevant tables
   2. Stored procedure handles transaction management
   3. Pega is returned back success or failure and an optional message string with any messages indicating business failures

Why Stored procedures?

There are more database resources skilled in writing stored procedures hence we choose to go down the stored procedure route. Previously we tried SOAP web services however it was realized that it was taking too much time and with the shortage of appropriate skilled resources in web services/SOAP it was delaying the whole delivery plan.

Why XML route?

XML provides an easy mechanism to encapsulate several records as a XML string to be passed as an argument to stored procedures. An operator in Pega can perform multiple address updates for SU on the same screen before pressing Submit. These address updates are essentially multiple records which we want to pass as 1 business operation to SOR database. Stored procedures at the other end can then handle transaction and error management on the entire business operation.. XML provides natural parsing/validation and future proofing in case there is a need to convert to a web service approach potentially utilizing the same XSD.

Constraints and Assumptions

• XML/XSD and all stored procedure development will be undertaken by DBA team. Pega will be consuming the XSDs and doing data transforms and dealing with errors returned by the stored procedures.

• External classes were ruled out as updates are not to single tables in SOR. Some of the updates involve complex updates involving the main table and some linked tables. External classes also exposes us to the entire update/business/update logic which will be difficult to maintain in a longer run. There was a requirement from the customer to want the updates to their database tables via published APIs – like stored procs.

Transaction management

Stored procedures will handle transaction management for us.

Handling con-current updates/data integrity

An entity/object for a SU can potentially be updated by external organizations while Pega user is in the middle of an operation. This is not something which will happen very often but can we need to deal with the situation if it were to happen.

To tackle this issue we have proposed that when we retrieve SU data from sql server external SOR DB we will also retrieve updatedatetime. And that same updatedatetime will be passed across to stored procedures to ensure that the record we are trying to update is the one which is essentially locked by us.

Before an update is committed and saved in SOR database existing updatedatetime in the concerned table will be compared with has been passed via Pega. If the two match, transaction will be allowed to execute in SOR database and a new updatedatetime will be recorded against the specific record in SOR database. If the two do not match, an appropriate message will be returned back to Pega application.

In addition there is a proposal to limit the launching of CRUD tasks once only for a service user. This will mean that a user will be prevented to launch a task if another instance of the task is already in progression. This is from a customer service application point of view.

Error handling

Business errors from stored procedures to be bubbled up as page messages on workpage.

Sql and other resource unavailability errors will be logged to the Pega log file and a standard error message will be bubbled up as a page message on workpage.