Reporting and data warehousing

Organizations often want to combine data from web applications, legacy applications, and other sources in order to make decisions in real time or near real time. To make these decisions, many organizations use business intelligence software to collect, format, and store the data, and provide software to analyze this data.

A data warehouse is a system used for reporting and data analysis. The data warehouse is a central repository of integrated data from one or more separate sources of data. The extract, transform, and load (ETL) process prepares the data for use by the data warehouse. The following conceptual image illustrates a typical end-to-end process of extracting data from systems of record and storing the data in the warehouse, then making that data available to reporting tools.

The key factor that determines whether you design your reports in the Pega application or leverage an external reporting tool is the impact on application performance. For example, if your reporting requirements state that you need to show how many assignments are in a workbasket at any given time, creating a report on the assignment workbasket table is appropriate. If you to analyze multiple years of case information to perform some type of trending analysis, use reporting tools suited for that purpose instead. You can provide a link to those reports from the end user portal in the Pega application.

Business Intelligence Exchange (BIX) allows you to extract data from your production application, and format the data to make it suitable for loading into a data warehouse. BIX is an optional add-on product consisting of a ruleset and a stand-alone Java program that can be run from a command line. BIX data from the BIX process can be formatted as XML or comma separated (CSV), or can be output directly to a database. The following diagram depicts the process of extracting the data from the Pega database and preparing the data for use by downstream reporting processes.

For more information on BIX, see the help topic Business Intelligence Exchange.

Another facet of the data management and warehousing solution is planning how and when to purge data from the production system. Over time, the work and history tables can grow significantly. In addition to making this data available for reporting from a data warehouse, create a strategy for managing the size of these tables. This strategy could include partitioning database tables or moving the data to a staging database. This strategy could also involve purging this data from the database after it has been archived in the warehouse.