Versions Compared

Version Old Version 1 New Version Current
Changes made by

Bryon Vaughn

Bryon Vaughn

Saved on

Key

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

Overview

In order to streamline the process of fraud management, a daily fraud report has been created and stored in a central location for sharing across the stakeholders. In addition, a process has been created to import the detected cases back into Slate for use in fraud review processes and reportingspirit 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.


SPS Admissions

Technical Details

Current Release Version: 20222023.1.0

Release Date: 


Supporting Documents

Stakeholders

Modified_Fraud_Query.sql.zip

FraudQuery_Sample.xlsx

Special Considerations

Dependencies

The existing output from the fraud query that is executed on the Slate platform and exported to the H: drive for sharing is used for the source format import file.

Feed Processing

  • The Fraud Query integration is executed on Daily at 7:15AM
    • All records in the fraud query output are processed with each run
    • Records are processed by Slate whenever a file is available
    • All files are copied to the incoming/processed folder and kept for 30 days on the SFTP server
    • Files that are processed can be viewed in the Slate sources section

Requested Features/Issues

Current Cycle

Release 2022.1.1 [December 2022]

Features to Complete

  • Not started

Features to Consider

  • Not started

Features Ready for Release

  • Not started

Previous Cycles

Release 2022.1.0 [July 2022]

  • Fraud Query (Slate)
    • Updated SQL to remove the metadata checks on the stream table
      • This was done because there were only two matches, and both were cases where the metadata in both check were matching on empty strings
      • Improved run performance and had zero impact on results
    • Added a new column to the output to capture the last update on the fraud records so it would be easy to determine what has been added since the last execution
  • Slate Configuration
    • Add and update the mapping for the Fraud Query Import source format
      • For each matching Ref, add a static tag for 'Fraud Report (Admin)'
    • Add new fields to capture Reason and Date Updated
      • Fraud Query Reasons (app-scoped)
      • Fraud Query Report Date (app-scoped)
  • Integration Server
    • Use Task Scheduler to automate the execution of all processes
      • C:/Scripts/Powershell_scripts/copy_fraud_to_sftp.ps1
        • Copies file from H: drive location back to the Slate incoming/fraud SFTP folder

Release 2021.1.0 [June 2021]

Fraud Query (Slate)
  • Revised the default Slate custom SQL fraud query so that it would run within the system time limits
  • Scheduled a daily run and saved to SFTP
    • Created script to copy the file from Slate SFTP to CU H: drive locationGranted permissions to the file share for the Admissions team

    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


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



    Image Added


    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


    Info
    titleDays 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.

     


    Image Added


    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)
     


    Image Added


    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


    Image Added

    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

    Image Added


    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

    Image Added

    Image Added

    Image Added


    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


    Image Added


    Image Added


    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.