How to run the SAS® Fraud Management consortium gap recovery by transaction date


SAS Fraud Management uses the cmx_lstupdt_timestamp from transactions to generate incremental obfuscated consortium feeds with transactions and updates from only the previous day, which are sent daily to SAS for use by SAS Modeling. The cmx_lstupdt_timestamp is initially set to cmx_create_timestamp. Updates are limited to fraud tagging and untagging.  

For information about consortium processing, see the SAS Fraud Management System Administrator’s Guide. 

SAS Modeling regularly analyzes and advises users about anomalies for consortium counts and received data. However, this review is based on the original transaction dates—not the dates created or updated in SAS Fraud Management. 

When issues occur that cause consortium processing to miss data, the gap-fill recovery consortium needs to run to ensure that SAS Modeling receives sufficient data for future models and refreshes. (For example, not running consortium jobs for a day, which results in a whole day being missed, or any other issues.)  

Historically, consortium gap filling entails running consortium jobs with simple overrides to re-pull from one or more “update” days. Consortium recovery to fill gaps is easiest and most viable in close proximity to the missing consortium transactions. As each subsequent day progresses, agents and any fraud tagging jobs will likely tag and untag more fraud transactions. Then, subsequent daily consortium jobs might pick up those transactions. 

When significant consortium issues, multiple issues, or issues over time occur, or if you prefer to recover exactly an entire target rqo_tran_date day, complete the following steps to get the transaction data. 

Assumptions 

These instructions assume the following:

  • The consortium processing is already working correctly.
  • This SAS KB article uses SAS® Fraud Management version 5 internal systems for examples, so you might need to make minor user or version syntax adjustments for other versions.
  • This SAS KB article uses Oracle 12C for examples, so you might need to make minor user or version syntax adjustments.
  • This SAS KB article references the CCCA transaction type, but you can use this paradigm for all needed consortium feeds.
  • SAS Fraud Management modeling does not rely on the Boolean UTC flag setting of rqo_tran_date. However, be sure to note the possible edge effects when using UTC versus local timestamps. 

Instructions 

To run recovery jobs that extract data to fill consortium gaps by rqo_tran_date days that SAS Fraud Management modeling identifies, you need to add a database view, grant access, and execute recovery jobs; repeated for each consortium transaction type needed. 

Note: Test thoroughly, create backups before deploying, and work with SAS Modeling to validate after making any changes.  

  1. Create a view that makes the rqo_tran_date appear as the cmx_lstupdt_timestamp.
  2. Grant select access on that view to the ID that runs the consortium.
  3. Run the consortium with the added parameter to use the view. 

Create A New View 

Run a SQL to create a new view that maps the rqo_tran_date in place of the cmx_lstupdt_timestamp. 

The following example Oracle DDL for the Consortium Recovery View, CR_CCCA, is for the CCCA transaction type. The critical “cast” change is bolded in this example. Every transaction table for the consortium uses these fields. For testing, this example was executed with sysdba. 

Create view CR_CCCA as 

Select 

     RQO_SEG_ID_VERSION, 

     RQO_TRAN_DATE, 

     RQO_TRAN_TIME, 

     RQO_TRAN_UTC_FLAG, 

     RQO_PROC_UTC_DATETIME, 

     RQO_TRAN_DATE_ALT, 

     RQO_TRAN_TIME_ALT, 

     RQO_TRAN_DATE_CLIENT, 

     RQO_TRAN_TIME_CLIENT, 

     CMX_SEG_ID_VERSION, 

     CMX_TRAN_ID, 

     CMX_CLIENT_AMT, 

     CMX_MOD_AMT, 

     CMX_LSTUPDT_USER, 

     cast(rqo_tran_date as timestamp) as CMX_LSTUPDT_TIMESTAMP, 

     CMX_CREATE_USER, 

     CMX_CREATE_TIMESTAMP, 

    [all the other columns in the DDL] 

     RUX_30BYTE_STRING_001, 

     RUX_30BYTE_STRING_002 

FROM FCM_CCCA; 

Grant Select Access 

Run a SQL to grant select access to the group whose IDs you use to run the consortium. For testing, the following example was executed with sysdba. 

grant select on cr_ccca to sastdrgrp;     # Assumes sassorbch runs consortium and is in sastdrgrp 

Run with Added Parameter 

Run the consortium job as usual, except adding the ALTTABLE  parameter. For testing, the following example was run with sassorbch: 

bat_run_job.sh -j 9000 -s "TAB=CCCA ALTTABLE=CR_CCCA STARTDT=17SEP2007”