Do You Provide Files to Vendors?

If the school district does, then you will want to learn about ETL

What is ETL?

ETL stands for Extract, Transform and Load. It is the data process in which data is exported to an ETL program, changed in some way, and then pushed to a new location, such as a local or remote database table.

The process can be accomplished by a series of scripts that require expertise to write, attention to see if they are executing correctly. In my work at Data Smart LLC, I no longer write scripts to transform data for uploading to our Oracle database. Instead, I use an ETL program that automates the process.

While I have used a few ETL programs, however, I found an ETL program that I really like and recommend. The program is EasyMorph   You really need to check out their website and watch the video demonstration. Now here is the great news about this program.  There is a free version so you can get the feel of the program and I use it for most of my ETL work. The professional version is about $750 per year. 

Here is a typical process that I create (in about 30 minutes):

On a pre-defined schedule that I set up in the program, the process is run.

  1. Set up a scheduled export from a source like Power School so the file is saved on your computer.
  2. EasyMorph will Import the file(s) from my desktop or a database source. (EXTRACT)
  3. Then the program will change the column headings, data type if I need number instead of text, remove unwanted characters such as “N/A”, and change the file from a .txt or tab-delimited to a .csv separated file type and save it. (TRANSFORM) This transforming process is accomplished by selecting pre-programmed actions which are arranged in a timeline sequence.
  4. The program then moves on to the process step of uploading the data file to the destination. (LOAD) The destination can be back to your desktop for attaching to an email, a local database or a remote hosted database table.
  • The real power comes from the ability to execute SQL queries on the data table BEFORE uploading the data. So if you are replacing data daily to a remote server, you can either on the front end merge your previous data file and select only new data, or run a TRUNCATE statement to remove all data and start fresh or run an UPDATE statement to add data if the data is changed.
  1. Lastly, I get an email from the program telling me that the process ran and a log of errors if there were any errors.

This is by far the best solution to getting your data from point A to point B in a format that meets the vendor’s specifications. No this is what I call be data smart.

Dr. Lew Johnson
Oracle APEX Developer
Data Smart LLC

Mid-Year Data Tasks

As you complete the mid-year testing in your district here are some “think abouts”:

You could –

  • Join a table or EVAAS Projected percentiles converted to NCE scores with the percentile scores (converted to NCE) and compute the difference to report growth.
  • Join the above growth file with Check-In scores in Math 1 and identify possible skill weaknesses of students and their corresponding teachers.
  • Create a file for each spring semester EOC teacher with each student’s previous test scores, and growth performance which will save them countless hours of looking up these students in EVAAS.
  • Create a file with previous student scores and join the Check-In test 1 with the Check-in test 2 so that teachers can get a good picture of student overall performance and identify students who are at-risk.    

All of this data work can be done manually using MS Access, but then sharing the data still could mean creating and sending exports.

Instead, with a data system hosted by Data Smart LLC in Greensboro, NC all that is required is uploading a few files from Winscan and the work is done for you.

Distribution is handled by secure logins by your administrators and they have access to summary reports, analysis reports, and teacher class rosters.      

If you are interested in hearing more, please contact me by email to schedule a no-cost initial consultation and demonstration of the system.   

Dr. Lewis Johnson
Data Analysis
Data Smart LLC