Versions Compared

Version Old Version 7 New Version 8
Changes made by

Bryon Vaughn

Bryon Vaughn

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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: 


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
 


Image Modified

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)