Importing FAA "SDR" Data to CAWIS

System Profile

 SDR refers to Service Difficulty Report

Open

Overview

In the 1980's, the TC WSDRS subsystem (of CAWIS) was a standalone application, copied directly from the SDR system run by the FAA. Since that time several data formats have been altered on the TC end by management request, while the FAA data formats have stayed relatively uniform.

To perform an FAA data import, we download a series of Zipped Text files from the FAA website, load a portion of this data into a CAWIS temp table (M70_SDR_TRANSIT) and perform analysis in terms of :

1. deleting the Temp records that we already have

2. reviewing & converting every column of incoming data on the remaining records (where necessary) to ensure it doesn't raise an exception on upload to the production CAWIS/WSDRS tables (M72, M73, M74 etc)

The Goal of this task is to download as much data as possible from the FAA, in a timely manner. The entire TC SDR database (approx 2 million reports) is pulled into BII (Business Intelligence https://tcbi.tc.gc.ca/ ) once per month in order to provide statistical/trend analysis reports for Continuing Airworthiness. It is also important that we give Canadian type-approved manufacturers, access to all possible potential issues with their products.

It is assumed that the reader has a functional knowledge of, and access to the CAWIS application
(Continuing Airworthiness Information System)

The TC/FAA data exchange agreement is covered under the “Implementation procedures for Airworthiness”, found here under Section IV - Continuing Airworthiness :

 International Agreements and implementation procedures

CAWIS/WSDRS - Process Bi-Weekly SDR (Service Difficulty Report) data imported from United States FAA (Federal Aviation Administration) site

Tools needed to complete the task:

TOAD or SQL Developer installed on the developer’s machine (with connect strings to CAWIS PROD: Go to the PINS Password Manager to get the connection info for user CAWISP in the “CAWIS” section of this file.)

SQL Developer is available at :
\\Tc4s0a\groups\AARA\AARAD\Software Library\Developer Tools\Oracle SQL Developer

Obtaining and preparing the FAA Data file:

• verify that the FAA download site is up & running
  https://av-info.faa.gov/dd_sublevel.asp?Folder=%5C%5CSDRS

• Ensure that your machine is able to perform the following basic Windows 10 functions in a batch format:

  • bitsadmin

  • unzip

  • powershell

  If not, please find a team member whose machine can support those functions.
  As a last resort, these tasks will have to be performed manually: Manual preparation of WSDRS-FAA data

  • download zip files for the past five years

  • unzip

  • concatenate

  • perform character replacement

• create a directory on your local drive “c:\data\faa\”

• save the file to c:\data\faa\

• rename FAA-PROCESS.TXT to FAA-PROCESS.BAT

• in a command prompt, run the FAA-PROCESS.BAT file, (it may take up to 30 mins to run). It will:

  • download and unzip the zip files

    • Each FAA data file is suffixed with either
      'a' - Commercial Aviation - or -
      'g' - General Aviation

  • concatenate the text files to produce DATA.TXT

  • delete all the unneeded ZIP and TXT files

  • perform the required Character replacements on DATA.TXT

  • open the output file named “data.txt” in Excel

    • If DATA.TXT is empty you may need to prepare the file manually
      (Manual preparation of WSDRS-FAA data ). The DATA.TXT is about 250mb as long as all the steps to create it worked properly.

FAA SDR DATA IMPORT PROCEDURE

Prepare the insert script for importing the data from DATA.TXT to M70_SDR_TRANSIT:

1. Before starting, make a backup of “DATA.TXT” to save time if you have to start over.
   For example, create a _backup folder a level up from FAA.

2. Shift the data to Column B by inserting an empty column in the excel worksheet.
   
   

   Open

   

    

3. Highlight the top data cell in column B.

4. Sort Column B descending (Z to A)
   
   

   
   
   

Determine when data was last uploaded into CAWIS, then scroll down to the beginning
of that month ( Entry date is the left-most value seen in the FAA data ).

Compare this date with the earliest and last dates in the ENTRY_DATE that you noted from the queries you ran before deleting the data from the table M70_SDR_TRANSIT.

Verification Check

• there should be multiple rows of headers from concatenating the text files, in the top rows.

  • the number matches the number of ZIP files downloaded.

  • delete those rows.

The newest SDR data will now be in the top rows and will look similar to the screen shot below. From this you can determine how much data to import into the M70_SDR_Transit table for analysis. Much of this data we already have, as new reports can be found anywhere in the batch. Duplicate data is removed by the scripts that process the data.

Paste in a repetitive character

[ into Column 'A' =
] and Column 'C'

for the 5,000 to 10,000 rows - this count will vary - corresponding to the data you determined has to be imported.

The [ and ] characters will be replaced with the text for creating the insert statement.

Delete the rows for any data that will not be imported.

DATA.TXT should now look similar to the screenshot of the first few rows.

Save the file as "Data2.txt" (make sure it is saved as a tab delimited text file.

Preparing the Insert Script

Copy the content of Data2.txt to a text editor

Replace [ with :

Replace ] with :

Verification:

Paste one insert statement into the editor tab of a tool that can run SQL statements, such as an editor tab in TOAD.
Do NOT run the script.
If the there is no formatting error in that statement:

• clear that one statement from the editor window.

• copy ALL of the insert statements from Excel into the editor tab of your SQL tool.

• check that no lines of the sql report an error.

• save the file with a .sql extension.

Perform the Inserts, and Parse the Data

• If it isn’t already open, open the insert script you saved in your database editor tool (TOAD, SQL Developer, etc.).

• Make sure the cursor is at the very top of the file (it looks like the insert statements are run starting from the point of where the cursor is positioned.

• Run the script.

• The data will be inserted to M70, the data CAWIS already has will then be deleted from m70
  and finally, the data will be moved, field by field over into its proper columns.

• Verify that the number of rows inserted into M70_SDR_TRANSIT matches the number of rows identified for insert and converted to insert statements:

•  

• Don’t forget to COMMIT the data.

Sample insert script from July 28, 2023

Open

Processing the data in M70_SDR_TRANSIT

Processing the data is done in three parts. Visual checks of the data are performed after parts 1 and 2. After part 3, the process is verified by logging into CAWIS production and comparing some of the SDRs that were imported with the FAA SDR data.

Part 1: Initial processing of the data

The script FAA-SDR-DATA_IMPORT-Processing-Part-1.sql:

• sets defaults on some fields.

• removes the last 25 characters of the “SDR_TEXT” in M70_SDR_TRANSIT.

• Parses the data to the correct columns in M70_SDR_TRANSIT.

  • The layout of the FAA data is provided here: https://av-info.faa.gov/data/SDRS/Filelayout.txt.

Download . Open it in your sql editor with a working connection string for CAWISP (TOAD, SQL Server, etc) and run it (if using TOAD select “run as script”).

Verification of the table M70_SDR_TRANSIT after part 1 of the script was run

Check that:

1. the data has been inserted into the correct columns.

2. date based sequential values were assigned in the column SDR_NUMBER.

3. the PART_TIME and PART_TOTAL_TIME columns should not contain numbers that are in the billions.

   • The following queries can be used to help verify that these columns were numbers are in the billions:

4. Check that the SDR_TEXT column is not null using this query:

Part 2: converting the data to CAWIS standards

Download . Open it in your sql editor with a working connection string for CAWISP (TOAD, SQL Server, etc) and run it (if using TOAD select “run as script”).

Verification of the table M70_SDR_TRANSIT after part 2 of the script was run:

1. check that the column SDR_NUMBER continues to be sequential.

2. the PART_TIME and PART_TOTAL_TIME columns should not contain numbers that are in the billions.

3. the SDR_TXT column is not NULL.

   • The queries provided in part 1 can be used to re-verify the:

     • PART_TIME

     • PART_TOTAL_TIM

     • SDR_TXT columns.

4. Verify that the date range in the table using the queries for min and max dates for the ENTRY_DTE column - refer to the section at the very beginning of this document for the queries.

Part 3: Moving the temporary data to the production tables.

This is the final part of the process. FAA-SDR-DATA_IMPORT-Processing-Part-3.sql moves the temporary data that was inserted and processed in M70_SDR_TRANSIT to the following production tables:

• M72_SDR

• M73_SDR_PRODUCT_MAKE_MODEL

• M74_SDR_JASC

• M77_SDR_NATURE

• M78_SDR_ACTION_TAKEN

It also ensures that an equipment record exists for every SDR.

Download Open it in your sql editor with a working connection string for CAWISP (TOAD, SQL Server, etc) and run it (if using TOAD select “run as script”).

VERIFICATION OF DATA ON COMPLETION OF PROCESSING

After the three scripts to process the imported data were successfully run:

Log into CAWIS production : CAWIS / SWIMN production using the TC Login option.

Select Reporting : Web Service Difficulty Reporting System (WSDRS) from the left-hand menu:

Select Quick Search from the sub-menu:

Copy any SDR number from the SDR_NUMBER_NUM column in M70_SDR_TRANSIT and paste it into the SDR number text box of the WSDRS - Quick Search form.

Press the Search button.

Click the link for the SDR number in the result set to view the full report.

Compare with the data at this site:  https://sdrs.faa.gov/Query.aspx

To search the Federal Aviation Administration site for the equivalent WSDRS report:

Copy the Submitter File Number from the SDR you are checking, for example, AALA202307216954. The

Paste it into the Operator Control # text box of the FAA site and then press Enter to search

Click the Unique Control link for the returned report. Compare key values, between the WSDRS report in CAWIS and FAA, including the Air craft information and Problem Description. The expected result is for the data between the two reports to be similar.

• Compare multiple SDR reports between CAWIS and the FAA site in this way.

  • See also: Subtask 282900 Verification of the data on completion of processing the excel file

• If the data does not correspond with the import revert the data using the script provided below.

Reverting the Data

Backup your files work to the shared server location:

zip your DATA directory and copy it to the shared server at
\\Tc4s0a\groups\AARA\AARAD\CAWIS\Developer_Documents\data backups

• create a subfolder with the devops task number

  • Example:
    \\Tc4s0a\groups\AARA\AARAD\CAWIS\Developer_Documents\data backups\277459

• Such a backup should be made on completion of the work.

  • If you could not complete the task by end of the day it is still valuable to backup the file to the shared location.

  • The location can be recorded in a subtask linked to the main DevOp tasks.

    • Example: 282924 Backups of table M70_SDR_TRANSIT and DATA.txt
      
      

Screenshots of directory structure/content of backup.

Previous Process for importing WSDRS FAA import data

Original WSDRS FAA Data Import Instructions

Related articles