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:
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
- Click the New Field button and create a field with the following settings
- 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
- Click the New Field button and create a field with the following settings
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
- Click the Insert link and create a tag with the following settings
- 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
- From the Upload Dataset choose the following settings
- 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
- Select the following values in the new static mapping
- 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
- For the following fields, click the linked value in the left column labeled Source Field, and set the destination to the designated value
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.
Add Comment