This SAS KB article explores some known behaviors and tuning recommendations for the SAS Fraud Management MEH, SOR, and TDR databases, along with some database vendor-specific notes. This article is generalized to help SAS Fraud Management and database experts discuss performance points, deferring to their expertise and to official SAS Fraud Management Guides. Note that it might also be helpful to review this article with SAS^® Professional Services during project engagements.

SAS Fraud Management can use Oracle, DB2, or PostgreSQL for database management. However, SAS is not a database vendor, so SAS defers to and relies on customer expert DBAs, their database vendor, and consulting resources for ultimate database support.

Note: See the Appendix – Glossary of Terms regarding mnemonic abbreviations.

General Recommendations for the SAS Fraud Management Databases

All three SAS Fraud Management Databases (MEH, SOR, and TDR) benefit from using common best practices for IT and databases.

Many SAS Fraud Management customers are large banks and financial institutions. So, performance is paramount to meet the need to successfully process real-time fraud scoring and ARD decisions for hundreds to thousands of transactions per second 24x7x365.

Note that the information below is general. This section addresses common issues and challenges. Optimal settings might vary across the MEH, SOR, and TDR databases. Subsequent sections of this article detail best practices that are applicable to each of the MEH, SOR, and TDR databases.

Recommendations

Use storage with the highest speed, the highest capacity, the lowest latency, and the highest bandwidth or throughput devices and configurations. This storage is important not only for the table spaces but also for database logs.
Use the largest block size to best facilitate IO for typically long SAS Fraud Management row lengths in the fewest physical IOs, ideally using a 32kb block size. Using 8k or 16k block size for many of the tables—especially the transaction tables—severely hinders performance.
Rely on DBA and database vendor experts to configure, monitor, and tune database settings like caches, buffers, and memory to optimize workloads.
- For example, with Oracle, it is common to need to expand PGA and SGA allocations.
- Each customer deployment contains significant unique features that can change its performance profile, including entities chosen, custom user variables, custom transaction types, custom alert types, sustained and peak volumes, mixes of transactions, and more.
If you consider using database compression and/or encryption, then performance test thoroughly.
- Device-level compression and/or encryption, especially for SSD, might speed up IO, in addition to reducing storage needs.
Run statistics regularly to ensure that your database query optimizers contain the most complete and current information to select the fastest data paths.

Best Practices

Establish a baseline for normal operation as part of post-implementation validation and acceptance.
- This baseline is essential to ensure the fastest root-cause analysis of subsequent issues; to enable proactive problem-solving via monitoring to identify performance trends over time to predict issues early enough to prevent them; to support justification for changes to facilitate ongoing administration; to optimize capacity planning; and to avoid alert fatigue through better understanding of “normal” behavior.
Establish a formal process to periodically review and update the baseline for normal operation, tracking changes as they evolve over time.
- This process provides a strong foundation to establish monitoring with thresholds to ensure early problem detection with minimal false-positive alerts.
- This process also provides a reliable basis for systems capacity planning.

Special Considerations

External access to SAS Fraud Management databases should be kept to a minimum.
Utilization of SAS Fraud Management IVR SOAP and REST automation facilities should be thoroughly tested and performance tested to ensure that they do not overwhelm systems.
- These tools are designed to support behavior emulating live agents. They support higher volumes and velocities, but unthrottled, they can cause a variety of issues.
The more user variable segments (for example, customer, account, card, device, or others), the more IOs, related work, and overhead are needed for every related transaction, and the longer the UOW takes to complete or commit.
- OOTB, SAS Fraud Management supports up to eight segments.
  - Segments on DB2 can manage 24kb of data.
  - Segments on Oracle can manage 22kb of data.
SAS Fraud Management implementations tend not to schedule running pruning jobs at implementation time. Over time, it will be necessary to run pruning jobs to remove obsolete data.
- Unchecked growth of tables is a common root cause of sometimes significant performance issues for customers.

About the MEH Database

The MEH database is a relatively denormalized OLTP type of database that serves SAS Fraud Management ODEs. It contains evolving model entity behavior for SAS and customer model signatures in Z## tables and user variable supplements in V## tables. The MEH database is essential to the successful, timely operation of SAS Fraud Management ODE engines, which are in the critical path of users’ host or orchestration ARD authorization decisions.

Recommendations

