When Pega app builders are designing and configuring new applications to streamline a business process, there is often a need to bring information from multiple systems together into a single screen for ease of viewing and interacting with the data. This is often referred to as presenting a "single pane of glass”, "composite view," or "customer 360". As part of the application creation process there is also usually a need to create common data table objects in the Pega platform instance to store and access the various common application data types.
Common data types might be Pega- related app settings, customer info, product codes, order data, and more. Creating internal application data types are perfect for creating and storing data as part of the Pega application database if the data is specific just to the application.
But what if you need real-time external data from outside the Pega application database?
Imagine you’re working on a new Pega Low Code application and find yourself in a situation where the application needs data from an external system of record such as an old CRM system, customer service application, an ERP system or other custom app. For our purposes let’s say that older system can only be accessed by automating screen interactions or via direct database calls. And the older apps don’t already have any web services available, so using REST calls are currently not an option. This leaves us with the choice of either using Robotics to interact with the application or directly connecting to the external database to read or update data without the need to replicate information. When given the choice I would choose the direct external database connection.
We see scenarios where data in an external database will continue to live on another database platform such as SQL Server, Oracle, MySQL, Postgres and more. We don't want to replace that database. We want to use the data in place where it lives.
How do we integrate with external data sources when some of the application data lives in another database such as SQL Server? Or maybe the data lives on a database in another data center such as Amazon, Google, or Azure cloud. How do we use the data from the cloud? Surely, you don't want to replicate the data into your Pega instance for your new applications. Fortunately, Pega provides several ways to access your data where it lives without replication by utilizing the Pega Live Data mechanism.
Accessing Live Linked SQL Server Tables and Other Data
If you're running a Pega instance and you need to access remote data from SQL Server using Pega Live Data, there are a few ways to accomplish the task:
The most common way to access external data in today's world is probably to surface the database information by creating an external web service API that allows a Pega app to connect to the external data by mapping a web service call to the Pega application using the Pega Live Data mechanism. Live data allows you to configure a remote connection in a Pega application, so the application developer doesn't need to know all the details of that connection. They just utilize the live data source as they would any other local data object or table in Pega by utilizing calls to data pages and data transforms, which are the objects used to make the actual API or database call. Once you’ve completed the web service import configuration, your app can make an external web service call to access the desired data. It will feel as if the web service data is local data because you can access it in real-time, as needed, just like a local Pega data type table object. (We will cover how to set up a web service API link in Pega in a future post.)
In a scenario where there isn't a web service data access API available, your Pega application can instead map to external data via live data by directly linking to a database table using the appropriate JDBC driver for your database. This would include Postgres, SQL Server, Oracle, MySQL, MariaDB and just about any database that supports JDBC.
To do this, you would simply configure a linked table connection in Pega. And once you’ve made that linked table connection your app can access the external table via live data by calling a data page or data transform object as we described above, which maps to the actual external database linked table. Once again, the Pega live data layer hides the database connection information and where the database lives, allowing the app developer to simply focus on developing the application and using the Pega application data types, without worrying about how the data is physically hooked up to the Pega application and where it lives. App developers can continue to stay focused on defining meaningful application journeys and providing successful outcomes regardless of where data lives.