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: 2022.1.0

Release Date: 29 Jul 2022

Supporting Documents

Modified_Fraud_Query.sql.zip

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.

Configuring the Fraud Query

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 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

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: Configure the Import

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
- 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
- On the Format Definition tab, add the following text to the XML section
  - <layout type="convert" h="1" />
- 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

Step 7: Remap the Source Format

Download the sample Excel file from the Supported Documents section at the top of this document
Click the Upload File(s) button on the Fraud Query Import source format page
- You should have landed on this page when you clicked Save at the end of Step 6
Click the Add Files button and select the FraudQuery_Sample.xlsx file from your system
Click the Upload button
You can either wait until the file is processed in the background by Slate, or click the

Implementing Fraud Management