Absolutely no external-to-SAS Fraud Management access should be allowed to the MEH database.
The MEH database is used in the critical path of real-time ARD authorization decisions, which can benefit greatly from using the fastest storage, like SSD.
Use a partitioning schema that is optimized for your usage patterns, such that your workload TPS is evenly distributed across all partitions, not disproportionately clustered accessing one or a few partitions.
Monitor periodically for any uneven skewing for access across partitions to determine whether any changes need to be made.
Customer SAS Fraud Management experts should work with customer DBA experts to assess (via performance testing) whether ODE local locking should be enabled or disabled.
- When enabled, each ODE manages its own lock lists so that each ODE sends only one entity-key signature request to the MEH at a time. Multiple ODEs can still compete for the signature row. This is a challenge for DBAs, who cannot see the full scope of real-time transaction flows to optimally tune the database.
  - This issue is made more complex and impactful when there are more entity signature fetches and updates per transaction. For example, one transaction with two SAS Model signatures, two customer model signatures, and multiple variable signatures—customer, account, card, merchant, point-of-sale, and so on. In these cases, disabling ODE local locking in favor of database managed locking might be significantly more efficient.
- When ODE local locking is disabled, all entity key signature requests will flow to the MEH as they arrive at the ODE. This process gives the DBAs visibility to the full scope of real-time transactions flows so that they can optimally tune the MEH.
Entity keys for the models and user variables should avoid low cardinality, because HVLC transaction flows, often batch-driven non-monetary transactions, can impede other higher-priority processing. This process can degrade real-time processing, cause timeouts, and potentially crash systems.
- HVLC might be relieved by deserializing same-entity keys in feeds, pacing or throttling feeds, dividing and pinning transactions to selected ODE engines, pinning transactions to database nodes, and other possibilities.

Special Considerations

- The MEH is a proprietary database, which does not have a published SAS Guide.
- Selecting and updating MEH data in the ODE is one of the longest legs of transaction scoring and rules that make ARD authorization decisions. It is essential that MEH access times be as fast as possible (ideally single digit milliseconds). As the time to work with data increases, even marginally, it can make the difference between meeting strict SLAs (like 40m) and failing moderate SLAs (like one second). As peak milliseconds and sustained tps counts increase, this time becomes ever more important.
  - DBAs need to remain aware and vigilant for upward trending select and update times.
- The MEH database contains two mechanisms for purging obsolete entity signatures.
  - One simple option is to run a regular batch job that deletes rows that have not seen a transaction in a configured period of time. This job tends to have the added benefit of removing stale signatures, which can be counterproductive—like a card user who left the country for a year, did not use the card, then returned but with different behavior. Refer to your SAS Fraud Management Administrator’s Guide for Job 7099.
  - Another option is to create a non-monetary delete-signature feed. For example, derived from A/R account or card purging. This feed can enable SAS Fraud Management to closely align with host systems of record. Refer to your SAS Fraud Management Message Specification Guide for DNX transfer and purge segments and to your implementation project’s MessageLayout*.xlsx.

About the SOR Database

The SOR database is a more normalized operational type of database that supports all of the SAS Fraud Management Web/Applications: Manager’s Workbench; SAS^® Rules Studio; and Analyst Workbench.

Recommendations

You might use limited, short non-locking external queries after thorough performance testing.
Automation using REST, SOAP, or others should be managed carefully and performance tested thoroughly.
- You should ensure that controls to start, throttle, pause, and stop automation are able to prioritize critical workloads. (For example, if you deploy unthrottled high-volume REST updates for analyst lists, they could impede case work, rule deployments, and ARD decisioning.)

Special Considerations

Most SOR tables are relatively small and might be slowly-changing over time, like tables defining strategies, queues, MCCs, users, roles, and others.
There are some more active tables, but they tend to be relatively small. (For example, supporting rules and lists.)
Critical tables support alert management, but again tend to be smaller, unless alerts are produced in high volume and retained for a long time.
Accordingly, most SOR tables do not benefit from partitioning nor compression—deferring to any unique customer SME/DBA recommendations.
From a size management perspective, the FCM_ALERT_ACTION table tends to be the largest table in the SOR, retaining rows for every event that touches every alert from alert creation through resolution. This table tends to be the one SOR table that might benefit from partitioning, deferring to customer requirements for retention and archiving or purging.
See the SAS Fraud Management Administrator’s Guide regarding SOR maintenance batch jobs for pruning SOR tables.
- Generally, preference settings guide how long data is retained for tables, and purge jobs will purge older rows with full logging, using Job 3006.
  - Job 3006 can purge TDR transaction tables, but preferences for purging TDR tables should be set to zero to prevent them from purging in this way, deferring to rolling partitions as noted in About the TDR Database.
