Overview

In the spirit of sharing and standardization, the following document has been created to allow other schools to implement the same approach to fraud management that is in use at Columbia SPS.

Technical Details

Current Release Version: 2023.1.0

Release Date: 23 Feb 2023

Supporting Documents

Modified_Fraud_Query.sql.zip

FraudQuery_Sample.xlsx

Special Considerations

Data Feeds

There is a requirement that the exported fraud data from Slate be loaded back into the system. While there are a number of ways to make this happen, SPS is using SFTP to transfer the data to and from an external file share. This is necessary for some external processes that require access to the exported data outside of Slate. If that is something needed by other schools, we can share the details of how the files are transferred as part of the data pipeline; however, for managing the fraud cases completely within Slate, this is not a requirement, and the steps below cover a fully self-contained approach.

Configuring the Fraud Automation

Step 1: Create the Query

Create a New Query and call it something like "Modified Fraud Query"
Ensure that the Type = Local, and the Base = Custom SQL
SPS defines the user as a Service Account to separate it from the general Slate users, but this is not required

Notes on Custom SQL

While it is true that Custom SQL is unsupported, the basis of this particular query is the standard Fraud Query provided by Technolutions with some minor modifications

Improved performance so it won't fail with large data sets
Removal of the metadata checks in binary file streams, which were not producing any results with SPS application scenarios
Additional output fields, including the date the fraud was last detected
Refactored to allow it to run without triggering the SQL blacklist, bypassing the need to submit a ticket to Technolutions Support to enable

Step 2: Edit the Query

After clicking Save in the create query step, you will be presented with the Edit Query page
Download and unzip the file linked in the Supporting Documents section at the top of this document
Open the SQL file and copy and paste the full text into the Custom SQL section of the edit form
Click the Parse button to confirm that the SQL has been properly pasted
- If everything is OK, you will see the message "Successfully parsed"
- If you see an error message, make sure that you have pasted the full SQL from the downloaded file
Click the Save button to store the SQL in Slate
- This is an easy step to miss, since all other types of queries are saved automatically as you edit them

Days of History to Retrieve

To reduce the time it takes to run this query, a parameter to specify the number of days to look back for fraud in the application references is included in the custom SQL. By default, it looks back seven days, and if you are uploading the data back to Slate that should be plenty once you have your history loaded.

If you want to pull a larger set of fraud data, such as during the first import to get historic fraud cases, change the number in the 'DECLARE @days' line at the top to the number of days of history you need.

Step 3: Schedule the Export

Click the Schedule Export button in the Edit Query form
Set the Status = Active, and the destination = Technolutions SFTP
Set the path to ../incoming/fraud/FraudQuery_%FT%T
- Note: The "../" at the beginning is important to ensure the file is stored in the incoming folder instead of the default outgoing folder
Configure the rest of the form as needed by your organization
- The settings in the screen capture are how SPS has the export configured
- We run the feed nightly in the overnight window and send any failure messages to a central email that opens a support ticket
- The output we use is Excel, which is relevant for the import back into Slate (more on this during the import steps)

Step 4: Configure the Fields

Click the Fields link in the Database section of Slate
FIELD: Fraud Query Reasons
- Click the New Field button and create a field with the following settings
  - Status = Active, Scope Category = Records, Scope = Applications
  - ID = app_fraud_query_reasons
  - Name = Fraud Query Reasons
  - Field Type = Free Text
  - Unique for Merging = Do not use value for merging
- All other settings can retain defaults
- Click the Save button to store the new field in Slate
FIELD: Fraud Query Report Date
- Click the New Field button and create a field with the following settings
  - Status = Active, Scope Category = Records, Scope = Applications
  - ID = app_fraud_report_date
  - Name = Fraud Query Report Date
  - Field Type = Free Text
  - Unique for Merging = Do not use value for merging
  - On the Advanced Settings tab, change the Data Type value to Date
- All other settings can retain defaults
- Click the Save button to store the new field in Slate

Step 5: Configure the Tag

Click the Tags link in the Database section of Slate
TAG: Fraud Report (Admin)
- Click the Insert link and create a tag with the following settings
  - Status = Active
  - ID = fraud_report_tag
  - Name = Fraud Report (Admin)
  - Scope = Person
  - Show in Reader = Inactive
  - Custom Read/Write Permission = Administrator (All Access)
  - Rules Only = Administrative Updates Allowed
- All other settings can retain defaults
- Click the Save button to store the new field in Slate
Refresh the Slate cache by clicking the link in the text section near the top of the Tags page when complete to make the new configuration available in subsequent steps

Step 6: Import the Sample Record

Download the sample Excel file from the Supported Documents section at the top of this document
Click the Sources / Upload Dataset link in the Database section of Slate
Click the Upload Dataset button
- From the Upload Dataset choose the following settings
  - Select File Format = New Spreadsheet/Data File
  - File Type = Excel Spreadsheet
  - Destination Scope = Person/Dataset Record
  - Record Type = Persons/Applications
  - Check all boxes for Unsafe, Update Only, and Hide
  - Drag the downloaded test file into the Upload section
  - Click the Upload button
On the resulting page, click the Build Import button
Once the file has been sent to Slate, a Field Mappings page will appear
- For the following fields, click the linked value in the left column labeled Source Field, and set the destination to the designated value
  - "id" → Application Fields: App: Slate GUID Matching Only
  - "Ref" → Record: Slate ID Matching Only
  - "Reasons" → Application Fields: App: Other - Fraud Query Reasons
  - "Updated" → Application Fields: App: Other - Fraud Query Report Date
- Click the Static Mappings link at the right side of the page
- Click the New Static Mapping link
  - Select the following values in the new static mapping
    - Destination = Tags, Tag: Fraud Report (Admin)
    - Value = Set
  - Click Save to store the mapping value
- Click the Review & Run Import link at the right side of the page
- Click the Run Import button
- Note: Because the IDs in the sample file will not map to any records in your instance of Slate, no data will actually be imported or changed

Step 7: Create the Source Format

On the resulting page after Step 6, click the Generate Source Format button
Click the Source Formats link in the Database section of Slate
Click the Insert link and create a tag with the following settings
- Status = Active
- Name = Fraud Query Import
- Format = Excel
- Remap As Of Date = 2023-01-01 (or current date)
- Remap Active = Active
- On the Import Automation tab set Import Path/Mask to the following
  - fraud/FraudQuery_*.xlsx
All other settings can retain defaults
Click the Save button to store the new source format in Slate

Conclusions

At this stage, you have created an automated nightly export of the fraud query that will place a file with the latest results into the '/incoming/fraud" folder. You have also configured a source format that will watch for a new file in that folder, and when it arrives, will import the data from that file for each matching application. All applications that are flagged as fraud by the query will have the Fraud Report (Admin) tag set, along with the reasons for detection, and the date of discovery stored in custom application fields.

You can now update your processes to utilize a combination of the tag and/or the fields to handle these cases as appropriate.

SPS-AIS

Implementing Fraud Management

Analytics