Certegy Payment Solutions is a leading provider of retail check verification and Automated Clearing House (ACH) payment solutions, serving a client base of over 4,000 national and regional retailers with over 300,000 locations. Its services include risk management and return check warranty. Using its proprietary data science and risk analytics tools, Certegy helps customers reduce risk and combat fraud.
Certegy had been a Hyperion customer for nearly two decades. Running on a legacy Hyperion Interactive Reporting site, Certegy would extract its vital business data from Hyperion and import it into Microsoft Excel to meet their reporting needs. To modernize their reporting platform, Certegy migrated from Hyperion Interactive Reporting to Oracle Analytics Cloud. The move to Oracle Analytics Cloud was a game changer, offering reporting that not only met their needs but was visually appealing as well.
Certegy’s original OAC implementation was mostly built using data sets and data flows and had not taken advantage of the RPD, modeling, or subject areas. While data sets and data flows allow for a quick, simple implementation, they lack the robust feature functionality of an analytic data model and have size limitations in the amount of data they can return.
The original OAC implementation was not without its challenges; these included:
- Training. The OAC data flows were more technical than what the risk analysts were used to; they were spending 90% of their time connecting data tables with data flows. This was taking time away from the analysts’ primary missions of identifying and stopping fraudulent transaction.
- Performance. Long-running queries were impacting the database, and reports, dashboards, and lists of filter values were all taking too long to load. Complex data flows and calculations were factors in the unacceptable performance.
- User Acceptance. Changing tools and platforms is never easy, but with two decades of installed Hyperion behind them, staff expressed strong skepticism about the switch to OAC.
To get the most from its OAC installation, Certegy needed to improve the performance and the visualizations, which in turn would boost user acceptance.
The Vlamis Solution
The Vlamis solution involved building a star schema model in the OAC repository, taking advantage of the following features:
- Dimensions for hierarchical drilling and aggregation.
- Dimension tables for faster filtering and reporting on dimension attributes.
- Fact tables with only facts in them.
- Summary table for query performance on higher-level analytics.
- Calculations defined once.
- Time series functions available in the repository (RPD).
Leverage Dimension Tables to Improve Performance
Vlamis did not change the format of the Certegy reports at all, but instead changed the back end and how the data was built. Originally, the reports had two data sources that were going straight to the data tables, and all the facts and attributes were in the same table. The size of these tables caused performance issues. The transaction table, for example, had about 114 million rows. As a result, when users filtered on that table, if the data weren’t cached, it could take up to a minute to return results going against all those rows. To address this issue, Vlamis created much smaller dimension tables with attributes that perform much better when being used for filtering purposes.
Use Summary Tables and Time Series to Improve Performance, Minimize Maintenance
In addition to dimension tables, Vlamis created a summary table for reports and analyses, so instead of querying 114 million rows, they are hitting a much smaller set of data and return results much more quickly. To take advantage of OAC’s time series functions, Vlamis created a time dimension and rolling periods, improving the ability to compare data from current year and period to prior years and periods, ensuring faster performance and less maintenance.
Share Common Calculations in the RPD
Another change Vlamis made was to centralize the calculations in the RPD instead of in the My Calculations area in each project, which limited the ability to share them across the organization. Removing hard-coded dates within calculations also eliminated what would have been an ongoing maintenance issue.
The changes introduced by Vlamis provide the following benefits:
- Certegy can now easily share common calculations across the organization.
- Future maintenance costs will be reduced significantly.
- Analytics displays and filter lists return in seconds.
- DBAs will make fewer calls to the analytics team to report problems with long-running queries.
- Oracle Autonomous Data Warehouse (ADW) to store the data
- Oracle Analytics Cloud (OAC) to present the data
Possible future steps include:
- Further changes to the definition of the OAC subject area to add additional dimension attributes and centrally-defined measures.
- Working with teams to identify where Certegy can take further advantage of subject areas.
- Developing additional analyses now that Certegy has a solid data strategy.
- Tracking and blocking fraud groups using spatial analysis.