- There is also a job to roll (delete oldest or create new) range partitions, Job 3020, for any SOR partitioned tables that might have been created.
  - The FCM_ALERT_ACTION might be a good candidate.

About the TDR Database

The TDR database is a partially denormalized DW-hybrid type of database that contains the supporting tables for transactional history and fraud tagging. It is hybrid in that it provides historical operational data to the Analyst Workstation and IVR SOAP and REST automation.

Recommendations

Limited, short non-locking external queries might be used, after thorough performance testing
TDR Transaction Tables:
- Are always appending inserts based on the datetime that the ODE processed the transactions.
- Will have very few updates, potentially changing very few column values very rarely and not affecting the row length.
  - Updates to transaction tables are limited to fraud [un-] tagging.
- Might benefit from zero to low pctfree settings, deferring to performance testing.
  - Performance testing helps determine the optimal setting.
  - Lower storage requirements.
  - Potentially better performance.
- Should be partitioned daily, barring special reasons otherwise.
  - This partitioning usually yields significant performance improvements:
    - Work that requires only information from specific days will have to work with only those days’ partitions, which is much faster.
    - Pruning can simply drop the oldest day partition each day, which is virtually instantaneous with no logging overhead. (Versus deleting by row for a time with significant and slow attendant commits and logging overhead impeding other work.)
- Might tolerate not needing repeated updates to stats after an initial daily stats update. (Because updates are few with minimal to no row length changes.)
- Might be a valuable place to use database compression, if resources and performance testing support it, since they are usually highly compressible, even to 80% or more.
For every transaction type that can generate an alert type, you must create an index for that transaction type and alert type in order to optimize performance for operational uses, like fetching transactions to display in Analyst Workstation transaction grids.
- Creation of optimal indexes is usually completed with the implementation project, but it is critical to remember to do this every time that you add a transaction type or add an alert type.
- Name the indexes as follows: CQF_<alert_type_value>_<table_name>
- For example, if you create an alert type M, which can be alerted from CSBF transactions, you should create an index named CQF_M_CSBF

CREATE INDEX IF NOT EXISTS <schema>cqf_m_csbf
ON <schema>.fcm_csbf USING btree (cqf_entity_value_m ASC NULLS LAST, smh_multi_org_id ASC NULLS LAST, smh_acct_type ASC NULLS LAST, smh_activity_type ASC NULLS LAST, rqo_proc_utc_datetime ASC NULLS LAST) TABLESPACE <index tablespace name>;

Special Considerations

TDR tables might be accessed for some guided rules or TAS purposes, which can create a significant workload, so thoroughly performance test these facilities to ensure that sufficient resources are available when deploying to production.
See the SAS Fraud Management Administrator’s Guide regarding TDR maintenance batch jobs for pruning TDR tables
- The TDR transaction tables, with their daily partitions, can use roll range partition Job 3021 to simply drop the oldest partition and create a new one, if needed, so there is no need for extensive row-by-row deletion and logging overhead.
  - DB2 roll deletes the oldest partition and creates a new partition.
  - Oracle deletes the oldest partition, but new partitions are created automatically to accommodate new data.

DBA Expectations

DBA experts might feel constrained by SAS Fraud Management’s OOTB architecture, so this section clarifies some general expectations to reinforce how critical DBA experts are to keep SAS Fraud Management optimally running and performant.

Deferring to your policies, procedures, preferences, and experts, here are some important points to consider.

SAS Fraud Management

Whenever any questions arise relative to your SAS Fraud Management deployment or version, please create a case for SAS Fraud Management Technical Support.
Always keep current with SAS and SAS Fraud Management software, since ongoing versions and hot fixes continually improve build quality, performance, security, and more. Changes that affect databases are rare, but they do occur. This best practice will keep your software as current as possible with evolving open-source and foundational components, which will be important to maintaining the strongest security position.
Do not make unilateral updates to third-party software, like Oracle drivers, without checking with SAS or SAS Fraud Management Support regarding compatibility.
- See SAS KB0036241, "SAS^® Fraud Management third-party support" for currently supported third-party software."
- SAS Fraud Management is built and tested with specific versions. Other versions might encounter issues and might need to be rolled back.
- Generally, dot-level updates (like 3.1 to 3.4) are okay but still require thorough testing, and they still might need to get rolled back if issues arise.
Expectations for customer DBAs go beyond just keeping the database running. Customer DBAs are the primary owners for monitoring database performance, recommending tuning improvements, and inquiring or escalating to SAS or SAS Fraud Management any observations and concerns, along with any recommended tuning changes.